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
             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
FND_NUMBER_1
 
Number
FND_NUMBER15
 
Number
Calling Report
 
p_enable_347
 
Number
p_enable_minvalue
 
Number
Enable Medium
 
FND_NUMBER
 
Number
FND_NUMBER_2
 
Number
FND_NUMBER_1
 
Number
JEES_MODELO_340_ENABLE
 
Number
Collective Declaration
 
LOV Oracle
Voucher Number
 
City
 
Postal Code
 
Street Number
 
Street Name
 
Street Type
 
LOV Oracle
Presenter VAT Identifier
 
Reporting Site Operating Unit
 
LOV Oracle
Declaration Type
 
LOV Oracle
Electronic Code
 
Period (Modelo 340)
 
LOV Oracle
Change in Declaration Periodicity
 
LOV Oracle
Period (for Modelo 349 only)
 
LOV Oracle
Period To (for Modelo 349 only)
 
LOV Oracle
Period From (for Modelo 349 only)
 
LOV Oracle
Minimum Cash Amount Received
 
Number
Minimum Amount Value
 
Medium
 
LOV Oracle
Tax Office Province and Region Codes
 
Total Purchase
 
Total Sales
 
Second Activity Code
 
Second Activity
 
Main Activity Code
 
Main Activity
 
Reference Number
 
Contact Telephone Code
 
Tax Office
 
Contact Name
 
Contact Telephone Number
 
Driving Date
 
LOV Oracle
Modelo Name
 
LOV Oracle
Tax Calendar Year
 
LOV Oracle
Reporting Identifier
 
LOV Oracle