1 PACKAGE WSM_Serial_support_PVT AUTHID CURRENT_USER AS
2 /* $Header: WSMVSERS.pls 120.4 2006/04/11 23:43 sthangad noship $ */
3
4 TYPE t_serial_intf_tbl_type is table of WSM_SERIAL_TXN_INTERFACE%ROWTYPE index by binary_integer;
5 TYPE t_serial_temp_tbl_type is table of WSM_SERIAL_TXN_TEMP%ROWTYPE index by binary_integer;
6 TYPE t_serial_number_tbl_type is table of MTL_SERIAL_NUMBERS%ROWTYPE index by binary_integer;
7 TYPE t_number is table of NUMBER index by binary_integer;
8 type t_varchar2 is table of varchar2(100) index by binary_integer;
9 TYPE t_wip_intf_tbl_type is table of WIP_SERIAL_MOVE_INTERFACE%ROWTYPE index by binary_integer;
10
11 WSM_ADD_SERIAL_NUM CONSTANT NUMBER := 1;
12 WSM_DELINK_SERIAL_NUM CONSTANT NUMBER := 2;
13 WSM_UPDATE_SERIAL_NUM CONSTANT NUMBER := 3;
14 WSM_GASSOC_SERIAL_NUM CONSTANT NUMBER := 4;
15
16
17 procedure update_serial( p_serial_number IN VARCHAR2,
18 p_inventory_item_id IN NUMBER,
19 -- p_new_inventory_item_id IN NUMBER,
20 p_organization_id IN NUMBER,
21 p_wip_entity_id IN NUMBER,
22 p_operation_seq_num IN NUMBER,
23 p_intraoperation_step_type IN NUMBER,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_error_msg OUT NOCOPY VARCHAR2,
26 x_error_count OUT NOCOPY NUMBER
27 );
28
29
30 PROCEDURE update_serial_attr( p_job_serial_number IN VARCHAR2,
31 p_inventory_item_id IN NUMBER,
32 p_organization_id IN NUMBER,
33 p_serial_desc_attr_tbl IN inv_serial_number_attr.char_table,
34 p_attribute_category IN VARCHAR2,
35 p_update_serial_attr IN NUMBER DEFAULT NULL,
36 p_update_desc_attr IN NUMBER,
37 p_serial_attributes_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_error_count OUT NOCOPY NUMBER,
40 x_error_msg OUT NOCOPY VARCHAR2
41 );
42
43 PROCEDURE validate_qty ( p_primary_item_id IN NUMBER,
44 p_organization_id IN NUMBER,
45 p_primary_qty IN NUMBER,
46 p_net_qty IN NUMBER,
47 p_primary_uom IN VARCHAR2,
48 p_transaction_qty IN NUMBER DEFAULT NULL,
49 p_transaction_uom IN VARCHAR2 DEFAULT NULL,
50 x_return_status OUT NOCOPY VARCHAR2,
51 x_error_count OUT NOCOPY NUMBER,
52 x_error_msg OUT NOCOPY VARCHAR2
53 );
54
55
56 Procedure add_assoc_serial_number(p_wip_entity_id IN NUMBER,
57 p_organization_id IN NUMBER,
58 p_inventory_item_id IN NUMBER,
59 -- will be null in case (Generation..)
60 p_serial_number IN OUT NOCOPY VARCHAR2,
61 -- pass 1 if the calling program knows that it is a new serial number
62 p_new_serial_number IN NUMBER DEFAULT NULL,
63 p_operation_seq_num IN NUMBER DEFAULT NULL,
64 p_intraoperation_step IN NUMBER DEFAULT NULL,
65 x_return_status OUT NOCOPY VARCHAR2,
66 x_error_msg OUT NOCOPY VARCHAR2,
67 x_error_count OUT NOCOPY NUMBER
68 );
72 p_inventory_item_id IN NUMBER,
69 Procedure LBJ_serial_intf_proc( p_header_id IN NUMBER,
70 p_wip_entity_id IN NUMBER,
71 p_organization_id IN NUMBER,
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_error_count OUT NOCOPY NUMBER,
75 x_error_msg OUT NOCOPY VARCHAR2
76 ) ;
77
78 -- The addition/deletion of serial numbers will be handled by this procedure...
79 -- Then it will invoke the main processor whose activities will be common...
80 -- When there is data in p_wsm_serial_nums_tbl, p_header_id will be ignored and no data will be fetched from the interface
81 Procedure Move_serial_intf_proc(p_header_id IN NUMBER,
82 p_wsm_serial_nums_tbl IN WSM_Serial_Support_GRP.WSM_SERIAL_NUM_TBL,
83 p_move_txn_type IN NUMBER,
84 p_wip_entity_id IN NUMBER,
85 p_organization_id IN NUMBER,
86 p_inventory_item_id IN NUMBER,
87 p_move_qty IN NUMBER,
88 p_scrap_qty IN NUMBER,
89 p_available_qty IN NUMBER,
90 p_curr_job_op_seq_num IN NUMBER,
91 p_curr_job_intraop_step IN NUMBER,
92 p_from_rtg_op_seq_num IN NUMBER,
93 p_to_rtg_op_seq_num IN NUMBER,
94 p_to_intraoperation_step IN NUMBER,
95 p_user_serial_tracking IN NUMBER,
96 p_move_txn_id IN NUMBER,
97 p_scrap_txn_id IN NUMBER,
98 p_old_move_txn_id IN NUMBER,
99 p_old_scrap_txn_id IN NUMBER,
100 p_jump_flag IN VARCHAR2 DEFAULT NULL,
101 p_scrap_at_operation IN NUMBER DEFAULT NULL,
102 -- ST : Fix for bug 5140761 Addded the above parameter --
103 x_serial_track_flag OUT NOCOPY NUMBER,
104 x_return_status OUT NOCOPY VARCHAR2,
105 x_error_msg OUT NOCOPY VARCHAR2,
106 x_error_count OUT NOCOPY NUMBER
107 );
108
109 Procedure Update_attr_move ( p_group_id IN NUMBER DEFAULT NULL, -- for interface...
110 p_internal_group_id IN NUMBER DEFAULT NULL,
111 p_move_txn_id IN NUMBER DEFAULT NULL, -- for forms...
112 p_scrap_txn_id IN NUMBER DEFAULT NULL, -- for forms...
113 p_organization_id IN NUMBER ,
114 x_return_status OUT NOCOPY VARCHAR2 ,
115 x_error_count OUT NOCOPY NUMBER ,
116 x_error_msg OUT NOCOPY VARCHAR2
117 );
118
119 Procedure Insert_move_attr ( p_group_id IN NUMBER DEFAULT NULL,
120 p_move_txn_id IN NUMBER DEFAULT NULL,
121 p_scrap_txn_id IN NUMBER DEFAULT NULL,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_error_count OUT NOCOPY NUMBER,
124 x_error_msg OUT NOCOPY VARCHAR2
125 );
126
127 Procedure Move_forms_serial_proc( p_move_txn_type IN NUMBER,
128 p_wip_entity_id IN NUMBER,
129 p_organization_id IN NUMBER,
130 p_inventory_item_id IN NUMBER,
131 p_move_qty IN NUMBER,
132 p_scrap_qty IN NUMBER,
133 p_available_qty IN NUMBER,
134 p_curr_job_op_seq_num IN NUMBER,
135 p_curr_job_intraop_step IN NUMBER,
136 p_from_rtg_op_seq_num IN NUMBER,
137 p_to_rtg_op_seq_num IN NUMBER,
138 p_to_intraoperation_step IN NUMBER,
139 p_user_serial_tracking IN NUMBER,
140 p_move_txn_id IN NUMBER,
141 p_scrap_txn_id IN NUMBER,
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_error_msg OUT NOCOPY VARCHAR2,
144 x_error_count OUT NOCOPY NUMBER
145 );
146
147 Procedure WLT_serial_intf_proc ( p_header_id IN NUMBER,
148 p_wip_entity_id IN NUMBER,
149 p_wip_entity_name IN VARCHAR2,
150 p_wlt_txn_type IN NUMBER,
151 p_organization_id IN NUMBER,
152 x_serial_num_tbl OUT NOCOPY WSM_Serial_Support_GRP.WSM_SERIAL_NUM_TBL,
153 x_return_status OUT NOCOPY VARCHAR2,
154 x_error_msg OUT NOCOPY VARCHAR2,
155 x_error_count OUT NOCOPY NUMBER
156 );
157
158
159 Procedure WLT_serial_processor ( p_calling_mode IN NUMBER,
160 p_wlt_txn_type IN NUMBER,
161 p_organization_id IN NUMBER,
162 p_txn_id IN NUMBER,
163 p_starting_jobs_tbl IN WSM_WIP_LOT_TXN_PVT.WLTX_STARTING_JOBS_TBL_TYPE,
164 p_resulting_jobs_tbl IN WSM_WIP_LOT_TXN_PVT.WLTX_RESULTING_JOBS_TBL_TYPE,
165 p_serial_num_tbl IN OUT NOCOPY WSM_Serial_Support_GRP.WSM_SERIAL_NUM_TBL,
166 x_return_status OUT NOCOPY VARCHAR2,
167 x_error_msg OUT NOCOPY VARCHAR2,
168 x_error_count OUT NOCOPY NUMBER
169 );
170
171 Procedure find_undo_ret_serials ( p_header_id IN NUMBER, -- passed value will be :parameter.move_txn_id
172 p_wip_entity_id IN NUMBER,
173 p_move_txn_type IN NUMBER,
174 p_organization_id IN NUMBER,
175 p_inventory_item_id IN NUMBER,
176 p_move_qty IN NUMBER,
177 p_scrap_qty IN NUMBER,
178 x_return_status OUT NOCOPY VARCHAR2,
179 x_error_msg OUT NOCOPY VARCHAR2,
180 x_error_count OUT NOCOPY NUMBER
181 );
182 END WSM_Serial_support_PVT;