JE Italian Polyvalent Declaration - Audit Report (Obsolete)

  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)