ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 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 8E2FF36EDE9279D2E0530100007F1FF2 GL Ledger / Set 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$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_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.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,'NONE',1,'PRIMARY',2,'SECONDARY',3,'ALC',4), gl.name AR جميع دفاتر الأستاذ ومجموعات دفتر الأستاذ D Alle Ledger und Ledger-Sets E Todos los libros de contabilidad y conjuntos de libros de contabilidad F Tous les grands livres et ensembles de grands livres I Tutti i registri e i set di registri JA すべての元帳と元帳セット KO 모든 원장 및 원장 세트 PTB Todos os livros contábeis e conjuntos de livros contábeis RU Все бухгалтерские книги и комплекты бухгалтерских книг S Alla reskontrar och reskontrasatser TR Tüm defterler ve defter setleri US All ledgers and ledger sets ZHS 所有分类账和分类账组 8E2FF36EDE9979D2E0530100007F1FF2 HR Organization Name Y select haouv.name value, haouv0.name||': '|| (select distinct listagg(flvv.meaning,', ') within group (order by flvv.meaning) over (partition by hoi.organization_id) classification from hr_organization_information hoi, fnd_lookup_values_vl flvv where haouv.organization_id=hoi.organization_id and hoi.org_information_context='CLASS' and hoi.org_information2='Y' and hoi.org_information1=flvv.lookup_code(+) and flvv.lookup_type(+)='ORG_CLASS' and flvv.view_application_id(+)=3 and flvv.security_group_id(+)=0 ) description from hr_all_organization_units_vl haouv0, hr_all_organization_units_vl haouv where haouv0.organization_id=haouv.business_group_id order by haouv.name US A978F7F5FBDC3D35E053B46B63584C8F select decode(xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200),'Standard',1,'Credit Memo',3) "DocumentType", null "SequencialNumberVK", null "SequencialNumberAK", aia.invoice_date "TransactionDate", aia.invoice_num "InvoiceNumber", aia.invoice_num "SupplierInvoiceNumber", null "LastInvoiceNumber", null "DocumentIndicator", null "OwnReference", null "ReferenceInvoiceNumber", null "ReferenceInvoiceDate", null "ReferenceTaxableBasis", null "ReferenceVAT", null "AutoFatturaNumber", aia.invoice_date "InvoiceDate", aia.invoice_currency_code "Currency", aia.payment_currency_code "Currency2", null "Currency3", null "DocumentCurrency", null "ExchangeRateDate", null "Discount", null "DateOfSupply", null "VATCode", null "VATCodeScheme", null "VATCodeDescription", null "CreditNoteOriginalDocument", null "CreditNoteReason", null "CreditNotePeriod", null "VATDate", null "InvoiceReceiptDate", null "IncomingPostingDate", null "OutsourcingPartyType", null "OutsourcingPartyName", null "OutsourcingPartyAddress", null "OutsourcingPartyVRN", asu.segment1 "SupplierID", asu.vendor_name "SupplierName", asu.first_name "SupplierPrivatePersonFirstName", asu.last_name "SupplierPrivatePersonLastName", assa.address_line1 "SupplierStreet", assa.address_line2 "SupplierHouseNumber", assa.zip "SupplierZip", assa.city "SupplierCity", assa.country "SupplierCountry", assa.area_code||assa.phone "SupplierTelephone", assa.fax_area_code||assa.fax "SupplierFax", asu.vat_registration_num "SupplierVATNumberUsed", null "SupplierCountryVATNumberUsed", assa.vat_code "SupplierVatNumberType", null "SupplierDeductionType", null "SupplierSpecialType", null "SupplierFiscalNumber", null "SupplierFiscalNumberIssuedBy", null supplierfiscalrepresentativena, null supplierfiscalrepresentativep1, null supplierfiscalrepresentativep2, null supplierfiscalrepresentativest, null supplierfiscalrepresentativeho, null supplierfiscalrepresentativezi, null supplierfiscalrepresentativeci, null supplierfiscalrepresentativeco, null supplierfiscalrepresentativete, null supplierfiscalrepresentativefa, null supplierfiscalrepresentativeem, null supplierfiscalrepresentativeva, null supplierfiscalrepresentativeta, assa.address_line1 supplierfixedestablishmentstre, assa.address_line2 supplierfixedestablishmenthous, assa.zip "SupplierFixedEstablishmentZip", assa.city "SupplierFixedEstablishmentCity", assa.country supplierfixedestablishmentcoun, assa.phone supplierfixedestablishmenttele, assa.fax "SupplierFixedEstablishmentFax", assa.email_address supplierfixedestablishmentemai, null supplieridentifierpassportnumb, null "SupplierIdentifierIdCardNumber", null supplieridentifierresidencycer, null "CustomerID", null "CustomerName", null "CustomerPrivatePersonFirstName", null "CustomerPrivatePersonLastName", null "CustomerStreet", null "CustomerHouseNumber", null "CustomerZip", null "CustomerCity", null "CustomerCountry", null "CustomerTelephone", null "CustomerFax", null "CustomerVATNumberUsed", null "CustomerCountryVATNumberUsed", null "CustomerVatNumberType", null "CustomerDeductionType", null "CustomerSpecialType", null "CustomerFiscalNumber", null "CustomerFiscalNumberIssuedBy", null customerfiscalrepresentativena, null customerfiscalrepresentativep1, null customerfiscalrepresentativep2, null customerfiscalrepresentativest, null customerfiscalrepresentativeho, null customerfiscalrepresentativezi, null customerfiscalrepresentativeci, null customerfiscalrepresentativeco, null customerfiscalrepresentativete, null customerfiscalrepresentativefa, null customerfiscalrepresentativeem, null customerfiscalrepresentativeva, null customerfiscalrepresentativeta, null customerfixedestablishmentstre, null customerfixedestablishmenthous, null "CustomerFixedEstablishmentZip", null "CustomerFixedEstablishmentCity", null customerfixedestablishmentcoun, null customerfixedestablishmenttele, null "CustomerFixedEstablishmentFax", null customerfixedestablishmentemai, null customeridentifierpassportnumb, null "CustomerIdentifierIdCardNumber", null customeridentifierresidencycer, aia.description "Description", null "ExemptionReason", null "ItemClassification", nvl(aia.invoice_amount,aia.base_amount) "TaxableBasis", aia.tax_amount "ValueVAT", null "SalesVATDueReverseCharge", aia.invoice_amount+aia.tax_amount "TotalValueLine", null "AmountVATDeducted", null "AmountVATReverseCharged", null "TaxableBasisCurrency2", null "ValueVATCurrency2", null salesvatduereversechargecurre2, null "TotalValueLineCurrency2", null "AmountVATDeductedCurrency2", null amountvatreversechargedcurren2, null "TaxableBasisCurrency3", null "ValueVATCurrency3", null salesvatduereversechargecurre3, null "TotalValueLineCurrency3", null "AmountVATDeductedCurrency3", null amountvatreversechargedcurren3, null "OutOfVAT", aila.quantity_invoiced "Quantity", aila.unit_meas_lookup_code "Unit", aila.inventory_item_id "ItemIdentifier", assa.country "CountryDispatch", assa.country "CountryArrival", null "ShipToCity", null "ShipToZIP", null "ShipToStreet", null "ShipToStreetNumber", null "ShipFromCity", null "ShipFromZIP", null "ShipFromStreet", null "ShipFromStreetNumber", null "CountryOperation", null "Installation", null "Transporter", null "CountryEUImportation", null "EUImporter", null "DeliveryConditions", null "PlaceOfDelivery", null "Triangulation", null "AdditionalDocumentReference", null "ReportingType", null "TransactionType", null "AdditionalTransactionType", null "IntrastatCode", null "AdditionalIntrastatCode", null "ExtrastatCode", null "AdditionalDescription", null "Quantity1", null "Unit1", null "Quantity2", null "Unit2", aia.invoice_amount+aia.tax_amount "CommercialValue", null "StatisticalValue", null "CommercialValueCurrency2", null "StatisticalValueCurrency2", null "CommercialValueCurrency3", null "StatisticalValueCurrency3", null "ModeOfTransport", null "ItemType", null "RegionDispatch", null "HarbourDispatch", null "RegionArrival", null "HarbourArrival", null "CountryOrigin", null "ServiceCode", null "NationalityTransportVehicle", null "AccountNumber", null "CashRegisterNumber", null "AccountNumberTaxableBasis", null "AccountNumberVAT", null "AccountNumberDeductibleVAT", null "AccountNumberNonDeductibleVAT", null "AccountNumberReversedVAT", null "RefundNatureOfItem", null "RefundDescription", null "RefundDescriptionLanguage", null "ImportDocumentNumber", null "ImportReferenceInformation", null "ScannedDocumentFileName", null "ScannedDocumentFileDescription", null "ClearingDate", null "ClearingDocumentNumber", null "ClearingDocumentAmount", null "ClearingDocumentCurrency", null clearingdocumentaccountinforma, null "ClearingDocumentPayementMethod", null "NationalityTransportMeans", null "CountryCustomsDeclaration", null "InternalModeOfTransport", null "EUCountryDispatch", null "EUCountryArrival", null "Container", null "EORINRPSI", null "EORINRPSIAgent", null "CustomsProcedureCode", null "PreferentialTreatment", null "StatisticalProcedure" /*---- decode(xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200),'Standard',1,'Credit Memo',3) "DocumentType", asu.vendor_name supplier, aia.invoice_num, 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, xxen_util.client_time(aia.creation_date) invoice_creation_date, aia.invoice_date, apsa.due_date, ceil(sysdate-apsa.due_date) days_due, apsa.gross_amount, asu.segment1 supplier_number, asu.num_1099 taxpayer_id, asu.vat_registration_num tax_registration_number, asu.end_date_active inactive_on, asu.customer_num, xxen_util.meaning(asu.one_time_flag,'YES_NO',0) one_time, asu.credit_status_lookup_code, asu.credit_limit, asu.withholding_status_lookup_code, asu.withholding_start_date, asu.vat_code, assa.vendor_site_id, assa.vendor_site_code, assa.vendor_site_code supplier_site, assa.vendor_site_code_alt, 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, aia.gl_date invoice_gl_date, aia.source invoice_source, xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type, aia.description invoice_description, aia.invoice_currency_code, aia.payment_currency_code, aia.payment_cross_rate, decode(aia.invoice_currency_code,gl.currency_code,aia.invoice_amount,aia.base_amount) invoice_amount_base_currency, aia.amount_paid invoice_amount_paid, nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) amount_remaining, apsa.discount_date, apsa.future_pay_due_date, apsa.gross_amount, apsa.hold_flag, nvl(xxen_util.meaning(apsa.payment_method_code,'PAYMENT METHOD',200),apsa.payment_method_code) payment_method, xxen_util.meaning(aia.payment_status_flag,'INVOICE PAYMENT STATUS',200) invoice_payment_status, xxen_util.meaning(apsa.payment_status_flag,'INVOICE PAYMENT STATUS',200) schedule_payment_status, 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, apsa.inv_curr_gross_amount, nvl(aia.amount_paid,0)/decode(nvl(aia.payment_cross_rate,1),0,1,aia.payment_cross_rate)*aia.exchange_rate amount_paid_base, nvl(aia.amount_applicable_to_discount,0)*aia.exchange_rate amt_applicable_to_disc_base, nvl(aia.discount_amount_taken,0)/decode(nvl(aia.payment_cross_rate,1),0,1,aia.payment_cross_rate)*aia.exchange_rate discount_amount_taken_base, nvl(aia.approved_amount,0)*aia.exchange_rate manual_approval_amount_base, nvl(aia.payment_amount_total,0)*aia.exchange_rate payment_amount_total_base, nvl(aia.tax_amount,0)*aia.exchange_rate tax_amount_base, aia.discount_amount_taken, aia.amount_applicable_to_discount, aia.tax_amount, aia.pay_curr_invoice_amount, aia.payment_cross_rate_date, at.name invoice_terms, aia.terms_date, aia.pay_group_lookup_code invoice_pay_group, aia.accts_pay_code_combination_id, xxen_util.concatenated_segments(aia.accts_pay_code_combination_id) account, xxen_util.segments_description(aia.accts_pay_code_combination_id) account_descripton, aia.base_amount invoice_base_amount, aia.approved_amount, xxen_util.meaning(aia.exclusive_payment_flag,'YES_NO',0) invoice_exclusive_payment, 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, aia.auto_tax_calc_flag, aia.invoice_amount, aia.amount_paid, -- decode (aia.po_number,'unmatched',null,'any multiple',null,aia.po_number ) po_number, gl.currency_code, apsa.payment_num, case when ceil(sysdate-apsa.due_date)=0 then nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end current_bucket, aila.line_number, aila.line_type_lookup_code line_type, replace(aila.description,'~','-') line_description, aila.amount line_amount, aila.tax_classification_code, aila.product_fisc_classification, aila.user_defined_fisc_class, aila.base_amount line_base_amount, arpa.recurring_pay_num, arpa.rec_pay_period_type, arpa.num_of_periods, arpa.description recurring_pmt_description, aia.invoice_id*/ from gl_ledgers gl, hr_all_organization_units_vl haouv, hr_all_organization_units_vl haouv1, hr_all_organization_units_vl haouv2, hr_all_organization_units_vl haouv3, ap_invoices_all aia, ap_payment_schedules_all apsa, ap_suppliers asu, ap_supplier_sites_all assa, (select aila.* from ap_invoice_lines_all aila where 'Y'='Y') aila, (select aida.* from ap_invoice_distributions_all aida where ''='Y') aida, ap_recurring_payments_all arpa, ap_terms at, pa_projects_all ppa, pa_tasks pt where aia.payment_status_flag in ('N','P') and aia.cancelled_date is null and 1=1 and aia.set_of_books_id=gl.ledger_id and aia.org_id=haouv.organization_id(+) and aia.expenditure_organization_id=haouv1.organization_id(+) and aila.expenditure_organization_id=haouv2.organization_id(+) and aida.expenditure_organization_id=haouv3.organization_id(+) and aia.invoice_id=apsa.invoice_id and nvl(apsa.amount_remaining,0)*nvl(aia.exchange_rate,1)!=0 and aia.vendor_id=asu.vendor_id and aia.vendor_site_id=assa.vendor_site_id and aia.invoice_id=aila.invoice_id(+) and aila.invoice_id=aida.invoice_id(+)and aila.line_number=aida.invoice_line_number(+) 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(+) and aia.invoice_type_lookup_code in ('STANDARD','CREDIT') Y SQLAP 55DEB4D48B1D727FE0530100007F0ADE US RK: AP Invoices and Lines Application: Payables Source: Invoice Aging Report and Invoice Detail Report Check for Avalara Draft 1=1 1 10 1=1 asu.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 2 30 1=1 aia.invoice_date>=:invoice_date_from Date US Inv. Date From 3 40 1=1 aia.invoice_date<:invoice_date_to+1 Date US Inv. Date To 4 50 &enable_aida Y LOV custom Y select 'Headers' value, 'Show Invoice Headers only' description from dual union all select 'Lines' value, 'Show Invoice Headers Lines only' description from dual union all select 'Lines and Distributions' value, 'Show Invoice Headers Lines and Distributions' description from dual Lines and Distributions Lines US Display Level 5 &enable_aila Y Lines% US Display Level 6 &invoice_detail_columns aila.line_number, aila.line_type_lookup_code line_type, replace(aila.description,'~','-') line_description, aila.amount line_amount, aila.base_amount line_base_amount, Lines US Display Level 7 &invoice_detail_columns aila.line_number, aila.line_type_lookup_code line_type, replace(aila.description,'~','-') line_description, aila.amount line_amount, aila.base_amount line_base_amount, aida.distribution_line_number, aida.parent_reversal_id, aida.dist_match_type, aida.accounting_date dist_accounting_date, aida.assets_addition_flag dist_assets_addition, aida.distribution_line_number dist_line_number, aida.dist_code_combination_id, xxen_util.concatenated_segments(aida.dist_code_combination_id) expense_account, aida.line_type_lookup_code dist_line_type, aida.period_name, aida.creation_date dist_creation_date, aida.last_update_date dist_last_update_date, aida.amount dist_amount, nvl(aida.amount,aida.base_amount) original_amount, aida.base_invoice_price_variance dist_base_inv_price_variance, replace(aida.description,'~','-') dist_description, aida.invoice_price_variance dist_invoice_price_variance, aida.match_status_flag dist_match_status, aida.po_distribution_id, aida.quantity_invoiced, aida.unit_price, aida.price_var_code_combination_id, xxen_util.concatenated_segments(aida.price_var_code_combination_id) price_variance_account, aida.expenditure_item_date pa_expenditure_item_date, aida.expenditure_type pa_expenditure_type, aida.expenditure_organization_id, coalesce(haouv1.name,haouv3.name,haouv2.name) expenditure_organization, aida.pa_addition_flag, aida.project_accounting_context, ppa.project_id, pt.task_id, 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, Lines and Distributions US Display Level 8 70 1=1 ceil(sysdate-apsa.due_date)>:days_due Number US Days Due Days Due (Greater than) 9 80 1=1 gcc.segment2=:gl_account_segment Char US Gl Account 10 82 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 11 85 1=1 aia.payment_status_flag in ('N','P') 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 Open only 12 90 1=1 aia.cancelled_date 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 Y US Exclude Cancelled 13 180 1=1 haouv.name=:org_name LOV HR Organization Name 8E2FF36EDE9979D2E0530100007F1FF2 Y select haouv.name value, haouv0.name||': '|| (select distinct listagg(flvv.meaning,', ') within group (order by flvv.meaning) over (partition by hoi.organization_id) classification from hr_organization_information hoi, fnd_lookup_values_vl flvv where haouv.organization_id=hoi.organization_id and hoi.org_information_context='CLASS' and hoi.org_information2='Y' and hoi.org_information1=flvv.lookup_code(+) and flvv.lookup_type(+)='ORG_CLASS' and flvv.view_application_id(+)=3 and flvv.security_group_id(+)=0 ) description from hr_all_organization_units_vl haouv0, hr_all_organization_units_vl haouv where haouv0.organization_id=haouv.business_group_id order by haouv.name US Organization 14 200 1=1 gl.name=:ledger LOV GL Ledger / Set 8E2FF36EDE9279D2E0530100007F1FF2 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$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_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.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,'NONE',1,'PRIMARY',2,'SECONDARY',3,'ALC',4), gl.name select gl.name from ar_system_parameters asp, gl_ledgers gl where asp.set_of_books_id=gl.ledger_id US Ledger