1 PACKAGE CSD_HV_WIP_JOB_PVT as
2 /* $Header: csdvhvjs.pls 120.20.12020000.6 2013/04/04 19:28:42 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 -- yvchen: bug 13258460 - 12.1.3+ add DFFs
89 ,ATTRIBUTE_CATEGORY VARCHAR2(30)
90 ,ATTRIBUTE1 VARCHAR2(150)
91 ,ATTRIBUTE2 VARCHAR2(150)
92 ,ATTRIBUTE3 VARCHAR2(150)
93 ,ATTRIBUTE4 VARCHAR2(150)
94 ,ATTRIBUTE5 VARCHAR2(150)
95 ,ATTRIBUTE6 VARCHAR2(150)
96 ,ATTRIBUTE7 VARCHAR2(150)
97 ,ATTRIBUTE8 VARCHAR2(150)
98 ,ATTRIBUTE9 VARCHAR2(150)
99 ,ATTRIBUTE10 VARCHAR2(150)
100 ,ATTRIBUTE11 VARCHAR2(150)
101 ,ATTRIBUTE12 VARCHAR2(150)
102 ,ATTRIBUTE13 VARCHAR2(150)
103 ,ATTRIBUTE14 VARCHAR2(150)
104 ,ATTRIBUTE15 VARCHAR2(150)
105 ,CREATE_RO_FLAG VARCHAR2(1) -- bug#13698799 auto create ro, parent ro
106 );
107
108 -- Table Type corresponding to JOB_BILL_ROUTING_REC_TYPE
109
110 TYPE MTL_TXN_DTLS_TBL_TYPE IS TABLE OF MTL_TXN_DTLS_REC_TYPE INDEX BY BINARY_INTEGER;
111
112
113
114
115 TYPE RES_TXN_DTLS_REC_TYPE IS RECORD (
116 WIP_TRANSACTION_DETAIL_ID NUMBER
117 ,required_quantity NUMBER
118 ,applied_quantity NUMBER
119 ,pending_quantity NUMBER
120 ,job_quantity NUMBER
121 ,op_scheduled_quantity NUMBER
122 ,basis_type NUMBER
123 ,RESOURCE_ID NUMBER
124 ,RESOURCE_SEQ_NUM NUMBER
125 ,WIP_ENTITY_ID NUMBER
126 ,ORGANIZATION_ID NUMBER
127 ,ORGANIZATION_CODE VARCHAR2(3)
128 ,OPERATION_SEQ_NUM NUMBER
129 ,TRANSACTION_QUANTITY NUMBER
130 ,TRANSACTION_UOM VARCHAR2(3)
131 ,UOM_CODE VARCHAR2(3)
132 ,WIP_ENTITY_NAME VARCHAR2(80)
133 ,employee_id NUMBER
134 ,EMPLOYEE_NUM VARCHAR2(30)
135 ,OBJECT_VERSION_NUMBER NUMBER
136 ,NEW_ROW VARCHAR2(1)
137 -- swai: bug 15955754, FP of bug 14823164 - 12.1.3+ add DFFs
138 ,ATTRIBUTE_CATEGORY VARCHAR2(30)
139 ,ATTRIBUTE1 VARCHAR2(150)
140 ,ATTRIBUTE2 VARCHAR2(150)
141 ,ATTRIBUTE3 VARCHAR2(150)
142 ,ATTRIBUTE4 VARCHAR2(150)
143 ,ATTRIBUTE5 VARCHAR2(150)
144 ,ATTRIBUTE6 VARCHAR2(150)
145 ,ATTRIBUTE7 VARCHAR2(150)
146 ,ATTRIBUTE8 VARCHAR2(150)
147 ,ATTRIBUTE9 VARCHAR2(150)
148 ,ATTRIBUTE10 VARCHAR2(150)
149 ,ATTRIBUTE11 VARCHAR2(150)
150 ,ATTRIBUTE12 VARCHAR2(150)
151 ,ATTRIBUTE13 VARCHAR2(150)
152 ,ATTRIBUTE14 VARCHAR2(150)
153 ,ATTRIBUTE15 VARCHAR2(150)
154 );
155
156 -- Table Type corresponding to JOB_BILL_ROUTING_REC_TYPE
157
158 TYPE RES_TXN_DTLS_TBL_TYPE IS TABLE OF RES_TXN_DTLS_REC_TYPE INDEX BY BINARY_INTEGER;
159
160
161
162 TYPE OP_DTLS_REC_TYPE IS RECORD (
163 WIP_TRANSACTION_DETAIL_ID NUMBER
164 ,BACKFLUSH_FLAG NUMBER -- swai: 4948649
165 ,COUNT_POINT_TYPE NUMBER -- swai: 4948649
166 ,DEPARTMENT_ID NUMBER
167 ,DESCRIPTION VARCHAR2(240)
168 ,FIRST_UNIT_COMPLETION_DATE DATE -- swai: 4948649
169 ,FIRST_UNIT_START_DATE DATE -- swai: 4948649
170 ,LAST_UNIT_COMPLETION_DATE DATE
171 ,LAST_UNIT_START_DATE DATE -- swai: 4948649
172 ,MINIMUM_TRANSFER_QUANTITY NUMBER -- swai: 4948649
173 ,OPERATION_SEQ_NUM NUMBER
174 ,STANDARD_OPERATION_ID NUMBER
175 ,WIP_ENTITY_ID NUMBER
176 ,ORGANIZATION_ID NUMBER
177 ,ORGANIZATION_CODE VARCHAR2(3)
178 ,WIP_ENTITY_NAME VARCHAR2(80)
179 ,OBJECT_VERSION_NUMBER NUMBER
180 ,NEW_ROW VARCHAR2(1)
181 -- yvchen: bug 13258460 - 12.1.3+ add DFFs
182 ,ATTRIBUTE_CATEGORY VARCHAR2(30)
183 ,ATTRIBUTE1 VARCHAR2(150)
184 ,ATTRIBUTE2 VARCHAR2(150)
185 ,ATTRIBUTE3 VARCHAR2(150)
186 ,ATTRIBUTE4 VARCHAR2(150)
187 ,ATTRIBUTE5 VARCHAR2(150)
188 ,ATTRIBUTE6 VARCHAR2(150)
189 ,ATTRIBUTE7 VARCHAR2(150)
190 ,ATTRIBUTE8 VARCHAR2(150)
191 ,ATTRIBUTE9 VARCHAR2(150)
192 ,ATTRIBUTE10 VARCHAR2(150)
193 ,ATTRIBUTE11 VARCHAR2(150)
194 ,ATTRIBUTE12 VARCHAR2(150)
195 ,ATTRIBUTE13 VARCHAR2(150)
196 ,ATTRIBUTE14 VARCHAR2(150)
197 ,ATTRIBUTE15 VARCHAR2(150)
198 );
199
200 -- Table Type corresponding to OP_DTLS_REC_TYPE
201
202 TYPE OP_DTLS_TBL_TYPE IS TABLE OF OP_DTLS_REC_TYPE INDEX BY BINARY_INTEGER;
203
204
205 -- Record Type for service code information.
206
207 TYPE SERVICE_CODE_REC_TYPE IS RECORD (
208 ro_service_code_id NUMBER,
209 inventory_item_id NUMBER,
210 service_code_id NUMBER,
211 object_version_number NUMBER
212 );
213
214
215 -- Table Type corresponding to SERVICE_CODE_REC_TYPE
216
217 TYPE SERVICE_CODE_TBL_TYPE IS TABLE OF SERVICE_CODE_REC_TYPE INDEX BY BINARY_INTEGER;
218
219 -- swai: bug 7182047 (FP of 6995498) wrapper function to get the default item
220 -- revision. Depending on the transaction type, check the corresponding profile
221 -- option and return null if the profile is No.
222 -- Transaction types are 'MAT_ISSUE' and 'JOB_COMP'. Passing null for
223 -- transaction type will always return a default from bom_revsions API.
224 FUNCTION get_default_item_revision
225 (
226 p_org_id IN NUMBER,
227 p_inventory_item_id IN NUMBER,
228 p_transaction_date IN DATE,
229 p_mat_transaction_type IN VARCHAR2 := null
230 ) RETURN VARCHAR2;
231
232 FUNCTION get_pending_quantity( p_wip_entity_id NUMBER,
233 p_operation_seq_num NUMBER,
234 p_resource_seq_num NUMBER,
235 p_primary_uom VARCHAR2 )
236 RETURN NUMBER ;
237
238 PROCEDURE process_oper_comp_txn
239 (
240 p_api_version_number IN NUMBER,
241 p_init_msg_list IN VARCHAR2,
242 p_commit IN VARCHAR2,
243 p_validation_level IN NUMBER,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 p_mv_txn_dtls_tbl IN MV_TXN_DTLS_TBL_TYPE
248 );
249
250 --
251 -- Inserts the transaction line(s) for job completion and then
252 -- processes the transaction lines if there are no details needed
253 -- OUT param:
254 -- x_transaction_header_id: If details are needed, the transaction
255 -- header ID will be populated. Otherwise
256 -- parameter is null.
257 --
258 PROCEDURE process_job_comp_txn
259 (
260 p_api_version_number IN NUMBER,
261 p_init_msg_list IN VARCHAR2 ,
262 p_commit IN VARCHAR2 ,
263 p_validation_level IN NUMBER,
264 x_return_status OUT NOCOPY VARCHAR2,
265 x_msg_count OUT NOCOPY NUMBER,
266 x_msg_data OUT NOCOPY VARCHAR2,
267 p_comp_job_dtls_rec IN JOB_DTLS_REC_TYPE,
268 --x_need_details_flag OUT NOCOPY VARCHAR2
269 x_transaction_header_id OUT NOCOPY NUMBER
270 );
271
272 --
273 -- Inserts the transaction line(s) for job completion
274 -- Does NOT process the transaction lines
275 -- OUT params:
276 -- x_need_details_flag: set to 'T' if details are neede, otherwise 'F'
277 -- x_transaction_header_id: Transaction header ID always passed back
278 -- regardless of need details param
279 --
280 PROCEDURE insert_job_comp_txn
281 (
282 p_api_version_number IN NUMBER,
283 p_init_msg_list IN VARCHAR2 ,
284 p_commit IN VARCHAR2 ,
285 p_validation_level IN NUMBER,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2,
289 p_comp_job_dtls_rec IN JOB_DTLS_REC_TYPE,
290 x_need_details_flag OUT NOCOPY VARCHAR2,
291 x_transaction_header_id OUT NOCOPY NUMBER
292 );
293
294 PROCEDURE process_mti_transactions
295 (
296 p_api_version_number IN NUMBER,
297 p_init_msg_list IN VARCHAR2,
298 p_commit IN VARCHAR2,
299 p_validation_level IN NUMBER,
300 x_return_status OUT NOCOPY VARCHAR2,
301 x_msg_count OUT NOCOPY NUMBER,
302 x_msg_data OUT NOCOPY VARCHAR2,
303 p_txn_header_id IN NUMBER
304 );
305
306
307 PROCEDURE process_issue_mtl_txn
308 (
309 p_api_version_number IN NUMBER,
310 p_init_msg_list IN VARCHAR2,
311 p_commit IN VARCHAR2,
312 p_validation_level IN NUMBER,
313 x_return_status OUT NOCOPY VARCHAR2,
314 x_msg_count OUT NOCOPY NUMBER,
315 x_msg_data OUT NOCOPY VARCHAR2,
316 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
317 -- p_ro_quantity IN NUMBER,
318 x_transaction_header_id OUT NOCOPY NUMBER
319 );
320
321 --
322 -- Updates the transaction lines with lot and serial numbers
326 (
323 -- and the processes the transaction lines
324 --
325 PROCEDURE process_issue_mtl_txns_lot_srl
327 p_api_version_number IN NUMBER,
328 p_init_msg_list IN VARCHAR2,
329 p_commit IN VARCHAR2,
330 p_validation_level IN NUMBER,
331 x_return_status OUT NOCOPY VARCHAR2,
332 x_msg_count OUT NOCOPY NUMBER,
333 x_msg_data OUT NOCOPY VARCHAR2,
334 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
335 p_transaction_header_id IN NUMBER
336 );
337
338 --
339 -- Updates the material transaction lines with lot and serial numbers only
340 -- Does NOT process the transaction lines
341 --
342 PROCEDURE update_mtl_txns_lot_srl
343 (
344 p_api_version_number IN NUMBER,
345 p_init_msg_list IN VARCHAR2,
346 p_commit IN VARCHAR2,
347 p_validation_level IN NUMBER,
348 x_return_status OUT NOCOPY VARCHAR2,
349 x_msg_count OUT NOCOPY NUMBER,
350 x_msg_data OUT NOCOPY VARCHAR2,
351 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
352 p_transaction_header_id IN NUMBER
353 );
354
355 PROCEDURE process_transact_res_txn
356 (
357 p_api_version_number IN NUMBER,
358 p_init_msg_list IN VARCHAR2,
359 p_commit IN VARCHAR2,
360 p_validation_level IN NUMBER,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_msg_count OUT NOCOPY NUMBER,
363 x_msg_data OUT NOCOPY VARCHAR2,
364 p_res_txn_dtls_tbl IN RES_TXN_DTLS_TBL_TYPE
365 -- p_ro_quantity IN NUMBER
366 );
367
368 PROCEDURE PROCESS_SAVE_MTL_TXN_DTLS
369 (
370 p_api_version_number IN NUMBER,
371 p_init_msg_list IN VARCHAR2 ,
372 p_commit IN VARCHAR2 ,
373 p_validation_level IN NUMBER ,
374 x_return_status OUT NOCOPY VARCHAR2,
375 x_msg_count OUT NOCOPY NUMBER,
376 x_msg_data OUT NOCOPY VARCHAR2,
377 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
378 x_op_created OUT NOCOPY VARCHAR
379 -- p_ro_quantity IN NUMBER
380 );
381
382
383 PROCEDURE PROCESS_SAVE_RES_TXN_DTLS
384 (
385 p_api_version_number IN NUMBER,
386 p_init_msg_list IN VARCHAR2,
387 p_Commit IN VARCHAR2,
388 p_validation_level IN NUMBER,
389 x_return_status OUT NOCOPY VARCHAR2,
390 x_msg_count OUT NOCOPY NUMBER,
391 x_msg_data OUT NOCOPY VARCHAR2,
392 p_res_txn_dtls_tbl IN res_TXN_DTLS_TBL_TYPE
393 -- p_ro_quantity IN NUMBER
394 );
395
396
397 /** swai **/
398 PROCEDURE PROCESS_SAVE_OP_DTLS
399 (
400 p_api_version_number IN NUMBER,
401 p_init_msg_list IN VARCHAR2,
402 p_Commit IN VARCHAR2,
403 p_validation_level IN NUMBER,
404 x_return_status OUT NOCOPY VARCHAR2,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_msg_data OUT NOCOPY VARCHAR2,
407 p_op_dtls_tbl IN OP_DTLS_TBL_TYPE
408 );
409
410 PROCEDURE create_wip_job
411 (
412 p_api_version_number IN NUMBER,
413 p_init_msg_list IN VARCHAR2 ,
414 p_commit IN VARCHAR2 ,
415 p_validation_level IN NUMBER,
416 x_return_status OUT NOCOPY VARCHAR2,
417 x_msg_count OUT NOCOPY NUMBER,
418 x_msg_data OUT NOCOPY VARCHAR2,
419 x_job_name OUT NOCOPY VARCHAR2,
420 p_repair_line_id IN NUMBER,
421 p_repair_quantity IN NUMBER,
422 p_inventory_item_Id IN NUMBER
423 );
424
425 PROCEDURE generate_wip_jobs_from_scs
426 (
427 p_api_version_number IN NUMBER,
428 p_init_msg_list IN VARCHAR2 ,
429 p_commit IN VARCHAR2 ,
430 p_validation_level IN NUMBER,
431 x_return_status OUT NOCOPY VARCHAR2,
432 x_msg_count OUT NOCOPY NUMBER,
433 x_msg_data OUT NOCOPY VARCHAR2,
434 p_repair_line_id IN NUMBER,
435 p_repair_quantity IN NUMBER,
439 --
436 p_service_code_tbl IN service_code_tbl_type
437 );
438
440 -- swai: 12.1.2 Time clock functionality
441 -- Auto-issues all material lines.
442 -- If WIP entity id and operaion are specified, then only materials for
443 -- that operation will be issued and repair line will be disregarded.
444 -- Future functionality:
445 -- If repair line id is specified without wip entity id and operation,
446 -- then all materials for all jobs on that repair order will be issued.
447 --
448 PROCEDURE process_auto_issue_mtl_txn
449 (
450 p_api_version_number IN NUMBER,
451 p_init_msg_list IN VARCHAR2,
452 p_commit IN VARCHAR2,
453 p_validation_level IN NUMBER,
454 x_return_status OUT NOCOPY VARCHAR2,
455 x_msg_count OUT NOCOPY NUMBER,
456 x_msg_data OUT NOCOPY VARCHAR2,
457 p_wip_entity_id IN NUMBER,
458 p_operation_seq_num IN NUMBER,
459 p_repair_line_id IN NUMBER,
460 x_transaction_header_id OUT NOCOPY NUMBER
461 );
462
463 --
464 -- swai: 12.1.2 Time clock functionality
465 -- Auto-transacts all resource lines.
466 -- If WIP entity id and operaion are specified, then only resources for
467 -- that operation will be issued and repair line will be disregarded.
468 -- Future functionality:
469 -- If repair line id is specified without wip entity id and operation,
470 -- then all resources for all jobs on that repair order will be issued.
471 --
472 PROCEDURE process_auto_transact_res_txn
473 (
474 p_api_version_number IN NUMBER,
475 p_init_msg_list IN VARCHAR2,
476 p_commit IN VARCHAR2,
477 p_validation_level IN NUMBER,
478 x_return_status OUT NOCOPY VARCHAR2,
479 x_msg_count OUT NOCOPY NUMBER,
480 x_msg_data OUT NOCOPY VARCHAR2,
481 p_wip_entity_id IN NUMBER,
482 p_operation_seq_num IN NUMBER,
483 p_repair_line_id IN NUMBER
484 );
485
486 --
487 -- swai: 12.1.2 Time clock functionality
488 -- Auto-completes an operations
489 -- If WIP entity id and operaion are specified, then only the spcified
490 -- operation will be completed.
491 -- Future functionality:
492 -- If repair line id is specified without wip entity id and operation,
493 -- then all operations on all jobs on that repair order will be completed.
494 --
495 PROCEDURE process_auto_oper_comp_txn
496 (
497 p_api_version_number IN NUMBER,
498 p_init_msg_list IN VARCHAR2,
499 p_commit IN VARCHAR2,
500 p_validation_level IN NUMBER,
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_msg_count OUT NOCOPY NUMBER,
503 x_msg_data OUT NOCOPY VARCHAR2,
504 p_wip_entity_id IN NUMBER,
505 p_operation_seq_num IN NUMBER,
506 p_repair_line_id IN NUMBER
507
508 );
509
510
511 --
512 -- swai: 12.1.2 Time clock functionality
513 -- Creates a resource or adds to an existing resource on a job/operation
514 -- for the given time clock entry id.
515 -- Notes:
516 -- (1) Resource comes from profile option CSD_DEF_HV_BOM_RESOURCE,
517 -- if the resource id from time clock entry is null
518 -- (2) Transaction qty is always specified in DAY UOM
519 -- (3) Org is always defined by profile CSD_DEF_REP_INV_ORG
520 --
521 PROCEDURE process_time_clock_res_txn
522 (
523 p_api_version_number IN NUMBER,
524 p_init_msg_list IN VARCHAR2,
525 p_commit IN VARCHAR2,
526 p_validation_level IN NUMBER,
527 x_return_status OUT NOCOPY VARCHAR2,
528 x_msg_count OUT NOCOPY NUMBER,
529 x_msg_data OUT NOCOPY VARCHAR2,
530 p_time_clock_entry_id IN NUMBER
531 );
532
533 --
534 -- swai: 12.1.2 Time clock functionality
535 -- Complete work button: change Repair Order Status
536 -- Changes repair order status for a given repair order to the
537 -- status specified by profile option 'CSD_COMPLETE_WORK_RO_STATUS'
538 --
539 PROCEDURE process_comp_work_ro_status
540 (
541 p_api_version_number IN NUMBER,
542 p_init_msg_list IN VARCHAR2,
543 p_commit IN VARCHAR2,
544 p_validation_level IN NUMBER,
545 x_return_status OUT NOCOPY VARCHAR2,
546 x_msg_count OUT NOCOPY NUMBER,
547 x_msg_data OUT NOCOPY VARCHAR2,
548 p_repair_line_id IN NUMBER,
549 x_new_flow_status_code OUT NOCOPY VARCHAR2,
550 x_new_ro_status_code OUT NOCOPY VARCHAR2
551
552 );
553
554
555 /* swai: 12.1.3
556 * bug 9640411
557 * Include the ability to delete an existing material transaction detail
558 * and its requirements, as long as no materials have been transacted.
559 * Deletes a saved material requirement that has not been transacted yet.
563 * inventory_item_id
560 * The following fields in p_mtl_txn_dtls are expected to be filled out:
561 * wip_entity_id
562 * organization_id
564 * operation_seq_num
565 * wip_transaction_detail_id (optional)
566 */
567 PROCEDURE process_delete_mtl_txn_dtl
568 (
569 p_api_version_number IN NUMBER,
570 p_init_msg_list IN VARCHAR2 ,
571 p_commit IN VARCHAR2 ,
572 p_validation_level IN NUMBER ,
573 x_return_status OUT NOCOPY VARCHAR2,
574 x_msg_count OUT NOCOPY NUMBER,
575 x_msg_data OUT NOCOPY VARCHAR2,
576 p_mtl_txn_dtls IN MTL_TXN_DTLS_REC_TYPE
577 );
578
579 /*
580 * swai: 12.1.3+ - bug 13449544 (FP bug 13600173)
581 * Calls new WIP API to determine whether or not an operation is deletable.
582 * In addition, checks Time Clock to see if user has checked into the operation
583 * and also checks Material transactions to see if user has transacted for
584 * Returns 'Y' if WIP API returns true AND there are NO time clock entries AND
585 * there are no Material transactions. Otherwise, returns 'N' for all
586 * other cases.
587 */
588 FUNCTION is_delete_wip_op_valid
589 (
590 p_organization_id IN NUMBER,
591 p_wip_entity_id IN NUMBER,
592 p_operation_seq_num IN NUMBER
593 ) RETURN VARCHAR2;
594
595 /* swai: 12.1.3+ - bug 13449544 (FP bug 13600173)
596 * Delete an existing operation and its associated requirements.
597 * The following fields in p_op_dtls are expected to be filled out:
598 * wip_entity_id
599 * organization_id
600 * operation_seq_num
601 */
602 PROCEDURE process_delete_op_dtl
603 (
604 p_api_version_number IN NUMBER,
605 p_init_msg_list IN VARCHAR2 ,
606 p_commit IN VARCHAR2 ,
607 p_validation_level IN NUMBER ,
608 x_return_status OUT NOCOPY VARCHAR2,
609 x_msg_count OUT NOCOPY NUMBER,
610 x_msg_data OUT NOCOPY VARCHAR2,
611 p_op_dtls IN OP_DTLS_REC_TYPE
612 );
613
614 /*
615 * swai: bug 13820264 FP of bug#13797285
616 * subhat: 12.1.3, bug#13797285
617 * procedure: update_mat_issue_quantities
618 * description: This procedure updates the actual issued quantity for a material reqmt
619 * along with recovered quantities for the material reqmt line. The API
620 * will add the quantities to the existing quantity. The API can update the
621 * individual material requirement line in CSD_WIP_TRANSACTION_DETAILS table or
622 * can be used to update all the material requirement under a job, or it
623 * can be used to update the table for all the repair orders. The API can
624 * also be used to update all the jobs for all the repairs orders.The actual
625 * code execution path will be decided by the parameters passed.
626 * @parameters:
627 * p_repair_line_id IN NUMBER repair line id for which the updation should be done.
628 * p_wip_entity_id IN NUMBER job for which the updateion should be done.
629 * p_wip_transaction_detail_id IN NUMBER The material requirement line corresponding to
630 * to this primary key will be updated.
631 * p_transaction_type_id IN NUMBER. The transaction type id. Currently 35 for wip issue
632 * and 43 for wip component return are supported.
633 * p_quantity IN NUMBER The quantity that was transacted.
634 * p_exclude_closed_jobs IN VARCHAR2. Specifies whether closed jobs should be
635 * processed or not.
636 * p_transaction_date_start IN DATE. When provided, only the transactions of specified date
637 * will be processed.
638 * p_transaction_date_end IN DATE. when provided along with p_transaction_date_start then
639 * transactions in the date range are processed. If p_transaction_date_start is provided
640 * and p_transaction_date_end is null then all the transaction between p_transaction_date_start
641 * and SYSDATE are processed.
642 *
643 * Processing logic
644 * if p_wip_transaction_detail_id AND p_transaction_type_id AND p_quantity
645 * directly update the CSD_WIP_TRANSACTION_DETAILS table.
646 * else if p_wip_transaction_detail_id is not null then get the values from MTL table
647 * and update.
648 * else if p_wip_entity_id is not null then get the values from MTL table and update all
649 * eligible material requirements under that job.
650 * else if p_repair_line_id is not null then get all the jobs under the repair order,
651 * update the actual issued and actual recovered parts for the material requirements.
652 * else run the updation program for all the repairs orders and update the corresponding
653 * quantities. If p_transaction_date_start is not null then look for MTL transactions
654 * starting from p_transaction_date_start to NVL(p_transaction_date_end, SYSDATE)
655 */
656
657 PROCEDURE update_mat_issue_quantities
658 (
659 p_api_version_number IN NUMBER,
660 p_init_msg_list IN VARCHAR2 ,
661 p_commit IN VARCHAR2 ,
662 p_validation_level IN NUMBER ,
663 x_return_status OUT NOCOPY VARCHAR2,
664 x_msg_count OUT NOCOPY NUMBER,
665 x_msg_data OUT NOCOPY VARCHAR2,
666 p_repair_line_id IN NUMBER DEFAULT NULL,
667 p_wip_entity_id IN NUMBER DEFAULT NULL,
671 p_exclude_closed_jobs IN VARCHAR2 DEFAULT 'Y',
668 p_wip_transaction_detail_id IN NUMBER DEFAULT NULL,
669 p_transaction_type_id IN NUMBER DEFAULT NULL,
670 p_quantity IN NUMBER DEFAULT NULL,
672 p_transaction_date_start IN DATE DEFAULT NULL,
673 p_transaction_date_end IN DATE DEFAULT NULL
674 );
675
676 /*
677 * swai: bug 13820264 FP of bug#13797285
678 * subhat: 12.1.3, bug#13797285
679 * procedure: update_mat_issue_quantities_cp
680 * description: Concurrent wrapper for update_mat_issue_quantities
681 */
682
683 PROCEDURE update_mat_issue_quantities_cp
684 (
685 retcode OUT NOCOPY VARCHAR2,
686 errbuf OUT NOCOPY VARCHAR2,
687 p_repair_line_id IN NUMBER DEFAULT NULL,
688 p_wip_entity_id IN NUMBER DEFAULT NULL,
689 p_exclude_closed_jobs IN VARCHAR2 DEFAULT 'Y',
690 p_transaction_date_start IN VARCHAR2 DEFAULT NULL,
691 p_transaction_date_end IN VARCHAR2 DEFAULT NULL
692 );
693
694 -- swai: Bug 14804275 FP of bug 14752421 new procedure for creating estimates from wip
695 /*--------------------------------------------------------------------*/
696 /* procedure name: GENERATE_ESTIMATE_FROM_WIP */
697 /* description : Creates estimate header and lines using data from */
698 /* WIP jobs Materials and Labor can be either */
699 /* requirements or transacted materials and labor, */
700 /* depending on profile settings. This procedure calls */
701 /* CSD_REPAIR_ESTIMATE_PVT.Import_Estimates_From_Wip */
702 /* for the majority of the processing. */
703 /* */
704 /* Called from : Repair Technician portal */
705 /* */
706 /*--------------------------------------------------------------------*/
707 PROCEDURE generate_estimate_from_wip
708 (
709 p_api_version_number IN NUMBER,
710 p_init_msg_list IN VARCHAR2 ,
711 p_commit IN VARCHAR2 ,
712 p_validation_level IN NUMBER ,
713 x_return_status OUT NOCOPY VARCHAR2,
714 x_msg_count OUT NOCOPY NUMBER,
715 x_msg_data OUT NOCOPY VARCHAR2,
716 p_repair_line_id IN NUMBER
717 );
718
719 END CSD_HV_WIP_JOB_PVT;
[Home] [Help]