1 PACKAGE BODY EAM_SKILL_INSTANCE AS
2 /* $Header: EAMSKILB.pls 115.9 2004/05/04 18:03:04 anjgupta ship $ */
3
4
5 /**
6 * This procedure is used to assign an instance to the resource
7 * this is the main procedure called from resource instance and
8 * skill search jsp's
9 */
10 procedure insert_instance(p_wip_entity_id in number,
11 p_operation_seq_num in number,
12 p_resource_seq_num in number,
13 p_organization_id in number,
14 p_user_id in number,
15 p_instance_id in number,
16 p_start_date in date,
17 p_completion_date in date,
18 p_assigned_units_updated out NOCOPY number) is
19
20 l_start_date DATE;
21 l_completion_date DATE;
22 l_user_id NUMBER;
23 l_org_id NUMBER;
24 x_duplicate BOOLEAN;
25 x_error VARCHAR2(240);
26 l_assigned_changed NUMBER;
27
28 begin
29
30 p_assigned_units_updated := 0;
31
32
33 if (p_start_date is null) or (p_completion_date is null) or (p_organization_id is null) then
34 select distinct start_date, completion_date, organization_id into l_start_date, l_completion_date, l_org_id
35 from wip_operation_resources wor
36 where wor.wip_entity_id = p_wip_entity_id
37 and wor.operation_seq_num = p_operation_seq_num
38 and wor.resource_seq_num = p_resource_seq_num;
39 end if;
40
41
42 --get the start date for instance
43 if (p_start_date is not null) then
44 l_start_date := p_start_date;
45 end if;
46
47
48 if (p_completion_date is not null) then
49 l_completion_date := p_completion_date;
50 end if;
51
52
53
54 if(p_user_id is not null) then
55 l_user_id := p_user_id;
56 else
57 l_user_id := FND_GLOBAL.USER_ID;
58 end if;
59
60 if(p_organization_id is not null) then
61 l_org_id := p_organization_id;
62 end if;
63
64 --call the insert statement only if this instance is not duplicate
65
66 x_duplicate := is_duplicate_instance(p_wip_entity_id, p_operation_seq_num,
67 p_resource_seq_num, l_org_id, p_instance_id);
68
69 if (NOT x_duplicate) then
70 INSERT INTO WIP_OP_RESOURCE_INSTANCES
71 (
72 WIP_ENTITY_ID,
73 OPERATION_SEQ_NUM,
74 RESOURCE_SEQ_NUM,
75 ORGANIZATION_ID,
76 LAST_UPDATE_DATE,
77 LAST_UPDATED_BY,
78 CREATION_DATE,
79 CREATED_BY,
80 INSTANCE_ID,
81 START_DATE,
82 COMPLETION_DATE)
83 VALUES(
84 p_wip_entity_id,
85 p_operation_seq_num,
86 p_resource_seq_num,
87 l_org_id,
88 sysdate,
89 l_user_id,
90 sysdate,
91 l_user_id,
92 p_instance_id,
93 l_start_date,
94 l_completion_date);
95
96 -- Bug 3568576 - Forward port of bug 3545813
97 -- Do not firm wo after resource instances are added
98 --after assigning the instance, firm the job,
99 --otherwise rescheduling will wipe out all the instances
100 --firm_work_order(p_wip_entity_id ,p_organization_id);
101 p_assigned_units_updated := p_assigned_units_updated + 1 ;
102 commit;
103
104 end if;
105
106 end insert_instance;
107
108
109 /**
110 * This function is used to check the duplicate instance assignment
111 * if we already filter the lov then this function should return false
112 */
113 function is_duplicate_instance(p_wip_entity_id in number,
114 p_operation_seq_num in number,
115 p_resource_seq_num in number,
116 p_organization_id in number,
117 p_instance_id in number) return boolean is
118 l_num NUMBER;
119 begin
120 select count(*) into l_num
121 from WIP_OP_RESOURCE_INSTANCES
122 where wip_entity_id = p_wip_entity_id and
123 operation_seq_num = p_operation_seq_num and
124 resource_seq_num = p_resource_seq_num and
125 instance_id = p_instance_id;
126
127 if( l_num <> 0 ) then
128 return true;
129 else
130 return false;
131 end if;
132
133 end is_duplicate_instance;
134
135
136 /**
137 * This procedure is used to check whether the assigned units are in
138 * sync with the number of resource instances, if its less then we
139 * increase the assigned units and return
140 */
141 procedure check_assigned_units(p_wip_entity_id in number,
142 p_operation_seq_num in number,
143 p_resource_seq_num in number,
144 p_organization_id in number,
145 p_assigned_changed out NOCOPY number) is
146 l_assigned_units_num NUMBER;
147 l_instance_num NUMBER;
148
149 begin
150
151 select count(*) into l_instance_num
152 from WIP_OP_RESOURCE_INSTANCES
153 where wip_entity_id = p_wip_entity_id and
154 operation_seq_num = p_operation_seq_num and
155 resource_seq_num = p_resource_seq_num;
156
157 select nvl(assigned_units,0) into l_assigned_units_num
158 from WIP_OPERATION_RESOURCES
159 where wip_entity_id = p_wip_entity_id and
160 operation_seq_num = p_operation_seq_num and
161 resource_seq_num = p_resource_seq_num;
162
163 --we would be adding 1 instance, so check l_instance+1
164 --if assigned units are less then increment
165
166 if( l_instance_num + 1 > l_assigned_units_num ) then
167 p_assigned_changed := 1;
168 update wip_operation_resources
169 set assigned_units = (l_instance_num + 1)
170 where wip_entity_id = p_wip_entity_id and
171 operation_seq_num = p_operation_seq_num and
172 resource_seq_num = p_resource_seq_num;
173 else
174 p_assigned_changed := 0;
175 end if;
176
177 end check_assigned_units;
178
179 /**
180 * This procedure is used to firm the workorder after the instance
181 * is assigned to the resource
182 */
183 procedure firm_work_order(p_wip_entity_id in number,
184 p_organization_id in number) is
185 begin
186
187 update wip_discrete_jobs
188 set firm_planned_flag = 1
189 where wip_entity_id = p_wip_entity_id;
190
191 end firm_work_order;
192
193
194 /**
195 * This procedure is used to remove an assigned instance from
196 * the resource
197 */
198 procedure remove_instance(p_wip_entity_id in number,
199 p_operation_seq_num in number,
200 p_resource_seq_num in number,
201 p_instance_id in number) is
202 begin
203
204 delete from wip_op_resource_instances
205 where wip_entity_id = p_wip_entity_id and
206 operation_seq_num = p_operation_seq_num and
207 resource_seq_num = p_resource_seq_num and
208 instance_id = p_instance_id;
209 commit;
210 end remove_instance;
211
212
213
214
215
216
217
218 END EAM_SKILL_INSTANCE;
[Home] [Help]