<?xml version="1.0" encoding="UTF-8"?>
<dataTemplate name="LOCUMPAY" version="1.0">
<properties>
<property name ="scalable_mode" value="on"/>
</properties>
<properties>
<property name="db_fetch_size" value="20"/>
</properties>
<parameters>
<parameter name="P_TAX_YR_END" datatype="number"/>
<parameter name="P_PAY_LIMIT" datatype="number"/>
</parameters>
<dataQuery>
<sqlStatement name="Q1_LPDATA_SELECTION">
<![CDATA[	
	SELECT  aps.vendor_name     Supplier_Name
		,apssa.address_line1 Address1
		,apssa.address_line2 Address2
		,apssa.address_line3 Address3
		,apssa.city          Address4
		,apssa.state         Address5
		,apssa.zip           Postcode
		,decode(apca.currency_code,'GBP',apca.amount,apca.base_amount) Gross_amount_paid
		,'GBP' Currency_code
		,(SELECT NVL((SELECT MAX('Y')
				FROM ap_checks_all apcax,
				ap_invoice_distributions_all apidx,
				ap_invoice_payments_all apipx
					WHERE 1=1
					AND apidx.invoice_id = apipx.invoice_id
					AND apcax.check_id = apipx.check_id
					AND apidx.line_type_lookup_code = 'REC_TAX'
					AND apcax.check_id = apca.check_id)
			,'N')
			FROM dual)Includes_vat
		,(SELECT last_day(to_date(('05-APR-'||:P_TAX_YR_END), 'DD/MM/YYYY'))FROM dual) Period_end_date
		,'Locum Pharmacy' Payment_description
		,(SELECT MAX(gsob.name)
			FROM gl_sets_of_books gsob,
			ap_invoices_all aia,
			ap_invoice_payments_all aipa,
			ap_checks_all acax
				WHERE 1=1
				AND aia.set_of_books_id = gsob.set_of_books_id
				AND aia.invoice_id = aipa.invoice_id
				AND acax.check_id = aipa.check_id
				AND acax.check_id = apca.check_id) Company_name
		,apca.check_number Payee_Reference
	FROM
	(	SELECT  DISTINCT acax.check_id,aia.vendor_id,aia.vendor_site_id
			FROM ap_invoice_payments_all aipa,
			ap_invoices_all aia,
			ap_checks_all acax
				WHERE aipa.check_id= acax.check_id
				AND  aipa.invoice_id = aia.invoice_id	) vend
	,(	SELECT SUM(decode(aca.currency_code,'GBP',aca.amount,aca.base_amount)) gross_amount_paid,
        	aca.check_id
			FROM ap_checks_all aca
			GROUP BY aca.check_id	) spend
	,ap_suppliers aps
	,ap_supplier_sites_all apssa
	,ap_checks_all apca
	WHERE  1=1
	AND  aps.num_1099 IS NOT NULL
	AND  aps.vendor_id        = vend.vendor_id
	AND  apssa.vendor_site_id = vend.vendor_site_id
	AND  apca.check_id        = vend.check_id
	AND  spend.check_id = apca.check_id
	AND  spend.gross_amount_paid > :P_PAY_LIMIT
	AND  apca.check_date BETWEEN ('06-APR-'||(:P_TAX_YR_END - 1)) and ('05-APR-'||:P_TAX_YR_END)
	ORDER BY aps.vendor_name ]]>	 
</sqlStatement>
</dataQuery>
<dataStructure>
	<group name="G_DATA_SELECTION_Q2" source="Q1_LPDATA_SELECTION">
		<element name="SUPPLIER_NAME" value="SUPPLIER_NAME"/>
		<element name="ADDRESS1" value="ADDRESS1"/>
		<element name="ADDRESS2" value="ADDRESS2"/>
		<element name="ADDRESS3" value="ADDRESS3"/>
		<element name="ADDRESS4" value="ADDRESS4"/>
		<element name="ADDRESS5" value="ADDRESS5"/>
		<element name="POSTCODE" value="POSTCODE"/>
		<element name="GROSS_AMOUNT_PAID" value="GROSS_AMOUNT_PAID"/>
		<element name="CURRENCY_CODE" value="CURRENCY_CODE"/>
		<element name="INCLUDES_VAT" value="Includes_vat"/>
		<element name="PERIOD_END_DATE" value="PERIOD_END_DATE"/>
		<element name="PAYMENT_DESCRIPTION" value="PAYMENT_DESCRIPTION"/>
		<element name="COMPANY_NAME" value="COMPANY_NAME"/>
		<element name="PAYEE_REFERENCE" value="PAYEE_REFERENCE"/>
	</group>
</dataStructure>
</dataTemplate>







