JE Italian B2G Electronic Invoices XML File Output

Description
Categories: BI Publisher
Columns: Trx Id, Trx Doc Type, Trx Cur Code, Trx Exchange Rate, Trx Date, Trx Number, Trx Po Number, Trx Po Date, Trx Waybill Number, Trx Ship Date Actual ...
Application: European Localizations
Source: Italian B2G Electronic Invoices XML File Output
Short Name: JEITEIFO_XMLP
DB package: JE_IT_B2G_ELECTRONIC_FILE
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