Relationship Between Charge_Transaction and Order_Invoice Tables

Resolving The Problem

The tables yfs_charge_transaction and yfs_order_invoice have FK pointing to each other. However, If you execute following SQL statement:

Select Order_Invoice_key, charge_transaction_key
from yfs_charge_transaction
where order_header_key = '&SHIPPED_OrderHeaderKey' and charge_type = 'SHIPMENT'

the result will be a few order_invoice_keys. Using these order_invoice_keys, the charge_transaction_key can be retrieved from the yfs_order_invoice table. The charge_transaction_key populated from yfs_order_invoice table matches with records in yfs_charge_transaction where charge_type = 'CHARGE' (if it exists), and does not match with originally obtained records from yfs_charge_transaction (charge_type = 'SHIPMENT').

Example:

-- obtain order_invoice_key
select Order_Invoice_key, charge_transaction_key
from yfs_charge_transaction
where order_header_key = '20000822180125164' and charge_type = 'SHIPMENT'

Order_Invoice_key charge_transaction_key
------------------------ ------------------------
20000822181308204 20000822181308206
200010031502378979 200010031502378981
20000908171922174 20000908171922176
20000911181443319 20000911181443321
===============

Select Charge_Transaction_key
from yfs_order_invoice
where order_invoice_key in ('20000822181308204',
'200010031502378979',
'20000908171922174',
'20000911181443319')

Charge_Transaction_key
------------------------
200009281315478792
200009281315478792
200009281315478792
200010121506299335
==================

Select Charge_Transaction_key, Order_Header_key, order_invoice_key, charge_type
from yfs_charge_transaction
where charge_transaction_key in
('200009281315478792' ,
'200010121506299335' )

Charge_Transaction_key Order_Header_key order_invoice_key charge_type
------------------------ ------------------------ ------------------------ --------------------
200009281315478792 20000822180125164 CHARGE
200010121506299335 20000822180125164 CHARGE
==================