ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 49A8B1A8037008ACE0530100007FEB91Y select haouv.name operating_unit, rcta.trx_number trx_number, rcta.trx_date, xxen_util.meaning(rctta.type,'INV/CM/ADJ',222) class, rctta.name type, rcta.ct_reference reference, ( select nvl2(acia0.cons_billing_number,acia0.cons_billing_number||' - ',null)||rcta0.trx_number credited_invoice from ra_customer_trx_all rcta0, ar_cons_inv_trx_all acita0, ar_cons_inv_all acia0 where rcta.previous_customer_trx_id=rcta0.customer_trx_id and rcta0.customer_trx_id=acita0.customer_trx_id(+) and acita0.cons_inv_id=acia0.cons_inv_id(+) ) credited_invoice, hca.account_number, hp.party_name, hcsua.location bill_to_location, hz_format_pub.format_address (hps.location_id,null,null,' , ') bill_to_address, hp.jgzz_fiscal_code taxpayer_id, rcta.invoice_currency_code currency, (select sum(rctla.extended_amount) from ra_customer_trx_lines_all rctla where rcta.customer_trx_id=rctla.customer_trx_id and rctla.line_type in ('LINE','CB','CHARGES')) amount, decode(rcta.status_trx,'CL','Closed','Open') state, rcta.status_trx status, rtv.name payment_term, decode(rcta.invoicing_rule_id,-3,'Arrears',-2,'Advance') invoicing_rule, rcta.term_due_date, rcta.ship_date_actual ship_date, xxen_util.meaning(rcta.printing_option,'INVOICE_PRINT_OPTIONS',222) print_option, rcta.printing_original_date first_printed_date, rcta.customer_reference, rcta.comments, jrret.resource_name sales_rep, nvl(rcta.interface_header_context,rbsa.name) category, xxen_util.user_name(rcta.created_by) created_by, xxen_util.client_time(rcta.creation_date) creation_date, xxen_util.user_name(rcta.last_updated_by) last_updated_by, xxen_util.client_time(rcta.last_update_date) last_update_date, arm.name receipt_method &il_reg_columns &il_columns_amt &il_columns_rate &il_columns_rec &il_columns_tp &payment_method_column from hr_all_organization_units_vl haouv, ra_customer_trx_all rcta, oe_sys_parameters_all ospa, ra_batch_sources_all rbsa, ra_cust_trx_types_all rctta, ra_terms_vl rtv, hz_cust_accounts hca, hz_parties hp, hz_cust_site_uses_all hcsua, hz_cust_acct_sites_all hcasa, hz_party_sites hps, jtf_rs_salesreps jrs, jtf_rs_resource_extns_tl jrret, ar_receipt_methods arm &payment_method_table &il_from where 1=1 and rcta.complete_flag='N' and rcta.org_id=haouv.organization_id(+) and rcta.org_id=ospa.org_id(+) and ospa.parameter_code(+)='MASTER_ORGANIZATION_ID' and rcta.term_id=rtv.term_id(+) and rcta.cust_trx_type_id=rctta.cust_trx_type_id(+) and rcta.org_id=rctta.org_id(+) and nvl2(rcta.interface_header_context,null,rcta.batch_source_id)=rbsa.batch_source_id(+) and nvl2(rcta.interface_header_context,null,rcta.org_id)=rbsa.org_id(+) and rcta.bill_to_customer_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) and rcta.bill_to_site_use_id=hcsua.site_use_id(+) and hcsua.cust_acct_site_id=hcasa.cust_acct_site_id(+) and hcasa.party_site_id=hps.party_site_id(+) and case when rcta.primary_salesrep_id>0 then rcta.primary_salesrep_id end=jrs.salesrep_id(+) and case when rcta.primary_salesrep_id>0 then rcta.org_id end=jrs.org_id(+) and jrs.resource_id=jrret.resource_id(+) and jrret.language(+)=userenv('lang') and rcta.receipt_method_id=arm.receipt_method_id(+) &payment_method_join &il_where order by haouv.name, rcta.trx_date desc, rcta.trx_number desc Added India GST columns, displayed only when India Localization is installed. AR AR Incomplete Transactions تقرير المعاملات غير مكتمل D AR Incomplete Transactions Bericht über unvollständige Transaktionen im Detail E AR Incomplete Transactions Informe detallado de transacciones incompletas F AR Incomplete Transactions Rapport de transaction détaillé et incomplet I AR Incomplete Transactions Rapporto dettagliato sulle transazioni incomplete JA AR Incomplete Transactions 不完全なトランザクションレポートの詳細 KO AR Incomplete Transactions 상세 불완전 거래 보고서 PTB AR Incomplete Transactions Detalhe do relatório de transação incompleto RU AR Incomplete Transactions Подробный неполный отчет об операции S AR Incomplete Transactions Detalj ofullständig transaktionsrapport TR AR Incomplete Transactions Ayrıntılı tamamlanmamış işlem raporu US AR Incomplete Transactions Detail incomplete transaction report When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, CGST Amount, SGST Amount, IGST Amount, CESS Amount, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code, HSN Length Warning, CGST Rate, SGST Rate, IGST Rate, CESS Rate, CGST Recoverable, CGST Non-Recoverable, SGST Recoverable, SGST Non-Recoverable, IGST Recoverable, IGST Non-Recoverable, Reverse Charge Flag, Place of Supply, Ship From State. ZHS AR Incomplete Transactions 详细的不完整交易报告 Enginatics &il_columns_amt &il_columns_rate &il_columns_rec &il_columns_tp &il_from &il_reg_columns &il_where &payment_method_column &payment_method_join &payment_method_table 1=1 1 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &il_columns_amt il_tax.cgst_amount "CGST Amount", il_tax.sgst_amount "SGST Amount", il_tax.igst_amount "IGST Amount", il_tax.cess_amount "CESS Amount", il_tax.custom_amount "Custom Amount", il_tax.unclassified_amount "Unclassified Tax Amount", il_tax.taxable_value "Taxable Value", il_tax.hsn_sac_code "HSN SAC Code", il_tax.hsn_length_warning "HSN Length Warning", Y US India Localization 3 &il_columns_rate il_tax.cgst_tax_rate "CGST Rate", il_tax.sgst_tax_rate "SGST Rate", il_tax.igst_tax_rate "IGST Rate", il_tax.cess_tax_rate "CESS Rate", Y US India Localization 4 &il_columns_rec il_tax.cgst_rec_amount "CGST Recoverable", il_tax.cgst_nrec_amount "CGST Non-Recoverable", il_tax.sgst_rec_amount "SGST Recoverable", il_tax.sgst_nrec_amount "SGST Non-Recoverable", il_tax.igst_rec_amount "IGST Recoverable", il_tax.igst_nrec_amount "IGST Non-Recoverable", Y US India Localization 5 &il_columns_tp xxen_util.meaning(il_tax.reverse_charge_flag,'YES_NO',0) "Reverse Charge Flag", (select max(jtdf.ship_to_state) from jai_tax_det_factors jtdf where jtdf.trx_id=rcta.customer_trx_id and jtdf.application_id=222) "Place of Supply", (select max(jtdf.ship_from_state) from jai_tax_det_factors jtdf where jtdf.trx_id=rcta.customer_trx_id and jtdf.application_id=222) "Ship From State" Y US India Localization 6 &il_from ,(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, 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, 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,'CGST',jtlv.rounded_taxable_amt_trx_curr,'IGST',jtlv.rounded_taxable_amt_trx_curr)) taxable_value, max(jrc.reporting_code) hsn_sac_code, max(case when length(jrc.reporting_code)<6 then 'Below 6 digits' end) hsn_length_warning from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav, jai_tax_det_factors jtdf, jai_reporting_codes jrc where jtlv.tax_type_id=jrav.entity_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(+)=222 and nvl(jtdf.hsn_code_id,jtdf.sac_code_id)=jrc.reporting_code_id(+) and jtlv.application_id=222 group by jtlv.trx_id ) il_tax Y US India Localization 7 &il_reg_columns ,nvl(coalesce( ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from hz_cust_site_uses_all hcsua, jai_party_regs_v jpr, jai_party_reg_lines_v jprl where rcta.bill_to_site_use_id=hcsua.site_use_id and hcsua.cust_acct_site_id=jpr.party_site_id and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.customer_flag='Y' and jpr.party_reg_id=jprl.party_reg_id and jprl.registration_type_code in ('GST','GSTIN') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.effective_from<=rcta.trx_date and (jprl.effective_to is null or jprl.effective_to>=rcta.trx_date) and rownum=1 ), ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from hz_cust_site_uses_all hcsua, jai_party_regs_v jpr, jai_party_reg_lines_v jprl where rcta.bill_to_site_use_id=hcsua.site_use_id and hcsua.cust_acct_site_id=jpr.party_site_id and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.customer_flag='Y' and jpr.party_reg_id=jprl.party_reg_id and 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') and jprl.effective_from<=rcta.trx_date and (jprl.effective_to is null or jprl.effective_to>=rcta.trx_date) and rownum=1 )),coalesce( ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where rcta.bill_to_customer_id=jpr.party_id and jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.customer_flag='Y' and jprl.registration_type_code in ('GST','GSTIN') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.effective_from<=rcta.trx_date and (jprl.effective_to is null or jprl.effective_to>=rcta.trx_date) and rownum=1 ), ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where rcta.bill_to_customer_id=jpr.party_id and jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.customer_flag='Y' and 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') and jprl.effective_from<=rcta.trx_date and (jprl.effective_to is null or jprl.effective_to>=rcta.trx_date) and rownum=1 ))) "GST Registration No", ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where rcta.bill_to_customer_id=jpr.party_id and jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.customer_flag='Y' and jprl.registration_type_code='PAN' and jprl.effective_from<=rcta.trx_date and (jprl.effective_to is null or jprl.effective_to>=rcta.trx_date) and rownum=1 ) "GST PAN No", Y US India Localization 8 &il_where and rcta.customer_trx_id=il_tax.trx_id(+) Y US India Localization 9 -10 &payment_method_column ,ifpct.payment_channel_name payment_method Char 12 select decode(fnd_release.major_version,'11','11','12') from dual F Version de la version EBS US EBS Release Version 10 &payment_method_column ,replace(initcap(arm.payment_type_code),'_',' ') payment_method 11 F Version de la version EBS US EBS Release Version 11 &payment_method_join and arm.payment_channel_code=ifpct.payment_channel_code(+) and ifpct.language(+)=userenv('lang') 12 F Version de la version EBS US EBS Release Version 12 &payment_method_table ,iby_fndcpt_pmt_chnnls_tl ifpct 12 F Version de la version EBS US EBS Release Version 13 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)) Y F Unité d'exploitation US Operating Unit 14 20 1=1 rctta.type=xxen_util.lookup_code(:transaction_class,'INV/CM/ADJ',222) LOV AR Transaction Class 8E2FF36EDE8079D2E0530100007F1FF2 Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='INV/CM/ADJ' and flvv.view_application_id=222 and flvv.security_group_id=0 order by flvv.meaning F Classe de transaction US Transaction Class 15 30 1=1 rctta.name=:trx_type LOV AR Transaction Type 8E2FF36EDE9A79D2E0530100007F1FF2 Y select rctta.name value, nvl(flvv.meaning,rctta.type)||': '||haouv.name description from hr_all_organization_units_vl haouv, ra_cust_trx_types_all rctta, fnd_lookup_values_vl flvv where (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and haouv.organization_id=rctta.org_id and rctta.type=flvv.lookup_code(+) and flvv.lookup_type(+)='INV/CM/ADJ' and flvv.view_application_id(+)=222 and flvv.security_group_id(+)=0 order by rctta.name, haouv.name F Type de transaction US Transaction Type 16 40 1=1 rcta.interface_header_context=:category LOV AR TRX Interface Context 8E2FF36EDE8179D2E0530100007F1FF2 Y select 'ORDER ENTRY' value, 'Order Entry' description from dual union all select 'PA INVOICES' value, 'Pa Invoices' description from dual union all select 'PA Internal Invoices' value, 'Pa Internal Invoices' description from dual union all select 'PROJECTS INVOICES' value, 'Projects Invoices' description from dual union all select 'OKL_CONTRACTS' value, 'Okl Contracts' description from dual union all select 'OKS CONTRACTS' value, 'Oks Contracts' description from dual union all select 'LOANS' value, 'Loans' description from dual union all select 'CLAIM' value, 'Claim' description from dual union all select 'INTERCOMPANY' value, 'Intercompany' description from dual union all select 'GLOBAL_PROCUREMENT' value, 'Global Procurement' description from dual union all select 'SERVICE' value, 'Service' description from dual union all select 'OTHER' value, 'All other transactions not in above categories' description from dual F Catégorie Contexte de la ligne d'interface pour les rapports d'EI US Category Interface line context for AR reports 17 1=1 nvl(rcta.interface_header_context,'x') not in ( 'ORDER ENTRY', 'PA INVOICES', 'PA Internal Invoices', 'PROJECTS INVOICES', 'OKL_CONTRACTS', 'OKS CONTRACTS', 'LOANS', 'CLAIM', 'INTERCOMPANY', 'GLOBAL_PROCUREMENT', 'SERVICE' ) OTHER F Catégorie US Category 18 50 1=1 rcta.created_by=xxen_util.user_id(:created_by) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name F Créé par US Created By 19 60 1=1 rcta.trx_date>=(select gps.start_date from gl_period_statuses gps where gps.period_name=:period_name and gps.set_of_books_id=(select hou.set_of_books_id from hr_operating_units hou where hou.organization_id=fnd_global.org_id) and gps.application_id=222) and rcta.trx_date<(select gps.end_date+1 from gl_period_statuses gps where gps.period_name=:period_name and gps.set_of_books_id=(select hou.set_of_books_id from hr_operating_units hou where hou.organization_id=fnd_global.org_id) and gps.application_id=222) LOV AR Period 8E2FF36EDEAF79D2E0530100007F1FF2 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.ledger_id= ( select asp.set_of_books_id from ar_system_parameters asp where (:$flex$.ledger is null or asp.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and (:$flex$.operating_unit is null or asp.set_of_books_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and rownum=1 ) and gps.start_date<=sysdate+400 and gps.application_id=222 order by gps.start_date desc F Période US Period 20 70 1=1 rcta.trx_date>=:trx_date_from Date F Date de l'Inv. US Inv. Date From 21 80 1=1 rcta.trx_date<:trx_date_to+1 Date last_day(:$flex$.inv_date_from) F Date de l'inv. US Inv. Date To 22 90 1=1 upper(hp.party_name) like upper(:customer_name) LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name F Nom du client US Customer Name 23 100 1=1 hca.account_number=:account_number LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 Y Y select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name F Numéro de compte US Account Number 24 110 1=1 rcta.trx_number=:trx_number Char F Numéro de transaction US Transaction Number