1 PACKAGE CSD_HV_WIP_JOB_PVT as
2 /* $Header: csdvhvjs.pls 120.10.12010000.2 2008/10/15 20:31:01 swai ship $ */
3 -- Start of Comments
4 -- Package name : CSD_HV_WIP_JOB_PVT
5 -- Purpose : This package is used for High Volume Repair Execution flow
6 --
7 --
8 -- History : 05/01/2005, Created by Shiv Ragunathan
9 -- History :
10 -- History :
11 -- NOTE :
12 -- End of Comments
13
14
15 -- Record Type for job header information
16
17 TYPE JOB_HEADER_REC_TYPE IS RECORD (
18 JOB_PREFIX VARCHAR2(80),
19 ORGANIZATION_ID NUMBER,
20 STATUS_type NUMBER,
21 SCHEDULED_START_DATE DATE,
22 SCHEDULED_END_DATE DATE,
23 INVENTORY_ITEM_ID NUMBER,
24 CLASS_CODE VARCHAR2(10),
25 QUANTITY NUMBER,
26 routing_reference_id NUMBER,
27 bom_reference_id NUMBER,
28 alternate_routing_designator VARCHAR2(10),
29 alternate_bom_designator VARCHAR2(10),
30 COMPLETION_SUBINVENTORY VARCHAR2(10),
31 COMPLETION_LOCATOR_ID NUMBER,
32 JOB_NAME VARCHAR2(240),
33 GROUP_ID NUMBER
34 );
35
36
37 TYPE JOB_DTLS_REC_TYPE IS RECORD (
38 WIP_ENTITY_ID NUMBER,
39 INVENTORY_ITEM_ID NUMBER,
40 ORGANIZATION_ID NUMBER,
41 TRANSACTION_QUANTITY NUMBER,
42 COMPLETION_SUBINVENTORY VARCHAR2(10),
43 COMPLETION_LOCATOR_ID NUMBER,
44 TRANSACTION_UOM VARCHAR2(3),
45 REVISION_QTY_CONTROl_CODE NUMBER,
46 SERIAL_NUMBER_CONTROL_CODE NUMBER,
47 LOT_CONTROL_CODE NUMBER );
48
49 TYPE MV_TXN_DTLS_REC_TYPE IS RECORD (
50 WIP_ENTITY_NAME VARCHAR2(240)
51 ,ORGANIZATION_ID NUMBER
52 ,FM_OPERATION_SEQ_NUM NUMBER
53 ,TO_OPERATION_SEQ_NUM NUMBER
54 ,TRANSACTION_QUANTITY NUMBER
55 ,TRANSACTION_UOM VARCHAR2(3)
56 ,WIP_ENTITY_ID NUMBER
57 );
58
59 -- Table Type corresponding to JOB_BILL_ROUTING_REC_TYPE
60
61 TYPE MV_TXN_DTLS_TBL_TYPE IS TABLE OF MV_TXN_DTLS_REC_TYPE INDEX BY BINARY_INTEGER;
62
63 TYPE MTL_TXN_DTLS_REC_TYPE IS RECORD (
64 WIP_TRANSACTION_DETAIL_ID NUMBER
65 ,REQUIRED_QUANTITY NUMBER
66 ,ISSUED_QUANTITY NUMBER
67 ,JOB_QUANTITY NUMBER
68 ,OP_SCHEDULED_QUANTITY NUMBER
69 ,INVENTORY_ITEM_ID NUMBER
70 ,WIP_ENTITY_ID NUMBER
71 ,ORGANIZATION_ID NUMBER
72 ,OPERATION_SEQ_NUM NUMBER
73 ,TRANSACTION_QUANTITY NUMBER
74 ,TRANSACTION_UOM VARCHAR2(3)
75 ,UOM_CODE VARCHAR2(3)
76 ,SERIAL_NUMBER VARCHAR2(30)
77 ,LOT_NUMBER VARCHAR2(80) -- fix for bug#4625226
78 ,REVISION VARCHAR2(3)
79 ,revision_qty_control_code NUMBER
80 ,SERIAL_NUMBER_CONTROL_CODE NUMBER
81 ,lot_control_code NUMBER
82 ,SUPPLY_SUBINVENTORY VARCHAR2(10)
83 ,SUPPLY_LOCATOR_ID NUMBER
84 ,TRANSACTION_INTERFACE_ID NUMBER
85 ,OBJECT_VERSION_NUMBER NUMBER
86 ,NEW_ROW VARCHAR2(1)
87 ,REASON_ID NUMBER -- swai bug 6841113
88 );
89
90 -- Table Type corresponding to JOB_BILL_ROUTING_REC_TYPE
91
92 TYPE MTL_TXN_DTLS_TBL_TYPE IS TABLE OF MTL_TXN_DTLS_REC_TYPE INDEX BY BINARY_INTEGER;
93
94
95
96
97 TYPE RES_TXN_DTLS_REC_TYPE IS RECORD (
98 WIP_TRANSACTION_DETAIL_ID NUMBER
99 ,required_quantity NUMBER
100 ,applied_quantity NUMBER
101 ,pending_quantity NUMBER
102 ,job_quantity NUMBER
103 ,op_scheduled_quantity NUMBER
104 ,basis_type NUMBER
105 ,RESOURCE_ID NUMBER
106 ,RESOURCE_SEQ_NUM NUMBER
107 ,WIP_ENTITY_ID NUMBER
108 ,ORGANIZATION_ID NUMBER
109 ,ORGANIZATION_CODE VARCHAR2(3)
110 ,OPERATION_SEQ_NUM NUMBER
111 ,TRANSACTION_QUANTITY NUMBER
112 ,TRANSACTION_UOM VARCHAR2(3)
113 ,UOM_CODE VARCHAR2(3)
114 ,WIP_ENTITY_NAME VARCHAR2(80)
115 ,employee_id NUMBER
116 ,EMPLOYEE_NUM VARCHAR2(30)
117 ,OBJECT_VERSION_NUMBER NUMBER
118 ,NEW_ROW VARCHAR2(1)
119 );
120
121 -- Table Type corresponding to JOB_BILL_ROUTING_REC_TYPE
122
123 TYPE RES_TXN_DTLS_TBL_TYPE IS TABLE OF RES_TXN_DTLS_REC_TYPE INDEX BY BINARY_INTEGER;
124
125
126
127 TYPE OP_DTLS_REC_TYPE IS RECORD (
128 WIP_TRANSACTION_DETAIL_ID NUMBER
129 ,BACKFLUSH_FLAG NUMBER -- swai: 4948649
130 ,COUNT_POINT_TYPE NUMBER -- swai: 4948649
131 ,DEPARTMENT_ID NUMBER
132 ,DESCRIPTION VARCHAR2(240)
133 ,FIRST_UNIT_COMPLETION_DATE DATE -- swai: 4948649
134 ,FIRST_UNIT_START_DATE DATE -- swai: 4948649
135 ,LAST_UNIT_COMPLETION_DATE DATE
136 ,LAST_UNIT_START_DATE DATE -- swai: 4948649
137 ,MINIMUM_TRANSFER_QUANTITY NUMBER -- swai: 4948649
138 ,OPERATION_SEQ_NUM NUMBER
139 ,STANDARD_OPERATION_ID NUMBER
140 ,WIP_ENTITY_ID NUMBER
141 ,ORGANIZATION_ID NUMBER
142 ,ORGANIZATION_CODE VARCHAR2(3)
143 ,WIP_ENTITY_NAME VARCHAR2(80)
144 ,OBJECT_VERSION_NUMBER NUMBER
145 ,NEW_ROW VARCHAR2(1)
146 );
147
148 -- Table Type corresponding to OP_DTLS_REC_TYPE
149
150 TYPE OP_DTLS_TBL_TYPE IS TABLE OF OP_DTLS_REC_TYPE INDEX BY BINARY_INTEGER;
151
152
153 -- Record Type for service code information.
154
155 TYPE SERVICE_CODE_REC_TYPE IS RECORD (
156 ro_service_code_id NUMBER,
157 inventory_item_id NUMBER,
158 service_code_id NUMBER,
159 object_version_number NUMBER
160 );
161
162
163 -- Table Type corresponding to SERVICE_CODE_REC_TYPE
164
165 TYPE SERVICE_CODE_TBL_TYPE IS TABLE OF SERVICE_CODE_REC_TYPE INDEX BY BINARY_INTEGER;
166
167 -- swai: bug 7182047 (FP of 6995498) wrapper function to get the default item
168 -- revision. Depending on the transaction type, check the corresponding profile
169 -- option and return null if the profile is No.
170 -- Transaction types are 'MAT_ISSUE' and 'JOB_COMP'. Passing null for
171 -- transaction type will always return a default from bom_revsions API.
172 FUNCTION get_default_item_revision
173 (
174 p_org_id IN NUMBER,
175 p_inventory_item_id IN NUMBER,
176 p_transaction_date IN DATE,
177 p_mat_transaction_type IN VARCHAR2 := null
178 ) RETURN VARCHAR2;
179
180 FUNCTION get_pending_quantity( p_wip_entity_id NUMBER,
181 p_operation_seq_num NUMBER,
182 p_resource_seq_num NUMBER,
183 p_primary_uom VARCHAR2 )
184 RETURN NUMBER ;
185
186 PROCEDURE process_oper_comp_txn
187 (
188 p_api_version_number IN NUMBER,
189 p_init_msg_list IN VARCHAR2,
190 p_commit IN VARCHAR2,
191 p_validation_level IN NUMBER,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2,
195 p_mv_txn_dtls_tbl IN MV_TXN_DTLS_TBL_TYPE
196 );
197
198 --
199 -- Inserts the transaction line(s) for job completion and then
200 -- processes the transaction lines if there are no details needed
201 -- OUT param:
202 -- x_transaction_header_id: If details are needed, the transaction
203 -- header ID will be populated. Otherwise
204 -- parameter is null.
205 --
206 PROCEDURE process_job_comp_txn
207 (
208 p_api_version_number IN NUMBER,
209 p_init_msg_list IN VARCHAR2 ,
210 p_commit IN VARCHAR2 ,
211 p_validation_level IN NUMBER,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2,
215 p_comp_job_dtls_rec IN JOB_DTLS_REC_TYPE,
216 --x_need_details_flag OUT NOCOPY VARCHAR2
217 x_transaction_header_id OUT NOCOPY NUMBER
218 );
219
220 --
221 -- Inserts the transaction line(s) for job completion
222 -- Does NOT process the transaction lines
223 -- OUT params:
224 -- x_need_details_flag: set to 'T' if details are neede, otherwise 'F'
225 -- x_transaction_header_id: Transaction header ID always passed back
226 -- regardless of need details param
227 --
228 PROCEDURE insert_job_comp_txn
229 (
230 p_api_version_number IN NUMBER,
231 p_init_msg_list IN VARCHAR2 ,
232 p_commit IN VARCHAR2 ,
233 p_validation_level IN NUMBER,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2,
237 p_comp_job_dtls_rec IN JOB_DTLS_REC_TYPE,
238 x_need_details_flag OUT NOCOPY VARCHAR2,
239 x_transaction_header_id OUT NOCOPY NUMBER
240 );
241
242 PROCEDURE process_mti_transactions
243 (
244 p_api_version_number IN NUMBER,
245 p_init_msg_list IN VARCHAR2,
246 p_commit IN VARCHAR2,
247 p_validation_level IN NUMBER,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2,
251 p_txn_header_id IN NUMBER
252 );
253
254
255 PROCEDURE process_issue_mtl_txn
256 (
257 p_api_version_number IN NUMBER,
258 p_init_msg_list IN VARCHAR2,
259 p_commit IN VARCHAR2,
260 p_validation_level IN NUMBER,
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_msg_count OUT NOCOPY NUMBER,
263 x_msg_data OUT NOCOPY VARCHAR2,
264 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
265 -- p_ro_quantity IN NUMBER,
266 x_transaction_header_id OUT NOCOPY NUMBER
267 );
268
269 --
270 -- Updates the transaction lines with lot and serial numbers
271 -- and the processes the transaction lines
272 --
273 PROCEDURE process_issue_mtl_txns_lot_srl
274 (
275 p_api_version_number IN NUMBER,
276 p_init_msg_list IN VARCHAR2,
277 p_commit IN VARCHAR2,
278 p_validation_level IN NUMBER,
279 x_return_status OUT NOCOPY VARCHAR2,
280 x_msg_count OUT NOCOPY NUMBER,
281 x_msg_data OUT NOCOPY VARCHAR2,
282 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
283 p_transaction_header_id IN NUMBER
284 );
285
286 --
287 -- Updates the material transaction lines with lot and serial numbers only
288 -- Does NOT process the transaction lines
289 --
290 PROCEDURE update_mtl_txns_lot_srl
291 (
292 p_api_version_number IN NUMBER,
293 p_init_msg_list IN VARCHAR2,
294 p_commit IN VARCHAR2,
295 p_validation_level IN NUMBER,
296 x_return_status OUT NOCOPY VARCHAR2,
297 x_msg_count OUT NOCOPY NUMBER,
298 x_msg_data OUT NOCOPY VARCHAR2,
299 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
300 p_transaction_header_id IN NUMBER
301 );
302
303 PROCEDURE process_transact_res_txn
304 (
305 p_api_version_number IN NUMBER,
306 p_init_msg_list IN VARCHAR2,
307 p_commit IN VARCHAR2,
308 p_validation_level IN NUMBER,
309 x_return_status OUT NOCOPY VARCHAR2,
310 x_msg_count OUT NOCOPY NUMBER,
311 x_msg_data OUT NOCOPY VARCHAR2,
312 p_res_txn_dtls_tbl IN RES_TXN_DTLS_TBL_TYPE
313 -- p_ro_quantity IN NUMBER
314 );
315
319 p_init_msg_list IN VARCHAR2 ,
316 PROCEDURE PROCESS_SAVE_MTL_TXN_DTLS
317 (
318 p_api_version_number IN NUMBER,
320 p_commit IN VARCHAR2 ,
321 p_validation_level IN NUMBER ,
322 x_return_status OUT NOCOPY VARCHAR2,
323 x_msg_count OUT NOCOPY NUMBER,
324 x_msg_data OUT NOCOPY VARCHAR2,
325 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
326 x_op_created OUT NOCOPY VARCHAR
327 -- p_ro_quantity IN NUMBER
328 );
329
330
331 PROCEDURE PROCESS_SAVE_RES_TXN_DTLS
332 (
333 p_api_version_number IN NUMBER,
334 p_init_msg_list IN VARCHAR2,
335 p_Commit IN VARCHAR2,
336 p_validation_level IN NUMBER,
337 x_return_status OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY NUMBER,
339 x_msg_data OUT NOCOPY VARCHAR2,
340 p_res_txn_dtls_tbl IN res_TXN_DTLS_TBL_TYPE
341 -- p_ro_quantity IN NUMBER
342 );
343
344
345 /** swai **/
346 PROCEDURE PROCESS_SAVE_OP_DTLS
347 (
348 p_api_version_number IN NUMBER,
349 p_init_msg_list IN VARCHAR2,
350 p_Commit IN VARCHAR2,
351 p_validation_level IN NUMBER,
352 x_return_status OUT NOCOPY VARCHAR2,
353 x_msg_count OUT NOCOPY NUMBER,
354 x_msg_data OUT NOCOPY VARCHAR2,
355 p_op_dtls_tbl IN OP_DTLS_TBL_TYPE
356 );
357
358 PROCEDURE create_wip_job
359 (
360 p_api_version_number IN NUMBER,
361 p_init_msg_list IN VARCHAR2 ,
362 p_commit IN VARCHAR2 ,
363 p_validation_level IN NUMBER,
364 x_return_status OUT NOCOPY VARCHAR2,
365 x_msg_count OUT NOCOPY NUMBER,
366 x_msg_data OUT NOCOPY VARCHAR2,
367 x_job_name OUT NOCOPY VARCHAR2,
368 p_repair_line_id IN NUMBER,
369 p_repair_quantity IN NUMBER,
370 p_inventory_item_Id IN NUMBER
371 );
372
373 PROCEDURE generate_wip_jobs_from_scs
374 (
375 p_api_version_number IN NUMBER,
376 p_init_msg_list IN VARCHAR2 ,
377 p_commit IN VARCHAR2 ,
378 p_validation_level IN NUMBER,
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2,
382 p_repair_line_id IN NUMBER,
383 p_repair_quantity IN NUMBER,
384 p_service_code_tbl IN service_code_tbl_type
385 );
386
387
388 END CSD_HV_WIP_JOB_PVT;
[Home] [Help]