1 PACKAGE inv_rcv_integration_pvt AUTHID CURRENT_USER AS
2 /* $Header: INVRCVVS.pls 120.10.12020000.3 2012/09/24 11:33:29 raminoch ship $*/
3
4 TYPE child_record_info IS RECORD
5 (orig_interface_trx_id NUMBER,
6 new_interface_trx_id NUMBER,
7 quantity NUMBER);
8
9 TYPE child_rec_tb_tp IS TABLE OF child_record_info
10 INDEX BY BINARY_INTEGER;
11
12 -- 14408061
13 TYPE lpn_tbl_typ IS TABLE OF NUMBER
14 INDEX BY BINARY_INTEGER;
15 g_lpn_tbl lpn_tbl_typ;
16 -- 14408061
17
18 g_atf_api_complete CONSTANT NUMBER := 1;
19 g_atf_api_cancel CONSTANT NUMBER := 2;
20 g_atf_api_abort CONSTANT NUMBER := 3;
21 g_atf_api_cleanup CONSTANT NUMBER := 4;
22
23 -- Description
24 -- Wrapper to be used by PO
25 -- if the LPN_GROUP is null then all LPN columns should be null, if not null then fail
26 -- Loop through all the distinct lpn_groups for the passed request_id and group_id and
27 -- lpn_groupis not null
28 -- For the rows fetched for each lpn_group call explode_lpn_contents
29 --
30
31 PROCEDURE Explode_lpn(p_request_id IN NUMBER,
32 p_group_id IN NUMBER );
33
34 -- Description
35 -- Procedure to Explode LPN when Iten info is Null
36 PROCEDURE Explode_lpn_contents(p_lpn_group_id IN NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2);
40
41 --Description
42 --Procedure to call the appropriate ATF api
43 PROCEDURE call_atf_api(x_return_status OUT nocopy VARCHAR2,
44 x_msg_data OUT nocopy VARCHAR2,
45 x_msg_count OUT nocopy NUMBER,
46 x_error_code OUT nocopy NUMBER,
47 p_source_task_id IN NUMBER,
48 p_activity_type_id IN NUMBER,
49 p_operation_type_id IN NUMBER DEFAULT NULL,
50 p_mol_id IN NUMBER,
51 p_atf_api_name IN NUMBER,
52 p_mmtt_error_code IN VARCHAR2 DEFAULT NULL,
53 p_mmtt_error_explanation IN VARCHAR2 DEFAULT NULL,
54 p_retain_mmtt IN VARCHAR2 DEFAULT 'N');
55 -- Description
56 -- Procedure to Validate LPN for each LPN group.
57 PROCEDURE Validate_lpn_info(p_lpn_group_id IN NUMBER,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2);
61
62 -- Description
63 -- Inserts a row in WLPNI
64 --
65 PROCEDURE insert_wlpni(p_LPN_ID IN NUMBER ,
66 p_LICENSE_PLATE_NUMBER IN VARCHAR2 DEFAULT NULL,
67 p_PARENT_LPN_ID IN NUMBER DEFAULT NULL,
68 p_PARENT_LICENSE_PLATE_NUMBER IN VARCHAR2 DEFAULT NULL,
69 p_REQUEST_ID IN NUMBER DEFAULT NULL,
70 p_INVENTORY_ITEM_ID IN NUMBER DEFAULT NULL,
71 p_REVISION IN VARCHAR2 DEFAULT NULL,
72 p_LOT_NUMBER IN VARCHAR2 DEFAULT NULL,
73 p_SERIAL_NUMBER IN VARCHAR2 DEFAULT NULL,
74 p_ORGANIZATION_ID IN NUMBER ,
75 p_SUBINVENTORY_CODE IN VARCHAR2 DEFAULT NULL,
76 p_LOCATOR_ID IN NUMBER DEFAULT NULL,
77 p_GROSS_WEIGHT_UOM_CODE IN VARCHAR2 DEFAULT NULL,
78 p_GROSS_WEIGHT IN NUMBER DEFAULT NULL,
79 p_CONTENT_VOLUME_UOM_CODE IN VARCHAR2 DEFAULT NULL,
80 p_CONTENT_VOLUME IN NUMBER DEFAULT NULL,
81 p_TARE_WEIGHT_UOM_CODE IN VARCHAR2 DEFAULT NULL,
82 p_TARE_WEIGHT IN NUMBER DEFAULT NULL,
83 p_STATUS_ID IN NUMBER DEFAULT NULL,
84 p_SEALED_STATUS IN NUMBER DEFAULT NULL,
85 p_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
86 p_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
87 p_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
88 p_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
89 p_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
90 p_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
91 p_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
92 p_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
93 p_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
94 p_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
95 p_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
96 p_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
97 p_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
98 p_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
99 p_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
100 p_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
101 p_COST_GROUP_ID IN NUMBER DEFAULT NULL,
102 p_LPN_CONTEXT IN NUMBER DEFAULT NULL,
103 p_LPN_REUSABILITY IN NUMBER DEFAULT NULL,
104 p_OUTERMOST_LPN_ID IN NUMBER DEFAULT NULL,
105 p_outermost_lpn IN VARCHAR2 DEFAULT NULL,
106 p_HOMOGENEOUS_CONTAINER IN NUMBER DEFAULT NULL,
107 p_SOURCE_TYPE_ID IN NUMBER DEFAULT NULL,
108 p_SOURCE_HEADER_ID IN NUMBER DEFAULT NULL,
109 p_SOURCE_LINE_ID IN NUMBER DEFAULT NULL,
110 p_SOURCE_LINE_DETAIL_ID IN NUMBER DEFAULT NULL,
111 p_SOURCE_NAME IN VARCHAR2 DEFAULT NULL,
112 p_LPN_GROUP_ID IN NUMBER,
113 x_return_status OUT NOCOPY VARCHAR2,
114 x_msg_count OUT NOCOPY NUMBER,
115 x_msg_data OUT NOCOPY VARCHAR2);
116
117
118 -- Description
119 -- Splits LOT/SERIAL
120 PROCEDURE split_lot_serial(p_rti_tb IN inv_rcv_integration_apis.child_rec_tb_tp,
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_msg_data OUT NOCOPY VARCHAR2);
124
125 PROCEDURE validate_lpn_locator( p_lpn_id IN NUMBER,
126 p_subinventory IN VARCHAR2,
127 p_locator_id IN NUMBER,
128 p_organization_id IN NUMBER,
129 x_lpn_match OUT NOCOPY VARCHAR2,
130 x_return_status OUT NOCOPY VARCHAR2,
131 x_msg_count OUT NOCOPY NUMBER,
132 x_msg_data OUT NOCOPY VARCHAR2);
133
134 PROCEDURE process_txn(p_txn_id IN NUMBER,
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2
138 ) ;
139
140 function get_primary_qty(
141 p_organization_id IN NUMBER,
142 p_inventory_item_id IN NUMBER,
143 p_uom IN VARCHAR2,
144 p_quantity IN NUMBER,
145 p_lot_number IN VARCHAR2 DEFAULT NULL) return number; --Bug#9570776
146
147 -- Description
148 -- Split the RTI based on the parent RT and
149 -- also update the parent_transaction_id information in RTI rows
150 --PROCEDURE Txn_match(p_group_id IN NUMBER,
151 -- x_return_status OUT NOCOPY VARCHAR2,
152 -- x_msg_count OUT NOCOPY NUMBER,
153 -- x_msg_data OUT NOCOPY VARCHAR2);
154 --
155
156
157
158 -- Description
159 -- Validate the LPN Status/ Lock the LPN row if needed
160 --PROCEDURE Check_lpn_status (p_group_id IN NUMBER default null,
161 -- p_lpn_group_id IN NUMBER default null,
162 -- x_return_status OUT NOCOPY VARCHAR2,
163 -- x_msg_count OUT NOCOPY NUMBER,
164 -- x_msg_data OUT NOCOPY VARCHAR2);
165 --
166 --TYPE cas_trans_rec_type IS RECORD
167 -- (
168 -- transaction_id NUMBER,
169 -- primary_quantity NUMBER
170 -- );
171
172 --TYPE trans_rec_tb_tp IS TABLE OF cas_trans_rec_type
173 -- INDEX BY BINARY_INTEGER;
174
175 -- Description
176 -- Gets the LOT/SERIAL CONTROL CODE
177 -- Splits MTLI/ MSNI
178 -- Creates new MTLT / MSNT
179 --PROCEDURE break(p_org_tid IN NUMBER,
180 -- p_new_transactions_tb IN trans_rec_tb_tp
181 -- );
182 --
183 --
184 -- Description
185 -- Pack Unpack Container
186 -- Update subinv/locator
187 -- Create MOL
188 -- Call ATF API
189
190 --PROCEDURE packunpack_complete ( p_transaction_id IN NUMBER);
191 --
192 --
193 --
194 -- Description
195 -- update lpn_id and transfer_lpn_id based on license_plate_number
196 -- and transfer_license_plate_number in RTI .
197
198 --PROCEDURE update_lpn_id ( p_lpn_group_id IN NUMBER);
199 --
200
201
202 -- Procedure validate_lot_Serial_info to validate lot/serial
203 -- information entered on receiving interface.
204 PROCEDURE VALIDATE_LOT_SERIAL_INFO (P_RTI_ID IN NUMBER,
205 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
206 X_MSG_COUNT OUT NOCOPY NUMBER,
207 X_MSG_DATA OUT NOCOPY VARCHAR2);
208
209 TYPE cas_mol_rec_type IS RECORD
210 ( transaction_type VARCHAR2(25)
211 ,organization_id NUMBER
212 ,lpn_id NUMBER
213 ,inventory_item_id NUMBER
214 ,lot_number VARCHAR2(80)
215 ,item_revision VARCHAR2(3)
216 ,from_subinventory_code VARCHAR2(10)
217 ,from_locator_id NUMBER
218 ,cost_group_id NUMBER
219 ,project_id NUMBER
220 ,task_id NUMBER
221 ,uom_code VARCHAR2(3)
222 ,backorder_delivery_detail_id NUMBER
223 ,crossdock_type NUMBER
224 ,inspection_status NUMBER
225 ,quantity NUMBER
226 ,secondary_quantity NUMBER
227 ,secondary_uom VARCHAR2(3)
228 ,transfer_org_id NUMBER
229 ,line_id NUMBER
230 ,primary_qty NUMBER
231 ,primary_uom_code VARCHAR2(3)
232 ,po_header_id NUMBER
233 ,po_line_location_id NUMBER
234 ,shipment_line_id NUMBER
235 ,requisition_line_id NUMBER
236 ,auto_transact_code VARCHAR2(25)
237 ,wip_supply_type NUMBER
238 ,routing_header_id NUMBER
239 ,source_document_code varchar2(25)
240 ,parent_transaction_id NUMBER
241 ,parent_txn_type VARCHAR2(25)
242 ,grand_parent_txn_type VARCHAR2(25)
243 ,call_atf_api NUMBER --used for DELIVER
244 ,mmtt_id NUMBER --used for DELIVER
245 ,asn_line_flag VARCHAR2(1)
246 ,subinventory_code VARCHAR2(10) --xfer sub. Used for deliver
247 ,locator_id NUMBER --xfer sub. Used for deliver
248 );
249
250 TYPE cas_mol_rec_tb_tp IS TABLE OF cas_mol_rec_type
251 INDEX BY BINARY_INTEGER;
252
253 PROCEDURE insert_mtli(p_product_transaction_id IN NUMBER,
254 p_product_code IN VARCHAR2,
255 p_interface_id IN NUMBER,
256 p_org_id IN NUMBER,
257 p_item_id IN NUMBER,
258 p_lot_number IN VARCHAR2,
259 p_transaction_quantity IN NUMBER,
260 p_primary_quantity IN NUMBER,
261 p_serial_interface_id IN NUMBER,
262 x_return_status OUT NOCOPY VARCHAR2,
263 x_msg_count OUT NOCOPY NUMBER,
264 x_msg_data OUT NOCOPY VARCHAR2,
265 p_sec_qty IN NUMBER DEFAULT NULL
266 );
267
268 -- Description
269 /*
270 This processdure validates the LPN to restrict multiple users doing transactions on same LPN
271 Added for the Bug:13613257
272 */
273
274 PROCEDURE Validate_Receiving_LPN(p_lpn_id IN NUMBER,
275 p_routing_id IN NUMBER,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_msg_data OUT NOCOPY VARCHAR2
279 );
280 --14615225
281 PROCEDURE check_over_receipt(p_lpn_id IN NUMBER,
282 p_item_id IN NUMBER,
283 p_organization_id IN NUMBER,
284 p_qty_entered IN NUMBER,
285 p_transaction_uom IN VARCHAR2,
286 x_qty_left OUT NOCOPY VARCHAR2,
287 x_return_status OUT NOCOPY VARCHAR2);
288 --14615225
289
290 END inv_rcv_integration_pvt;
[Home] [Help]