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 |
