JE Italian Polyvalent Declaration - Electronic File (Obsolete)

Description

Categories: BI Publisher
Application: European Localizations
Source: Italian Polyvalent Declaration - Electronic File (Obsolete)
Short Name: JEITPDEF_XMLP
DB package: JE_IT_POLYVALENT_DECL_RPT_PKG

Imported from BI Publisher
Italian Polyvalent Declaration - Electronic File

SQL 172 lines

SELECT A_Fe,
						  MAX(A1)
						  ||MAX(A2)
						  ||MAX(A3)
						  ||MAX(A4)
						  ||MAX(A5)
						  ||MAX(A6) Trans_Data
						FROM
						  (SELECT A_Fe,
							DECODE(B,0,Nvl2(Vat_Reg_Num,Rpad('FE001001'
							||Vat_Reg_Num,24,' '),NULL)
							||Nvl2(taxpayer_id,'FE001002'
							||Rpad(taxpayer_id,16,' '),NULL)
							||Nvl2(Recap_Inv_Flag,'FE001003'
							||Lpad(Recap_Inv_Flag,16,' '),NULL)
							||Nvl2(Vat_Not_Exp_Flag,'FE001004'
							||Lpad(Vat_Not_Exp_Flag,16,' '),NULL)
							||Nvl2(Rental_Flag,Rpad('FE001005'
							||Rental_Flag,24,' '),NULL)
							||Nvl2(Self_Inv_Flag,'FE001006'
							||Lpad(Self_Inv_Flag,16,' '),NULL)
							||Nvl2(Document_Date,'FE001007'
							||Lpad(Document_Date,16,' '),NULL)
							||Nvl2(Registration_Date,'FE001008'
							||Lpad(Registration_Date,16,' '),NULL)
							||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE001009',Trx_Num),NULL)
							||Nvl2(Taxable_Amt,'FE001010'
							||Lpad(Taxable_Amt,16,' '),NULL)
							||Nvl2(Tax_Amt,'FE001011'
							||Lpad(Tax_Amt,16,' '),NULL)
							||Nvl2(Conf_Flag,'FE001012'
							||Lpad(Conf_Flag,16,' '),NULL)) A1,
							DECODE(B,1,Nvl2(Vat_Reg_Num,Rpad('FE002001'
							||Vat_Reg_Num,24,' '),NULL)
							||Nvl2(taxpayer_id,'FE002002'
							||Rpad(taxpayer_id,16,' '),NULL)
							||Nvl2(Recap_Inv_Flag,'FE002003'
							||Lpad(Recap_Inv_Flag,16,' '),NULL)
							||Nvl2(Vat_Not_Exp_Flag,'FE002004'
							||Lpad(Vat_Not_Exp_Flag,16,' '),NULL)
							||Nvl2(Rental_Flag,Rpad('FE002005'
							||Rental_Flag,24,' '),NULL)
							||Nvl2(Self_Inv_Flag,'FE002006'
							||Lpad(Self_Inv_Flag,16,' '),NULL)
							||Nvl2(Document_Date,'FE002007'
							||Lpad(Document_Date,16,' '),NULL)
							||Nvl2(Registration_Date,'FE002008'
							||Lpad(Registration_Date,16,' '),NULL)
							||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE002009',Trx_Num),NULL)
							||Nvl2(Taxable_Amt,'FE002010'
							||Lpad(Taxable_Amt,16,' '),NULL)
							||Nvl2(Tax_Amt,'FE002011'
							||Lpad(Tax_Amt,16,' '),NULL)
							||Nvl2(Conf_Flag,'FE002012'
							||Lpad(Conf_Flag,16,' '),NULL)) A2,
							DECODE(B,2,Nvl2(Vat_Reg_Num,Rpad('FE003001'
							||Vat_Reg_Num,24,' '),NULL)
							||Nvl2(taxpayer_id,'FE003002'
							||Rpad(taxpayer_id,16,' '),NULL)
							||Nvl2(Recap_Inv_Flag,'FE003003'
							||Lpad(Recap_Inv_Flag,16,' '),NULL)
							||Nvl2(Vat_Not_Exp_Flag,'FE003004'
							||Lpad(Vat_Not_Exp_Flag,16,' '),NULL)
							||Nvl2(Rental_Flag,Rpad('FE003005'
							||Rental_Flag,24,' '),NULL)
							||Nvl2(Self_Inv_Flag,'FE003006'
							||Lpad(Self_Inv_Flag,16,' '),NULL)
							||Nvl2(Document_Date,'FE003007'
							||Lpad(Document_Date,16,' '),NULL)
							||Nvl2(Registration_Date,'FE003008'
							||Lpad(Registration_Date,16,' '),NULL)
							||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE003009',Trx_Num),NULL)
							||Nvl2(Taxable_Amt,'FE003010'
							||Lpad(Taxable_Amt,16,' '),NULL)
							||Nvl2(Tax_Amt,'FE003011'
							||Lpad(Tax_Amt,16,' '),NULL)
							||Nvl2(Conf_Flag,'FE003012'
							||Lpad(Conf_Flag,16,' '),NULL)) A3,
							DECODE(B,3,Nvl2(Vat_Reg_Num,Rpad('FE004001'
							||Vat_Reg_Num,24,' '),NULL)
							||Nvl2(taxpayer_id,'FE004002'
							||Rpad(taxpayer_id,16,' '),NULL)
							||Nvl2(Recap_Inv_Flag,'FE004003'
							||Lpad(Recap_Inv_Flag,16,' '),NULL)
							||Nvl2(Vat_Not_Exp_Flag,'FE004004'
							||Lpad(Vat_Not_Exp_Flag,16,' '),NULL)
							||Nvl2(Rental_Flag,Rpad('FE004005'
							||Rental_Flag,24,' '),NULL)
							||Nvl2(Self_Inv_Flag,'FE004006'
							||Lpad(Self_Inv_Flag,16,' '),NULL)
							||Nvl2(Document_Date,'FE004007'
							||Lpad(Document_Date,16,' '),NULL)
							||Nvl2(Registration_Date,'FE004008'
							||Lpad(Registration_Date,16,' '),NULL)
							||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE004009',Trx_Num),NULL)
							||Nvl2(Taxable_Amt,'FE004010'
							||Lpad(Taxable_Amt,16,' '),NULL)
							||Nvl2(Tax_Amt,'FE004011'
							||Lpad(Tax_Amt,16,' '),NULL)
							||Nvl2(Conf_Flag,'FE004012'
							||Lpad(Conf_Flag,16,' '),NULL)) A4,
							DECODE(B,4,Nvl2(Vat_Reg_Num,Rpad('FE005001'
							||Vat_Reg_Num,24,' '),NULL)
							||Nvl2(taxpayer_id,'FE005002'
							||Rpad(taxpayer_id,16,' '),NULL)
							||Nvl2(Recap_Inv_Flag,'FE005003'
							||Lpad(Recap_Inv_Flag,16,' '),NULL)
							||Nvl2(Vat_Not_Exp_Flag,'FE005004'
							||Lpad(Vat_Not_Exp_Flag,16,' '),NULL)
							||Nvl2(Rental_Flag,Rpad('FE005005'
							||Rental_Flag,24,' '),NULL)
							||Nvl2(Self_Inv_Flag,'FE005006'
							||Lpad(Self_Inv_Flag,16,' '),NULL)
							||Nvl2(Document_Date,'FE005007'
							||Lpad(Document_Date,16,' '),NULL)
							||Nvl2(Registration_Date,'FE005008'
							||Lpad(Registration_Date,16,' '),NULL)
							||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE005009',Trx_Num),NULL)
							||Nvl2(Taxable_Amt,'FE005010'
							||Lpad(Taxable_Amt,16,' '),NULL)
							||Nvl2(Tax_Amt,'FE005011'
							||Lpad(Tax_Amt,16,' '),NULL)
							||Nvl2(Conf_Flag,'FE005012'
							||Lpad(Conf_Flag,16,' '),NULL)) A5,
							DECODE(B,5,Nvl2(Vat_Reg_Num,Rpad('FE006001'
							||Vat_Reg_Num,24,' '),NULL)
							||Nvl2(taxpayer_id,'FE006002'
							||Rpad(taxpayer_id,16,' '),NULL)
							||Nvl2(Recap_Inv_Flag,'FE006003'
							||Lpad(Recap_Inv_Flag,16,' '),NULL)
							||Nvl2(Vat_Not_Exp_Flag,'FE006004'
							||Lpad(Vat_Not_Exp_Flag,16,' '),NULL)
							||Nvl2(Rental_Flag,Rpad('FE006005'
							||Rental_Flag,24,' '),NULL)
							||Nvl2(Self_Inv_Flag,'FE006006'
							||Lpad(Self_Inv_Flag,16,' '),NULL)
							||Nvl2(Document_Date,'FE006007'
							||Lpad(Document_Date,16,' '),NULL)
							||Nvl2(Registration_Date,'FE006008'
							||Lpad(Registration_Date,16,' '),NULL)
							||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE006009',Trx_Num),NULL)
							||Nvl2(Taxable_Amt,'FE006010'
							||Lpad(Taxable_Amt,16,' '),NULL)
							||Nvl2(Tax_Amt,'FE006011'
							||Lpad(Tax_Amt,16,' '),NULL)
							||Nvl2(Conf_Flag,'FE006012'
							||Lpad(Conf_Flag,16,' '),NULL)) A6
						  FROM
							(SELECT DECODE(Recap_Inv_Flag,'Y',NULL,DECODE(sign(length(Vat_Registration_Num)-11),0,Vat_Registration_Num,null)) Vat_Reg_Num,
							  DECODE(Recap_Inv_Flag,'Y',NULL,DECODE(sign(length(Vat_Registration_Num)-11),0,null,tax_payer_id)) Taxpayer_Id,
							  DECODE(Recap_Inv_Flag,'Y','1',NULL) Recap_Inv_Flag,
							  DECODE(Vat_Not_Exp_Flag,'Y','1',NULL)Vat_Not_Exp_Flag,
							  DECODE(Self_Inv_Flag,'Y',NULL,trx.rental_flag) Rental_Flag,
							  DECODE(Self_Inv_Flag,'Y','1',NULL) Self_Inv_Flag,
							  TO_CHAR(Trx_Date,'DDMMYYYY') Document_Date,
							  TO_CHAR(Gl_Date,'DDMMYYYY') Registration_Date,
							  Trx_Num,
							  ROUND(Taxable_Amt) Taxable_Amt,
							  DECODE(Vat_Not_Exp_Flag,'Y','0',ROUND(Tax_Amt)) Tax_Amt,
							  DECODE(SIGN(Taxable_Amt -999999),1,'1',NULL) Conf_Flag,
							  TRUNC((Rank_In_Section-1)/6) A_Fe,
							  Mod(Rank_In_Section   -1,6) B
							FROM Je_It_Rpt_Trx_All Trx
							WHERE Report_Section = 'FE'
							AND Eft_Num          = :P_Eft_Num
							AND Request_Id       = :G_Conc_Request_Id
							ORDER BY Rank_In_Section
							)
						  )
						GROUP BY A_Fe
						ORDER BY A_Fe

