1 PACKAGE AHL_WIP_JOB_PVT AS
2 /* $Header: AHLVWIPS.pls 120.0 2005/05/26 00:13:56 appldev noship $ */
3
4 -- Define Record Type for AHL Work Order Record --
5 -- when creating a new job, set dml_type to 'I', all the other cases,
6 -- set dml_type to 'U'
7 TYPE ahl_wo_rec_type IS RECORD
8 (
9 wo_name VARCHAR2(240),
10 organization_id NUMBER,
11 status NUMBER,
12 scheduled_start DATE,
13 scheduled_end DATE,
14 inventory_item_id NUMBER,
15 item_instance_id NUMBER,
16 completion_subinventory VARCHAR2(10),
17 completion_locator_id NUMBER,
18 wip_supply_type NUMBER,
19 firm_planned_flag NUMBER,
20 project_id NUMBER,
21 prj_task_id NUMBER,
22 quantity NUMBER,
23 mrp_quantity NUMBER,
24 class_code VARCHAR2(10),
25 priority NUMBER,
26 department_id NUMBER,
27 allow_explosion VARCHAR2(1),
28 manual_rebuild_flag VARCHAR2(1),
29 rebuild_serial_number VARCHAR2(30),
30 scheduling_method NUMBER,
31 description VARCHAR2(240),
32 dml_type VARCHAR2(1)
33 );
34
35 -- Define Record Type for AHL Job Detail Operation Record --
36 -- dml_type can only be set to 'I' or 'U' depending on adding or
37 -- updating an operation
38 TYPE ahl_wo_op_rec_type IS RECORD
39 (
40 organization_id NUMBER,
41 operation_seq_num NUMBER,
42 department_id NUMBER,
43 description VARCHAR2(240),
44 minimum_transfer_quantity NUMBER,
45 count_point_type NUMBER,
46 backflush_flag NUMBER,
47 scheduled_start DATE,
48 scheduled_end DATE,
49 dml_type VARCHAR2(1)
50 );
51
52 -- Define Table Type for AHL Job Detail Operation --
53 TYPE ahl_wo_op_tbl_type IS TABLE OF ahl_wo_op_rec_type INDEX BY BINARY_INTEGER;
54
55 -- Define Record Type for AHL Job Detail Resource Requirement Record --
56 -- dml_type can only be set to 'I','U' or 'D' depending on adding, updating
57 -- or removing a resource requirement. If dml_type='I', then leave resource_id_old
58 -- NULL and put the resource_id into resource_id_new. If dml_type='U'
59 -- then put the old resource_id into resource_id_old and the new resource_id
60 -- into resource_id_new or leave it blank if no change to the resource_id.
61 TYPE ahl_wo_res_rec_type IS RECORD
62 (
63 operation_seq_num NUMBER,
64 resource_seq_num NUMBER,
65 organization_id NUMBER,
66 department_id NUMBER,
67 scheduled_sequence NUMBER,
68 resource_id_old NUMBER,
69 resource_id_new NUMBER,
70 uom VARCHAR2(30),
71 cost_basis NUMBER,
72 quantity NUMBER,
73 assigned_units NUMBER,
74 scheduled_flag NUMBER,
75 activity_id NUMBER,
76 autocharge_type NUMBER,
77 standard_rate_flag NUMBER,
78 applied_resource_units NUMBER,
79 applied_resource_value NUMBER,
80 description VARCHAR2(240),
81 start_date DATE,
82 end_date DATE,
83 setup_id NUMBER,
84 dml_type VARCHAR2(1)
85 );
86
87 -- Define Table Type for AHL Job Detail Resource Requirement --
88 TYPE ahl_wo_res_tbl_type IS TABLE OF ahl_wo_res_rec_type INDEX BY BINARY_INTEGER;
89
90 -- Define Record Type for AHL Job Detail Material Requirement Record --
91 -- dml_type can only be set to 'I','U' or 'D' depending on adding, updating
92 -- or removing a material requirement. If dml_type='I', then leave inventory_item_id_old
93 -- NULL and put the inventory_item_id into inventory_item_id_new. If dml_type='U'
94 -- then put the old inventory_item_id into inventory_item_id_old and the new inventory_item_id
95 -- into inventory_item_id_new or leave it blank if no change to the inventory_item_id.
96 TYPE ahl_wo_mtl_rec_type IS RECORD
97 (
98 operation_seq_num NUMBER,
99 inventory_item_id_old NUMBER,
100 inventory_item_id_new NUMBER,
101 organization_id NUMBER,
102 mrp_net NUMBER,
103 quantity_per_assembly NUMBER,
104 required_quantity NUMBER,
105 supply_type NUMBER,
106 supply_locator_id NUMBER,
107 supply_subinventory VARCHAR2(10),
108 date_required DATE,
109 dml_type VARCHAR2(1)
110 );
111
112 -- Define Table Type for AHL Job Detail Material Requirement --
113 TYPE ahl_wo_mtl_tbl_type IS TABLE OF ahl_wo_mtl_rec_type INDEX BY BINARY_INTEGER;
114
115 -- Define Record Type for AHL Workorder Resource Transaction Record --
116 TYPE ahl_res_txn_rec_type IS RECORD
117 (
118 wip_entity_id NUMBER,
119 operation_seq_num NUMBER,
120 resource_seq_num NUMBER,
121 resource_id NUMBER,
122 transaction_type NUMBER,
123 transaction_date DATE,
124 transaction_quantity NUMBER,
125 transaction_uom VARCHAR2(3),
126 department_id NUMBER,
127 employee_id NUMBER,
128 activity_id NUMBER,
129 activity_meaning VARCHAR2(80),
130 reason_id NUMBER,
131 reason VARCHAR2(80),
132 serial_number VARCHAR2(30),
133 reference VARCHAR2(240)
134 );
135
136 -- Define Table Type for AHL Workorder Resource Transaction --
137 TYPE ahl_res_txn_tbl_type IS TABLE OF ahl_res_txn_rec_type INDEX BY BINARY_INTEGER;
138
139 -- Define Record Type for multiple jobs returned from WIP Mass LOAD_WIP_JOB
140 TYPE ahl_wip_job_rec_type IS RECORD
141 (
142 wip_entity_id NUMBER,
143 wip_entity_name VARCHAR2(240),
144 organization_id NUMBER,
145 error VARCHAR2(5000)
146 );
147
148 -- Define Table Type for AHL Workorder Resource Transaction --
149 TYPE ahl_wip_job_tbl_type IS TABLE OF ahl_wip_job_rec_type INDEX BY BINARY_INTEGER;
150
151 -- Define Procedure load_wip_job --
152 -- This API is Autonomous Transaction. And we have to commit or rollback explicitly
153 -- when it exits, so p_commit flag is not necessary here.
154 PROCEDURE load_wip_job (
155 p_api_version IN NUMBER,
156 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
157 --p_commit IN VARCHAR2 := FND_API.G_FALSE,
158 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
159 x_return_status OUT NOCOPY VARCHAR2,
160 x_msg_count OUT NOCOPY NUMBER,
161 x_msg_data OUT NOCOPY VARCHAR2,
162 p_ahl_wo_rec IN ahl_wo_rec_type,
163 p_ahl_wo_op_tbl IN ahl_wo_op_tbl_type,
164 p_ahl_wo_res_tbl IN ahl_wo_res_tbl_type,
165 p_ahl_wo_mtl_tbl IN ahl_wo_mtl_tbl_type,
166 x_wip_entity_id OUT NOCOPY NUMBER
167 );
168 -- Start of Comments --
169 --
170 -- Procedure name : load_wip_job
171 -- Type : Private
172 -- Function : load(either create or update Job header and Job details information
173 -- in WIP entities.
174 -- Pre-reqs :
175 --
176 -- load_wip_job Parameters :
177 -- p_ahl_wo_rec IN NUMBER Required
178 -- Record of job header attributes
179 -- p_ahl_wo_op_tbl IN NUMBER Required
180 -- Table of job detail: operation record
181 -- p_ahl_wo_res_tbl IN VARCHAR2 Required
182 -- Table of job detail: resource requirement record
183 -- p_ahl_wo_mtl_tbl IN VARCHAR2 Required
184 -- Table of job detail: material requirement record
185 -- x_wip_entity_id OUT NOCOPY NUMBER Required
186 -- Stores the returned wip_entity_id.
187 -- Version :
188 -- Initial Version 1.0
189 --
190 -- End of Comments --
191
192 -- Define Procedure insert_resource_txn --
193 PROCEDURE insert_resource_txn (
194 p_api_version IN NUMBER,
195 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
196 p_commit IN VARCHAR2 := FND_API.G_FALSE,
197 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2,
201 p_ahl_res_txn_tbl IN ahl_res_txn_tbl_type
202 );
203 -- Start of Comments --
204 --
205 -- Procedure name : insert_resource_txn
206 -- Type : Private
207 -- Function : to accept Job Resource Transaction information from AHL side and
208 -- INSERT it in WIP_COST_TXN_INTERFACE table
209 -- Pre-reqs :
210 --
211 -- create_wip_job Parameters :
212 -- p_organization_id IN VARCHAR2 Required
213 -- Organization_id
214 -- p_wip_entity_id IN VARCHAR2 Required
215 -- Wip_entity_id
216 -- p_ahl_res_txn_tbl IN VARCHAR2 Required
217 -- Table of Workorder Resource Transaction record
218 -- Version :
219 -- Initial Version 1.0
220 --
221 -- End of Comments --
222
223 -- Define Function wip_massload_pending --
224 FUNCTION wip_massload_pending(
225 p_wip_entity_id IN NUMBER
226 ) RETURN BOOLEAN;
227 -- Start of Comments --
228 --
229 -- FUNCTION name : is_wip_massload_pending
230 -- Type : Private
231 -- Function : to check whether the specified workorder is in WIP Mass Load phase
232 -- Pre-reqs :
233 --
234 -- create_wip_job Parameters :
235 -- p_wip_entity_id IN NUMBER Required
236 -- Identifier of workorder
237 -- Version :
238 -- Initial Version 1.0
239 --
240 -- End of Comments --
241
242 -- Define Procedure load_wip_batch_jobs --
243 -- This API is Autonomous Transaction. And we have to commit or rollback explicitly
244 -- when it exits, so p_commit flag is not necessary here.
245 PROCEDURE load_wip_batch_jobs (
246 p_api_version IN NUMBER,
247 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
248 --p_commit IN VARCHAR2 := FND_API.G_FALSE,
249 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
250 x_return_status OUT NOCOPY VARCHAR2,
251 x_msg_count OUT NOCOPY NUMBER,
252 x_msg_data OUT NOCOPY VARCHAR2,
253 p_group_id IN NUMBER,
254 p_header_id IN NUMBER,
255 p_submit_flag IN VARCHAR2,
256 p_ahl_wo_rec IN ahl_wo_rec_type,
257 p_ahl_wo_op_tbl IN ahl_wo_op_tbl_type,
258 p_ahl_wo_res_tbl IN ahl_wo_res_tbl_type,
259 p_ahl_wo_mtl_tbl IN ahl_wo_mtl_tbl_type,
260 x_group_id OUT NOCOPY NUMBER,
261 x_header_id OUT NOCOPY NUMBER,
262 x_ahl_wip_job_tbl OUT NOCOPY ahl_wip_job_tbl_type
263 );
264 -- Start of Comments --
265 --
266 -- Procedure name : load_wip_batch_jobs
267 -- Type : Private
268 -- Function : load(either create or update) Job header and Job details information
269 -- in WIP entities. And submit WIP Mass Load in a batch mode. This API
270 -- is used for creating multiple jobs only.
271 -- Pre-reqs :
272 --
273 -- load_wip_job Parameters :
274 -- p_group_id IN NUMBER Required
275 -- group_id for the batch of jobs. NULL for the first job.
276 -- p_header_id IN NUMBER Required
277 -- header_id for each job. NULL for the first job.
278 -- p_submit_flag IN NUMBER Required
279 -- Indicator to show whether to call standard submit request.
280 -- p_ahl_wo_rec IN NUMBER Required
281 -- Record of job header attributes
282 -- p_ahl_wo_op_tbl IN NUMBER Required
283 -- Table of job detail: operation record
284 -- p_ahl_wo_res_tbl IN VARCHAR2 Required
285 -- Table of job detail: resource requirement record
286 -- p_ahl_wo_mtl_tbl IN VARCHAR2 Required
287 -- Table of job detail: material requirement record
288 -- x_group_id OUT NOCOPY NUMBER Required
289 -- Keeps the group_id of the batch of jobs
290 -- x_header_id OUT NOCOPY NUMBER Required
291 -- Keeps the header_id of each job
292 -- x_ahl_wip_job_tbl OUT NOCOPY ahl_wip_job_tbl_type Required
293 -- Stores the returned wip_entity_id if success or error message
294 -- if failure
295 -- Version :
296 -- Initial Version 1.0
297 --
298 -- End of Comments --
299
300 END AHL_WIP_JOB_PVT; -- Package spec
[Home] [Help]