[Home] [Help]
PACKAGE BODY: APPS.PRIMARY_CONTACTS_MGR
Source
1 PACKAGE BODY PRIMARY_CONTACTS_MGR AS
2 /* $Header: JTFAPRMB.pls 120.1 2005/07/02 02:00:24 appldev ship $ */
3
4 FUNCTION query_primary_contacts(API_VERSION IN NUMBER DEFAULT 1.0,
5 P_PARTY_ID IN NUMBER)
6 RETURN VARCHAR2 IS
7
8 l_sort_data JTF_TASKS_PUB.SORT_DATA;
9 l_total_retrieved NUMBER;
10 l_total_returned NUMBER;
11 l_rs VARCHAR2(1);
12 l_msg_count NUMBER;
13 l_msg_data VARCHAR2(2000);
14 l_version_num NUMBER;
15
16 l_first_name VARCHAR2(2000);
17 l_middle_name VARCHAR2(2000);
18 l_last_name VARCHAR2(2000);
19
20 new_row VARCHAR2(4000) := '';
21 result VARCHAR2(4000) := '';
22
23 current_count NUMBER := 1;
24 row_count NUMBER := 10;
25
26 -- new field names:
27 --SELECT PERSON_FIRST_NAME, PERSON_LAST_NAME,
28 -- PERSON_MIDDLE_NAME
29 --FROM JTF_PARTY_CUSTOMERS_V
30 --WHERE ROW_ID = P_COMPANY_ID;
31
32 --SELECT FIRST_NAME, LAST_NAME,
33 -- MIDDLE_NAME
34 --FROM JTF_PARTY_CUSTOMERS_V
35 --WHERE PARTY_ID = P_COMPANY_ID;
36
37 CURSOR primary_contacts IS
38 SELECT FIRST_NAME, LAST_NAME, MIDDLE_NAME, PARTY_ID
39 FROM JTF_PARTY_CUSTOMERS_V
40 --WHERE LAST_NAME IS NOT NULL
41 WHERE PARTY_ID = P_PARTY_ID
42 --AND PARTY_ID < 9075
43 ORDER BY 1;
44
45 one_row primary_contacts%rowtype;
46
47 BEGIN
48
49 --DBMS_OUTPUT.PUT_LINE('Querying primary contacts...');
50
51 OPEN primary_contacts;
52 FETCH primary_contacts INTO one_row;
53
54 WHILE primary_contacts%FOUND LOOP
55
56 current_count := current_count + 1;
57 IF (current_count > row_count) THEN
58 EXIT;
59 END IF;
60
61 l_first_name := one_row.first_name;
62 l_middle_name := one_row.middle_name;
63 l_last_name := one_row.last_name;
64
65 IF( l_first_name IS NULL ) THEN
66 l_first_name := 'first name null '||
67 one_row.party_id;
68 END IF;
69
70 IF( l_middle_name IS NULL ) THEN
71 l_middle_name := 'middle name null '||
72 one_row.party_id;
73 END IF;
74
75 IF( l_last_name IS NULL ) THEN
76 l_last_name := 'last name null '||
77 one_row.party_id;
78 END IF;
79
80 new_row := trim(l_first_name)||' '||trim(l_middle_name)
81 ||' '||trim(l_last_name)||'^';
82 result := result || new_row;
83
84 FETCH primary_contacts INTO one_row;
85 END LOOP;
86
87 CLOSE primary_contacts;
88
89 RETURN( result );
90
91 END QUERY_PRIMARY_CONTACTS;
92
93
94 PROCEDURE test(API_VERSION IN NUMBER DEFAULT 1.0,
95 P_PARTY_ID IN NUMBER DEFAULT 1) IS
96
97 l_sort_data JTF_TASKS_PUB.SORT_DATA;
98 l_total_retrieved NUMBER;
99 l_total_returned NUMBER;
100 l_rs VARCHAR2(1);
101 l_msg_count NUMBER;
102 l_msg_data VARCHAR2(2000);
103 l_version_num NUMBER;
104
105 l_first_name VARCHAR2(2000) := '';
106 l_middle_name VARCHAR2(2000) := '';
107 l_last_name VARCHAR2(2000) := '';
108
109 new_row VARCHAR2(4000) := '';
110 result VARCHAR2(4000) := '';
111 current_count NUMBER := 1;
112 row_count NUMBER := 10;
113
114 -- new field names:
115 --SELECT PERSON_FIRST_NAME, PERSON_LAST_NAME,
116 -- PERSON_MIDDLE_NAME
117 --FROM JTF_PARTY_CUSTOMERS_V
118 --WHERE PARTY_ID = P_PARTY_ID
119
120 --SELECT FIRST_NAME, LAST_NAME,
121 -- MIDDLE_NAME
122 --FROM JTF_PARTY_CUSTOMERS_V
123 --WHERE PARTY_ID = P_PARTY_ID
124
125 CURSOR primary_contacts IS
126 SELECT FIRST_NAME, LAST_NAME, MIDDLE_NAME, PARTY_ID
127 FROM JTF_PARTY_CUSTOMERS_V
128 --WHERE LAST_NAME IS NOT NULL
129 WHERE PARTY_ID = P_PARTY_ID
130 --AND PARTY_ID < 9075
131 ORDER BY 1;
132
133 one_row primary_contacts%rowtype;
134
135 BEGIN
136
137 -- DBMS_OUTPUT.PUT_LINE('test Querying primary contacts...');
138
139 OPEN primary_contacts;
140 FETCH primary_contacts INTO one_row;
141
142 WHILE primary_contacts%FOUND LOOP
143
144 l_first_name := one_row.first_name;
145 l_middle_name := one_row.middle_name;
146 l_last_name := one_row.last_name;
147
148 IF( l_first_name IS NULL ) THEN
149 l_first_name := 'first name null '||
150 one_row.party_id;
151 END IF;
152
153 IF( l_middle_name IS NULL ) THEN
154 l_middle_name := 'middle name null '||
155 one_row.party_id;
156 END IF;
157
158 IF( l_last_name IS NULL ) THEN
159 l_last_name := 'last name null '||
160 one_row.party_id;
161 END IF;
162
163 new_row := trim(l_first_name)||' '||trim(l_middle_name)
164 ||' '||trim(l_last_name)||'^';
165 result := result || new_row;
166
167 --DBMS_OUTPUT.PUT_LINE('3 current_count = '||current_count);
168 --DBMS_OUTPUT.PUT_LINE('3 new row = '||new_row);
169
170 current_count := current_count + 1;
171 IF (current_count > row_count) THEN
172 EXIT;
173 END IF;
174
175 FETCH primary_contacts INTO one_row;
176 END LOOP;
177
178 CLOSE primary_contacts;
179
180 --DBMS_OUTPUT.PUT_LINE('result = '||result);
181
182 END test;
183
184 END PRIMARY_CONTACTS_MGR;