JL Argentine Payables Withholding Certificate - draft

Description
Categories: BI Publisher
Columns: Awt Type Code, Payment Reference, Payment Document Name, Check Number, Certificate Number, Certificate Number Num, Withholding Date, Tax Name, Withholding Amount, Credit Amount ...
Application: Latin America Localizations
Source: Argentine Payables Withholding Certificate (XML) - Not Supported: Reserved For Future Use
Short Name: JLARPCWT_XML
DB package: JL_JLARPCWT_XMLP_PKG
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
Parameter Name SQL text Validation
Reporting Level
 
LOV Oracle
Reporting Context
 
LOV Oracle
Legal Entity Name
 
LOV Oracle
Instruction Reference Number
 
LOV Oracle
Withholding Type Code
 
LOV Oracle
Certificate Number
 
LOV Oracle
Start Date
 
Date
End Date
 
Date
Supplier Name
 
LOV Oracle
Supplier Name Range From
 
LOV Oracle
Supplier Name Range To
 
LOV Oracle
Supplier Number Range From
 
LOV Oracle
Supplier Number Range To
 
LOV Oracle