[Home] [Help]
PACKAGE BODY: APPS.AS_RESOURCE_MERGE_PUB
Source
1 PACKAGE BODY AS_RESOURCE_MERGE_PUB as
2 /* $Header: asxrsmrb.pls 120.2 2005/12/22 22:53:57 subabu noship $ */
3
4 -- Start of Comments
5 -- Package name : AS_RESOURCE_MERGE_PUB
6 --
7 -- Purpose : This package should be called in event subscription of
8 -- event oracle.apps.jtf.jres.resource.update.effectdate.
9 -- It will update the salesforce_id(resource_id) column in AS
10 -- tables due to resource merge.
11 --
12 -- NOTES
13 --
14 -- HISTORY
15 -- 12/31/03 FFANG Created.
16 --
17 --
18
19 FUNCTION update_resource_enddate (
20 p_subscription_guid in raw,
21 p_event in out NOCOPY wf_event_t )
22 RETURN VARCHAR2
23 IS
24 l_event_key varchar2(240) := p_event.GetEventKey();
25 l_event_name varchar2(240) := p_event.GetEventName();
26 l_event_details varchar2(2000);
27 l_resource_id number;
28 l_resource_name varchar2(240);
29 l_category varchar2(30);
30 l_new_start_date date;
31 l_new_end_date date;
32 l_old_start_date date;
33 l_old_end_date date;
34
35 l_logdir VARCHAR2(500);
36 l_logfile VARCHAR2(200) := 'asxrsmrb.log';
37 l_file_ptr UTL_FILE.FILE_TYPE;
38 l_filepath VARCHAR2(500);
39 l_begin_pos NUMBER;
40 l_length NUMBER;
41 l_sysdate VARCHAR2(100) := TO_CHAR(SYSDATE,'DD-MON-YYYY-HH:MI:SS');
42
43 cursor c_get_from_resource (c_resource_id NUMBER) is
44 select resource_id from jtf_rs_resource_extns
45 where category in ('PARTNER', 'PARTY')
46 and source_id =
47 (select party_id from hz_relationships
48 where relationship_id=
49 (select merge_to_entity_id from hz_merge_party_details
50 where merge_from_entity_id =
51 (select relationship_id
52 from hz_relationships
53 where party_id=
54 (select source_id
55 from jtf_rs_resource_extns
56 where resource_id=c_resource_id) ) ) );
57
58 l_to_resource_id NUMBER;
59
60 BEGIN
61
62 -- Fetch the directories to which UTL_FILE has access to write
63 -- they will be seperated by comma ","
64 SELECT value
65 INTO l_logdir
66 FROM v$parameter
67 WHERE UPPER(name) = 'UTL_FILE_DIR';
68
69 -- Parse the logdir for the first comma and get the first directory
70 l_begin_pos := instr(l_logdir,',');
71 IF l_begin_pos > 0 THEN
72 l_length := l_begin_pos-1;
73 l_filepath := ltrim(substr(l_logdir,1,l_length));
74 ELSE
75 l_filepath := ltrim(l_logdir);
76 END IF;
77
78 l_file_ptr := UTL_FILE.FOPEN(l_filepath,l_logfile,'a');
79 UTL_FILE.Put_Line(l_file_ptr,'===========================================');
80 UTL_FILE.Put_Line(l_file_ptr,'Event subscription Start '||l_sysdate);
81
82 -- Get event parameters
83 l_resource_id := p_event.GetValueForParameter('RESOURCE_ID');
84 l_category := p_event.GetValueForParameter('CATEGORY');
85 l_resource_name := p_event.GetValueForParameter('RESOURCE_NAME');
86 l_new_start_date := p_event.GetValueForParameter('NEW_START_DATE_ACTIVE');
87 l_old_start_date := p_event.GetValueForParameter('OLD_START_DATE_ACTIVE');
88 l_new_end_date := p_event.GetValueForParameter('NEW_END_DATE_ACTIVE');
89 l_old_end_date := p_event.GetValueForParameter('OLD_END_DATE_ACTIVE');
90 l_event_details := 'Active Date of '||l_category || ' resource: '''
91 || l_resource_id || ' - '
92 || l_resource_name
93 || ' is changed.';
94
95 if (nvl(l_new_start_date, sysdate+100000) <>
96 nvl(l_old_start_date, sysdate+100000))
97 then
98 UTL_FILE.Put_Line(l_file_ptr,l_event_details);
99 UTL_FILE.Put_Line(l_file_ptr,'New Start Date: '|| l_new_start_date);
100 UTL_FILE.Put_Line(l_file_ptr,'Old Start Date: '|| l_old_start_date);
101 end if;
102
103 if (nvl(l_new_end_date, sysdate+100000) <>
104 nvl(l_old_end_date, sysdate+100000))
105 then
106 UTL_FILE.Put_Line(l_file_ptr,l_event_details);
107 UTL_FILE.Put_Line(l_file_ptr,'New End Date: '|| l_new_end_date);
108 UTL_FILE.Put_Line(l_file_ptr,'Old End Date: '|| l_old_end_date);
109
110 -- resource got end-dated, check if it is because of resource merge;
111 -- if yes, update the resources in AS tables
112 open c_get_from_resource (l_resource_id);
113 fetch c_get_from_resource into l_to_resource_id;
114
115 UTL_FILE.Put_Line(l_file_ptr,'To Resource Id: '||l_to_resource_id);
116
117 -- Bug 3555514
118 -- Don't update salesforce_id if to_resource_id is null while end-dating resource
119 if (l_to_resource_id is not null)
120 then
121 UTL_FILE.Put_Line(l_file_ptr,'Salesforce Update Start');
122
123 -- AS_ACCESSES_ALL
124 begin
125 UTL_FILE.Put_Line(l_file_ptr, 'Updating AS_ACCESSES_ALL');
126 update AS_ACCESSES_ALL
127 set salesforce_id = l_to_resource_id
128 where salesforce_id = l_resource_id;
129 exception
130 when others then
131 UTL_FILE.Put_Line(l_file_ptr,
132 'Errors when updating AS_ACCESSES_ALL');
133 RAISE;
134 end;
135
136 -- AS_SALES_CREDITS
137 begin
138 UTL_FILE.Put_Line(l_file_ptr, 'Updating AS_SALES_CREDITS');
139 update AS_SALES_CREDITS
140 set salesforce_id = l_to_resource_id
141 where salesforce_id = l_resource_id;
142 exception
143 when others then
144 UTL_FILE.Put_Line(l_file_ptr,
145 'Errors when updating AS_SALES_CREDITS');
146 RAISE;
147 end;
148
149 -- AS_SALES_CREDITS_DENORM
150 begin
151 UTL_FILE.Put_Line(l_file_ptr, 'Updating AS_SALES_CREDITS_DENORM');
152 update AS_SALES_CREDITS_DENORM
153 set salesforce_id = l_to_resource_id
154 where salesforce_id = l_resource_id;
155 exception
156 when others then
157 UTL_FILE.Put_Line(l_file_ptr,
158 'Errors when updating AS_SALES_CREDITS_DENORM');
159 RAISE;
160 end;
161
162 -- AS_INTERNAL_FORECASTS
163 begin
164 UTL_FILE.Put_Line(l_file_ptr, 'Updating AS_INTERNAL_FORECASTS');
165 update AS_INTERNAL_FORECASTS
166 set salesforce_id = l_to_resource_id
167 where salesforce_id = l_resource_id;
168 exception
169 when others then
170 UTL_FILE.Put_Line(l_file_ptr,
171 'Errors when updating AS_INTERNAL_FORECASTS');
172 RAISE;
173 end;
174
175 -- AS_FORECAST_WORKSHEETS
176 begin
177 UTL_FILE.Put_Line(l_file_ptr, 'Updating AS_FORECAST_WORKSHEETS');
178 update AS_FORECAST_WORKSHEETS
179 set salesforce_id = l_to_resource_id
180 where salesforce_id = l_resource_id;
181 exception
182 when others then
183 UTL_FILE.Put_Line(l_file_ptr,
184 'Errors when updating AS_FORECAST_WORKSHEETS');
185 RAISE;
186 end;
187
188 -- AS_PROD_WORKSHEET_LINES
189 begin
190 UTL_FILE.Put_Line(l_file_ptr, 'Updating AS_PROD_WORKSHEET_LINES');
191 update AS_PROD_WORKSHEET_LINES
192 set salesforce_id = l_to_resource_id
193 where salesforce_id = l_resource_id;
194 exception
195 when others then
196 UTL_FILE.Put_Line(l_file_ptr,
197 'Errors when updating AS_PROD_WORKSHEET_LINES');
198 RAISE;
199 end;
200 UTL_FILE.Put_Line(l_file_ptr,'Salesforce Update End');
201 end if;
202 end if;
203
204 UTL_FILE.FFLUSH(l_file_ptr);
205 UTL_FILE.FClose(l_file_ptr);
206
207 return 'SUCCESS';
208
209 EXCEPTION
210 WHEN OTHERS THEN
211 WF_CORE.CONTEXT('AS_RESOURCE_MERGE_PUB', 'update_resource_enddate',
212 p_event.getEventName(), p_subscription_guid);
213 WF_EVENT.setErrorInfo(p_event, 'ERROR');
214 UTL_FILE.PUT_LINE(l_file_ptr, 'Error number ' || to_char(SQLCODE));
215 UTL_FILE.PUT_LINE(l_file_ptr, 'Error message ' || SQLERRM);
216 UTL_FILE.FFLUSH(l_file_ptr);
217 UTL_FILE.FCLOSE(l_file_ptr);
218
219 RETURN 'ERROR';
220 END update_resource_enddate;
221
222 END AS_RESOURCE_MERGE_PUB;