JE Italian Polyvalent Declaration - Audit Report (Obsolete)

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Italian Invoices Above Threshold - Auditing Report
Application: European Localizations
Source: Italian Polyvalent Declaration - Audit Report (Obsolete)
Short Name: JEITPDAR_XMLP
DB package: JE_IT_POLYVALENT_DECL_RPT_PKG
Run JE Italian Polyvalent Declaration - Audit Report (Obsolete) and other Oracle EBS reports with Blitz Report™ on our demo environment
  SELECT  ROWNUM SEQ
					,PARTY_NAME
					,LAST_NAME
					,FIRST_NAME
					,DOB
					,INDV_COUNTRY_OF_BIRTH
					,INDV_PROVINCE_CODE
					,FOREIGN_COUNTRY
					,COMPANY_NAME
					,COMPANY_CITY
					,COMP_COUNTRY
					,COMPANY_ADDRESS
					,VAT_REG_NUM
					,TOTAL_AMT_AR
					,TOTAL_TAX_AR
					,TOTAL_AMT_GOODS
					,TOTAL_AMT_SERVICES
					,TOTAL_AMT_AR_C
					,TOTAL_TAX_AR_C
					,TOTAL_AMT_AP
					,TOTAL_TAX_AP
					,TOTAL_AMT
					,TOTAL_AMT_AP_C
					,TOTAL_TAX_AP_C
			FROM    (SELECT   PARTY_NAME
					,LAST_NAME
					,FIRST_NAME
					,DOB 
					,INDV_COUNTRY_OF_BIRTH
					,INDV_PROVINCE_CODE 
					,FOREIGN_COUNTRY
					,COMPANY_NAME
					,COMPANY_CITY
					,COMP_COUNTRY
					,COMPANY_ADDRESS
					,VAT_REG_NUM
					,SUM(TOTAL_AMT_AR) TOTAL_AMT_AR
					,SUM(TOTAL_TAX_AR) TOTAL_TAX_AR
					,SUM(TOTAL_AMT_GOODS) TOTAL_AMT_GOODS
					,SUM(TOTAL_AMT_SERVICES) TOTAL_AMT_SERVICES
					,SUM(TOTAL_AMT_AR_C) TOTAL_AMT_AR_C
					,SUM(TOTAL_TAX_AR_C) TOTAL_TAX_AR_C
					,SUM(TOTAL_AMT_AP) TOTAL_AMT_AP
					,SUM(TOTAL_TAX_AP) TOTAL_TAX_AP
					,SUM(TOTAL_AMT) TOTAL_AMT
					,SUM(TOTAL_AMT_AP_C) TOTAL_AMT_AP_C
					,SUM(TOTAL_TAX_AP_C) TOTAL_TAX_AP_C
			FROM
					(
					  SELECT JRL.PARTY_NAME,
            DECODE(TRX.PARTY_TYPE,'PERSON',TRX.INDV_PARTY_LAST_NAME,NULL) LAST_NAME,
					  DECODE(TRX.PARTY_TYPE,'PERSON',Trx.INDV_PARTY_FIRST_NAME,NULL ) FIRST_NAME,
					  DECODE(TRX.PARTY_TYPE,'PERSON',TO_CHAR(TRX.INDV_PARTY_DOB,'DD-Mon-YYYY'),NULL ) DOB,
					  DECODE(TRX.PARTY_TYPE,'PERSON',TRX.INDV_PARTY_BIRTH_CITY,NULL) INDV_COUNTRY_OF_BIRTH ,
					  DECODE(TRX.PARTY_TYPE,'PERSON',DECODE(Trx.INDV_PARTY_PROVINCE,'IT',Trx.INDV_PARTY_PROVINCE,'EE')) INDV_PROVINCE_CODE,
					  DECODE(TRX.PARTY_TYPE,'PERSON',NVL2(CC.ITALY_CODE,LPAD(CC.ITALY_CODE,3,'0'),NULL)) FOREIGN_COUNTRY, --SHOULD BE NUMERIC
					  DECODE(TRX.PARTY_TYPE,'ORGANIZATION',TRX.PARTY_NAME,NULL) COMPANY_NAME,
					  DECODE(Trx.Party_Type,'ORGANIZATION',Trx.Party_City,NULL) COMPANY_CITY,
					  DECODE(TRX.PARTY_TYPE,'ORGANIZATION',NVL2(CC.ITALY_CODE,LPAD(CC.ITALY_CODE,3,'0'),NULL)) COMP_COUNTRY, --SHOULD BE NUMERIC
					  DECODE(TRX.PARTY_TYPE,'ORGANIZATION',TRX.PARTY_ADDRESS,NULL) COMPANY_ADDRESS,
                      Nvl(Trx.Vat_Registration_Num,Null) Vat_Reg_Num,
					  DECODE(JRL.LISTING_COLUMN_CODE,'NONVAT',0,DECODE(JRL.APPLICATION_ID,'200',(JRL.TAXABLE_AMT),0)) TOTAL_AMT_AP,
					  Decode(Jrl.Listing_Column_Code,'NONVAT',0,Decode(Jrl.Application_Id,'200',Jrl.Tax_Amt,0)) Total_Tax_Ap,
					  DECODE(JRL.LISTING_COLUMN_CODE,'NONVAT',0,DECODE(JRL.APPLICATION_ID,'222',(JRL.TAXABLE_AMT),0)) TOTAL_AMT_AR,
					  DECODE(JRL.LISTING_COLUMN_CODE,'NONVAT',0,DECODE(JRL.APPLICATION_ID,'222',JRL.TAX_AMT,0)) TOTAL_TAX_AR,
					  DECODE(JRL.LISTING_COLUMN_CODE,'NONVAT',DECODE(JRL.APPLICATION_ID,'200',(JRL.TAXABLE_AMT),0),0) TOTAL_AMT,
					  DECODE(JRL.LISTING_COLUMN_CODE,'NONVAT',DECODE(JRL.APPLICATION_ID,'222',DECODE(PRODUCT_TYPE,'G',(JRL.TAXABLE_AMT),0),0),0) TOTAL_AMT_GOODS,
					  DECODE(JRL.LISTING_COLUMN_CODE,'NONVAT',DECODE(JRL.APPLICATION_ID,'222',DECODE(PRODUCT_TYPE,'S',(JRL.TAXABLE_AMT),0),0),0) TOTAL_AMT_SERVICES,
					  0 TOTAL_AMT_AP_C,
					  0 TOTAL_TAX_AP_C,
					  0 TOTAL_AMT_AR_C,
					  0 TOTAL_TAX_AR_C
					FROM Je_It_Rpt_Trx_Lines_All Jrl,
					  Je_It_Rpt_Trx_All Trx,
					  (SELECT DISTINCT Country_Code,
						Italian_Country_Code Italy_Code
					  FROM JE_IT_COUNTRY_REP_CODES
					  ) Cc
					Where Jrl.Request_Id         = :G_Conc_Request_Id
          AND Jrl.Trx_Type not In ('CREDIT','DEBIT','CM','DM')
					AND JRL.REQUEST_ID           = TRX.REQUEST_ID
					AND JRL.PARTY_ID = TRX.PARTY_ID
					And JRL.Party_Site_Id     = TRX.Party_Site_Id
					and jrl.is_above_threshold_flag = 'Y'
					And 	TRX.Report_Section = 'BL'
					AND TRX.COUNTRY     = CC.COUNTRY_CODE (+)
					UNION ALL
					SELECT JRL.PARTY_NAME,
						DECODE(TRX.PARTY_TYPE,'PERSON',TRX.INDV_PARTY_LAST_NAME,NULL) LAST_NAME,
						DECODE(TRX.PARTY_TYPE,'PERSON',Trx.INDV_PARTY_FIRST_NAME,NULL ) FIRST_NAME,
						DECODE(TRX.PARTY_TYPE,'PERSON',TO_CHAR(TRX.INDV_PARTY_DOB,'DD-Mon-YYYY'),NULL ) DOB,
						DECODE(TRX.PARTY_TYPE,'PERSON',TRX.INDV_PARTY_BIRTH_CITY,NULL) INDV_COUNTRY_OF_BIRTH ,
						DECODE(TRX.PARTY_TYPE,'PERSON',DECODE(Trx.INDV_PARTY_PROVINCE,'IT',Trx.INDV_PARTY_PROVINCE,'EE')) INDV_PROVINCE_CODE,
						DECODE(TRX.PARTY_TYPE,'PERSON',NVL2(CC.ITALY_CODE,LPAD(CC.ITALY_CODE,3,'0'),NULL)) FOREIGN_COUNTRY, --SHOULD BE NUMERIC
						DECODE(TRX.PARTY_TYPE,'ORGANIZATION',TRX.PARTY_NAME,NULL) COMPANY_NAME,
						DECODE(Trx.Party_Type,'ORGANIZATION',Trx.Party_City,NULL) Company_CITY,
						DECODE(TRX.PARTY_TYPE,'ORGANIZATION',NVL2(CC.ITALY_CODE,LPAD(CC.ITALY_CODE,3,'0'),NULL)) COMP_COUNTRY, --SHOULD BE NUMERIC
						DECODE(TRX.PARTY_TYPE,'ORGANIZATION',TRX.PARTY_ADDRESS,NULL) COMPANY_ADDRESS,
						NVL(trx.Vat_Registration_Num,NULL) VAT_REG_NUM,
					  0 TOTAL_AMT_AP,
					  0 TOTAL_TAX_AP,
					  0 TOTAL_AMT_AR,
					  0 TOTAL_TAX_AR,
					  0 TOTAL_AMT,
					  0 TOTAL_AMT_GOODS,
					  0 TOTAL_AMT_SERVICES,
					  Nvl(Decode(Jrl.Application_Id,'200',(Jrl.Taxable_Amt)),0) Total_Amt_Ap_C,
					  Nvl(Decode(Jrl.Application_Id,'200',Jrl.Tax_Amt),0) Total_Tax_Ap_C,
					  Nvl(Decode(Jrl.Application_Id,'222',(Jrl.Taxable_Amt)),0) Total_Amt_Ar_C,
					  NVL(DECODE(JRL.APPLICATION_ID,'222',JRL.TAX_AMT),0) TOTAL_TAX_AR_C
					From Je_It_Rpt_Trx_Lines_All Jrl,
					     Je_It_Rpt_Trx_All Trx,
					  (SELECT DISTINCT Country_Code,
						Italian_Country_Code Italy_Code
					  FROM JE_IT_COUNTRY_REP_CODES
					  ) Cc
					WHERE JRL.REQUEST_ID = :G_Conc_Request_Id --40669700
						AND Jrl.Trx_Type In ('CREDIT','DEBIT','CM','DM')
						AND JRL.REQUEST_ID   = TRX.REQUEST_ID
						AND JRL.PARTY_ID = TRX.PARTY_ID
						And JRL.Party_Site_Id     = TRX.Party_Site_Id
						and jrl.is_above_threshold_flag = 'Y'
						And 	TRX.Report_Section = 'BL'
						AND Trx.Country     = Cc.Country_Code (+)
					  )
					GROUP BY PARTY_NAME,
            Vat_Reg_Num,
            LAST_NAME,
            FIRST_NAME,
            DOB,
            INDV_COUNTRY_OF_BIRTH ,
            INDV_PROVINCE_CODE,
            FOREIGN_COUNTRY,
            COMPANY_NAME,
            COMPANY_CITY,
            COMP_COUNTRY,
            COMPANY_ADDRESS)
Parameter Name SQL text Validation
Reporting Identifier 
 
 LOV Oracle
Declaration Type 
 
 LOV Oracle
Year of Declaration 
 
 LOV Oracle
Month of Declaration 
 
 LOV Oracle
Transmission Type 
 
 LOV Oracle
Download
Related Blitz Reports
XXSJ Report VAT Register AP Open Balances Revaluation (1) JE Italian Polyvalent Declaration - Electronic File (Obsolete) JE Online VAT Register Reporting for Spain JE Portuguese Customers Recapitulative JG EMEA VAT: Turnover AR Non TRL Extract CE Bank Statement Cashflow Creation Execution - draft CE General Ledger Reconciliation JE Italian Annual Customer Listing
Blitz Report™

Blitz Report™ provides multiple benefits: