Currently my boss ask me to create a new project on invoice checking
between our Own Factory and our invoice.

In current Database:
Orders (PRO_INV_NO, PLAN_NO, INV_NO, ...)
OrdItems (INV_NO, ITEM, QTY...)

Print out to Factory:
PLAN_NO, ITEM, QTY ...

Where PLAN_NO is the P.O. of Factory.

New project request to check out the factory invoice about the value correct
or not including check QTY, Fact_cost.

It seem simply, but really not.
1) Partial Shipment:
Current Database give 1+ INV_NO as invoice per shipment.
but Factory invoice give Same PLAN_NO, Same ITEM

2) HK Sales may not yet fill in INVOICE...
3) Factory may miss item on invoice or same item ask for pay twice.

To handle whoelse may not finish task, I need use Full Join

4) HK Sales may fill up both shipment but Factory give 1st shipment invoice
only. If both shipment involve same item#...

Full Join cause duplicate record on factory parts..

Sample result:

HKinv_no  PLAN_NO ITEM     QTY  | FacPLAN_NO FacInv_no FacITEM FacQTY 

6933      12345   MS-4421   20     12345   FT445    MS-4421   20   <~ ok
6939      12345   MS-4421   80     12345   FT445    MS-4421   20   <~ not yet

.NULL.    .NULL.  .NULL.  .NULL.   12346   FT446    MS-4567   30   <~ notfill

6935      12347   MS-4344   30     12347   FT447    MS-4344   30   <~ ok
6935      12347   MS-4346   60     .NULL.  .NULL.   <~ Factory miss data

6937      12348   MS-4354   38     12347   FT447    MS-4354   36   <~ ??

3rd row show HK Sales not yet fill invoice, fail to match.
5th row show Factory miss item not yet delivery, is it stock?

First 2 rows make me headache.
HK sales fill up both invoice information and retrieve them all.

Another Similar case is 2nd shipment, take away HK data from matching by
remove those record which already counted in last matching.
That is ok although the data of FULL join give similar result as above..

Now, if HK Sales fill up both, it come twice on Factory data..
Of cos, there is only ONE row of qty 20, not TWO row.. (not double count)

Original idea will desire the new project can check out any error on both staff work, such as last row, why the qty by factory is less 2 only...
May final find out there was a little accidient and 2 items was broken..
Thus, fail to delivery and need HK sales adjust the invoice correctly..

Since both HK sales and Factory staff may fill data wrongly and duplicate,
the checking process is hard..

My Question is:

How to cut off the 2nd row of above sample result after full join.
Full join using PLAN_NO, ITEM as matching key because factory donno HK INV_NO#