Site, Location and Code Combination Checking in AP

http://aporaclepayables.blogspot.com/2016/06/for-invoice-header-select-ps.html

For Invoice Header

select aia.DOC_SEQUENCE_VALUE,aia.GL_DATE,ps.VENDOR_SITE_CODE, aia.ACCTS_PAY_CODE_COMBINATION_ID,gcc.SEGMENT1, gcc.SEGMENT2, gcc.SEGMENT4
 from  po_vendors pv, po_vendor_sites_all ps, ap_invoices_all aia, ap_invoice_distributions_all ai, gl_code_combinations gcc

where pv.VENDOR_ID=ps.VENDOR_ID
and aia.VENDOR_ID=pv.VENDOR_ID
and ps.VENDOR_ID=aia.VENDOR_ID
and ps.VENDOR_SITE_ID=aia.VENDOR_SITE_ID
and ps.ORG_ID=aia.ORG_ID
and ai.INVOICE_ID=aia.INVOICE_ID
and ai.ORG_ID=aia.ORG_ID
and ps.ORG_ID=ai.ORG_ID
and gcc.CODE_COMBINATION_ID=aia.ACCTS_PAY_CODE_COMBINATION_ID

and ps.VENDOR_SITE_CODE='CH'

and gcc.SEGMENT2!=00

/*and gcc.CODE_COMBINATION_ID=ai.DIST_CODE_COMBINATION_ID
and ps.ACCTS_PAY_CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
*/

For Invoice Distribution

select aia.DOC_SEQUENCE_VALUE,aia.GL_DATE,ps.VENDOR_SITE_CODE, aia.ACCTS_PAY_CODE_COMBINATION_ID,gcc.SEGMENT1, gcc.SEGMENT2, gcc.SEGMENT4
 from  po_vendors pv, po_vendor_sites_all ps, ap_invoices_all aia, ap_invoice_distributions_all ai, gl_code_combinations gcc

where pv.VENDOR_ID=ps.VENDOR_ID
and aia.VENDOR_ID=pv.VENDOR_ID
and ps.VENDOR_ID=aia.VENDOR_ID
and ps.VENDOR_SITE_ID=aia.VENDOR_SITE_ID
and ps.ORG_ID=aia.ORG_ID
and ai.INVOICE_ID=aia.INVOICE_ID
and ai.ORG_ID=aia.ORG_ID
and ps.ORG_ID=ai.ORG_ID
--and gcc.CODE_COMBINATION_ID=aia.ACCTS_PAY_CODE_COMBINATION_ID

and ps.VENDOR_SITE_CODE='HO'
and gcc.SEGMENT2!=05
and aia.GL_DATE>'01-JAN-2015'

and gcc.CODE_COMBINATION_ID=ai.DIST_CODE_COMBINATION_ID

--and ps.ACCTS_PAY_CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID