JE Spanish Annual Modelo Magnetic Format

Description

Categories: BI Publisher
Application: European Localizations
Source: Spanish Annual Modelo Magnetic Format Report
Short Name: JEESAMOR_XMLP
DB package: je_es_modelo_ext_pkg

Imported from BI Publisher
Spanish Annual Modelo Magnetic Format Reporta

SQL 164 lines

             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)

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)

Parameters 35

Parameter	Type	List of values SQL
Reporting Identifier	LOV Oracle
Tax Calendar Year	LOV Oracle
Modelo Name	LOV Oracle
Driving Date	LOV Oracle
Contact Telephone Number	Char
Contact Name	Char
Tax Office	Char
Contact Telephone Code	Char
Reference Number	Char
Main Activity	Char
Main Activity Code	Char
Second Activity	Char
Second Activity Code	Char
Total Sales	Char
Total Purchase	Char
Tax Office Province and Region Codes	Char
Medium	LOV Oracle
Minimum Amount Value	Char
Minimum Cash Amount Received	Number
Period From (for Modelo 349 only)	LOV Oracle
Period To (for Modelo 349 only)	LOV Oracle
Period (for Modelo 349 only)	LOV Oracle
Change in Declaration Periodicity	LOV Oracle
Period (Modelo 340)	LOV Oracle
Electronic Code	Char
Declaration Type	LOV Oracle
Reporting Site Operating Unit	LOV Oracle
Presenter VAT Identifier	Char
Street Type	LOV Oracle
Street Name	Char
Street Number	Char
Postal Code	Char
City	Char
Voucher Number	Char
Collective Declaration	LOV Oracle

Description

SQL 164 lines

Parameters 35

Find out more

Features

Download

FAQ