JE Spanish Periodic Modelo

Description
Categories: BI Publisher
Columns: Record Type, Model, Year, Declaring Nif, Declared Nif, Legal Rep Nif, Declared Name, Country Code, Key Id, Fiscal Id ...
Application: European Localizations
Source: Spanish Periodic Modelo Report
Short Name: JEESPMOR_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
Reporting Identifier
 
LOV Oracle
Tax Calendar Period From
 
LOV Oracle
Tax Calendar Period To
 
LOV Oracle
Modelo Name
 
LOV Oracle
Driving Date
 
LOV Oracle
Source
 
LOV Oracle
Minimum Invoiced Amount
 
Minimum Cash Amount Received
 
Number
Reporting Site Operating Unit
 
LOV Oracle
JEES_MODELO_340_ENABLE
 
Number
FND_NUMBER
 
Number
Enable Minimum Amount field
 
Number
p_enable_347
 
Number
Calling Report
 
FND_NUMBER_2
 
Number