JE EMEA VAT: Spanish Modelo Extract
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Spanish Modelo Extract
Application: European Localizations
Source: EMEA VAT: Spanish Modelo Extract
Short Name: JEESMODELOEXT_XMLP
DB package: je_es_modelo_ext_pkg
Description: Spanish Modelo Extract
Application: European Localizations
Source: EMEA VAT: Spanish Modelo Extract
Short Name: JEESMODELOEXT_XMLP
DB package: je_es_modelo_ext_pkg
Run
JE EMEA VAT: Spanish Modelo Extract and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT RECORD_TYPE, MODEL, YEAR, DECLARING_NIF, DECLARED_NIF, LEGAL_REP_NIF, DECLARED_NAME, COUNTRY_CODE, KEY_ID, FISCAL_ID, BOOK_TYPE, TRANSACTION_CODE, ISSUE_DATE, TRANSACTION_DATE, TAX_RATE , DECODE(SIGN(SUM(TAXABLE_AMT_ORIG)), -1, 'N', ' ') || SUBSTR(TO_CHAR(SUM(TAXABLE_AMT_ORIG), '00000000000D00'), 2, 11) || substrb(TO_CHAR(SUM(TAXABLE_AMT_ORIG), '00000000000D00'), 14, 2) TAXABLE_AMT, decode(SIGN(sum(TAX_AMT_ORIG)), -1, 'N', ' ') || substr(to_char(sum(TAX_AMT_ORIG), '00000000000D00'), 2, 11) || substrb(to_char(sum(TAX_AMT_ORIG), '00000000000D00'), 14, 2) TAX_AMT, decode(SIGN(sum(INV_TOTAL_AMT_ORIG)), -1, 'N', ' ') || substr(to_char(sum(INV_TOTAL_AMT_ORIG), '00000000000D00'), 2, 11) || substrb(to_char(sum(INV_TOTAL_AMT_ORIG), '00000000000D00'), 14, 2)INV_TOTAL_AMT, decode(SIGN(nvl(sum(COST_TAX_AMT1),0)), -1, 'N', ' ') || substr(to_char(nvl(sum(COST_TAX_AMT1),0), '00000000000D00'), 2, 11) || substrb(to_char(nvl(sum(COST_TAX_AMT1),0), '00000000000D00'), 14, 2) COST_TAX_AMT, INVOICE_IDENT, REGISTER_NUMBER, NUM_OF_INVOICES, NUM_OF_RECORDS, ACCUM_INT_IDENT, decode(SIGN(nvl(sum(DEDUCTABLE_AMT),0)), -1, 'N', ' ') || substr(to_char(sum(DEDUCTABLE_AMT), '00000000000D00'), 2, 11) || substrb(to_char(sum(DEDUCTABLE_AMT), '00000000000D00'), 14, 2) DEDUCTABLE_AMT, TRANSACTION_DATE_ORIG, ISSUE_DATE_ORIG, sum(TAXABLE_AMT_ORIG) TAXABLE_AMT_ORIG, sum(TAX_AMT_ORIG) TAX_AMT_ORIG, sum(INV_TOTAL_AMT_ORIG) INV_TOTAL_AMT_ORIG, sum(TAX_RATE_ORIG) TAX_RATE_ORIG , SUBSTR(TO_CHAR(SUM(PAYMENT_AMT_ORIG), '00000000000D00'), 2, 11) || substrb(TO_CHAR(SUM(PAYMENT_AMT_ORIG), '00000000000D00'), 14, 2) PAYMENT_AMT ,sum(PAYMENT_AMT_ORIG) PAYMENT_AMT_ORIG ,PAYMENT_DATE_ORIG ,PAYMENT_DATE ,PAYMENT_REFERENCE ,PAYMENT_METHOD_CODE ,DEFERRED_TRX FROM (SELECT '2' RECORD_TYPE ,jg_info_v1 MODEL ,jg_info_v20 YEAR ,jg_info_v2 DECLARING_NIF ,jg_info_v4 DECLARED_NIF ,null LEGAL_REP_NIF ,jg_info_v5 DECLARED_NAME ,jg_info_v6 COUNTRY_CODE ,jg_info_v7 KEY_ID ,jg_info_v8 FISCAL_ID ,jg_info_v9 BOOK_TYPE ,jg_info_v10 TRANSACTION_CODE ,to_char(jg_info_d1, 'YYYYMMDD') ISSUE_DATE ,to_char(jg_info_d2, 'YYYYMMDD') TRANSACTION_DATE ,substr(to_char(jg_info_n1, '000D00'), 2, 3) || substr(to_char(jg_info_n1, '000D00'), 6, 2) TAX_RATE ,sum(jg_info_n5) COST_TAX_AMT1 ,jg_info_v11 INVOICE_IDENT ,jg_info_v12 REGISTER_NUMBER ,'1' NUM_OF_INVOICES ,decode (jg_info_v10,'C',row_number () over (partition by jg_info_v1 ,jg_info_v20 ,jg_info_v2 ,jg_info_v4 ,jg_info_v5 ,jg_info_v6 ,jg_info_v7 ,jg_info_v8 ,jg_info_v9 -- ,jg_info_v10 ,to_char(jg_info_d1, 'YYYYMMDD') ,to_char(jg_info_d2, 'YYYYMMDD') -- ,substr(to_char(jg_info_n1, '000D00'), 2, 3) || substr(to_char(jg_info_n1, '000D00'), 6, 2) ,jg_info_v11 ,jg_info_v12 ,to_char(jg_info_d2,'YYYY-MM-DD') ,to_char(jg_info_d1,'YYYY-MM-DD') order by jg_info_v5,jg_info_v11,jg_info_v31 nulls first) ,1) NUM_OF_RECORDS -- Bug 16236159 ,null ACCUM_INT_IDENT , sum(jg_info_n7) DEDUCTABLE_AMT ,to_char(jg_info_d2,'YYYY-MM-DD') TRANSACTION_DATE_ORIG ,to_char(jg_info_d1,'YYYY-MM-DD') ISSUE_DATE_ORIG ,sum(jg_info_n2) TAXABLE_AMT_ORIG ,sum(jg_info_n3) TAX_AMT_ORIG ,sum(jg_info_n4) INV_TOTAL_AMT_ORIG ,jg_info_n1 TAX_RATE_ORIG ,to_char(jg_info_d3,'YYYY-MM-DD') PAYMENT_DATE_ORIG ,to_char(jg_info_d3, 'YYYYMMDD') PAYMENT_DATE ,jg_info_v33 PAYMENT_REFERENCE ,sum(nvl(jg_info_n11,0)) PAYMENT_AMT_ORIG ,jg_info_v35 PAYMENT_METHOD_CODE ,jg_info_v36 DEFERRED_TRX FROM JG_ZZ_VAT_TRX_GT WHERE NVL(jg_info_v30, 'X') <> 'H' AND jg_info_v1 = '340' AND jg_info_v9 in ('R','S') AND :P_MODELO = '340' GROUP BY '2' ,jg_info_v1 ,jg_info_v20 ,jg_info_v2 ,jg_info_v4 ,null ,jg_info_v5 ,jg_info_v6 ,jg_info_v7 ,jg_info_v8 ,jg_info_v9 ,jg_info_v10 ,to_char(jg_info_d1, 'YYYYMMDD') ,to_char(jg_info_d2, 'YYYYMMDD') ,substr(to_char(jg_info_n1, '000D00'), 2, 3) || substr(to_char(jg_info_n1, '000D00'), 6, 2) ,jg_info_v11 ,jg_info_v31 -- Bug 16236159 ,jg_info_v32 -- Bug 16236159 ,jg_info_v12 ,'1' ,null ,to_char(jg_info_d2,'YYYY-MM-DD') ,to_char(jg_info_d1,'YYYY-MM-DD') ,jg_info_n1 --19350438 ,to_char(jg_info_d3, 'YYYYMMDD') ,to_char(jg_info_d3,'YYYY-MM-DD') ,jg_info_v33 ,jg_info_v35 ,jg_info_v36 HAVING (sum(jg_info_n2) <> 0 OR sum(jg_info_n3) <> 0 OR sum(jg_info_n4) <> 0 OR sum(nvl(jg_info_n11,0)) <> 0 ) ORDER BY jg_info_v11 desc ) T_TABLE group by RECORD_TYPE, MODEL, YEAR, DECLARING_NIF, DECLARED_NIF, LEGAL_REP_NIF, DECLARED_NAME, COUNTRY_CODE, KEY_ID, FISCAL_ID, BOOK_TYPE, TRANSACTION_CODE, ISSUE_DATE, TRANSACTION_DATE, TAX_RATE, INVOICE_IDENT, REGISTER_NUMBER, NUM_OF_INVOICES, NUM_OF_RECORDS, ACCUM_INT_IDENT, TRANSACTION_DATE_ORIG, ISSUE_DATE_ORIG ,PAYMENT_DATE_ORIG ,PAYMENT_DATE ,PAYMENT_REFERENCE ,PAYMENT_METHOD_CODE ,DEFERRED_TRX HAVING (sum(TAXABLE_AMT_ORIG) <> 0 OR sum(TAX_AMT_ORIG) <> 0 OR sum(INV_TOTAL_AMT_ORIG) <> 0 OR sum(PAYMENT_AMT_ORIG) <> 0) |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Reporting Identifier |
|
LOV Oracle | |
| Tax Calendar Period |
|
LOV Oracle | |
| Source |
|
LOV Oracle |
