gistfile1.txt

SELECT * from 
(
	select a.doc_no from bill_attachments as a JOIN bill_headers as b on a.ref_header_id = b.id where b.status in ("Approved","Printed")
	UNION ALL
	select a.doc_no from receipt_app_attachments as a JOIN bill_headers as b on a.ref_header_id = b.id where b.status in ("Approved","Printed")
) as tbl
LEFT JOIN
(
SELECT j.doc_type_id, j.doc_type, partners.name,CAST(j.doc_no as UNSIGNED) as doc_no, j.date, j.partner_id, j.amount, j.gov, j.vat, j.nv, j.zr, j.nt, j.ewt
									FROM (
									(SELECT receipt_app_attachments.doc_type_id as doc_type_id, 'RCT' as doc_type,
									       CAST(receipt_app_attachments.doc_no as UNSIGNED)  as doc_no, receipt_app_attachments.attachment_date as date, postings.partner_id as partner_id,
												 receipt_app_attachments.amount as amount, 
											   SUM(IF(posting_sales.sales_type='gov',posting_sales.amount,NULL)) AS gov,
										     SUM(IF(posting_sales.sales_type='vat',posting_sales.amount,NULL)) AS vat,
												 SUM(IF(posting_sales.sales_type='nv',posting_sales.amount,NULL)) AS nv,
									       SUM(IF(posting_sales.sales_type='zr',posting_sales.amount,NULL)) AS zr,
									       SUM(IF(posting_sales.sales_type='nt',posting_sales.amount,NULL)) AS nt, 
												 sum(posting_sales.ewt) as ewt
									from app_headers 
									left outer join receipt_app_attachments on app_headers.id = receipt_app_attachments.ref_header_id
									left join postings on receipt_app_attachments.id = postings.attachment_no
									left join posting_sales on postings.id = posting_sales.posting_id
									where app_headers.status in ('Approved','Printed')
									and postings.doc_type = 'ReceiptApp' 
									and postings.status = 'Posted' and postings.posting_type = 'Sales'
									GROUP BY receipt_app_attachments.doc_no)
									UNION ALL
									(SELECT bill_attachments.doc_type_id  as doc_type_id, 'Bill' as doc_type,
									       CAST(bill_attachments.doc_no as UNSIGNED) as doc_no, bill_attachments.attachment_date as date, postings.partner_id as partner_id,
												 bill_attachments.amount as amount, 
											   SUM(IF(posting_sales.sales_type='gov',posting_sales.amount,NULL)) AS gov,
										     SUM(IF(posting_sales.sales_type='vat',posting_sales.amount,NULL)) AS vat,
												 SUM(IF(posting_sales.sales_type='nv',posting_sales.amount,NULL)) AS nv,
									       SUM(IF(posting_sales.sales_type='zr',posting_sales.amount,NULL)) AS zr,
									       SUM(IF(posting_sales.sales_type='nt',posting_sales.amount,NULL)) AS nt, 
												 sum(posting_sales.ewt) as ewt
									from bill_headers 
									left outer join bill_attachments on bill_headers.id = bill_attachments.ref_header_id
									left join postings on bill_attachments.id = postings.attachment_no
									left join posting_sales on postings.id = posting_sales.posting_id
									where bill_headers.status in ('Approved','Printed')
									and postings.doc_type = 'Bill' and postings.status = 'Posted' and postings.posting_type = 'Sales'
									GROUP BY bill_attachments.doc_no)
									UNION ALL
									(
										select '4' as doc_type_id, 'JV' as doc_type, postings.doc_no as doc_no, postings.post_date as date, postings.partner_id as partner_id,
									jv_headers.dbtotal as amount,
									SUM(IF(posting_sales.sales_type='gov',posting_sales.amount,NULL)) AS gov,
										     SUM(IF(posting_sales.sales_type='vat',posting_sales.amount,NULL)) AS vat,
												 SUM(IF(posting_sales.sales_type='nv',posting_sales.amount,NULL)) AS nv,
									       SUM(IF(posting_sales.sales_type='zr',posting_sales.amount,NULL)) AS zr,
									       SUM(IF(posting_sales.sales_type='nt',posting_sales.amount,NULL)) AS nt, 
												 sum(posting_sales.ewt) as ewt
									  from jv_headers
									LEFT JOIN  postings on jv_headers.id = postings.doc_no
									left join posting_sales on postings.id = posting_sales.posting_id
									where postings.posting_type = 'Sales' and postings.doc_type = 'JV' and postings.status = 'Posted' and jv_headers.status in('Approved','Printed')
									)
									UNION ALL
									(
									select '4' as doc_type_id, 'Memo' as doc_type, postings.doc_no as doc_no, postings.post_date as date, postings.partner_id as partner_id,
									memo_app_headers.total as amount,
									SUM(IF(posting_sales.sales_type='gov',posting_sales.amount,NULL)) AS gov,
										     SUM(IF(posting_sales.sales_type='vat',posting_sales.amount,NULL)) AS vat,
												 SUM(IF(posting_sales.sales_type='nv',posting_sales.amount,NULL)) AS nv,
									       SUM(IF(posting_sales.sales_type='zr',posting_sales.amount,NULL)) AS zr,
									       SUM(IF(posting_sales.sales_type='nt',posting_sales.amount,NULL)) AS nt, 
												 sum(posting_sales.ewt) as ewt
									  from memo_app_headers
									LEFT JOIN  postings on memo_app_headers.id = postings.doc_no
									left join posting_sales on postings.id = posting_sales.posting_id
									where postings.posting_type = 'Sales' and postings.doc_type in ('DMA','CMA') and postings.status = 'Posted' and memo_app_headers.status in('Approved','Printed')
									)
									) as j
									LEFT JOIN partners on j.partner_id = partners.id
									where j.doc_no IS NOT NULL 
									ORDER BY j.doc_no asc
) as tbl1 on tbl.doc_no = tbl1.doc_no
ORDER BY tbl1.doc_no asc