select
'FAA'||';'||
lpad(nvl(substr(regexp_replace(:seller_account_number,'[^0-9]'),1,5),'0'),5,'0')||';'||
to_char(greatest(xxen_util.client_time(sysdate),max(y.document_date) over ()),'YYYYMMDDHH24MI')||';'||
rpad(nvl(substr(y.debtor_identifier,1,15),' '),15)||';'||
lpad(nvl(substr(regexp_replace(y.debtor_id_number,'[^0-9]'),1,9),'0'),9,'0')||';'||
lpad(nvl(substr(regexp_replace(y.debtor_activity_code,'[^0-9]'),1,5),'0'),5,'0')||';'||
rpad(nvl(substr(y.country,1,2),' '),2)||';'||
rpad(nvl(substr(y.debtor_name,1,40),' '),40)||';'||
rpad(nvl(substr(y.trade_name,1,40),' '),40)||';'||
rpad(nvl(substr(y.address1,1,30),' '),30)||';'||
rpad(nvl(substr(y.address2,1,30),' '),30)||';'||
rpad(nvl(substr(y.address3,1,30),' '),30)||';'||
rpad(nvl(substr(y.town,1,30),' '),30)||';'||
rpad(nvl(substr(y.postal_code,1,10),' '),10)||';'||
rpad(nvl(substr(y.contact_name,1,20),' '),20)||';'||
rpad(nvl(substr(y.telephone,1,15),' '),15)||';'||
rpad(nvl(substr(y.fax,1,15),' '),15)||';'||
y.document_type||';'||
rpad(nvl(substr(y.document_number,1,20),' '),20)||';'||
'D'||';'||
rpad(nvl(substr(y.credited_invoice_number,1,20),' '),20)||';'||
nvl(to_char(y.document_date,'YYYYMMDD'),'00000000')||';'||
nvl(to_char(y.due_date,'YYYYMMDD'),'00000000')||';'||
rpad(nvl(substr(y.currency,1,3),' '),3)||';'||
lpad(to_char(round(abs(nvl(y.amount_including_tax,0))*power(10,nvl(y.currency_precision,2))),'FM999999999999999'),15,'0')||';'||
lpad(to_char(round(abs(nvl(y.amount_excluding_tax,0))*power(10,nvl(y.currency_precision,2))),'FM999999999999999'),15,'0')||';'||
lpad(to_char(round(nvl(y.vat_rate,0)*100),'FM9999'),4,'0')||';'||
rpad(nvl(substr(y.order_reference,1,10),' '),10)||';'||
rpad(nvl(substr(y.other_reference,1,25),' '),25)||';'||
rpad(nvl(substr(y.invoice_status,1,30),' '),30)||';'||
rpad(nvl(substr(y.free_field,1,30),' '),30)||';' faa_record
from
(
select
translate(hca.account_number,chr(9)||chr(10)||chr(13)||';',' ') debtor_identifier,
hp.jgzz_fiscal_code debtor_id_number,
hp.sic_code debtor_activity_code,
hl.country,
translate(hp.party_name,chr(9)||chr(10)||chr(13)||';',' ') debtor_name,
translate(hp.known_as,chr(9)||chr(10)||chr(13)||';',' ') trade_name,
translate(hl.address1,chr(9)||chr(10)||chr(13)||';',' ') address1,
translate(hl.address2,chr(9)||chr(10)||chr(13)||';',' ') address2,
translate(hl.address3,chr(9)||chr(10)||chr(13)||';',' ') address3,
translate(hl.city,chr(9)||chr(10)||chr(13)||';',' ') town,
translate(hl.postal_code,chr(9)||chr(10)||chr(13)||';',' ') postal_code,
to_char(null) contact_name,
to_char(null) telephone,
to_char(null) fax,
decode(x.class,'CM','A','F') document_type,
translate(x.trx_number,chr(9)||chr(10)||chr(13)||';',' ') document_number,
translate(ci.credited_invoice_number,chr(9)||chr(10)||chr(13)||';',' ') credited_invoice_number,
x.trx_date document_date,
x.due_date,
x.invoice_currency_code currency,
fc.precision currency_precision,
x.amount_including_tax,
x.amount_including_tax-x.tax_amount amount_excluding_tax,
(select max(zxl.tax_rate) from zx_lines zxl where x.customer_trx_id=zxl.trx_id and zxl.application_id=222 and zxl.entity_code='TRANSACTIONS') vat_rate,
translate(x.purchase_order,chr(9)||chr(10)||chr(13)||';',' ') order_reference,
to_char(null) other_reference,
x.invoice_status,
to_char(null) free_field
from
(
select
rcta.customer_trx_id,
rcta.trx_number,
rcta.trx_date,
rcta.invoice_currency_code,
rcta.bill_to_customer_id,
rcta.bill_to_site_use_id,
rcta.purchase_order,
apsa.class,
sum(apsa.amount_due_original) amount_including_tax,
min(apsa.due_date) due_date,
case when sum(nvl(apsa.amount_in_dispute,0))<>0 then 'LIT1' else 'RGL' end invoice_status,
nvl((select sum(rctla.extended_amount) from ra_customer_trx_lines_all rctla where rcta.customer_trx_id=rctla.customer_trx_id and rctla.line_type='TAX'),0) tax_amount
from
ra_customer_trx_all rcta,
ar_payment_schedules_all apsa,
hr_operating_units hou
where
1=1 and
rcta.org_id=hou.organization_id and
rcta.complete_flag='Y' and
rcta.customer_trx_id=apsa.customer_trx_id and
apsa.class in ('INV','CM')
group by
rcta.customer_trx_id,
rcta.trx_number,
rcta.trx_date,
rcta.invoice_currency_code,
rcta.bill_to_customer_id,
rcta.bill_to_site_use_id,
rcta.purchase_order,
apsa.class
) x,
(
select
araa.customer_trx_id,
max(rcta_inv.trx_number) keep (dense_rank first order by abs(araa.amount_applied) desc, rcta_inv.trx_number) credited_invoice_number
from
ar_receivable_applications_all araa,
ra_customer_trx_all rcta_inv
where
araa.application_type='CM' and
araa.status='APP' and
araa.display='Y' and
araa.applied_customer_trx_id=rcta_inv.customer_trx_id
group by
araa.customer_trx_id
) ci,
hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl,
fnd_currencies fc
where
x.customer_trx_id=ci.customer_trx_id(+) and
x.bill_to_customer_id=hca.cust_account_id and
hca.party_id=hp.party_id and
x.bill_to_site_use_id=hcsua.site_use_id(+) and
hcsua.cust_acct_site_id=hcasa.cust_acct_site_id(+) and
hcasa.party_site_id=hps.party_site_id(+) and
hps.location_id=hl.location_id(+) and
x.invoice_currency_code=fc.currency_code(+)
) y
order by
y.debtor_identifier,
y.document_number |
