ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 49A8B1A803BC08ACE0530100007FEB91Y select haouv.name operating_unit, rila.batch_source_name batch_source, rila.request_id, xxen_util.yes(decode(rila.interface_status,'P','Y')) processed, rila.interface_line_context, rtrim(rila.interface_line_attribute1||'|'||rila.interface_line_attribute2||'|'||rila.interface_line_attribute3||'|'||rila.interface_line_attribute4||'|'||rila.interface_line_attribute5||'|'||rila.interface_line_attribute6||'|'||rila.interface_line_attribute7||'|'||rila.interface_line_attribute8||'|'||rila.interface_line_attribute9||'|'||rila.interface_line_attribute10||'|'||rila.interface_line_attribute11||'|'||rila.interface_line_attribute12||'|'||rila.interface_line_attribute13||'|'||rila.interface_line_attribute14||'|'||rila.interface_line_attribute15,'|') interface_line_reference, rila.sales_order, rila.sales_order_line, decode(rila.interface_line_context,'ORDER ENTRY',rila.interface_line_attribute1) order_number, rila.trx_number, rila.trx_date, rila.gl_date, nvl(rctta.name,rila.cust_trx_type_name) transaction_type, xxen_util.meaning(rila.line_type,'STD_LINE_TYPE',222) line_type, nvl(hp.party_name,rila.orig_system_bill_customer_ref) bill_to_customer, hca.account_number bill_to_account_number, (select msiv.concatenated_segments from mtl_system_items_vl msiv where rila.inventory_item_id=msiv.inventory_item_id and nvl(rila.warehouse_id,oe_sys_parameters.value('MASTER_ORGANIZATION_ID',rila.org_id))=msiv.organization_id) item, rila.description, rila.uom_code uom, case when row_number() over (partition by rila.rowid order by null&show_dist_order)=1 then rila.quantity end quantity, rila.unit_selling_price, case when row_number() over (partition by rila.rowid order by null&show_dist_order)=1 then rila.amount end amount, rila.currency_code currency, case when row_number() over (partition by rila.rowid order by null&show_dist_order)=1 then 1 end line_count, &lp_columns riea.error_count, riea.error_messages, riea.invalid_values, &show_dist_columns xxen_util.user_name(rila.created_by) created_by, xxen_util.client_time(rila.creation_date) creation_date, rila.interface_line_id from hr_all_organization_units_vl haouv, ra_interface_lines_all rila, ( select x.interface_line_id, max(x.error_count) error_count, listagg(case when x.message_len<=4000 then x.message_text end,'; ') within group (order by x.message_number) error_messages, listagg(case when x.invalid_len<=4000 then x.invalid_value end,'; ') within group (order by x.message_number) invalid_values from ( select riea.interface_line_id, riea.message_text, riea.invalid_value, row_number() over (partition by riea.interface_line_id order by riea.rowid) message_number, count(*) over (partition by riea.interface_line_id) error_count, sum(lengthb(riea.message_text)+2) over (partition by riea.interface_line_id order by riea.rowid rows between unbounded preceding and current row) message_len, sum(nvl(lengthb(riea.invalid_value),0)+2) over (partition by riea.interface_line_id order by riea.rowid rows between unbounded preceding and current row) invalid_len from ra_interface_errors_all riea ) x group by x.interface_line_id ) riea, ra_cust_trx_types_all rctta, hz_cust_accounts hca, hz_parties hp &show_dist_from where 1=1 and 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) and haouv.organization_id=rila.org_id and rila.interface_line_id=riea.interface_line_id(+) and rila.cust_trx_type_id=rctta.cust_trx_type_id(+) and rila.org_id=rctta.org_id(+) and rila.orig_system_bill_customer_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) &show_dist_where order by haouv.name, rila.batch_source_name, rila.interface_line_context, rila.interface_line_attribute1, rila.interface_line_attribute2, rila.interface_line_attribute3, rila.interface_line_id Reworked to one row per interface line, with the errors of a line concatenated. A line with several errors was counted, and its amount summed, once per error. New parameters Batch Source, Interface Line Context, Request Id, Sales Order, Creation Date From/To, Errors Only and Show Distributions and Sales Credits. Renamed from AR Autoinvoice Interface Summary; the summary output is the template Autoinvoice Interface Summary. AR AR Autoinvoice Interface Summary ملخص السجلات في واجهة "الفاتورة التلقائية" ، مرتبة حسب وحدة التشغيل ، بما في ذلك عدد الطلبات وإجمالي قيمة الأوامر ورمز العملة. D AR Autoinvoice Interface Summary Zusammenfassung der Datensätze in der Auto-Rechnungsschnittstelle, sortiert nach Bedieneinheit und einschließlich Auftragsanzahl, Gesamtauftragswert und Währungscode. E AR Autoinvoice Interface Summary Resumen de los registros en la interfaz de autofacturación, ordenados por unidad operativa, e incluyendo el recuento de pedidos, el valor total de los mismos y el código de moneda. F AR Autoinvoice Interface Summary Résumé des enregistrements dans l'interface de facturation automatique, triés par unité opérationnelle, et comprenant le nombre de commandes, la valeur totale des commandes et le code de la devise. I AR Autoinvoice Interface Summary Riepilogo delle registrazioni nell'interfaccia di autofattura, ordinate per unità operativa e comprendenti il conteggio degli ordini, il valore totale degli ordini e il codice della valuta. JA AR Autoinvoice Interface Summary オートインボイスのインターフェイスのレコードを操作単位でソートし、注文数、注文総額、通貨コードを含めてまとめています。 KO AR Autoinvoice Interface Summary 운영 단위별로 정렬되고 주문 수, 총 주문 금액 및 통화 코드를 포함하여 자동 송장 인터페이스의 레코드 요약. PTB AR Autoinvoice Interface Summary Resumo dos registros na interface da nota fiscal automática, classificados por unidade operacional, e incluindo contagem de pedidos, valor total dos pedidos e código da moeda. RU AR Autoinvoice Interface Summary Сводка записей в интерфейсе автосчета, отсортированная по операционной единице, с учетом количества заказов, общей стоимости заказов и кода валюты. S AR Autoinvoice Interface Summary Sammanfattning av poster i autofakturagränssnittet, sorterat efter operativ enhet, inklusive orderantal, totalt ordervärde och valutakod. TR AR Autoinvoice Interface Summary Otomatik fatura arayüzündeki kayıtların özeti, işletim birimine göre sıralanmış ve sipariş sayısı, toplam sipariş değeri ve para birimi kodu dahil. US AR Autoinvoice Interface Lines in the Autoinvoice interface table, one row per interface line, with all errors of the line from the last Autoinvoice run concatenated in Error Messages and Invalid Values. Interface Line Id is assigned by Autoinvoice, so lines that it has not picked up yet have none and cannot have errors. With Show Distributions and Sales Credits, each line is repeated per interface distribution and sales credit, and its quantity, amount and Line Count of 1 are shown on the first of these rows only. The template Autoinvoice Interface Summary totals the lines per operating unit, batch source, context, error messages and request. ZHS AR Autoinvoice Interface Summary 自动发票界面的记录汇总，按操作单位排序，包括订单数、订单总价值和货币代码。 Enginatics &lp_columns &show_dist_columns &show_dist_from &show_dist_order &show_dist_where 1=1 1 10 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 2 20 &lp_columns trunc(sysdate-rila.creation_date) age, LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual F Afficher l'âge US Show Age 3 30 1=1 rila.batch_source_name=:batch_source LOV custom Y select distinct rbsa.name value, null description from ra_batch_sources_all rbsa where rbsa.batch_source_type='FOREIGN' and rbsa.org_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 rbsa.name US Batch Source 4 40 1=1 rila.interface_line_context=:interface_line_context LOV custom Y select fdfcv.descriptive_flex_context_code value, fdfcv.description from fnd_descr_flex_contexts_vl fdfcv where fdfcv.application_id=222 and fdfcv.descriptive_flexfield_name='RA_INTERFACE_LINES' and fdfcv.global_flag='N' and fdfcv.enabled_flag='Y' order by fdfcv.descriptive_flex_context_code US Interface Line Context 5 50 1=1 rila.request_id=:request_id Number US Request Id 6 60 1=1 rila.sales_order=:sales_order Char US Sales Order 7 70 1=1 rila.creation_date>=:creation_date_from Date US Creation Date From 8 80 1=1 rila.creation_date<:creation_date_to+1 Date last_day(:$flex$.creation_date_from) US Creation Date To 9 90 1=1 rila.interface_line_id in (select riea.interface_line_id from ra_interface_errors_all riea) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Errors Only 10 100 &show_dist_columns xxen_util.meaning(ric.account_class,'AUTOGL_TYPE',222) account_class, (select gcck.concatenated_segments from gl_code_combinations_kfv gcck where ric.code_combination_id=gcck.code_combination_id) account, ric.salesperson, ric.sales_credit_type, ric.percent, ric.child_amount, LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Distributions and Sales Credits Adds one row per interface distribution and sales credit of each line 11 &show_dist_from , ( select '1' child_type, rida.interface_line_id, rida.interface_distribution_id child_id, rida.account_class, rida.code_combination_id, null salesperson, null sales_credit_type, rida.percent, rida.amount child_amount from ra_interface_distributions_all rida union all select '2' child_type, risa.interface_line_id, risa.interface_salescredit_id child_id, null account_class, to_number(null) code_combination_id, coalesce((select jrrev.resource_name from jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev where risa.salesrep_id=jrs.salesrep_id and risa.org_id=jrs.org_id and jrs.resource_id=jrrev.resource_id),risa.salesrep_number) salesperson, nvl((select osct.name from oe_sales_credit_types osct where risa.sales_credit_type_id=osct.sales_credit_type_id),risa.sales_credit_type_name) sales_credit_type, risa.sales_credit_percent_split percent, risa.sales_credit_amount_split child_amount from ra_interface_salescredits_all risa ) ric US Show Distributions and Sales Credits 12 &show_dist_order , ric.child_type, ric.child_id US Show Distributions and Sales Credits 13 &show_dist_where and rila.interface_line_id=ric.interface_line_id(+) US Show Distributions and Sales Credits 4B34A0D108ED5F04B2B75EC2133CFCDE Autoinvoice Interface Summary Number of interface lines, number of sales orders and amount per operating unit, batch source, context, request and error messages. ENGINATICS 4 AGE 5 AMOUNT SUM 8 BATCH_SOURCE 2 BILL_TO_ACCOUNT_NUMBER BILL_TO_CUSTOMER CREATION_DATE 6 CURRENCY DESCRIPTION 7 ERROR_MESSAGES GL_DATE 9 INTERFACE_LINE_CONTEXT 3 INTERFACE_LINE_ID INTERFACE_LINE_REFERENCE INVALID_VALUES ITEM 2 LINE_COUNT SUM LINE_TYPE 1 OPERATING_UNIT 1 3 ORDER_NUMBER COUNT_DISTINCT 11 PROCESSED QUANTITY 10 REQUEST_ID 4 SALES_ORDER SALES_ORDER_LINE TRANSACTION_TYPE TRX_DATE TRX_NUMBER UNIT_SELLING_PRICE UOM S Site F0AF3133558E5DA8ADCC32150B09731C Autoinvoice Rejected Lines Interface lines rejected by Autoinvoice with their error messages and invalid values, to correct and resubmit. ENGINATICS 15 AMOUNT 2 BATCH_SOURCE 2 11 BILL_TO_ACCOUNT_NUMBER 10 BILL_TO_CUSTOMER CREATION_DATE 16 CURRENCY 13 DESCRIPTION 18 ERROR_COUNT 19 ERROR_MESSAGES 3 17 GL_DATE 4 INTERFACE_LINE_CONTEXT INTERFACE_LINE_ID 5 INTERFACE_LINE_REFERENCE 20 INVALID_VALUES 12 ITEM LINE_COUNT 8 LINE_TYPE 1 OPERATING_UNIT 1 ORDER_NUMBER PROCESSED 14 QUANTITY 3 REQUEST_ID 6 SALES_ORDER SALES_ORDER_LINE 9 TRANSACTION_TYPE TRX_DATE 7 TRX_NUMBER UNIT_SELLING_PRICE UOM Errors Only Y S Site