[Home] [Help]
PACKAGE BODY: APPS.HZ_EBI_CUST_LOAD
Source
1 PACKAGE BODY HZ_EBI_CUST_LOAD AS
2 /* $Header: ARHEICSTLDB.pls 120.4 2009/07/20 10:31:32 aashah noship $ */
3
4 PROCEDURE RAISE_CUST_LOAD_EVENT( p_event_name IN VARCHAR2
5 ,p_event_id IN NUMBER DEFAULT NULL
6 ,x_msg_data OUT NOCOPY VARCHAR2
7 ,x_return_status OUT NOCOPY VARCHAR2
8 );
9
10 PROCEDURE PURGE_EVENTLOG
11 IS
12 BEGIN
13 DELETE FROM HZ_EBI_CUST_LOAD_LOG;
14 COMMIT;
15
16 END PURGE_EVENTLOG;
17
18 PROCEDURE GENERATE_EVENTS( p_batch_size IN NUMBER DEFAULT 20
19 ,p_max_events IN NUMBER DEFAULT NULL
20 ,x_err_msg OUT NOCOPY VARCHAR2)
21
22 IS
23 l_logCustCount NUMBER;
24 l_CustCount NUMBER;
25 l_eventId NUMBER;
26 l_eventsRaised NUMBER :=0;
27 l_msg_data VARCHAR2(500);
28 l_return_status VARCHAR2(500);
29 l_batch_size NUMBER;
30 l_max_events NUMBER;
31
32 BEGIN
33 x_err_msg := '';
34 l_logCustCount := 0;
35 l_batch_size := p_batch_size;
36 l_max_events :=p_max_events ;
37
38 --get count from log(HZ_EBI_CUST_LOAD_LOG) table
39 SELECT count(*) INTO l_logCustCount FROM HZ_EBI_CUST_LOAD_LOG;
40
41 --Insert records to Event Log table, if there are no records in the event log table.
42 IF(l_logCustCount = 0) THEN
43 --Insert required item records into event log table .
44 x_err_msg := x_err_msg || '\n Inserting Records to Event log table';
45 -- Initialize Event Id to 0
46 l_eventId := 0;
47 INSERT INTO HZ_EBI_CUST_LOAD_LOG( PARTY_ID , EVENT_ID)
48 Select distinct party.PARTY_ID ,NULL
49 from HZ_PARTIES party, HZ_CUST_ACCOUNTS accnt where
50 accnt.status='A' and party.PARTY_ID = accnt.party_id;
51 --Commit records. To check commit frequency
52 COMMIT;
53
54 ELSE
55 -- Initialize Event Id to 0
56 SELECT MAX(NVL(EVENT_ID,0)) into l_eventId FROM HZ_EBI_CUST_LOAD_LOG;
57
58 END IF;
59
60 x_err_msg := 'Event Id Initialized to ' || TO_CHAR(l_eventId) ;
61 --Get Count of Item for which the Event should be raised
62 SELECT count(*) INTO l_CustCount FROM HZ_EBI_CUST_LOAD_LOG WHERE EVENT_ID IS NULL;
63 WHILE (l_CustCount > 0 AND (l_max_events IS NULL OR l_eventsRaised < l_max_events))
64 LOOP
65 --Generate new <event-id>
66 l_eventId := l_eventId +1;
67 x_err_msg := 'Event Id ' ||TO_CHAR(l_eventId)|| ' updating';
68 UPDATE HZ_EBI_CUST_LOAD_LOG
69 SET EVENT_ID = l_eventId
70 WHERE EVENT_ID IS NULL AND ROWNUM < l_batch_size +1;
71 l_CustCount := l_CustCount - l_batch_size;
72 --Raise event <event-id>
73 Raise_CUST_LOAD_Event (HZ_EBI_CUST_LOAD.G_CUST_LOAD_EVENT, l_eventId, l_msg_data, l_return_status);
74 x_err_msg := 'Raised Event' || TO_CHAR(l_eventId) || ' with return status = ' || l_return_status ;
75 l_eventsRaised := l_eventsRaised +1;
76 COMMIT;
77
78 END LOOP;
79 x_err_msg := 'Raised ' || TO_CHAR(l_eventsRaised) || ' events. ';
80 IF ( l_CustCount >0 ) THEN
81 x_err_msg := x_err_msg || ' There are ' || TO_CHAR(l_CustCount ) || ' more customers pending.';
82 END IF;
83
84
85 END Generate_Events;
86
87 --To regenrate failed event provide the event id
88 PROCEDURE REGENERATE_FAILED_EVENT( p_event_id IN NUMBER
89 ,x_err_msg OUT NOCOPY VARCHAR2
90 )
91 IS
92 l_logCustCount NUMBER;
93 l_msg_data VARCHAR2(500);
94 l_return_status VARCHAR2(500);
95 l_eventId NUMBER;
96
97
98 BEGIN
99 x_err_msg := '';
100 l_return_status := '';
101 l_msg_data := '';
102 -- Get the Item count in the event log for the event
103 SELECT count(*) INTO l_logCustCount
104 FROM HZ_EBI_CUST_LOAD_LOG
105 WHERE EVENT_ID = p_event_id;
106
107 --if there are records in the event log table for the event, then update the batch and then raise the event.
108 IF (l_logCustCount > 0) THEN
109 BEGIN
110
111 x_err_msg := x_err_msg || 'Deleting recods from Event log table for the event ' || TO_CHAR(p_event_id) || ' that are not active';
112 --Deleting recods from Event log table for the event that are not active
113 DELETE FROM HZ_EBI_CUST_LOAD_LOG
114 WHERE EVENT_ID = p_event_id AND
115 PARTY_ID NOT IN(
116 SELECT DISTINCT party.PARTY_ID
117 FROM HZ_PARTIES party, HZ_CUST_ACCOUNTS accnt
118 WHERE accnt.STATUS='A' and party.PARTY_ID = accnt.PARTY_ID);
119
120 SELECT MAX(NVL(EVENT_ID,0))+1 into l_eventId FROM HZ_EBI_CUST_LOAD_LOG;
121
122 UPDATE HZ_EBI_CUST_LOAD_LOG
123 SET EVENT_ID = l_eventId
124 WHERE EVENT_ID = p_event_id ;
125 --Raise event <event-id>
126 Raise_CUST_LOAD_Event (HZ_EBI_CUST_LOAD.G_CUST_LOAD_EVENT, l_eventId, l_msg_data, l_return_status);
127 x_err_msg := 'Raised Event' || TO_CHAR(l_eventId) || ' with return status = ' || l_return_status ;
128 COMMIT;
129 END;
130 ELSE
131 x_err_msg := 'Could not find event id' || TO_CHAR(p_event_id);
132 END IF;
133
134 END REGENERATE_FAILED_EVENT;
135
136 PROCEDURE RAISE_CUST_LOAD_EVENT (
137 p_event_name IN VARCHAR2
138 ,p_event_id IN NUMBER DEFAULT NULL
139 ,x_msg_data OUT NOCOPY VARCHAR2
140 ,x_return_status OUT NOCOPY VARCHAR2
141 )
142 IS
143 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
144 l_parameter_t WF_PARAMETER_T:= WF_PARAMETER_T(null, null);
145 l_event_name VARCHAR2(240);
146 l_event_key VARCHAR2(240);
147 BEGIN
148 l_event_name := p_event_name ;
149 l_event_key := p_event_id || SUBSTRB(l_event_name, 1, 225) || '-' || TO_CHAR(SYSDATE, 'J.SSSSS');
150 WF_EVENT.AddParameterToList( p_name => 'CDH_EVENT_ID'
151 ,p_value => p_event_id
152 ,p_ParameterList => l_parameter_List);
153
154 WF_EVENT.Raise( p_event_name => l_event_name
155 ,p_event_key => l_event_key
156 ,p_parameters => l_parameter_list);
157 l_parameter_list.DELETE;
158 x_return_status := FND_API.G_RET_STS_SUCCESS;
159
160 EXCEPTION
161 WHEN Others THEN
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
163 x_msg_data := SQLERRM ;
164
165 END Raise_CUST_LOAD_Event;
166
167 PROCEDURE Get_Org_Custs_BO( p_event_id IN NUMBER
168 ,x_org_cust_objs OUT NOCOPY HZ_ORG_CUST_BO_TBL
169 ,x_return_status OUT NOCOPY VARCHAR2
170 ,x_messages OUT NOCOPY HZ_MESSAGE_OBJ_TBL
171 )
172 Is
173 x_org_cust_obj HZ_ORG_CUST_BO;
174 x_msg_data VARCHAR2(2000);
175 x_msg_count NUMBER;
176 party_id NUMBER;
177 l_count NUMBER ;
178
179 CURSOR cust_cur IS
180 SELECT party_id from HZ_EBI_CUST_LOAD_LOG where event_id = p_event_id;
181
182 BEGIN
183 x_org_cust_objs := HZ_ORG_CUST_BO_TBL();
184 x_messages := HZ_MESSAGE_OBJ_TBL();
185 l_count :=0;
186 OPEN cust_cur;
187 LOOP
188 FETCH cust_cur INTO party_id;
189 EXIT WHEN cust_cur%NOTFOUND;
190 x_org_cust_objs.extend(1);
191 x_messages.extend(1) ;
192 l_count := l_count+1;
193 HZ_ORG_CUST_BO_PUB.get_org_cust_bo( p_organization_id => party_id
194 ,p_organization_os =>NULL
195 ,p_organization_osr =>NULL
196 ,x_org_cust_obj => x_org_cust_obj
197 ,x_return_status => x_return_status
198 ,x_msg_count => x_msg_count
199 ,x_msg_data => x_msg_data
200 );
201
202 x_org_cust_objs(l_count) := x_org_cust_obj;
203 x_messages(l_count) := HZ_MESSAGE_OBJ(x_msg_data);
204
205 END LOOP;
206 CLOSE cust_cur;
207
208 IF (l_count =0) THEN
209 x_messages.extend(1) ;
210 x_msg_data :=' This event_id : '||p_event_id||' does not Exist ';
211 x_messages(1):=HZ_MESSAGE_OBJ(x_msg_data);
212 END IF;
213
214
215 END Get_Org_Custs_BO;
216
217 END HZ_EBI_CUST_LOAD;