Troubleshooting
Problem
Relationship Between Charge_Transaction and Order_Invoice Tables
Symptom
PART: Platform 2.0 SP3 Platform
COMPONENT: <None>
OS: Windows NT - 4.0
SP 3
DATABASE: Oracle - 8.1.6
WEB SERVER: IIS - 3.0
WEB BROWSER:
Internet Explorer - 5.5 sp1
Cause
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
==================
Historical Number
PRI48334
Product Synonym
[<p><b>]Fact[</b><p>];
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21525210