[Home] [Help]
PACKAGE BODY: APPS.EGO_EBI_ITEM_LOAD
Source
1 PACKAGE BODY "EGO_EBI_ITEM_LOAD" AS
2 /* $Header: EGOVEILB.pls 120.3 2009/07/20 11:29:18 aashah noship $ */
3 --Private prcoedure for raising the events
4 PROCEDURE RAISE_ITEM_LOAD_EVENT(
5 p_event_name IN VARCHAR2
6 ,p_event_id IN NUMBER DEFAULT NULL
7 ,x_msg_data OUT NOCOPY VARCHAR2
8 ,x_return_status OUT NOCOPY VARCHAR2
9 );
10
11 --Purge load events from Event log table
12 PROCEDURE PURGE_EVENTLOG
13 IS
14 BEGIN
15 DELETE FROM EGO_EBI_ITEM_LOAD_LOG;
16 COMMIT;
17 END PURGE_EVENTLOG;
18
19 --Genrate Item Load Events
20 PROCEDURE GENERATE_EVENTS( p_organization_id IN NUMBER
21 ,p_batch_size IN NUMBER DEFAULT 20
22 ,p_max_events IN NUMBER DEFAULT NULL
23 ,x_err_msg OUT NOCOPY VARCHAR2)
24 IS
25 l_logItemCount NUMBER;
26 l_ItemCount NUMBER;
27 l_eventId NUMBER;
28 l_eventsRaised NUMBER :=0;
29 l_msg_data VARCHAR2(500);
30 l_return_status VARCHAR2(500);
31 l_batch_size NUMBER;
32 l_max_events NUMBER;
33
34
35 BEGIN
36 x_err_msg := '';
37 l_batch_size := p_batch_size;
38 l_max_events := p_max_events;
39
40 -- Get the Item count in the event log
41 SELECT count(*) INTO l_logItemCount FROM EGO_EBI_ITEM_LOAD_LOG;
42
43 --if there are no records in the event log table Insert records to Event Log table .
44 IF (l_logItemCount = 0) THEN
45 BEGIN
46 --Insert required item records into event log table .
47 x_err_msg := x_err_msg || '\n Inserting Records to Event log table';
48 -- Initialize Event Id to 0
49 l_eventId := 0;
50 INSERT INTO EGO_EBI_ITEM_LOAD_LOG (INVENTORY_ITEM_ID,ORGANIZATION_ID, EVENT_ID)
51 SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, NULL
52 FROM MTL_SYSTEM_ITEMS_B
53 WHERE ORGANIZATION_ID = p_organization_id
54 AND bom_item_type in (1, 2, 4)
55 AND customer_order_flag = 'Y'
56 AND customer_order_enabled_flag = 'Y';
57 COMMIT;
58 END;
59 ELSE
60 -- Initialize Event Id from the last run of the generate_events
61 SELECT MAX(NVL(EVENT_ID,0)) into l_eventId FROM EGO_EBI_ITEM_LOAD_LOG;
62 END IF;
63 x_err_msg := 'Event Id Initialized to ' || TO_CHAR(l_eventId) ;
64
65 --Get Count of Item for which the Event should be raised
66 SELECT count(*) INTO l_ItemCount FROM EGO_EBI_ITEM_LOAD_LOG WHERE EVENT_ID IS NULL;
67 WHILE (l_ItemCount > 0 AND (l_max_events IS NULL OR l_eventsRaised < l_max_events)) LOOP
68 BEGIN
69 --Generate new <event-id>
70 l_eventId := l_eventId +1;
71 x_err_msg := 'Event Id ' ||TO_CHAR(l_eventId)|| ' updating';
72 UPDATE EGO_EBI_ITEM_LOAD_LOG
73 SET EVENT_ID = l_eventId
74 WHERE EVENT_ID IS NULl AND ROWNUM < l_batch_size +1;
75 l_ItemCount := l_ItemCount - l_batch_size;
76
77 --Raise event <event-id>
78 Raise_Item_LOAD_Event (EGO_EBI_ITEM_LOAD.G_ITEM_LOAD_EVENT, l_eventId, l_msg_data, l_return_status);
79 x_err_msg := 'Raised Event' || TO_CHAR(l_eventId) || ' with return status = ' || l_return_status ;
80 l_eventsRaised := l_eventsRaised +1;
81 COMMIT;
82 END;
83 END LOOP;
84 x_err_msg := 'Raised ' || TO_CHAR(l_eventsRaised) || ' events. ';
85 IF ( l_ItemCount >0 ) THEN
86 x_err_msg := x_err_msg || ' There are ' || TO_CHAR(l_ItemCount) || ' more items pending.';
87 END IF;
88 END Generate_Events;
89
90
91 --To regenrate failed event provide the event id
92 PROCEDURE REGENERATE_FAILED_EVENT( p_organization_id IN NUMBER
93 ,p_event_id IN NUMBER
94 ,x_err_msg OUT NOCOPY VARCHAR2
95 )
96 IS
97 l_logItemCount NUMBER;
98 l_msg_data VARCHAR2(500);
99 l_return_status VARCHAR2(500);
100 l_eventId NUMBER;
101
102
103 BEGIN
104 x_err_msg := '';
105 l_return_status := '';
106 l_msg_data := '';
107 -- Get the Item count in the event log for the event
108 SELECT count(*) INTO l_logItemCount
109 FROM EGO_EBI_ITEM_LOAD_LOG
110 WHERE ORGANIZATION_ID = p_organization_id
111 AND EVENT_ID = p_event_id;
112
113 --if there are records in the event log table Insert records to Event Log table .
114 IF (l_logItemCount > 0) THEN
115 BEGIN
116 --Insert required item records into event log table .
117 x_err_msg := x_err_msg || 'Deleting recods from Event log table for the event ' || TO_CHAR(p_event_id) || ' that are not active';
118
119 DELETE FROM EGO_EBI_ITEM_LOAD_LOG
120 WHERE ORGANIZATION_ID = p_organization_id AND
121 EVENT_ID = p_event_id AND
122 INVENTORY_ITEM_ID NOT IN(
123 SELECT INVENTORY_ITEM_ID
124 FROM MTL_SYSTEM_ITEMS_B
125 WHERE ORGANIZATION_ID = p_organization_id
126 AND bom_item_type in (1, 2, 4)
127 AND customer_order_flag = 'Y'
128 AND customer_order_enabled_flag = 'Y');
129
130 SELECT MAX(NVL(EVENT_ID,0))+1 into l_eventId FROM EGO_EBI_ITEM_LOAD_LOG;
131 UPDATE EGO_EBI_ITEM_LOAD_LOG
132 SET EVENT_ID = l_eventId
133 WHERE EVENT_ID = p_event_id ;
134
135 --Raise event <event-id>
136 Raise_Item_LOAD_Event (EGO_EBI_ITEM_LOAD.G_ITEM_LOAD_EVENT, l_eventId , l_msg_data, l_return_status);
137 x_err_msg := 'Raised Event' || TO_CHAR(l_eventId ) || ' with return status = ' || l_return_status ;
138 COMMIT;
139 END;
140 ELSE
141 x_err_msg := 'Could not find event id' || TO_CHAR(p_event_id);
142 END IF;
143
144 END REGENERATE_FAILED_EVENT;
145
146
147
148 PROCEDURE RAISE_ITEM_LOAD_EVENT (
149 p_event_name IN VARCHAR2
150 ,p_event_id IN NUMBER DEFAULT NULL
151 ,x_msg_data OUT NOCOPY VARCHAR2
152 ,x_return_status OUT NOCOPY VARCHAR2
153 )
154 IS
155 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
156 l_parameter_t WF_PARAMETER_T:= WF_PARAMETER_T(null, null);
157 l_event_name VARCHAR2(240);
158 l_event_key VARCHAR2(240);
159 BEGIN
160
161 l_event_name := p_event_name ;
162 l_event_key := p_event_id || SUBSTRB(l_event_name, 1, 225) || '-' || TO_CHAR(SYSDATE, 'J.SSSSS');
163 --Adding the parameters EVENT ID param not required as
164 WF_EVENT.AddParameterToList( p_name => 'EVENT_ID'
165 ,p_value => p_event_id
166 ,p_ParameterList => l_parameter_List);
167
168 WF_EVENT.Raise(p_event_name => l_event_name
169 ,p_event_key => l_event_key
170 ,p_parameters => l_parameter_list);
171 l_parameter_list.DELETE;
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173
174 END Raise_Item_LOAD_Event;
175
176
177 END EGO_EBI_ITEM_LOAD;