I have to select for remittance a set of invoices which meet the following conditions. All criteria are successfully met except #5.

1. They must never have been included in a previous remittance.
2. All invoices for the reporting period which have been paid in the
reporting period or immediately preceding period must be included.
3. Any invoices for the reporting period which have been paid in the
immediately following period must be included.
4. Any invoices more than two periods old must be included.

5. Any invoices for the following period and paid in the following period must not be included. (This is the part which is giving me the trouble.)

The information on which period monies were received to pay invoices is stored in the receipts table. How the receipts have been used to pay different invoices is stored in the payment allocation table.

RECEIPTS PK creceiptkey
SK cfiscper_key && when $$ received

PAYALLOC PK cpayalloc_key && (not used in this example)
SK creceiptkey
SK cinvoi_key

INVHEAD PK cinvoi_key
SK cfiscper_key && when invoice created

I'm trying to do this trough two views. The first, lv_ok_for_pf_payt, fulfills criteria #2 and #3.

The second, lv_pfundrep, is intended to fulfill the remaining criteria. It all works except that #5 is never met. If an invoice generated in the following period is also paid in that period it is included, *and it shouldn't be*. I was hoping that the line:
"AND alltrim( Invhead.cinvoi_key ) not in ( ?lcNextFiscPer_Key )"
would force the exclusion.

The SQL follows - I've removed many of the selected fields to reduce clutter. Here are the parameters and their meaning:

?lcFiscPer_key -- the reporting period
?lcPrevFiscPer_Key -- the period preceding
?lcNextFiscPer_key -- the period following



*********************************************
******** create view lv_ok_for_pf_payt ******
*********************************************
create sql view lv_ok_for_pf_payt as ;
sele payalloc.cReceiptKey, cInvoi_Key ;
from payalloc ;
where payalloc.cReceiptKey in ;
( select cReceiptKey from receipt ;
where upper( alltrim( receipt.cFiscPer_Key ) ) = ?lcFiscPer_key ;
or upper( alltrim( receipt.cFiscPer_Key ) ) = ?lcPrevFiscPer_Key ;
or upper( alltrim( receipt.cFiscPer_Key ) ) = ?lcNextFiscPer_key );
order by cInvoi_Key


*************************************
***** create view lv_PfundRep ******
*************************************

CREATE SQL VIEW lv_PfundRep AS ;
SELECT Invhead.cinvoi_key, Invhead.cinvoiceno,;
Invhead.ysubtotal,Invhead.ytotal, ;
Invhead.lfullypaid, Invhead.cfiscper_key ;
FROM hea!invhead INNER JOIN hea!company ;
ON Invhead.ccompa_key = Company.ccompa_key;
WHERE ( alltrim( Invhead.cinvoicetype ) not in ;
( "HFX", "MEM", "INT", "THEA" );
AND Invhead.lpfundreport = .F.;
AND Invhead.lfullypaid = .T.;
AND alltrim( Invhead.cinvoi_key ) not in ( ?lcNextFiscPer_Key ) ;
AND Invhead.cinvoi_key in ;
( select cInvoi_key from lv_ok_for_pf_payt ) );
OR ;
( alltrim( Invhead.cinvoicetype ) not in ;
( "HFX", "MEM", "INT", "THEA" );
AND InvHead.lCommitted = .T. ;
AND Invhead.lpfundreport = .F.;
AND InvHead.cFiscPer_Key < ?lcPrevFiscPer_Key );
ORDER by cInvoi_Key


I'm baffled, tired, and probably missing the obvious. Please have a look at it, and if you can, point out where I'm going wrong.

Thanks in advance - Miles Thompson