[Home] [Help]
PACKAGE BODY: APPS.WIP_RES_USAGE_DEFAULT
Source
1 Package Body WIP_RES_USAGE_DEFAULT as
2 /* $Header: wiprudfb.pls 120.0 2005/05/25 08:09:23 appldev noship $ */
3
4 Procedure Default_Resource_Usages(p_group_id in number,
5 p_parent_header_id in number := null,
6 p_wip_entity_id in number,
7 p_organization_id in number,
8 x_err_code out nocopy varchar2,
9 x_err_msg out nocopy varchar2,
10 x_return_status out nocopy varchar2) IS
11
12 Cursor Usage_info (p_group_id number,
13 p_wip_entity_id number,
14 p_organization_id number) IS
15 SELECT operation_seq_num, resource_seq_num
16 FROM WIP_JOB_DTLS_INTERFACE
17 WHERE group_id = p_group_id
18 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
19 AND process_status = WIP_CONSTANTS.RUNNING
20 AND wip_entity_id = p_wip_entity_id
21 AND organization_id = p_organization_id
22 AND load_type in
23 (WIP_JOB_DETAILS.WIP_RES_USAGE, WIP_JOB_DETAILS.WIP_RES_INSTANCE_USAGE)
24 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD
25 ORDER BY operation_seq_num, resource_seq_num;
26
27 Begin
28
29 FOR cur_row in Usage_info(p_group_id,
30 p_wip_entity_id, p_organization_id) LOOP
31
32 Default_Res_Usage (p_group_id,
33 p_parent_header_id,p_wip_entity_id,
34 p_organization_id,
35 cur_row.operation_seq_num,
36 cur_row.resource_seq_num,
37 WIP_JOB_DETAILS.WIP_ADD,
38 x_err_code,
39 x_err_msg,
40 x_return_status);
41
42 END LOOP;
43
44 END DEFAULT_RESOURCE_USAGES;
45
46 Procedure Default_Res_Usage ( p_group_id in number,
47 p_parent_header_id in number := null,
48 p_wip_entity_id in number,
49 p_organization_id in number,
50 p_operation_seq_num in number,
51 p_resource_seq_num in number,
52 p_substitution_type in number,
53 x_err_code out nocopy varchar2,
54 x_err_msg out nocopy varchar2,
55 x_return_status out nocopy varchar2) IS
56
57 l_last_update_login number;
58 l_request_id number;
59 l_program_application_id number;
60 l_program_id number;
61 l_program_update_date date;
62 l_end_date date;
63 l_assigned_units number;
64
65 l_parent_header number;
66 l_oper_resource number;
67 BEGIN
68
69 l_last_update_login := 0;
70 l_request_id := 0;
71 l_program_application_id := 0;
72 l_program_id := 0;
73 l_program_update_date := sysdate;
74 l_end_date := sysdate;
75 l_assigned_units := 0;
76
77 begin
78
79 IF p_group_id IS NULL OR p_wip_entity_id IS NULL OR
80 p_organization_id IS NULL OR p_operation_seq_num IS NULL OR
81 p_resource_seq_num IS NULL OR p_substitution_type IS NULL THEN
82 x_err_code := SQLCODE;
83 x_err_msg := 'Error in wiprudfb.pls: Primary key cannot be NULL!';
84 x_return_status := FND_API.G_RET_STS_ERROR;
85 return;
86 END IF;
87
88 IF p_substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
89
90 IF WIP_JOB_DETAILS.std_alone = 0 THEN
91
92 SELECT COUNT(*) INTO l_parent_header
93 FROM WIP_JOB_SCHEDULE_INTERFACE
94 WHERE header_id = p_parent_header_id
95 AND group_id = p_group_id;
96
97 IF l_parent_header =1 THEN
98 SELECT last_update_login,
99 request_id,
100 program_application_id,
101 program_id,
102 program_update_date
103 INTO l_last_update_login,
104 l_request_id,
105 l_program_application_id,
106 l_program_id,
107 l_program_update_date
108 FROM WIP_JOB_SCHEDULE_INTERFACE
109 WHERE wip_entity_id = p_wip_entity_id
110 AND organization_id = p_organization_id
111 AND header_id = p_parent_header_id
112 AND group_id = p_group_id;
113 END IF;
114
115 ELSE
116
117 select count(*) into l_oper_resource
118 from wip_operation_resources
119 where wip_entity_id = p_wip_entity_id
120 and organization_id = p_organization_id
121 and operation_seq_num = p_operation_seq_num
122 and resource_seq_num = p_resource_seq_num;
123
124 IF l_oper_resource = 1 THEN
125 SELECT last_update_login,
126 request_id,
127 program_application_id,
128 program_id,
129 program_update_date
130 INTO l_last_update_login,
131 l_request_id,
132 l_program_application_id,
133 l_program_id,
134 l_program_update_date
135 FROM WIP_OPERATION_RESOURCES
136 WHERE wip_entity_id = p_wip_entity_id
137 AND organization_id = p_organization_id
138 AND operation_seq_num = p_operation_seq_num
139 AND resource_seq_num = p_resource_seq_num;
140 END IF;
141
142 END IF;
143
144 UPDATE WIP_JOB_DTLS_INTERFACE
145 SET last_update_login = NVL(last_update_login, l_last_update_login),
146 request_id = NVL(request_id, l_request_id),
147 program_application_id = NVL(program_application_id,
148 l_program_application_id ),
149 program_id = NVL(program_id, l_program_id),
150 program_update_date = NVL(program_update_date,l_program_update_date)
151 WHERE group_id = p_group_id
152 AND wip_entity_id = p_wip_entity_id
153 AND organization_id = p_organization_id
154 AND operation_seq_num = p_operation_seq_num
155 AND resource_seq_num = p_resource_seq_num
156 AND load_type in (WIP_JOB_DETAILS.WIP_RES_USAGE,
157 WIP_JOB_DETAILS.WIP_RES_INSTANCE_USAGE)
158 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
159
160 END IF;
161
162 exception
163 When others then
164 x_err_code := SQLCODE;
165 x_err_msg := 'Error in wiprudfb: '|| SQLERRM;
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 return;
168
169 end;
170
171 END DEFAULT_RES_USAGE;
172
173 END WIP_RES_USAGE_DEFAULT;