JE EMEA VAT: Spanish Modelo Extract

Description
Categories: BI Publisher
Application: European Localizations
Source: EMEA VAT: Spanish Modelo Extract
Short Name: JEESMODELOEXT_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
Enable Minimum Amount field
 
Number
FND_NUMBER
 
Number
Source
 
LOV Oracle
Tax Calendar Period
 
LOV Oracle
Reporting Identifier
 
LOV Oracle
Ask a question