[Home] [Help]
PACKAGE BODY: APPS.WIP_OPERATION_DEFAULT
Source
1 Package Body WIP_OPERATION_DEFAULT as
2 /* $Header: wipopdfb.pls 115.7 2002/11/29 09:33:36 rmahidha ship $ */
3
4 Procedure Default_Operations(p_group_id in number,
5 p_parent_header_id in number,
6 p_wip_entity_id in number,
7 p_organization_id in number,
8 p_substitution_type in number,
9 x_err_code out nocopy varchar2,
10 x_err_msg out nocopy varchar2,
11 x_return_status out nocopy varchar2 ) IS
12
13 CURSOR oper_info ( p_group_id number,
14 p_wip_entity_id number,
15 p_organization_id number,
16 p_substitution_type number) IS
17 SELECT distinct parent_header_id,operation_seq_num, standard_operation_id,
18 department_id, description , first_unit_start_date,
19 first_unit_completion_date, last_unit_start_date,
20 last_unit_completion_date, minimum_transfer_quantity,
21 count_point_type, backflush_flag,last_update_date,
22 last_updated_by, creation_date,created_by, last_update_login,
23 request_id, program_application_id, program_id, program_update_date,
24 attribute_category, attribute1, attribute2, attribute3,
25 attribute4, attribute5,
26 attribute6, attribute7, attribute8, attribute9, attribute10,
27 attribute11, attribute12, attribute13, attribute14, attribute15
28 FROM WIP_JOB_DTLS_INTERFACE
29 WHERE group_id = p_group_id
30 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
31 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
32 AND wip_entity_id = p_wip_entity_id
33 AND organization_id = p_organization_id
34 AND load_type = WIP_JOB_DETAILS.WIP_OPERATION
35 AND substitution_type = p_substitution_type
36 ORDER BY operation_seq_num;
37
38 Begin
39
40 For cur_row in oper_info (p_group_id, p_wip_entity_id,
41 p_organization_id, p_substitution_type) Loop
42
43 Default_Oper(p_group_id, cur_row.parent_header_id,
44 p_wip_entity_id, p_organization_id,
45 cur_row.operation_seq_num, p_substitution_type,
46 cur_row.description,cur_row.department_id,
47 cur_row.standard_operation_id,
48 cur_row.first_unit_start_date,
49 cur_row.first_unit_completion_date,
50 cur_row.last_unit_start_date,
51 cur_row.last_unit_completion_date,
52 cur_row.minimum_transfer_quantity,
53 cur_row.count_point_type,
54 cur_row.backflush_flag,
55 x_err_code,x_err_msg, x_return_status);
56
57 END LOOP;
58
59 exception
60 when others then
61 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
62
63 END DEFAULT_OPERATIONS;
64
65 Procedure Default_Oper (p_group_id in number,
66 p_parent_header_id in number := NULL,
67 p_wip_entity_id number,
68 p_organization_id number,
69 p_operation_seq_num number,
70 p_substitution_type number,
71 p_description varchar2 := NULL,
72 p_department_id number := NULL,
73 p_standard_operation_id number:=NULL,
74 p_fusd date := NULL,
75 p_fucd date := NULL,
76 p_lusd date := NULL,
77 p_lucd date := NULL,
78 p_min_xfer_qty number := NULL,
79 p_count_point number := NULL,
80 p_backflush_flag number := NULL,
81 x_err_code out nocopy varchar2,
82 x_err_msg out nocopy varchar2,
83 x_return_status out nocopy varchar2 ) IS
84
85 l_department_id NUMBER;
86 l_min_xfer_qty NUMBER;
87 l_count_point_type NUMBER;
88 l_operation_description VARCHAR2(240);
89 l_backflush_flag NUMBER;
90 l_organization_id NUMBER;
91 l_first_unit_start_date DATE;
92 l_first_unit_completion_date DATE;
93 l_last_unit_start_date DATE;
94 l_last_unit_completion_date DATE;
95 l_start_quantity NUMBER;
96 l_standard_oper number;
97 l_parent_header number;
98
99 BEGIN
100
101 l_department_id := 0;
102 l_min_xfer_qty := 0;
103 l_count_point_type := 0;
104 l_operation_description := null;
105 l_backflush_flag := 0;
106 l_organization_id := 0;
107 l_first_unit_start_date := null;
108 l_first_unit_completion_date := null;
109 l_last_unit_start_date := null;
110 l_last_unit_completion_date := null;
111 l_start_quantity := 0;
112 l_standard_oper := 0;
113 l_parent_header := 0;
114
115 begin
116
117 IF p_group_id IS NULL OR
118 (p_wip_entity_id IS NULL and p_parent_header_id IS NULL)OR
119 p_organization_id IS NULL OR p_operation_seq_num IS NULL THEN
120
121 x_err_code := SQLCODE;
122 x_err_msg := 'Primary key cannot be NULL!';
123 x_return_status := FND_API.G_RET_STS_ERROR;
124 return;
125 END IF;
126
127 IF p_substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
128
129 IF p_standard_operation_id IS NOT NULL
130 AND p_standard_operation_id IS NOT NULL THEN
131
132 select count(*) into l_standard_oper
133 from bom_standard_operations
134 where standard_operation_id = p_standard_operation_id
135 and organization_id = p_organization_id;
136
137 IF l_standard_oper = 1 THEN
138 SELECT department_id, minimum_transfer_quantity,
139 count_point_type, operation_description,
140 backflush_flag
141 INTO l_department_id, l_min_xfer_qty, l_count_point_type,
142 l_operation_description, l_backflush_flag
143 FROM BOM_STANDARD_OPERATIONS
144 WHERE standard_operation_id = p_standard_operation_id
145 AND organization_id = p_organization_id;
146 END IF;
147
148 END IF;
149
150 IF WIP_JOB_DETAILS.std_alone = 0 THEN
151
152 SELECT COUNT(*) INTO l_parent_header
153 FROM WIP_JOB_SCHEDULE_INTERFACE
154 WHERE header_id = p_parent_header_id
155 AND group_id = p_group_id;
156
157 IF l_parent_header = 1 THEN
158 SELECT organization_id, first_unit_start_date,
159 first_unit_completion_date, last_unit_start_date,
160 last_unit_completion_date, start_quantity
161 INTO l_organization_id, l_first_unit_start_date,
162 l_first_unit_completion_date, l_last_unit_start_date,
163 l_last_unit_completion_date, l_start_quantity
164 FROM WIP_JOB_SCHEDULE_INTERFACE
165 WHERE header_id = p_parent_header_id
166 AND group_id = p_group_id;
167 END IF;
168
169 END IF;
170 END IF;
171
172 Exception
173 WHEN others then
174 x_return_status := FND_API.G_RET_STS_ERROR;
175 x_err_msg := 'ERROR IN WIPOPDFB: ' || SQLERRM;
176 x_err_code := SQLCODE;
177 return;
178 end;
179
180 /******* UPDATE THE WIP_JOB_DTLS_INTERFACE TABLE *************/
181
182 UPDATE WIP_JOB_DTLS_INTERFACE
183 SET
184 department_id = decode(p_department_id,
185 NULL, l_department_id, p_department_id),
186 minimum_transfer_quantity = decode(p_min_xfer_qty,
187 NULL,l_min_xfer_qty,p_min_xfer_qty),
188 count_point_type = decode(p_count_point,
189 NULL, l_count_point_type,p_count_point),
190 description = decode(p_description,
191 NULL, l_operation_description,p_description),
192 backflush_flag= decode(p_backflush_flag,
193 NULL,l_backflush_flag,p_backflush_flag),
194 first_unit_start_date= decode(p_fusd,
195 NULL,
196 l_first_unit_start_date,
197 p_fusd),
198 first_unit_completion_date= decode(p_fucd,
199 NULL,
200 l_first_unit_completion_date,
201 p_fucd),
202 last_unit_start_date = decode(p_lusd,
203 NULL,
204 l_last_unit_start_date,
205 p_lusd),
206 last_unit_completion_date = decode(p_lucd,
207 NULL,
208 l_last_unit_completion_date,
209 p_lucd)
210 WHERE group_id = p_group_id
211 AND (parent_header_id = p_parent_header_id OR
212 wip_entity_id = p_wip_entity_id)
213 AND organization_id = p_organization_id
214 AND load_type = WIP_JOB_DETAILS.WIP_OPERATION
215 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD
216 AND operation_seq_num = p_operation_seq_num;
217
218 END Default_Oper;
219 END WIP_OPERATION_DEFAULT;