ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDF3079D2E0530100007F1FF2 AP Invoice Number Y Y select aia.invoice_num value, aps.vendor_name||' ('||aia.invoice_date||')' description from ap_invoices_all aia, ap_suppliers aps where (:$flex$.operating_unit is null or aia.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or aia.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and aia.vendor_id=aps.vendor_id order by aia.invoice_date desc US B20C1D394FC36EDDE0530100007F5621 AP Invoice Type Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type(+)='INVOICE TYPE' and flvv.view_application_id(+)=200 and flvv.security_group_id(+)=0 order by flvv.meaning US B9847D20A0E4742FE0538931640A6379 AP Supplier Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id US 8E2FF36EDF0279D2E0530100007F1FF2 AP Supplier Site Y select distinct assa.vendor_site_code value, assa.address_line1||', '||assa.city||', '||nvl(ftv.territory_short_name,assa.country) description from hr_all_organization_units_vl haouv, ap_suppliers aps, ap_supplier_sites_all assa, fnd_territories_vl ftv where (:$flex$.ap_supplier is null or xxen_util.contains(:$flex$.ap_supplier,aps.vendor_name)='Y') and (:$flex$.ap_supplier_number is null or xxen_util.contains(:$flex$.ap_supplier_number,aps.segment1)='Y') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and haouv.organization_id=assa.org_id and aps.vendor_id=assa.vendor_id and assa.country=ftv.territory_code(+) order by assa.vendor_site_code US 8E2FF36EDEB879D2E0530100007F1FF2 GL Ledger Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='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(+)) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name AR دفاتر الأستاذ المقيدة بمجموعة الوصول ، المحددة بواسطة قيمة الملف الشخصي "مجموعة الوصول إلى بيانات GL" لمسؤولية تسجيل الدخول الحالية D Ledger, die durch das Zugriffsset eingeschränkt sind, definiert durch den Profilwert 'GL Data Access Set' der aktuellen Anmeldeverantwortung E Libros restringidos por el conjunto de acceso, definido por el valor del perfil "Conjunto de acceso a datos GL" de la responsabilidad de inicio de sesión actual F Grands livres limités par un ensemble d'accès, défini par la valeur du profil "GL Data Access Set" de la responsabilité de connexion actuelle I Libri mastri limitati dal set di accesso, definito dal valore del profilo 'GL Data Access Set' della responsabilità di login corrente JA 現在のログイン責任者のプロファイル値'GL Data Access Set'によって定義されたアクセスセットによって制限された元帳 KO 현재 로그인 권한의 프로파일 값 'GL 데이터 액세스 세트'로 정의 된 액세스 세트로 제한되는 원장 PTB Ledgers restritos pelo conjunto de acesso, definido pelo valor do perfil 'GL Data Access Set' da atual responsabilidade de login RU Леджеры, ограниченные набором доступа, определяемые значением профиля "GL Data Access Set" текущей ответственности за вход в систему S Ledgers begränsade av åtkomstuppsättning, definierade av profilvärde 'GL Data Access Set' för det aktuella inloggningsansvaret TR Mevcut oturum açma sorumluluğunun profil değeri 'GL Veri Erişim Kümesi' ile tanımlanan erişim kümesiyle kısıtlanmış defterler US Ledgers restricted by access set, defined by profile value 'GL Data Access Set' of the current login responsibility ZHS 由访问集限制的分类账，由当前登录责任的配置文件值 "GL数据访问集 "定义。 8E2FF36EDEB979D2E0530100007F1FF2 HR Operating Unit Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name Fixed where clause duplication AR وحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC) D Bedieneinheit eingeschränkt durch Multi Org Access Control (MOAC) E Unidad operativa restringida por el control de acceso multiorgánico (MOAC) F Unité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC) I Unità operativa limitata dal controllo di accesso multiorgano (MOAC) JA 多機関アクセス制御(MOAC)で制限された操作ユニット KO 다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위 PTB Unidade operacional restrita por controle de acesso multi org (MOAC) RU Операционное устройство, ограниченное мультиорганизованным контролем доступа (MOAC) S Styrenhet begränsad av multi-org access control (MOAC) TR Çoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimi US Operating unit restricted by multi org access control (MOAC) ZHS 受多机构访问控制（MOAC）限制的操作单元。 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US B5F889C2A60B0296E0530100007FC863 select inv.ledger, inv.ledger_currency, inv.operating_unit, inv.supplier, inv.supplier_number, inv.supplier_site, inv.invoice_num, inv.invoice_source, inv.invoice_type, inv.invoice_status, inv.invoice_date, inv.invoice_date_period, inv.invoice_creation_date, inv.invoice_creation_period, inv.invoice_creation_delay, inv.invoice_gl_date, inv.invoice_description, inv.invoice_terms, inv.invoice_terms_date, inv.invoice_pay_group, inv.invoice_exclusive_payment, -- invoice amounts inv.invoice_currency_code, decode(inv.first_invoice,'Y',inv.invoice_amount) invoice_amount, decode(inv.first_invoice,'Y',inv.approved_amount) approved_amount, decode(inv.first_invoice,'Y',inv.amount_applicable_to_discount) amount_applicable_to_discount, decode(inv.first_invoice,'Y',inv.tax_amount) tax_amount, inv.payment_currency_code, decode(inv.first_invoice,'Y',inv.pay_curr_invoice_amount) pay_curr_invoice_amount, decode(inv.first_invoice,'Y',inv.invoice_amount_paid) invoice_amount_paid, decode(inv.first_invoice,'Y',inv.discount_amount_taken) discount_amount_taken, -- accounted amounts decode(inv.first_invoice,'Y',inv.invoice_amount_acctd) invoice_amount_acctd, decode(inv.first_invoice,'Y',inv.approved_amount_acctd) approved_amount_acctd, decode(inv.first_invoice,'Y',inv.amount_applic_to_disc_acctd) amount_applic_to_disc_acctd, decode(inv.first_invoice,'Y',inv.tax_amount_acctd) tax_amount_acctd, decode(inv.first_invoice,'Y',inv.amount_paid_acctd) amount_paid_acctd, decode(inv.first_invoice,'Y',inv.discount_amount_taken_acctd) discount_amount_taken_acctd, inv.exchange_rate_type, inv.exchange_date, inv.exchange_rate, inv.payment_cross_rate_type, inv.payment_cross_rate_date, inv.payment_cross_rate, -- inv.invoice_created_by, inv.invoice_on_hold, inv.invoice_has_attachment, inv.liability_account, inv.liability_account_descripton, inv.invoice_cancelled_date, inv.invoice_cancelled_amount, inv.invoice_cancelled_by, decode(inv.first_invoice,'Y',inv.invoice_temp_cancelled_amount) invoice_temp_cancelled_amount, inv.recurring_pay_num, inv.recurring_period_type, inv.recurring_number_of_periods, inv.recurring_pmt_description, -- payment_schedules inv.payment_num, inv.due_date, inv.days_due, inv.future_pay_due_date, inv.inv_payment_status, inv.sched_payment_status, inv.hold_flag, decode(inv.first_psched,'Y',inv.inv_curr_gross_amount) inv_curr_gross_amount, decode(inv.first_psched,'Y',inv.gross_amount) gross_amount, decode(inv.first_psched,'Y',inv.amount_remaining) amount_remaining, &aging_bucket_cols2 inv.payment_method, inv.payment_priority, inv.bank_name, inv.iban, inv.actual_payment_date, inv.discount_date, inv.second_discount_date, inv.third_discount_date, decode(inv.first_psched,'Y',inv.discount_amount_available) discount_amount_available, decode(inv.first_psched,'Y',inv.second_disc_amt_available) second_disc_amt_available, decode(inv.first_psched,'Y',inv.third_disc_amt_available) third_disc_amt_available, decode(inv.first_psched,'Y',inv.discount_amount_remaining) discount_amount_remaining, -- kpis inv.validated_without_holds, inv.late_po_flag, inv.late_po_number, inv.late_po_cost_centre, inv.late_po_creation_date, inv.late_po_created_by, inv.invoice_count, inv.distribution_count, -- -- &invoice_detail_columns -- -- supplier/site details inv.supplier_inactive_on, inv.taxpayer_id, inv.tax_registration_number, inv.vat_code, inv.supplier_customer_num, inv.one_time_supplier, inv.credit_status_lookup_code, inv.credit_limit, inv.withholding_status_lookup_code, inv.withholding_start_date, inv.purchasing_site, inv.rfq_site, inv.pay_site, inv.tax_reporting_site, inv.p_card_site, inv.attention_ar, inv.address_line1, inv.address_line2, inv.address_line3, inv.address_line4, inv.city, inv.state, inv.zip, inv.county, inv.province, inv.country, inv.area_code, inv.phone, inv.fax_area_code, inv.fax, inv.supplier_notif_method, inv.email_address, inv.remittance_email, -- &dff_columns2 -- inv.invoice_id from ( select gl.name ledger, gl.currency_code ledger_currency, haouv.name operating_unit, aps.vendor_name supplier, aps.segment1 supplier_number, assa.vendor_site_code supplier_site, aia.invoice_num, aia.source invoice_source, xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type, xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) invoice_status, aia.invoice_date, (select gps.period_name from gl_period_statuses gps where gps.set_of_books_id = aia.set_of_books_id and gps.application_id = 200 and gps.adjustment_period_flag = 'N' and aia.invoice_date between gps.start_date and gps.end_date ) invoice_date_period, xxen_util.client_time(aia.creation_date) invoice_creation_date, (select gps.period_name from gl_period_statuses gps where gps.set_of_books_id = aia.set_of_books_id and gps.application_id = 200 and gps.adjustment_period_flag = 'N' and aia.creation_date between gps.start_date and gps.end_date ) invoice_creation_period, (trunc(aia.creation_date)-trunc(aia.invoice_date)) invoice_creation_delay, aia.gl_date invoice_gl_date, aia.description invoice_description, at.name invoice_terms, aia.terms_date invoice_terms_date, aia.pay_group_lookup_code invoice_pay_group, xxen_util.meaning(aia.exclusive_payment_flag,'YES_NO',0) invoice_exclusive_payment, -- invoice amounts aia.invoice_currency_code, aia.invoice_amount, aia.approved_amount, aia.amount_applicable_to_discount, aia.tax_amount, aia.payment_currency_code, aia.pay_curr_invoice_amount, aia.amount_paid invoice_amount_paid, aia.discount_amount_taken, -- accounted amounts decode(aia.invoice_currency_code,gl.currency_code,aia.invoice_amount,aia.base_amount) invoice_amount_acctd, nvl(aia.approved_amount,0)*aia.exchange_rate approved_amount_acctd, nvl(aia.amount_applicable_to_discount,0)*nvl(aia.exchange_rate,1) amount_applic_to_disc_acctd, nvl(aia.tax_amount,0)*nvl(aia.exchange_rate,1) tax_amount_acctd, nvl(aia.amount_paid,0)/decode(nvl(aia.payment_cross_rate,1),0,1,aia.payment_cross_rate)*nvl(aia.exchange_rate,1) amount_paid_acctd, nvl(aia.discount_amount_taken,0)/decode(nvl(aia.payment_cross_rate,1),0,1,aia.payment_cross_rate)*nvl(aia.exchange_rate,1) discount_amount_taken_acctd, (select gdct.user_conversion_type from gl_daily_conversion_types gdct where gdct.conversion_type = aia.exchange_rate_type) exchange_rate_type, aia.exchange_date, aia.exchange_rate, (select gdct.user_conversion_type from gl_daily_conversion_types gdct where gdct.conversion_type = aia.payment_cross_rate_type) payment_cross_rate_type, aia.payment_cross_rate_date, aia.payment_cross_rate, -- xxen_util.user_name(aia.created_by) invoice_created_by, case when apsa.hold_flag = 'Y' or (assa.hold_all_payments_flag = 'Y' and aia.payment_status_flag != 'Y' and aia.cancelled_date is null) or exists (select null from ap_holds_all aha where aha.invoice_id = aia.invoice_id and aha.release_lookup_code is null ) then xxen_util.meaning('Y','YES_NO',0) end invoice_on_hold, nvl((select xxen_util.meaning('Y','YES_NO',0) from fnd_attached_documents fad, fnd_documents_vl fdv where fad.document_id = fdv.document_id and fad.entity_name = 'AP_INVOICES' and fad.pk1_value = aia.invoice_id and rownum <= 1 ), xxen_util.meaning('N','YES_NO',0) ) invoice_has_attachment, xxen_util.concatenated_segments(aia.accts_pay_code_combination_id) liability_account, xxen_util.segments_description(aia.accts_pay_code_combination_id) liability_account_descripton, aia.cancelled_date invoice_cancelled_date, aia.cancelled_amount invoice_cancelled_amount, xxen_util.user_name(aia.cancelled_by) invoice_cancelled_by, aia.temp_cancelled_amount invoice_temp_cancelled_amount, arpa.recurring_pay_num, arpa.rec_pay_period_type recurring_period_type, arpa.num_of_periods recurring_number_of_periods, arpa.description recurring_pmt_description, -- payment_schedules apsa.payment_num, apsa.due_date, ceil(sysdate-apsa.due_date) days_due, apsa.future_pay_due_date, xxen_util.meaning(aia.payment_status_flag,'INVOICE PAYMENT STATUS',200) inv_payment_status, xxen_util.meaning(apsa.payment_status_flag,'INVOICE PAYMENT STATUS',200) sched_payment_status, apsa.hold_flag, apsa.inv_curr_gross_amount, apsa.gross_amount, apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) amount_remaining, &aging_bucket_cols1 nvl(xxen_util.meaning(apsa.payment_method_lookup_code,'PAYMENT METHOD',200),apsa.payment_method_lookup_code) payment_method, apsa.payment_priority, abb.bank_name, aba.iban_number iban, (select max(aca.check_date) from ap_invoice_payments_all aipa, ap_checks_all aca where apsa.invoice_id=aipa.invoice_id and apsa.payment_num=aipa.payment_num and aipa.check_id=aca.check_id and aca.void_date is null and aca.stopped_date is null ) actual_payment_date, apsa.discount_date, apsa.second_discount_date, apsa.third_discount_date, apsa.discount_amount_available, apsa.second_disc_amt_available, apsa.third_disc_amt_available, apsa.discount_amount_remaining, -- kpis (select xxen_util.meaning( min( case when aha2.creation_date is not null and aha2.hold_date <= nvl(aaea2.creation_date,sysdate) then 'N' else 'Y' end ),'YES_NO',0) from ap_invoices_all aia2, ap_invoice_distributions_all aida2, ap_accounting_events_all aaea2, ap_holds_all aha2 where aia2.invoice_id = aia.invoice_id and aia2.invoice_id = aida2.invoice_id (+) and aida2.accounting_event_id = aaea2.accounting_event_id (+) and aia2.invoice_id = aha2.invoice_id (+) and aha2.held_by (+) = 5 -- held by system ) validated_without_holds, -- nvl2(pocc.po_number,xxen_util.meaning('Y','YES_NO',0),null) late_po_flag, pocc.po_number late_po_number, pocc.po_cost_center late_po_cost_centre, pocc.po_creation_date late_po_creation_date, pocc.po_created_by late_po_created_by, -- case when 1 = row_number() over (partition by aia.invoice_id order by apsa.payment_num,aida.distribution_line_number) then 1 else null end invoice_count, case when 1 = row_number() over (partition by aia.invoice_id order by apsa.payment_num,aida.distribution_line_number) then (select count(*) from ap_invoice_distributions_all aida where aida.invoice_id = aia.invoice_id and aida.line_type_lookup_code not in ('TAX','AWT')) else null end distribution_count, -- aida.distribution_line_number dist_line_number, xxen_util.meaning(aida.line_type_lookup_code,'INVOICE DISTRIBUTION TYPE',200) dist_line_type, aida.quantity_invoiced, aida.unit_price, aida.amount dist_amount, nvl(aida.base_amount,aida.amount) dist_acctd_amount, aida.period_name dist_period, aida.accounting_date dist_accounting_date, xxen_util.concatenated_segments(aida.dist_code_combination_id) dist_expense_account, replace(aida.description,'~','-') dist_description, aida.assets_addition_flag dist_assets_addition, aida.invoice_price_variance dist_invoice_price_variance, aida.base_invoice_price_variance dist_base_inv_price_variance, xxen_util.concatenated_segments(aida.price_var_code_combination_id) dist_price_variance_account, -- po match xxen_util.meaning(aida.dist_match_type,'MATCH_STATUS',200) dist_match_type, aida.match_status_flag dist_match_status, pha.segment1 po_number, xxen_util.user_name(pha.created_by) po_created_by, trunc(pha.creation_date) po_creation_date, case when (pha.creation_date - aia.creation_date) > 0 then xxen_util.meaning('Y','YES_NO',0) else null end po_created_after_invoice, nvl2(pda.code_combination_id,xxen_util.concatenated_segments(pda.code_combination_id),null) po_dist_account, nvl2(pda.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg','SQLGL','GL#',gl.chart_of_accounts_id,null,pda.code_combination_id,'FA_COST_CTR','Y','VALUE'),null) po_dist_cost_centre, -- -- project ppa.segment1 project_number, ppa.name project_name, ppa.description project_description, ppa.project_type, ppa.project_status_code, ppa.start_date project_start_date, ppa.completion_date project_completion_date, pt.task_number, pt.task_name, pt.description task_description, pt.service_type_code, pt.start_date task_start_date, pt.completion_date task_completion_date, aida.expenditure_item_date pa_expenditure_item_date, aida.expenditure_type pa_expenditure_type, coalesce(haouv1.name,haouv3.name) pa_expenditure_organization, aida.pa_addition_flag, aida.project_accounting_context, -- aida.creation_date dist_creation_date, aida.last_update_date dist_last_update_date, -- -- supplier/site details aps.end_date_active supplier_inactive_on, aps.num_1099 taxpayer_id, aps.vat_registration_num tax_registration_number, aps.vat_code, aps.customer_num supplier_customer_num, xxen_util.meaning(aps.one_time_flag,'YES_NO',0) one_time_supplier, aps.credit_status_lookup_code, aps.credit_limit, aps.withholding_status_lookup_code, aps.withholding_start_date, xxen_util.meaning(assa.purchasing_site_flag,'YES_NO',0) purchasing_site, xxen_util.meaning(assa.rfq_only_site_flag,'YES_NO',0) rfq_site, xxen_util.meaning(assa.pay_site_flag,'YES_NO',0) pay_site, xxen_util.meaning(assa.tax_reporting_site_flag,'YES_NO',0) tax_reporting_site, xxen_util.meaning(assa.pcard_site_flag,'YES_NO',0) p_card_site, xxen_util.meaning(assa.attention_ar_flag,'YES_NO',0) attention_ar, assa.address_line1, assa.address_line2, assa.address_line3, assa.address_line4, assa.city, assa.state, assa.zip, assa.county, assa.province, assa.country, assa.area_code, assa.phone, assa.fax_area_code, assa.fax, assa.supplier_notif_method, assa.email_address, assa.remittance_email, -- &dff_columns1 -- aia.invoice_id, decode(row_number() over (partition by apsa.invoice_id order by nvl(apsa.payment_num,1), aida.accounting_date,aida.distribution_line_number),1,'Y') first_invoice, decode(row_number() over (partition by apsa.invoice_id,apsa.payment_num order by aida.accounting_date,aida.distribution_line_number),1,'Y') first_psched from gl_sets_of_books gl, hr_all_organization_units_vl haouv, hr_all_organization_units_vl haouv1, hr_all_organization_units_vl haouv3, ap_invoices_all aia, ap_payment_schedules_all apsa, ap_bank_accounts_all aba, ap_bank_branches abb, po_vendors aps, po_vendor_sites_all assa, (select distinct y.invoice_id, first_value(y.cctr) over (partition by y.invoice_id order by y.cctr_amount desc, y.po_header_id desc, y.cctr rows between unbounded preceding and unbounded following) po_cost_center, first_value(y.po_number) over (partition by y.invoice_id order by y.cctr_amount desc, y.po_header_id desc, y.cctr rows between unbounded preceding and unbounded following) po_number, first_value(y.po_creation_date) over (partition by y.invoice_id order by y.cctr_amount desc, y.po_header_id desc, y.cctr rows between unbounded preceding and unbounded following) po_creation_date, first_value(y.po_created_by) over (partition by y.invoice_id order by y.cctr_amount desc, y.po_header_id desc, y.cctr rows between unbounded preceding and unbounded following) po_created_by from (select aida.invoice_id, pha.po_header_id, pha.segment1 po_number, xxen_util.user_name(pha.creation_date) po_creation_date, xxen_util.user_name(pha.created_by) po_created_by, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg','SQLGL','GL#',gcc.chart_of_accounts_id,null,gcc.code_combination_id,'FA_COST_CTR','Y','VALUE') cctr, sum(aida.amount) over (partition by aida.invoice_id,pha.po_header_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg','SQLGL','GL#',gcc.chart_of_accounts_id,null,gcc.code_combination_id,'FA_COST_CTR','Y','VALUE')) cctr_amount from ap_invoice_distributions_all aida, po_distributions_all pda, po_headers_all pha, gl_code_combinations gcc where aida.line_type_lookup_code != 'TAX' and aida.po_distribution_id = pda.po_distribution_id and pda.po_header_id = pha.po_header_id and pda.code_combination_id = gcc.code_combination_id and aida.creation_date < pha.creation_date ) y ) pocc, (select aida.* from ap_invoice_distributions_all aida where '&show_aida'='Y') aida, gl_code_combinations gcc, po_distributions_all pda, po_headers_all pha, ap_recurring_payments_all arpa, ap_terms at, pa_projects_all ppa, pa_tasks pt where 1=1 and ( (nvl('&show_aida','N') = 'Y' and 2=2 ) or (nvl('&show_aida','N') != 'Y' and exists --need this to apply dist level restrictions in case report is run at header or line level (select null from ap_invoice_distributions_all aida, gl_code_combinations gcc where aida.invoice_id = aia.invoice_id and aida.dist_code_combination_id = gcc.code_combination_id and 2=2 ) ) ) and aia.set_of_books_id=gl.set_of_books_id and aia.org_id=haouv.organization_id(+) and aia.expenditure_organization_id=haouv1.organization_id(+) and aida.expenditure_organization_id=haouv3.organization_id(+) and aia.invoice_id=apsa.invoice_id and apsa.external_bank_account_id = aba.bank_account_id (+) and aba.account_type (+) = 'SUPPLIER' and aba.bank_branch_id = abb.bank_branch_id (+) and aia.vendor_id=aps.vendor_id and aia.vendor_site_id=assa.vendor_site_id and aia.invoice_id = pocc.invoice_id (+) and aia.invoice_id=aida.invoice_id (+) and aida.dist_code_combination_id = gcc.code_combination_id (+) and aida.po_distribution_id = pda.po_distribution_id (+) and pda.po_header_id = pha.po_header_id (+) and aida.project_id=ppa.project_id(+) and aida.task_id=pt.task_id(+) and aia.recurring_payment_id=arpa.recurring_payment_id(+) and aia.terms_id=at.term_id(+) ) inv order by inv.operating_unit, inv.supplier, inv.supplier_number, inv.invoice_date, inv.invoice_gl_date, inv.invoice_num, inv.payment_num, inv.dist_accounting_date, inv.dist_line_number Y SQLAP (:Ledger is not null or :Operating_Unit is not null) US AP Invoices and Lines 11i Detail Invoice Aging report with line item details and amounts You must select a Ledger and/or Operating Unit Enginatics &aging_bucket_cols1 &aging_bucket_cols2 &dff_columns1 &dff_columns2 &invoice_detail_columns &show_aida 1=1 2=2 1 10 1=1 gl.name=:ledger and (haouv.organization_id is null or haouv.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)) LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='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(+)) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name coalesce(xxen_util.previous_parameter_value(:parameter_id),xxen_util.default_ledger) US Ledger 2 20 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name US Operating Unit 3 30 &aging_bucket_cols1 select 'case when ceil(sysdate-apsa.due_date) between ' || to_char(aap.days_start) || ' and ' || to_char(aap.days_to) || ' and apsa.amount_remaining != 0 then apsa.amount_remaining/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end bucket' || to_char(aap.period_sequence_num) || ',' from (select aapl.period_sequence_num, aapl.days_start, aapl.days_to, substr(aapl.report_heading1 || nvl2(aapl.report_heading1,nvl2(aapl.report_heading2,' ',null),null) || aapl.report_heading2,1,xxen_report.max_column_length) title from ap_aging_periods aap, ap_aging_period_lines aapl where aap.aging_period_id = aapl.aging_period_id and aap.period_name = :p_aging_bucket_name order by aapl.period_sequence_num ) aap union select ' case when ceil(sysdate-apsa.due_date)<=0 and apsa.amount_remaining != 0 then apsa.amount_remaining/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end current_bucket, case when ceil(sysdate-apsa.due_date) between 1 and 30 and apsa.amount_remaining != 0 then apsa.amount_remaining/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_1_30, case when ceil(sysdate-apsa.due_date) between 31 and 60 and apsa.amount_remaining != 0 then apsa.amount_remaining/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_31_60, case when ceil(sysdate-apsa.due_date) between 61 and 90 and apsa.amount_remaining != 0 then apsa.amount_remaining/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_61_90, case when ceil(sysdate-apsa.due_date) between 91 and 120 and apsa.amount_remaining != 0 then apsa.amount_remaining/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_91_120, case when ceil(sysdate-apsa.due_date) between 121 and 150 and apsa.amount_remaining != 0 then apsa.amount_remaining/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_121_150, case when ceil(sysdate-apsa.due_date) between 151 and 180 and apsa.amount_remaining != 0 then apsa.amount_remaining/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_151_180, case when ceil(sysdate-apsa.due_date) >=181 and apsa.amount_remaining != 0 then apsa.amount_remaining/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end greater_than_180,' from dual where not exists (select null from ap_aging_periods aap where aap.period_name = :p_aging_bucket_name and nvl(aap.status,'A') = 'A') LOV custom Y select x.value, x.description from ( select aap.period_name value, aap.description description from ap_aging_periods aap where nvl(aap.status,'A') = 'A' union select '*Default*' value, null description from dual ) x order by x.value *Default* US Aging Bucket Name 4 &aging_bucket_cols2 select 'case inv.first_psched when ''Y'' then inv.bucket' || to_char(aap.period_sequence_num) || ' end "' || aap.title || '",' from (select aapl.period_sequence_num, aapl.days_start, aapl.days_to, substr(aapl.report_heading1 || nvl2(aapl.report_heading1,nvl2(aapl.report_heading2,' ',null),null) || aapl.report_heading2,1,xxen_report.max_column_length) title from ap_aging_periods aap, ap_aging_period_lines aapl where aap.aging_period_id = aapl.aging_period_id and aap.period_name = :p_aging_bucket_name order by aapl.period_sequence_num ) aap union select 'case inv.first_psched when ''Y'' then inv.current_bucket end "Current", case inv.first_psched when ''Y'' then inv.between_1_30 end between_1_30, case inv.first_psched when ''Y'' then inv.between_31_60 end between_31_60, case inv.first_psched when ''Y'' then inv.between_61_90 end between_61_90, case inv.first_psched when ''Y'' then inv.between_91_120 end between_91_120, case inv.first_psched when ''Y'' then inv.between_121_150 end between_121_150, case inv.first_psched when ''Y'' then inv.between_151_180 end between_151_180, case inv.first_psched when ''Y'' then inv.greater_than_180 end greater_than_180,' from dual where not exists (select null from ap_aging_periods aap where aap.period_name = :p_aging_bucket_name and nvl(aap.status,'A') = 'A') US Aging Bucket Name 5 40 &show_aida Y LOV custom Y select 'Headers' value, 'Show Invoice Headers only' description from dual union all select 'Distributions' value, 'Show Invoice Headers and Distributions' description from dual Distributions Distributions US Display Level 6 &invoice_detail_columns inv.dist_line_number, inv.dist_line_type, inv.quantity_invoiced, inv.unit_price, inv.dist_amount, inv.dist_acctd_amount, inv.dist_period, inv.dist_accounting_date, inv.dist_expense_account, inv.dist_description, inv.dist_assets_addition, inv.dist_invoice_price_variance, inv.dist_base_inv_price_variance, inv.dist_price_variance_account, -- po match inv.dist_match_type, inv.dist_match_status, inv.po_number, inv.po_created_by, inv.po_creation_date, inv.po_created_after_invoice, inv.po_dist_account, inv.po_dist_cost_centre, -- -- -- project inv.project_number, inv.project_name, inv.project_description, inv.project_type, inv.project_status_code, inv.project_start_date, inv.project_completion_date, inv.task_number, inv.task_name, inv.task_description, inv.service_type_code, inv.task_start_date, inv.task_completion_date, inv.pa_expenditure_item_date, inv.pa_expenditure_type, inv.pa_expenditure_organization, inv.pa_addition_flag, inv.project_accounting_context, -- inv.dist_creation_date, inv.dist_last_update_date, Distributions US Display Level 7 50 1=1 aps.vendor_name=:supplier_name LOV AP Supplier B9847D20A0E4742FE0538931640A6379 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id US Supplier 8 60 1=1 assa.vendor_site_code=:vendor_site_code LOV AP Supplier Site 8E2FF36EDF0279D2E0530100007F1FF2 Y select distinct assa.vendor_site_code value, assa.address_line1||', '||assa.city||', '||nvl(ftv.territory_short_name,assa.country) description from hr_all_organization_units_vl haouv, ap_suppliers aps, ap_supplier_sites_all assa, fnd_territories_vl ftv where (:$flex$.ap_supplier is null or xxen_util.contains(:$flex$.ap_supplier,aps.vendor_name)='Y') and (:$flex$.ap_supplier_number is null or xxen_util.contains(:$flex$.ap_supplier_number,aps.segment1)='Y') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and haouv.organization_id=assa.org_id and aps.vendor_id=assa.vendor_id and assa.country=ftv.territory_code(+) order by assa.vendor_site_code US Supplier Site 9 70 1=1 aia.invoice_num=:invoice_number LOV AP Invoice Number 8E2FF36EDF3079D2E0530100007F1FF2 Y Y select aia.invoice_num value, aps.vendor_name||' ('||aia.invoice_date||')' description from ap_invoices_all aia, ap_suppliers aps where (:$flex$.operating_unit is null or aia.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or aia.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and aia.vendor_id=aps.vendor_id order by aia.invoice_date desc US Invoice Number 10 80 1=1 aia.invoice_date>=(select gps.start_date from gl_period_statuses gps where gps.period_name=:period_name and gps.set_of_books_id=gl.set_of_books_id and gps.application_id=200) and aia.invoice_date<(select gps.end_date+1 from gl_period_statuses gps where gps.period_name=:period_name and gps.set_of_books_id=gl.set_of_books_id and gps.application_id=200) LOV custom Y select gps.period_name value, xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',222)||': '||gps.start_date||' - '||gps.end_date description from gl_period_statuses gps where gps.set_of_books_id= ( select asp.set_of_books_id from ap_system_parameters asp, gl_sets_of_books gl, hr_operating_units hou where asp.set_of_books_id=gl.set_of_books_id and asp.set_of_books_id=hou.set_of_books_id and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,hou.name)='Y') and rownum=1 ) and gps.start_date<=sysdate+400 and gps.application_id=200 order by gps.start_date desc US Invoice Date Period 11 90 1=1 aia.invoice_date>=:invoice_date_from Date US Invoice Date From 12 100 1=1 aia.invoice_date<:invoice_date_to+1 Date last_day(:$flex$.invoice_date_from) US Invoice Date To 13 110 1=1 aia.creation_date>=(select gps.start_date from gl_period_statuses gps where gps.period_name=:period_name and gps.set_of_books_id=gl.set_of_books_id and gps.application_id=200) and aia.creation_date<(select gps.end_date+1 from gl_period_statuses gps where gps.period_name=:period_name and gps.set_of_books_id=gl.set_of_books_id and gps.application_id=200) LOV custom Y select gps.period_name value, xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',222)||': '||gps.start_date||' - '||gps.end_date description from gl_period_statuses gps where gps.set_of_books_id= ( select asp.set_of_books_id from ap_system_parameters asp, gl_sets_of_books gl, hr_operating_units hou where asp.set_of_books_id=gl.set_of_books_id and asp.set_of_books_id=hou.set_of_books_id and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,hou.name)='Y') and rownum=1 ) and gps.start_date<=sysdate+400 and gps.application_id=200 order by gps.start_date desc US Invoice Creation Period 14 120 1=1 aia.creation_date>=:creation_date_from Date US Invoice Creation Date From 15 130 1=1 aia.creation_date<:creation_date_to+1 Date last_day(:$flex$.invoice_creation_date_from) US Invoice Creation Date To 16 140 2=2 select 'aida.accounting_date>=to_date(''' || to_char(gps.start_date,'YYYY/MM/DD') || ''',''YYYY/MM/DD'') and aida.accounting_date < to_date(''' || to_char(gps.end_date+1,'YYYY/MM/DD') || ''',''YYYY/MM/DD'')' from gl_period_statuses gps, gl_sets_of_books gl where gps.period_name=:acctg_period_name and gps.set_of_books_id=gl.set_of_books_id and gps.application_id=200 and gl.set_of_books_id in (select asp.set_of_books_id from ap_system_parameters asp) and (:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and rownum=1 LOV custom Y select gps.period_name value, xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',222)||': '||gps.start_date||' - '||gps.end_date description from gl_period_statuses gps where gps.set_of_books_id= ( select asp.set_of_books_id from ap_system_parameters asp, gl_sets_of_books gl, hr_operating_units hou where asp.set_of_books_id=gl.set_of_books_id and asp.set_of_books_id=hou.set_of_books_id and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,hou.name)='Y') and rownum=1 ) and gps.start_date<=sysdate+400 and gps.application_id=200 order by gps.start_date desc US Accounting Period 17 150 2=2 aida.accounting_date>=:accounting_date_from Date US Accounting Date From 18 160 2=2 aida.accounting_date<:accounting_date_to+1 Date last_day(:$flex$.accounting_date_from) US Accounting Date To 19 170 1=1 aia.invoice_id in (select aipa.invoice_id from gl_period_statuses gps, ap_checks_all aca, ap_invoice_payments_all aipa where gps.period_name=:payment_period and gps.set_of_books_id=gl.set_of_books_id and gps.application_id=200 and aca.check_date >= gps.start_date and aca.check_date < gps.end_date+1 and aca.void_date is null and aca.stopped_date is null and aipa.check_id = aca.check_id ) LOV custom Y select gps.period_name value, xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',222)||': '||gps.start_date||' - '||gps.end_date description from gl_period_statuses gps where gps.set_of_books_id= ( select asp.set_of_books_id from ap_system_parameters asp, gl_sets_of_books gl, hr_operating_units hou where asp.set_of_books_id=gl.set_of_books_id and asp.set_of_books_id=hou.set_of_books_id and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,hou.name)='Y') and rownum=1 ) and gps.start_date<=sysdate+400 and gps.application_id=200 order by gps.start_date desc US Payment Period 20 180 1=1 (select max(aca.check_date) from ap_invoice_payments_all aipa, ap_checks_all aca where aipa.check_id = aca.check_id and aca.void_date is null and aca.stopped_date is null and aipa.invoice_id = apsa.invoice_id and aipa.payment_num = apsa.payment_num )>=:payment_date_from Date US Payment Date From 21 190 1=1 (select max(aca.check_date) from ap_invoice_payments_all aipa, ap_checks_all aca where aipa.check_id = aca.check_id and aca.void_date is null and aca.stopped_date is null and aipa.invoice_id = apsa.invoice_id and aipa.payment_num = apsa.payment_num )<:payment_date_to+1 Date last_day(:$flex$.payment_date_from) US Payment Date To 22 200 1=1 aia.payment_status_flag in ('N','P') and apsa.amount_remaining<>0 LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y select 'Y' from dual where :$flex$.invoice_number is null US Open only 23 210 1=1 ceil(sysdate-apsa.due_date)>:days_due Number US Days Overdue Days Due (Greater than) 24 220 1=1 aia.invoice_type_lookup_code=xxen_util.lookup_code(:invoice_type,'INVOICE TYPE',200) LOV AP Invoice Type B20C1D394FC36EDDE0530100007F5621 Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type(+)='INVOICE TYPE' and flvv.view_application_id(+)=200 and flvv.security_group_id(+)=0 order by flvv.meaning US Invoice Type 25 230 1=1 xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) = :p_invoice_status LOV custom Y select flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type in ('NLS TRANSLATION','PREPAY STATUS') and flvv.view_application_id = 200 and flvv.security_group_id = fnd_global.security_group_id and flvv.lookup_code in ('APPROVED', 'NEEDS REAPPROVAL', 'NEVER APPROVED', 'UNAPPROVED', 'UNPAID', 'FULL', 'PERMANENT', 'AVAILABLE', 'SELECTED FOR PAYMENT', 'SELECTED FOR VALIDATION', 'SELECTED FOR APPROVAL' ) order by flvv.meaning US Invoice Status 26 240 1=1 aia.payment_status_flag=xxen_util.lookup_code(:payment_status,'INVOICE PAYMENT STATUS',200) LOV custom Y select flv.meaning value, flv.description from fnd_lookup_values flv where flv.lookup_type(+)='INVOICE PAYMENT STATUS' and flv.view_application_id(+)=200 and flv.language(+)=userenv('lang') and flv.security_group_id(+)=0 order by flv.lookup_code US Payment Status 27 250 1=1 (aida.line_type_lookup_code=:distribution_type or aida.line_type_lookup_code is null) LOV custom Y select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type(+)='INVOICE DISTRIBUTION TYPE' and flvv.view_application_id(+)=200 and flvv.security_group_id(+)=0 order by flvv.meaning US Distribution Type 28 260 1=1 aia.cancelled_date is null LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y select 'Y' from dual where :$flex$.invoice_number is null US Exclude Cancelled 29 270 1=1 exists (select null from ap_invoice_distributions_all aida where aida.invoice_id = aia.invoice_id and aida.po_distribution_id is not null ) 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 US Invoice PO Matched 30 1=1 not exists (select null from ap_invoice_distributions_all aida where aida.invoice_id = aia.invoice_id and aida.po_distribution_id is not null ) N US Invoice PO Matched 31 280 1=1 ( apsa.hold_flag = 'Y' or (assa.hold_all_payments_flag = 'Y' and aia.payment_status_flag != 'Y' and aia.cancelled_date is null ) or exists (select null from ap_holds_all aha where aha.invoice_id = aia.invoice_id and aha.release_lookup_code is null ) ) 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 US Invoice on Hold 32 1=1 not ( apsa.hold_flag = 'Y' or (assa.hold_all_payments_flag = 'Y' and aia.payment_status_flag != 'Y' and aia.cancelled_date is null ) or exists (select null from ap_holds_all aha where aha.invoice_id = aia.invoice_id and aha.release_lookup_code is null ) ) N US Invoice on Hold 33 290 1=1 ( (:p_hold_code = 'Scheduled Payment Hold' and apsa.hold_flag = 'Y' ) or (:p_hold_code = 'Supplier Site Hold' and assa.hold_all_payments_flag = 'Y' and aia.payment_status_flag != 'Y' and aia.cancelled_date is null ) or exists (select null from ap_holds_all aha where aha.invoice_id = aia.invoice_id and aha.release_lookup_code is null and aha.hold_lookup_code = xxen_util.lookup_code(:p_hold_code,'HOLD CODE',200) ) ) LOV custom Y select x.value, x.description from ( select displayed_field value, null description from ap_lookup_codes where lookup_type='HOLD CODE' and lookup_code in(select hold_lookup_code from ap_hold_codes where (hold_type like '%REASON' or hold_type in ('INSUFFICIENT INFORMATION','PERIOD HOLD TYPE','PERIOD RELEASE TYPE')) and nvl(inactive_date,sysdate+1)>sysdate) and enabled_flag='Y' union select 'Scheduled Payment Hold' value, null description from dual union select 'Supplier Site Hold' value, null description from dual ) x order by x.value, x.description US Hold Name 34 300 1=1 exists (select null from fnd_attached_documents fad where to_char(aia.invoice_id)=fad.pk1_value and aia.invoice_id=fad.pk1_value and fad.entity_name='AP_INVOICES') 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 US Has Attachment 35 1=1 not exists (select null from fnd_attached_documents fad where to_char(aia.invoice_id)=fad.pk1_value and aia.invoice_id=fad.pk1_value and fad.entity_name='AP_INVOICES') N US Has Attachment 36 310 2=2 select decode(rownum,1,' ','and ')||x.segment_name||' >= '''||x.seg_val||'''' from ( select 'gcc.'||fifs.application_column_name segment_name, substr(:p_expense_account_from,sum(fifs.display_size+1) over (order by fifs.segment_num rows between unbounded preceding and current row)-fifs.display_size,fifs.display_size) seg_val from fnd_id_flex_segments fifs where fifs.enabled_flag='Y' and fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num= ( select gl.chart_of_accounts_id from gl_sets_of_books gl where gl.set_of_books_id in (select asp.set_of_books_id from ap_system_parameters asp) and (:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and rownum=1 ) order by fifs.segment_num ) x LOV custom Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.chart_of_accounts_id= ( select gl.chart_of_accounts_id from gl_sets_of_books gl where gl.set_of_books_id in (select asp.set_of_books_id from ap_system_parameters asp) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and rownum=1 ) and gcck.detail_posting_allowed='Y' and gcck.enabled_flag='Y' and gcck.summary_flag='N' order by gcck.concatenated_segments US Expense Account From 37 320 2=2 select decode(rownum,1,' ','and ')||x.segment_name||' <= '''||x.seg_val||'''' from ( select 'gcc.'||fifs.application_column_name segment_name, substr(:p_expense_account_to,sum(fifs.display_size+1) over (order by fifs.segment_num rows between unbounded preceding and current row)-fifs.display_size,fifs.display_size) seg_val from fnd_id_flex_segments fifs where fifs.enabled_flag='Y' and fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num= ( select gl.chart_of_accounts_id from gl_sets_of_books gl where gl.set_of_books_id in (select asp.set_of_books_id from ap_system_parameters asp) and (:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and rownum=1 ) order by fifs.segment_num ) x LOV custom Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.chart_of_accounts_id= ( select gl.chart_of_accounts_id from gl_sets_of_books gl where gl.set_of_books_id in (select asp.set_of_books_id from ap_system_parameters asp) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and rownum=1 ) and gcck.detail_posting_allowed='Y' and gcck.enabled_flag='Y' and gcck.summary_flag='N' order by gcck.concatenated_segments value :$flex$.Expense_Account_From US Expense Account To 38 330 &dff_columns1 select case when :show_dff is not null then xxen_util.dff_columns(p_table_name=>'po_vendors',p_table_alias=>'aps',p_column_name_prefix=>'Supplier: ')|| xxen_util.dff_columns(p_table_name=>'po_vendor_sites_all',p_table_alias=>'assa',p_column_name_prefix=>'Site: ')|| xxen_util.dff_columns(p_table_name=>'ap_invoices_all',p_table_alias=>'aia',p_column_name_prefix=>'Invoice: ')|| case when :display_level = 'Distributions' then xxen_util.dff_columns(p_table_name=>'ap_invoice_distributions_all',p_table_alias=>'aida',p_column_name_prefix=>'Dist: ') end end from dual LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y Y US Show DFF Attributes 39 &dff_columns2 select case when :show_dff is not null then xxen_util.dff_columns(p_table_name=>'po_vendors',p_column_name_prefix=>'Supplier: ',p_prefix=>'inv.')|| xxen_util.dff_columns(p_table_name=>'po_vendor_sites_all',p_table_alias=>'assa',p_column_name_prefix=>'Site: ',p_prefix=>'inv.')|| xxen_util.dff_columns(p_table_name=>'ap_invoices_all',p_column_name_prefix=>'Invoice: ',p_prefix=>'inv.')|| case when :display_level = 'Distributions' then xxen_util.dff_columns(p_table_name=>'ap_invoice_lines_all',p_column_name_prefix=>'Dist: ',p_prefix=>'inv.') end end from dual US Show DFF Attributes :$flex$.Expense_Account_From Expense Account From Expense Account To :$flex$.accounting_date_from Accounting Date From Y Accounting Date To :$flex$.ap_supplier Supplier Supplier Site :$flex$.invoice_creation_date_from Invoice Creation Date From Y Invoice Creation Date To :$flex$.invoice_date_from Invoice Date From Y Invoice Date To :$flex$.invoice_number Invoice Number Exclude Cancelled :$flex$.invoice_number Invoice Number Open only :$flex$.ledger Ledger Accounting Period :$flex$.ledger Ledger Expense Account From :$flex$.ledger Ledger Expense Account To :$flex$.ledger Ledger Invoice Creation Period :$flex$.ledger Ledger Invoice Date Period :$flex$.ledger Ledger Invoice Number :$flex$.ledger Ledger Operating Unit :$flex$.ledger Ledger Payment Period :$flex$.operating_unit Operating Unit Accounting Period :$flex$.operating_unit Operating Unit Invoice Creation Period :$flex$.operating_unit Operating Unit Invoice Date Period :$flex$.operating_unit Operating Unit Invoice Number :$flex$.operating_unit Operating Unit Payment Period :$flex$.operating_unit Operating Unit Supplier :$flex$.operating_unit Operating Unit Supplier Site :$flex$.payment_date_from Payment Date From Y Payment Date To