By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE your_data (TRANSACTION_ID, WAREHOUSE, INTERCOMPANY_ID, ACCOUNT_LINE_TYPE, CREATED_FROM) AS
SELECT 57261018, 'CA', NULL, 'INCOME', NULL FROM DUAL UNION ALL
SELECT 57281538, 'CA', 57281544, 'ASSET', 57261018 FROM DUAL UNION ALL
SELECT 57281544, 'WY', 57281538, 'INTERCOINCOME', NULL FROM DUAL;
3 rows affected
SELECT a.transaction_id,
COALESCE(ii.warehouse, a.warehouse) AS warehouse,
a.intercompany_id,
a.account_line_type,
a.created_from
FROM your_data a
LEFT OUTER JOIN your_data i
ON ( a.account_line_type = 'ASSET'
AND i.account_line_type = 'INCOME'
AND i.transaction_id = a.created_from )
LEFT OUTER JOIN your_data ii
ON ( a.account_line_type = 'ASSET'
AND ii.account_line_type = 'INTERCOINCOME'
AND ii.transaction_id = a.intercompany_id
AND i.transaction_id IS NOT NULL );
| TRANSACTION_ID | WAREHOUSE | INTERCOMPANY_ID | ACCOUNT_LINE_TYPE | CREATED_FROM |
|---|---|---|---|---|
| 57281538 | WY | 57281544 | ASSET | 57261018 |
| 57281544 | WY | 57281538 | INTERCOINCOME | null |
| 57261018 | CA | null | INCOME | null |