SELECT
hca.account_number,
(
SELECT
hp.party_name
FROM
apps.hz_parties hp
WHERE
hp.party_id = hca.party_id
) customer_name,
jnb.creation_date notes_creation_date,
jnb.note_type note_type,
fls.meaning note_type_meaning,
jnt.notes notes
FROM
apps.hz_cust_accounts hca,
apps.jtf_notes_b jnb,
apps.jtf_notes_tl jnt,
apps.fnd_lookups fls
WHERE
1 = 1
AND EXISTS (
SELECT
1 from
apps.hz_cust_acct_sites_all hcasa
WHERE
hca.cust_account_id = hcasa.cust_account_id
AND ( hcasa.org_id = 82 or hcasa.org_id = 83 )
)
AND hca.party_id = jnb.source_object_id
AND jnb.source_object_code = ‘PARTY’
AND jnt.jtf_note_id = jnb.jtf_note_id
AND jnt.language = ‘US’
AND fls.lookup_type (+) = ‘JTF_NOTE_TYPE’
AND fls.lookup_code (+) = jnb.note_type
/*AND hca.account_number IN (
‘44017.0V7368’
)*/
ORDER BY
2,
3;
Recommended Posts
