SELECT trx.customer_trx_id trx_id,
nvl(trx.global_attribute7,decode(trx_types.type,'INV','TD01','CM','TD04','DM','TD05')) trx_doc_type,
trx.invoice_currency_code trx_cur_code,
trx.exchange_rate trx_exchange_rate,
to_char(trx.trx_date,'YYYY-MM-DD') trx_date,
trx.trx_number trx_number,
trx.purchase_order trx_po_number,
to_char(trx.purchase_order_date,'YYYY-MM-DD') trx_po_date,
trx.waybill_number trx_waybill_number,
trx.ship_date_actual trx_ship_date_actual,
trx.attribute1 trx_hdr_attribute1,
trx.attribute2 trx_hdr_attribute2,
trx.attribute3 trx_hdr_attribute3,
trx.attribute4 trx_hdr_attribute4,
trx.attribute5 trx_hdr_attribute5,
trx.attribute6 trx_hdr_attribute6,
trx.attribute7 trx_hdr_attribute7,
trx.attribute8 trx_hdr_attribute8,
trx.attribute9 trx_hdr_attribute9,
trx.attribute10 trx_hdr_attribute10,
trx.attribute11 trx_hdr_attribute11,
trx.attribute12 trx_hdr_attribute12,
trx.attribute13 trx_hdr_attribute13,
trx.attribute15 trx_hdr_attribute14,
trx.attribute15 trx_hdr_attribute15,
trx.interface_header_attribute1 interface_hdr_attribute1,
trx.interface_header_attribute2 interface_hdr_attribute2,
trx.interface_header_attribute3 interface_hdr_attribute3,
trx.interface_header_attribute4 interface_hdr_attribute4,
trx.interface_header_attribute5 interface_hdr_attribute5,
trx.interface_header_attribute6 interface_hdr_attribute6,
trx.interface_header_attribute7 interface_hdr_attribute7,
trx.interface_header_attribute8 interface_hdr_attribute8,
trx.interface_header_attribute9 interface_hdr_attribute9,
trx.interface_header_attribute10 interface_hdr_attribute10,
trx.interface_header_attribute11 interface_hdr_attribute11,
trx.interface_header_attribute12 interface_hdr_attribute12,
trx.interface_header_attribute13 interface_hdr_attribute13,
trx.interface_header_attribute14 interface_hdr_attribute14,
trx.interface_header_attribute15 interface_hdr_attribute15,
hl.address1||hl.address2 ship_to_address,
hl.city ship_to_city,
hl.postal_code ship_to_postal_code,
hl.country ship_to_country,
hl.province ship_to_province
FROM ra_customer_trx trx,
ra_cust_trx_types trx_types,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_party_sites hps,
hz_locations hl
WHERE trx.legal_entity_id = :P_LEGAL_ENTITY_ID
and trx.bill_to_customer_id = :P_CUST_ACCOUNT_ID
and trx.bill_to_site_use_id = :P_BILL_TO_SITE_USE_ID
and trx.ship_to_site_use_id = hcsu.site_use_id(+)
and hcsu.cust_acct_site_id = hcas.cust_acct_site_id(+)
and hcas.party_site_id = hps.party_site_id(+)
and hps.location_id = hl.location_id(+)
and trx.cust_trx_type_id = trx_types.cust_trx_type_id
and trx_types.type in ('INV','CM','DM') --19234760
and trx.org_id = trx_types.org_id
and trx.customer_trx_id = nvl(:P_TRX_ID,customer_trx_id)
and trx.complete_flag = 'Y'
and trx.trx_date between :P_TRX_DATE_FROM and :P_TRX_DATE_TO
and ((:P_GEN_OPTION='1' and trx.global_attribute8 is NULL ) -- electronic invoice status is null
or (:P_GEN_OPTION in ('2','3') and trx.global_attribute8 is NOT NULL and :p_trx_id is not null ))
and exists (select 1
from ra_customer_trx_lines_all trx_lines
where trx_lines.customer_trx_id = trx.customer_trx_id
and trx_lines.line_type = 'TAX' )
UNION ALL
SELECT trx.customer_trx_id trx_id,
nvl(trx.global_attribute7,decode(trx_types.type,'INV','TD01','CM','TD04','DM','TD05')) trx_doc_type,
trx.invoice_currency_code trx_cur_code,
trx.exchange_rate trx_exchange_rate,
to_char(trx.trx_date,'YYYY-MM-DD') trx_date,
trx.trx_number trx_number,
trx.purchase_order trx_po_number,
to_char(trx.purchase_order_date,'YYYY-MM-DD') trx_po_date,
trx.waybill_number trx_waybill_number,
trx.ship_date_actual trx_ship_date_actual,
trx.attribute1 trx_hdr_attribute1,
trx.attribute2 trx_hdr_attribute2,
trx.attribute3 trx_hdr_attribute3,
trx.attribute4 trx_hdr_attribute4,
trx.attribute5 trx_hdr_attribute5,
trx.attribute6 trx_hdr_attribute6,
trx.attribute7 trx_hdr_attribute7,
trx.attribute8 trx_hdr_attribute8,
trx.attribute9 trx_hdr_attribute9,
trx.attribute10 trx_hdr_attribute10,
trx.attribute11 trx_hdr_attribute11,
trx.attribute12 trx_hdr_attribute12,
trx.attribute13 trx_hdr_attribute13,
trx.attribute15 trx_hdr_attribute14,
trx.attribute15 trx_hdr_attribute15,
trx.interface_header_attribute1 interface_hdr_attribute1,
trx.interface_header_attribute2 interface_hdr_attribute2,
trx.interface_header_attribute3 interface_hdr_attribute3,
trx.interface_header_attribute4 interface_hdr_attribute4,
trx.interface_header_attribute5 interface_hdr_attribute5,
trx.interface_header_attribute6 interface_hdr_attribute6,
trx.interface_header_attribute7 interface_hdr_attribute7,
trx.interface_header_attribute8 interface_hdr_attribute8,
trx.interface_header_attribute9 interface_hdr_attribute9,
trx.interface_header_attribute10 interface_hdr_attribute10,
trx.interface_header_attribute11 interface_hdr_attribute11,
trx.interface_header_attribute12 interface_hdr_attribute12,
trx.interface_header_attribute13 interface_hdr_attribute13,
trx.interface_header_attribute14 interface_hdr_attribute14,
trx.interface_header_attribute15 interface_hdr_attribute15,
hl.address1||hl.address2 ship_to_address,
hl.city ship_to_city,
hl.postal_code ship_to_postal_code,
hl.country ship_to_country,
hl.province ship_to_province
FROM ra_customer_trx trx,
ra_cust_trx_types trx_types,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_party_sites hps,
hz_locations hl
WHERE trx.legal_entity_id = :P_LEGAL_ENTITY_ID
and trx.bill_to_customer_id = :P_CUST_ACCOUNT_ID
and trx.bill_to_site_use_id = :P_BILL_TO_SITE_USE_ID
and trx.ship_to_site_use_id = hcsu.site_use_id(+)
and hcsu.cust_acct_site_id = hcas.cust_acct_site_id(+)
and hcas.party_site_id = hps.party_site_id(+)
and hps.location_id = hl.location_id(+)
and trx.cust_trx_type_id = trx_types.cust_trx_type_id
and trx_types.type in ('INV','CM','DM') --19234760
and trx.org_id = trx_types.org_id
and trx.complete_flag = 'Y'
and trx.trx_date between :P_TRX_DATE_FROM and :P_TRX_DATE_TO
and :P_GEN_OPTION in ('2','3')
and trx.global_attribute8 is NOT NULL -- electronic invoice status is not null
and trx.global_attribute9 = :P_OLD_TRANSMISSION_NUM |
