JL Brazilian Receivables Invoice Print Invoice in AutoInvoice - draft

SELECT  ract.customer_trx_id c_customer_trx_id,
               ract.ship_to_site_use_id c_ship_to_site_use_id,
               ract.internal_notes c_internal_notes,
               ract.trx_date c_trx_date,
               ractt.global_attribute2 c_operation_type,
               rabs2.name c_sub_series,
               substr(lpad(ract.trx_number,6,'0'),1,3)||'.'|| substr(lpad(ract.trx_number,6,'0'),4,6) c_trx_number,
               pty.party_name c_cust_name,
               ad.global_attribute3 || decode(ad.global_attribute2,2,'/'||
                    ad.global_attribute4,3,'/'||
                    ad.global_attribute4) || '-' ||
                    ad.global_attribute5 c_inscription_number,
               ract.global_attribute8 c_issue_date,
               loc.address1 c_address,
               loc.address4 c_district,
               loc.postal_code c_zip,
               loc.city c_city,
               loc.state c_state,
               ad.global_attribute6 c_state_inscription,
               loc1.address1 || '-' ||
                     loc1.address4 || '-' ||
                     loc1.postal_code || '-' ||
                     loc1.city || '-' ||
                     loc1.state c_bill_to_address,
               fnd_number.canonical_to_number(ract.global_attribute9) c_freight_expense,
               fnd_number.canonical_to_number(ract.global_attribute10) c_insurance_expense,
               fnd_number.canonical_to_number(ract.global_attribute11) c_other_expense,
               DECODE(ract.fob_point,'SEL',1,'BUY',2) c_freight_responsibility,
               ract.global_attribute12 c_license_plate,
               ract.global_attribute13 c_volume_quantity,
               ract.global_attribute14 c_volume_type,
               ract.global_attribute15 c_volume_number,
               ract.global_attribute16 c_total_gross_weight,
               ract.global_attribute17 c_total_net_weight,
               ract.ship_date_actual c_shipping_date,
               ract.status_trx c_status_trx,
               ract.ship_via ship_via,
               ract.trx_number trx_num,
               ract.term_due_date term_due_date, 
	JL_JLBRRINV_XMLP_PKG.cf_amount_wordsformula(:CS_amount_due) CF_Amount_Words, 
	JL_JLBRRINV_XMLP_PKG.cf_printing_count(ract.trx_number, ract.customer_trx_id) CF_printing_count, 
	JL_JLBRRINV_XMLP_PKG.cf_additional_infoformula(ract.customer_trx_id, ract.status_trx) CF_Additional_Info, 
	JL_JLBRRINV_XMLP_PKG.cf_brandformula() CF_Brand, 
	JL_JLBRRINV_XMLP_PKG.ccf_state_insc_substitutionfor(ract.ship_to_site_use_id, ract.trx_date) CF_State_Insc_Substitution, 
	JL_JLBRRINV_XMLP_PKG.cf_total_priceformula(:CS_Total_Price, :CS_Total_Base_ICMS) CF_Total_Price, 
	JL_JLBRRINV_XMLP_PKG.cf_total_extended_amountformul(:CS_Total_Extended_Amount) CF_TOTAL_EXTENDED_AMOUNT, 
	JL_JLBRRINV_XMLP_PKG.cf_freight_state_ins_numberfor(ract.ship_via) CF_freight_state_ins_number,
	JL_JLBRRINV_XMLP_PKG.CP_Operation_Nature_p CP_Operation_Nature,
	JL_JLBRRINV_XMLP_PKG.CP_Operation_Code_p CP_Operation_Code,
	JL_JLBRRINV_XMLP_PKG.CP_freight_city_p CP_freight_city,
	JL_JLBRRINV_XMLP_PKG.CP_freight_state_p CP_freight_state,
	JL_JLBRRINV_XMLP_PKG.CP_freight_address_p CP_freight_address,
	JL_JLBRRINV_XMLP_PKG.CF_freight_inscription_number CP_freight_inscription_number,
	JL_JLBRRINV_XMLP_PKG.CP_freight_name_p CP_freight_name
FROM  ra_customer_trx ract,
              hz_cust_accounts cust,
              hz_parties pty,
              hz_cust_acct_sites ad,
              hz_party_sites pst,
              hz_locations loc,
              hz_locations loc1,
              hz_party_sites pst1,
              hz_cust_acct_sites ad1,
              hz_cust_site_uses rsu,
              hz_cust_site_uses rsu1,
              ra_cust_trx_types ractt,
              ra_batch_sources rabs1,
              ra_batch_sources rabs2
WHERE rsu.site_use_id = ract.ship_to_site_use_id
     AND ad.cust_acct_site_id = rsu.cust_acct_site_id
     AND cust.cust_account_id = ad.cust_account_id
     AND cust.party_id = pty.party_id
     AND ad.party_site_id = pst.party_site_id
     AND loc.location_id = pst.location_id
     AND ad1.party_site_id = pst1.party_site_id
     AND pst1.location_id = loc1.location_id
     AND ract.bill_to_site_use_id = rsu1.site_use_id      
     AND rsu1.cust_acct_site_id = ad1.cust_acct_site_id
     AND ractt.cust_trx_type_id = ract.cust_trx_type_id
     AND rabs1.batch_source_id = ract.batch_source_id
     AND rabs2.batch_source_id (+) = to_number(rabs1.global_attribute1)
     AND ract.complete_flag = 'Y'
&C_Inv_Form_Predicate
ORDER BY ract.trx_number asc