JL Argentine Payables Withholding Certificate - draft

Select 	jaac.awt_type_code						awt_type_code,
	rtrim(substr(jaac.bank_account_num,1,15),' ')||
	' - '||rtrim(substr(jaac.payment_document_name,1,15),' ')||
	' - '||rtrim(substr(jaac.check_number,1,15),' ') 			payment_reference,
	jaac.payment_document_name					payment_document_name,
	jaac.check_number						check_number,
	jaac.certificate_number					certificate_number,
	jaac.certificate_number					certificate_number_NUM,
	jaac.awt_date						withholding_date,
	jaac.tax_name						tax_name,
                  (jaac.withholding_amount*(-1) + nvl(jaac.credit_amount,0))                    withholding_amount,
                  jaac.credit_amount * (-1)                                                                        credit_amount,
	abs(jaac.taxable_base_amount)	                                                                taxable_base_amount,
                  (jaac.withholding_amount* (-1))                                                             total_awt_amount,
	jzat.jurisdiction_type						jurisdiction_type,
	jzat.description						description,
	jzat.taxable_base_amount_basis                                                                taxable_base_amount_basis,
	jzat.certificate_header					certificate_header,
	jzat.credit_letter_flag						credit_letter_flag,
	atc.global_attribute7						zone,
	atc.description						description_tax_name,
	jzat.description						withholding_type_description,
	xle.name                                                                                                   Company_name,
	xle.address_line_1						Company_address_line_1,
	xle.address_line_2						Company_address_line_2,
	xle.address_line_3						Company_address_line_3,
	xle.country||'-'||xle.postal_code||'-'||fl.meaning 		                Company_address_line_4,
	xle.registration_number                                                                             CUIT_or_Tax_Id_Number_13,
	pv1.vendor_name						Supplier_Name,
	pvs.address_line1						Supplier_address_line_1,
	pvs.address_line2						Supplier_address_line_2,
	pvs.address_line3						Supplier_address_line_3,
	pvs.city||' - '||pvs.zip||' - '||pvs.country				Supplier_address_line_4,
 substr(replace(pv1.num_1099,'-'),1,2)||'-'||substr(replace(pv1.num_1099,'-'),3)||'-'||pv1.global_attribute12   Supplier_CUIT,
	jzat.province_code						province_code,
	xle.registration_number					comp_primary_id_number,
	jzat.tax_authority_id						comp_tax_authority_id,
	jcat.tax_authority_type					comp_tax_authority_type,
                pv1.num_1099                                                                                            supp_primary_id_number,
	jzat.tax_authority_id						supp_tax_authority_id,
	jsat.tax_authority_type					supp_tax_authority_type, 
	JL_JLARPCWT_XMLP_PKG.cf_payment_amountformula(jaac.check_number) CF_Payment_amount, 
	JL_JLARPCWT_XMLP_PKG.cf_1formula(jzat.jurisdiction_type, jzat.taxable_base_amount_basis, jaac.awt_type_code, jzat.credit_letter_flag) CF_Certificate_type, 
	JL_JLARPCWT_XMLP_PKG.cf_comp_num_agent_retencion7fo(:CF_Certificate_type, xle.registration_number, jzat.tax_authority_id, jcat.tax_authority_type) CF_comp_num_agent_ret, 
	JL_JLARPCWT_XMLP_PKG.cf_supp_province_numberformula(:CF_Certificate_type, pv1.num_1099, jzat.tax_authority_id, jsat.tax_authority_type, jaac.awt_date) CF_supp_province_number, 
	JL_JLARPCWT_XMLP_PKG.province_nameformula(:CF_Certificate_type, jzat.province_code) CF_province_name
	,	JL_JLARPCWT_XMLP_PKG.cf_1formula0039(jaac.awt_type_code, :CERTIFICATE_NUMBER_NUM) CF_UPDATE_CERTIFICATES
From
	jl_zz_ap_supp_awt_types 	jsat ,
	po_vendor_sites_all		pvs,
	po_vendors		pv1,
	jl_zz_ap_awt_types 		jzat,
	jl_ZZ_ap_comp_awt_types 	jcat,
                 xle_firstparty_information_v    xle,
	ap_tax_codes_all		atc,
	jl_ar_ap_awt_certif_all 	jaac,
              fnd_lookups fl  
where
 	jaac.awt_date 		>=	nvl(:P_DATE_FROM,jaac.awt_date)
and 	jaac.awt_date 		< =	nvl(:P_DATE_TO+1,jaac.awt_date +1)
and	jaac.status <> 'VOID'
and	jaac.checkrun_name 		=  	nvl(:P_CHECKRUN_NAME,jaac.checkrun_name)
and	jaac.awt_type_code		=	nvl(:P_AWT_TYPE_CODE,jaac.awt_type_code)
and           NVL(jaac.payment_instruction_id,-99)   =  nvl(:p_payment_instruction, nvl(jaac.payment_instruction_id,-99))
and 	atc.name			= 	jaac.tax_name
and 	jaac.awt_type_code		=  	jzat.awt_type_code
and	jcat.awt_type_code		=	jzat.awt_type_code
and  	pvs.global_attribute17	=	'Y'
and	pvs.vendor_id		=	pv1.vendor_id
and	jsat.awt_type_code		=	jzat.awt_type_code	
and 	jsat.vendor_id		=	pv1.vendor_id
and     	pv1.vendor_id		=	jaac.vendor_id
and 	:p_ok_parameter_flag	=	'Y'
and           xle.legal_entity_id                     =  jcat.legal_entity_id(+)
and           jaac.legal_entity_id                   =  jcat.legal_entity_id
&lp_jl_ar_ap_awt_certif_all_wh
and     fl.lookup_type = 'JLZZ_CITY' 
and     NVL(fl.start_date_active, SYSDATE) <= SYSDATE 
and     NVL(fl.end_date_active, SYSDATE) >= SYSDATE 
and     fl.enabled_flag = 'Y' 
and     fl.lookup_code =  xle.town_or_city
&P_Selected_Suppliers      
ORDER BY pv1.vendor_name,
	jzat.awt_type_code,
	jaac.certificate_number