ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F1383869E053B46B63588994Y select extract_source_ledger, doc_seq_name, doc_seq_value, trx_id, trx_number, trx_line_number, taxable_line_amt, tax_line_amt, accounting_date, tax_box_recoverable, tax_box_non_recoverable, taxable_box, output_box, period_type, vat_box_allocation_id, financial_document_type, vat_transaction_type, tax_code, tax_status, tax_jurisdiction_code, tax_rate_id, tax_rate_code, final_reporting_id, tax_registration_number, entity_identifier, tax_line_id, allocation_rule_id, cancel_flag, reverse_flag, taxable_item_source_id, recoverable_flag, self_assessed_flag from ( select jzvtd.extract_source_ledger , jzvtd.doc_seq_name , jzvtd.doc_seq_value , jzvtd.trx_id , jzvtd.trx_number , jzvtd.trx_line_number , sum(decode(jzvtd.tax_recoverable_flag,'N',0,nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt) * decode(jzvar.taxable_non_rec_sign_flag, '-', -1, 1))) taxable_line_amt , sum (NVL(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0) * decode(jzvtd.extract_source_ledger, 'AR', decode(jzvar.tax_rec_sign_flag, '-', -1, 1), 'AP', decode(jzvba.tax_recoverable_flag, 'Y', decode(jzvar.tax_rec_sign_flag, '-', -1, 1), decode(jzvar.tax_non_rec_sign_flag, '-', -1, 1)), decode(jzvar.tax_non_rec_sign_flag, '-', -1, 1)) )tax_line_amt , jzvtd.accounting_date , '-XX' tax_box_recoverable , '-XX' tax_box_non_recoverable , '-XX' taxable_box ,max( decode(jzvtd.extract_source_ledger,'AP',decode(jzvar.self_assessed_flag,'Y',nvl(jzvba.output_box,'-XX'),'-XX'),'-XX')) output_box , jzvba.period_type ,max( jzvba.vat_box_allocation_id) vat_box_allocation_id , jzvar.financial_document_type , jzvar.vat_transaction_type , jzvar.tax_code , jzvar.tax_status , jzvar.tax_jurisdiction_code , jzvar.tax_rate_id , jzvar.tax_rate_code , jzvrs.final_reporting_process_id final_reporting_id , jzvrs.tax_registration_number , jzvre.entity_identifier , jzvtd.tax_line_id , jzvba.allocation_rule_id , nvl(jzvtd.cancel_flag,'N') cancel_flag , nvl(jzvtd.reverse_flag,'N') reverse_flag , jzvtd.taxable_item_source_id , nvl(jzvtd.tax_recoverable_flag,'Y') recoverable_flag ,nvl(jzvtd.self_assessed_flag,'N') self_assessed_flag from jg_zz_vat_alloc_rules jzvar , jg_zz_vat_box_allocs jzvba , jg_zz_vat_rep_status jzvrs , jg_zz_vat_trx_details jzvtd , jg_zz_vat_rep_entities jzvre where jzvba.vat_transaction_id = jzvtd.vat_transaction_id and jzvre.vat_reporting_entity_id = :p_vat_reporting_entity_id and jzvre.vat_reporting_entity_id = jzvrs.vat_reporting_entity_id and ((jzvre.entity_type_code = 'ACCOUNTING' and jzvre.mapping_vat_rep_entity_id = jzvar.vat_reporting_entity_id ) OR (jzvre.entity_type_code = 'LEGAL' and jzvre.vat_reporting_entity_id = jzvar.vat_reporting_entity_id)) and jzvrs.reporting_status_id = jzvtd.reporting_status_id and jzvrs.source = jzvtd.extract_source_ledger and jzvba.allocation_rule_id = jzvar.allocation_rule_id and jzvrs.tax_calendar_period = :P_PERIOD_NAME and (:p_source is null or :p_source = 'ALL' or jzvtd.extract_source_ledger = :P_SOURCE) and (:p_vat_tran_type is null or jzvar.vat_transaction_type = :P_VAT_TRAN_TYPE) and (:p_doc_sequence_name is null or jzvtd.doc_seq_id = :p_doc_sequence_name) and (:p_from_doc_seq_number is null or jzvtd.doc_seq_value >= :p_from_doc_seq_number) and (:p_to_doc_seq_number is null or jzvtd.doc_seq_value <= :p_to_doc_seq_number) and not exists (select 1 from jg_zz_vat_box_errors where vat_transaction_id = jzvba.vat_transaction_id) and jzvtd.self_assessed_flag ='Y' and jzvar.self_assessed_flag ='Y' and jzvtd.extract_source_ledger = 'AP' Group by jzvtd.extract_source_ledger , jzvtd.doc_seq_name , jzvtd.doc_seq_value , jzvtd.trx_id , jzvtd.trx_number , jzvtd.trx_line_number -- , nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt) * decode(jzvar.taxable_non_rec_sign_flag, '-', -1, 1) , jzvtd.accounting_date , jzvba.period_type -- , jzvba.vat_box_allocation_id , jzvar.financial_document_type , jzvar.vat_transaction_type , jzvar.tax_code , jzvar.tax_status , jzvar.tax_jurisdiction_code , jzvar.tax_rate_id , jzvar.tax_rate_code , jzvrs.final_reporting_process_id , jzvrs.tax_registration_number , jzvre.entity_identifier , jzvtd.tax_line_id , jzvba.allocation_rule_id , nvl(jzvtd.cancel_flag,'N') , nvl(jzvtd.reverse_flag,'N') , jzvtd.taxable_item_source_id , nvl(jzvtd.tax_recoverable_flag,'Y') ,jzvtd.self_assessed_flag union select jzvtd.extract_source_ledger , jzvtd.doc_seq_name , jzvtd.doc_seq_value , jzvtd.trx_id , jzvtd.trx_number , jzvtd.trx_line_number , nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt) * decode(jzvar.taxable_non_rec_sign_flag, '-', -1, 1) taxable_line_amt , NVL(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0) * decode(jzvtd.extract_source_ledger, 'AR', decode(jzvar.tax_rec_sign_flag, '-', -1, 1), 'AP', decode(jzvba.tax_recoverable_flag, 'Y', decode(jzvar.tax_rec_sign_flag, '-', -1, 1), decode(jzvar.tax_non_rec_sign_flag, '-', -1, 1)), decode(jzvar.tax_non_rec_sign_flag, '-', -1, 1)) tax_line_amt , jzvtd.accounting_date , decode(decode(jzvtd.extract_source_ledger,'AR','Y',nvl(jzvba.tax_recoverable_flag,'N')) ,'Y',nvl(jzvba.tax_box,'-XX'),'-XX') tax_box_recoverable , decode(decode(jzvtd.extract_source_ledger,'AR','Y',nvl(jzvba.tax_recoverable_flag,'N')) ,'N',nvl(jzvba.tax_box,'-XX'),'-XX') tax_box_non_recoverable , nvl(jzvba.taxable_box,'-XX') taxable_box , '-XX' output_box , jzvba.period_type , jzvba.vat_box_allocation_id , jzvar.financial_document_type , jzvar.vat_transaction_type , jzvar.tax_code , jzvar.tax_status , jzvar.tax_jurisdiction_code , jzvar.tax_rate_id , jzvar.tax_rate_code , jzvrs.final_reporting_process_id final_reporting_id , jzvrs.tax_registration_number , jzvre.entity_identifier , jzvtd.tax_line_id , jzvba.allocation_rule_id , nvl(jzvtd.cancel_flag,'N') cancel_flag , nvl(jzvtd.reverse_flag,'N') reverse_flag , jzvtd.taxable_item_source_id , nvl(jzvtd.tax_recoverable_flag,'Y') recoverable_flag -- For AR, this flag will be null ,nvl(jzvtd.self_assessed_flag,'N') self_assessed_flag from jg_zz_vat_alloc_rules jzvar , jg_zz_vat_box_allocs jzvba , jg_zz_vat_rep_status jzvrs , jg_zz_vat_trx_details jzvtd , jg_zz_vat_rep_entities jzvre where jzvba.vat_transaction_id = jzvtd.vat_transaction_id and jzvre.vat_reporting_entity_id = :p_vat_reporting_entity_id and jzvre.vat_reporting_entity_id = jzvrs.vat_reporting_entity_id and ((jzvre.entity_type_code = 'ACCOUNTING' and jzvre.mapping_vat_rep_entity_id = jzvar.vat_reporting_entity_id ) OR (jzvre.entity_type_code = 'LEGAL' and jzvre.vat_reporting_entity_id = jzvar.vat_reporting_entity_id)) and jzvrs.reporting_status_id = jzvtd.reporting_status_id and jzvrs.source = jzvtd.extract_source_ledger and jzvba.allocation_rule_id = jzvar.allocation_rule_id and jzvrs.tax_calendar_period = :P_PERIOD_NAME and (:p_source is null or :p_source = 'ALL' or jzvtd.extract_source_ledger = :P_SOURCE) and (:p_vat_tran_type is null or jzvar.vat_transaction_type = :P_VAT_TRAN_TYPE) and (:p_doc_sequence_name is null or jzvtd.doc_seq_id = :p_doc_sequence_name) and (:p_from_doc_seq_number is null or jzvtd.doc_seq_value >= :p_from_doc_seq_number) and (:p_to_doc_seq_number is null or jzvtd.doc_seq_value <= :p_to_doc_seq_number) and not exists (select 1 from jg_zz_vat_box_errors where vat_transaction_id = jzvba.vat_transaction_id)) order by 14,25,24,1,5,4,26,27,30,28,29,31 desc JG JGZZALST AR JG ضريبة القيمة المضافة لـ EMEA: قائمة التخصيصات Application: المحليات الأقليمية Source: ضريبة القيمة المضافة لـ EMEA: قائمة التخصيصات Short Name: JGZZALST DB package: D JG EMEA USt: Umlagen Application: Regionale Lokalisierungen Source: EMEA USt: Umlagen Short Name: JGZZALST DB package: F JG EMEA - TVA : Liste des répartitions Application: Regional Localizations Source: EMEA - TVA : Liste des répartitions Short Name: JGZZALST DB package: US JG EMEA VAT: Allocations Listing Application: Regional Localizations Source: EMEA VAT: Allocations Listing Short Name: JGZZALST DB package: ZHS JG EMEA 增值税：分摊列表 Application: 区域性本地化 Source: EMEA 增值税：分摊列表 Short Name: JGZZALST DB package: BI Publisher :p_doc_sequence_name :p_from_doc_seq_number :p_period_name :p_source :p_to_doc_seq_number :p_vat_reporting_entity_id :p_vat_tran_type 1 10 :p_vat_reporting_entity_id LOV Oracle JGZZ_VAT_REPORTING_ENTITY_ID Y select vat_reporting_entity_id id, entity_identifier value, null description from jg_zz_vat_rep_entities order by value,description Y AR معرف إعداد التقرير D Kennung Steuerpflichtiger F Identifiant de reporting US Reporting Identifier ZHS 申报标识 2 20 :p_period_name LOV Oracle JGZZ_TAX_CALENDAR_PERIOD_ENTITY Y select rps.tax_calendar_period id, rps.tax_calendar_period value, null description from jg_zz_vat_rep_status rps where rps.vat_reporting_entity_id=:$flex$.jgzz_vat_reporting_entity_id and rps.rowid in ( select max(rps1.rowid) from jg_zz_vat_rep_status rps1 where rps1.vat_reporting_entity_id=:$flex$.jgzz_vat_reporting_entity_id group by rps1.tax_calendar_period) order by period_start_date Y AR اسم الفترة D Periodenbezeichnung F Nom de la période US Period Name ZHS 期间名称 3 30 :p_source LOV Oracle JGZZ_VAT_SOURCES Y select lookup_code id, lookup_code value, meaning description from fnd_lookup_values where lookup_type='JGZZ_SOURCE' and enabled_flag='Y' and sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate) and language=userenv('LANG') order by value,description AR المصدر D Herkunft F Origine US Source ZHS 来源 4 40 :p_vat_tran_type LOV Oracle JGZZ_VAT_TRANS_TYPES Y select lookup_code id, lookup_code value, meaning description from fnd_lookup_values where lookup_type='ZX_JEBE_VAT_TRANS_TYPE' and enabled_flag='Y' and sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate) and language=userenv('LANG') order by value,description AR نوع حركة ضريبة القيمة المضافة D USt-Transaktionsart F Type de mouvement de TVA US VAT Transaction Type ZHS 增值税事务处理类型 5 50 :p_doc_sequence_name LOV Oracle JGZZ_VAT_DOC_SEQ_NAME Y select doc_sequence_id id, name value, null description from fnd_document_sequences where doc_sequence_id in (select distinct doc_sequence_id from fnd_doc_sequence_assignments where application_id in (200,222,101) and type in('A','G')) order by name AR اسم مسلسل المستند D Belegfolge F Nom de séquence de document US Document Sequence Name ZHS 单据序号名 6 60 :p_from_doc_seq_number Number AR من رقم مسلسل المستند D Von Belegnummer F Du numéro de pièce US From Document Sequence Number ZHS 自单据序号 7 70 :p_to_doc_seq_number Number AR إلى رقم مسلسل المستند D Bis Belegnummer F Au numéro de pièce US To Document Sequence Number ZHS 至单据序号