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