To gather phone, fax, and email in Address Book we can use this query:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
-- Author : $ Ritesh Jain
--
-- Creation Date : 25 October 2017
--
-- Description : Sql query to get supplier, who does not have
-- Contact directory Information
--------------------------------------------------------------------------
SELECT ASS.VENDOR_NAME "SUPPLIER NAME",
ASS.SEGMENT1 "VENDOR NUMBER",
ASS.VENDOR_ID "VENDOR ID",
'NOT AVAILABLE' AS "CONTACT DIRECTORY"
FROM AP_SUPPLIERS ASS,
HZ_PARTIES HP
WHERE 1=1
AND ASS.PARTY_ID=HP.PARTY_ID
AND NOT EXISTS(SELECT *
FROM HZ_RELATIONSHIPS HR
WHERE HR.SUBJECT_ID =ASS.PARTY_ID
AND HR.SUBJECT_TYPE ='ORGANIZATION'
AND HR.RELATIONSHIP_CODE ='CONTACT'
AND HR.RELATIONSHIP_TYPE ='CONTACT'
AND HR.STATUS ='A'
AND HR.object_table_name = 'HZ_PARTIES'
)
ORDER BY ASS.CREATION_DATE DESC;
Posted By Ritesh Jain
|
No comments:
Post a Comment