SELECT A_Fe, MAX(A1) ||MAX(A2) ||MAX(A3) ||MAX(A4) ||MAX(A5) ||MAX(A6) Trans_Data FROM (SELECT A_Fe, DECODE(B,0,Nvl2(Vat_Reg_Num,Rpad('FE001001' ||Vat_Reg_Num,24,' '),NULL) ||Nvl2(taxpayer_id,'FE001002' ||Rpad(taxpayer_id,16,' '),NULL) ||Nvl2(Recap_Inv_Flag,'FE001003' ||Lpad(Recap_Inv_Flag,16,' '),NULL) ||Nvl2(Vat_Not_Exp_Flag,'FE001004' ||Lpad(Vat_Not_Exp_Flag,16,' '),NULL) ||Nvl2(Rental_Flag,Rpad('FE001005' ||Rental_Flag,24,' '),NULL) ||Nvl2(Self_Inv_Flag,'FE001006' ||Lpad(Self_Inv_Flag,16,' '),NULL) ||Nvl2(Document_Date,'FE001007' ||Lpad(Document_Date,16,' '),NULL) ||Nvl2(Registration_Date,'FE001008' ||Lpad(Registration_Date,16,' '),NULL) ||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE001009',Trx_Num),NULL) ||Nvl2(Taxable_Amt,'FE001010' ||Lpad(Taxable_Amt,16,' '),NULL) ||Nvl2(Tax_Amt,'FE001011' ||Lpad(Tax_Amt,16,' '),NULL) ||Nvl2(Conf_Flag,'FE001012' ||Lpad(Conf_Flag,16,' '),NULL)) A1, DECODE(B,1,Nvl2(Vat_Reg_Num,Rpad('FE002001' ||Vat_Reg_Num,24,' '),NULL) ||Nvl2(taxpayer_id,'FE002002' ||Rpad(taxpayer_id,16,' '),NULL) ||Nvl2(Recap_Inv_Flag,'FE002003' ||Lpad(Recap_Inv_Flag,16,' '),NULL) ||Nvl2(Vat_Not_Exp_Flag,'FE002004' ||Lpad(Vat_Not_Exp_Flag,16,' '),NULL) ||Nvl2(Rental_Flag,Rpad('FE002005' ||Rental_Flag,24,' '),NULL) ||Nvl2(Self_Inv_Flag,'FE002006' ||Lpad(Self_Inv_Flag,16,' '),NULL) ||Nvl2(Document_Date,'FE002007' ||Lpad(Document_Date,16,' '),NULL) ||Nvl2(Registration_Date,'FE002008' ||Lpad(Registration_Date,16,' '),NULL) ||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE002009',Trx_Num),NULL) ||Nvl2(Taxable_Amt,'FE002010' ||Lpad(Taxable_Amt,16,' '),NULL) ||Nvl2(Tax_Amt,'FE002011' ||Lpad(Tax_Amt,16,' '),NULL) ||Nvl2(Conf_Flag,'FE002012' ||Lpad(Conf_Flag,16,' '),NULL)) A2, DECODE(B,2,Nvl2(Vat_Reg_Num,Rpad('FE003001' ||Vat_Reg_Num,24,' '),NULL) ||Nvl2(taxpayer_id,'FE003002' ||Rpad(taxpayer_id,16,' '),NULL) ||Nvl2(Recap_Inv_Flag,'FE003003' ||Lpad(Recap_Inv_Flag,16,' '),NULL) ||Nvl2(Vat_Not_Exp_Flag,'FE003004' ||Lpad(Vat_Not_Exp_Flag,16,' '),NULL) ||Nvl2(Rental_Flag,Rpad('FE003005' ||Rental_Flag,24,' '),NULL) ||Nvl2(Self_Inv_Flag,'FE003006' ||Lpad(Self_Inv_Flag,16,' '),NULL) ||Nvl2(Document_Date,'FE003007' ||Lpad(Document_Date,16,' '),NULL) ||Nvl2(Registration_Date,'FE003008' ||Lpad(Registration_Date,16,' '),NULL) ||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE003009',Trx_Num),NULL) ||Nvl2(Taxable_Amt,'FE003010' ||Lpad(Taxable_Amt,16,' '),NULL) ||Nvl2(Tax_Amt,'FE003011' ||Lpad(Tax_Amt,16,' '),NULL) ||Nvl2(Conf_Flag,'FE003012' ||Lpad(Conf_Flag,16,' '),NULL)) A3, DECODE(B,3,Nvl2(Vat_Reg_Num,Rpad('FE004001' ||Vat_Reg_Num,24,' '),NULL) ||Nvl2(taxpayer_id,'FE004002' ||Rpad(taxpayer_id,16,' '),NULL) ||Nvl2(Recap_Inv_Flag,'FE004003' ||Lpad(Recap_Inv_Flag,16,' '),NULL) ||Nvl2(Vat_Not_Exp_Flag,'FE004004' ||Lpad(Vat_Not_Exp_Flag,16,' '),NULL) ||Nvl2(Rental_Flag,Rpad('FE004005' ||Rental_Flag,24,' '),NULL) ||Nvl2(Self_Inv_Flag,'FE004006' ||Lpad(Self_Inv_Flag,16,' '),NULL) ||Nvl2(Document_Date,'FE004007' ||Lpad(Document_Date,16,' '),NULL) ||Nvl2(Registration_Date,'FE004008' ||Lpad(Registration_Date,16,' '),NULL) ||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE004009',Trx_Num),NULL) ||Nvl2(Taxable_Amt,'FE004010' ||Lpad(Taxable_Amt,16,' '),NULL) ||Nvl2(Tax_Amt,'FE004011' ||Lpad(Tax_Amt,16,' '),NULL) ||Nvl2(Conf_Flag,'FE004012' ||Lpad(Conf_Flag,16,' '),NULL)) A4, DECODE(B,4,Nvl2(Vat_Reg_Num,Rpad('FE005001' ||Vat_Reg_Num,24,' '),NULL) ||Nvl2(taxpayer_id,'FE005002' ||Rpad(taxpayer_id,16,' '),NULL) ||Nvl2(Recap_Inv_Flag,'FE005003' ||Lpad(Recap_Inv_Flag,16,' '),NULL) ||Nvl2(Vat_Not_Exp_Flag,'FE005004' ||Lpad(Vat_Not_Exp_Flag,16,' '),NULL) ||Nvl2(Rental_Flag,Rpad('FE005005' ||Rental_Flag,24,' '),NULL) ||Nvl2(Self_Inv_Flag,'FE005006' ||Lpad(Self_Inv_Flag,16,' '),NULL) ||Nvl2(Document_Date,'FE005007' ||Lpad(Document_Date,16,' '),NULL) ||Nvl2(Registration_Date,'FE005008' ||Lpad(Registration_Date,16,' '),NULL) ||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE005009',Trx_Num),NULL) ||Nvl2(Taxable_Amt,'FE005010' ||Lpad(Taxable_Amt,16,' '),NULL) ||Nvl2(Tax_Amt,'FE005011' ||Lpad(Tax_Amt,16,' '),NULL) ||Nvl2(Conf_Flag,'FE005012' ||Lpad(Conf_Flag,16,' '),NULL)) A5, DECODE(B,5,Nvl2(Vat_Reg_Num,Rpad('FE006001' ||Vat_Reg_Num,24,' '),NULL) ||Nvl2(taxpayer_id,'FE006002' ||Rpad(taxpayer_id,16,' '),NULL) ||Nvl2(Recap_Inv_Flag,'FE006003' ||Lpad(Recap_Inv_Flag,16,' '),NULL) ||Nvl2(Vat_Not_Exp_Flag,'FE006004' ||Lpad(Vat_Not_Exp_Flag,16,' '),NULL) ||Nvl2(Rental_Flag,Rpad('FE006005' ||Rental_Flag,24,' '),NULL) ||Nvl2(Self_Inv_Flag,'FE006006' ||Lpad(Self_Inv_Flag,16,' '),NULL) ||Nvl2(Document_Date,'FE006007' ||Lpad(Document_Date,16,' '),NULL) ||Nvl2(Registration_Date,'FE006008' ||Lpad(Registration_Date,16,' '),NULL) ||Nvl2(Trx_Num,Je_It_Polyvalent_Decl_Rpt_Pkg.Get_Formatted_String('FE006009',Trx_Num),NULL) ||Nvl2(Taxable_Amt,'FE006010' ||Lpad(Taxable_Amt,16,' '),NULL) ||Nvl2(Tax_Amt,'FE006011' ||Lpad(Tax_Amt,16,' '),NULL) ||Nvl2(Conf_Flag,'FE006012' ||Lpad(Conf_Flag,16,' '),NULL)) A6 FROM (SELECT DECODE(Recap_Inv_Flag,'Y',NULL,DECODE(sign(length(Vat_Registration_Num)-11),0,Vat_Registration_Num,null)) Vat_Reg_Num, DECODE(Recap_Inv_Flag,'Y',NULL,DECODE(sign(length(Vat_Registration_Num)-11),0,null,tax_payer_id)) Taxpayer_Id, DECODE(Recap_Inv_Flag,'Y','1',NULL) Recap_Inv_Flag, DECODE(Vat_Not_Exp_Flag,'Y','1',NULL)Vat_Not_Exp_Flag, DECODE(Self_Inv_Flag,'Y',NULL,trx.rental_flag) Rental_Flag, DECODE(Self_Inv_Flag,'Y','1',NULL) Self_Inv_Flag, TO_CHAR(Trx_Date,'DDMMYYYY') Document_Date, TO_CHAR(Gl_Date,'DDMMYYYY') Registration_Date, Trx_Num, ROUND(Taxable_Amt) Taxable_Amt, DECODE(Vat_Not_Exp_Flag,'Y','0',ROUND(Tax_Amt)) Tax_Amt, DECODE(SIGN(Taxable_Amt -999999),1,'1',NULL) Conf_Flag, TRUNC((Rank_In_Section-1)/6) A_Fe, Mod(Rank_In_Section -1,6) B FROM Je_It_Rpt_Trx_All Trx WHERE Report_Section = 'FE' AND Eft_Num = :P_Eft_Num AND Request_Id = :G_Conc_Request_Id ORDER BY Rank_In_Section ) ) GROUP BY A_Fe ORDER BY A_Fe

Parameters 6

Parameter	Type	List of values SQL
Reporting Identifier	LOV Oracle
Declaration Type	LOV Oracle
Year of Declaration	LOV Oracle
Month of Declaration	LOV Oracle
Transmission Type	LOV Oracle
Repot Mode	LOV Oracle

Description

SQL 172 lines

Parameters 6

Find out more

Features

Download

FAQ