JE Spanish Annual Modelo Magnetic Format

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Spanish Annual Modelo Magnetic Format Reporta
Application: European Localizations
Source: Spanish Annual Modelo Magnetic Format Report
Short Name: JEESAMOR_XMLP
DB package: je_es_modelo_ext_pkg
Run JE Spanish Annual Modelo Magnetic Format 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 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
Blitz Report™