1 PACKAGE WMS_PACKING_WORKBENCH_PVT AS
2 /* $Header: WMSPACVS.pls 120.2.12010000.1 2008/07/28 18:35:30 appldev ship $ */
3
4 /*******************************
5 Variable Types
6 ********************************/
7
8 TYPE kit_rec_type IS RECORD(
9 kit_item_id NUMBER
10 , top_model_line_id NUMBER
11 , exist_flag VARCHAR2(1)
12 , identified_flag VARCHAR2(1)
13 );
14 TYPE kit_tbl_type IS TABLE OF kit_rec_type INDEX BY BINARY_INTEGER;
15
16 TYPE kit_component_rec_type IS RECORD(
17 kit_item_id NUMBER
18 , component_item_id NUMBER
19 , packed_qty NUMBER
20 , packed_qty_disp VARCHAR2(20)
21 );
22 TYPE kit_component_tbl_type IS TABLE OF kit_component_rec_type INDEX BY BINARY_INTEGER;
23
24 TYPE move_order_rec_type is RECORD
25 (
26 move_order_line_id NUMBER
27 , transaction_quantity NUMBER
28 , transaction_uom VARCHAR2(3)
29 , primary_quantity NUMBER
30 , secondary_transaction_quantity NUMBER --INVCONV kkillams
31 , secondary_uom_code VARCHAR2(3) --INVCONV kkillams
32 , grade_code VARCHAR2(150) --INVCONV kkillams
33 );
34 TYPE move_order_tbl_type IS TABLE OF move_order_rec_type INDEX BY BINARY_INTEGER;
35 l_null_mol_list move_order_tbl_type;
36
37 TYPE mmtt_mtlt_rec_type IS RECORD
38 (
39 move_order_line_id NUMBER
40 , inventory_item_id NUMBER
41 , revision VARCHAR2(3)
42 , transaction_quantity NUMBER
43 , transaction_uom VARCHAR2(3)
44 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
45 , lot_number VARCHAR2(80)
46 , serial_transaction_temp_id NUMBER
47 , secondary_uom_code VARCHAR2(3) --INVCONV kkillams
48 , secondary_transaction_quantity NUMBER --INVCONV kkillams
49
50 );
51 l_null_rec mmtt_mtlt_rec_type;
52
53 /*********************************
54 Procedure to query the eligible material for pack/split/unpack transactions
55 For inbound, it queries move order lines
56 For outbound, it queries delivery detail lines
57 After it finds results, it populates global temp table
58 WMS_PACKING_MATERIAL_GTEMP to display on the spreadtable on packing workbench form
59
60 Input Parameter:
61 p_source_id: 1=>Inbound, 2=>Outbound
62
63 The following input parameters applies for both inbound and outbound
64 p_organization_id: Organization
65 p_subinventory_code: Subinventory
66 p_locator_id: ID for Locator
67 p_inventory_item_id: ID for Item
68 p_from_lpn_id: ID for From LPN
69 p_project_id: ID for Project
70 p_task_id: ID for Task
71
72 The following parameters applies for inbound
73 p_document_type: 'ASN', 'INTSHIP', 'PO', 'REQ', 'RMA'
74 p_document_id: ID for inbound document
75 p_document_line_id: ID for inbound document line
76 p_receipt_number: Receipt number
77 p_partner_id: it can be vendor_id or internal org_id
78 p_partner_type: 1=> Vendor, 2=> Internal Organization
79 p_rcv_location_id: ID for receiving location
80
81 The following parameters applies for outbound
82 p_delivery_id: ID for delivery
83 p_order_header_id: ID for sales order header
84 p_carrier_id: ID for carrier
85 p_trip_id: ID for Trip
86 p_delivery_state: 'Y'=> Deliveries that are completed packed
87 'N"=> Deliveries that are not completed packed
88 NULL=> all deliveries
89 p_customer_id: ID for customer
90 *********************************/
91 PROCEDURE query_eligible_material(
92 x_return_status OUT NOCOPY VARCHAR2
93 , p_source_id IN NUMBER
94 , p_organization_id IN NUMBER
95 , p_organization_code IN VARCHAR2
96 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
97 , p_locator_id IN NUMBER DEFAULT NULL
98 , p_locator IN VARCHAR2 DEFAULT NULL
99 , p_inventory_item_id IN NUMBER DEFAULT NULL
100 , p_item IN VARCHAR2 DEFAULT NULL
101 , p_from_lpn_id IN NUMBER DEFAULT NULL
102 , p_project_id IN NUMBER DEFAULT NULL
103 , p_project IN VARCHAR2 DEFAULT NULL
104 , p_task_id IN NUMBER DEFAULT NULL
105 , p_task IN VARCHAR2 DEFAULT NULL
106 , p_document_type IN VARCHAR2 DEFAULT NULL
107 , p_document_id IN NUMBER DEFAULT NULL
108 , p_document_number IN VARCHAR2 DEFAULT NULL
109 , p_document_line_id IN NUMBER DEFAULT NULL
110 , p_document_line_num IN NUMBER DEFAULT NULL
111 , p_receipt_number IN VARCHAR2 DEFAULT NULL
112 , p_partner_id IN NUMBER DEFAULT NULL
113 , p_partner_type IN NUMBER DEFAULT NULL
114 , p_partner_name IN VARCHAR2 DEFAULT NULL
115 , p_rcv_location_id IN NUMBER DEFAULT NULL
116 , p_rcv_location IN VARCHAR2 DEFAULT NULL
117 , p_delivery_id IN NUMBER DEFAULT NULL
118 , p_delivery IN VARCHAR2 DEFAULT NULL
119 , p_order_header_id IN NUMBER DEFAULT NULL
120 , p_order_number IN VARCHAR2 DEFAULT NULL
121 , p_order_type IN VARCHAR2 DEFAULT NULL
122 , p_carrier_id IN NUMBER DEFAULT NULL
123 , p_carrier IN VARCHAR2 DEFAULT NULL
124 , p_trip_id IN NUMBER DEFAULT NULL
125 , p_trip IN VARCHAR2 DEFAULT NULL
126 , p_delivery_state IN VARCHAR2 DEFAULT NULL
127 , p_customer_id IN NUMBER DEFAULT NULL
128 , p_customer IN VARCHAR2 DEFAULT NULL
129 , p_is_pjm_enabled_org IN VARCHAR2 DEFAULT 'N'
130 , x_source_unique OUT nocopy VARCHAR2
131 );
132
133
134 /*******************************************
135 * Procedure to create MMTT/MTLT/MSNT record
136 * For a pack/split/unpack transaction
137 *******************************************/
138 PROCEDURE create_txn(
139 x_return_status OUT NOCOPY VARCHAR2
140 , x_proc_msg OUT NOCOPY VARCHAR2
141 , p_source IN NUMBER
142 , p_pack_process IN NUMBER
143 , p_organization_id IN NUMBER
144 , p_inventory_item_id IN NUMBER DEFAULT NULL
145 , p_primary_uom IN VARCHAR2 DEFAULT NULL
146 , p_revision IN VARCHAR2 DEFAULT NULL
147 , p_lot_number IN VARCHAR2 DEFAULT NULL
148 , p_lot_expiration_date IN DATE DEFAULT NULL
149 , p_fm_serial_number IN VARCHAR2 DEFAULT NULL
150 , p_to_serial_number IN VARCHAR2 DEFAULT NULL
151 , p_from_lpn_id IN NUMBER DEFAULT NULL
152 , p_content_lpn_id IN NUMBER DEFAULT NULL
153 , p_to_lpn_id IN NUMBER DEFAULT NULL
154 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
155 , p_locator_id IN NUMBER DEFAULT NULL
156 , p_to_subinventory IN VARCHAR2 DEFAULT NULL
157 , p_to_locator_id IN NUMBER DEFAULT NULL
158 , p_project_id IN NUMBER DEFAULT NULL
159 , p_task_id IN NUMBER DEFAULT NULL
160 , p_transaction_qty IN NUMBER DEFAULT NULL
161 , p_transaction_uom IN VARCHAR2 DEFAULT NULL
162 , p_primary_qty IN NUMBER DEFAULT NULL
163 , p_secondary_qty IN NUMBER DEFAULT NULL
164 , p_secondary_uom IN VARCHAR2 DEFAULT NULL
165 , p_transaction_header_id IN NUMBER DEFAULT NULL
166 , p_transaction_temp_id IN NUMBER DEFAULT NULL
167 , x_transaction_header_id OUT NOCOPY NUMBER
168 , x_transaction_temp_id OUT NOCOPY NUMBER
169 , x_serial_transaction_temp_id OUT NOCOPY NUMBER
170 , p_grade_code IN VARCHAR2 DEFAULT NULL --INVCONV kkillams
171 );
172
173 /*******************************************
174 * Procedure to delete MMTT/MTLT/MSNT record
175 * For a pack/split/unpack transaction
176 * This is used when user choose to do a UNDO
177 *******************************************/
178 PROCEDURE delete_txn(
179 x_return_status OUT NOCOPY VARCHAR2
180 , x_msg_count OUT NOCOPY NUMBER
181 , x_msg_data OUT NOCOPY VARCHAR2
182 , p_transaction_header_id IN NUMBER
183 , p_transaction_temp_id IN NUMBER
184 , p_lot_number IN VARCHAR2 DEFAULT NULL
185 , p_serial_number IN VARCHAR2 DEFAULT NULL
186 , p_quantity IN NUMBER DEFAULT NULL
187 , p_uom IN VARCHAR2 DEFAULT NULL
188 );
189
190 /*******************************************
191 * Procedure to call transaction manager
192 * to process the MMTT records
193 * This is used when user close a LPN
194 *******************************************/
195 PROCEDURE process_txn(
196 p_source IN NUMBER
197 , p_trx_hdr_id IN NUMBER
198 , x_return_status OUT NOCOPY VARCHAR2
199 , x_proc_msg OUT NOCOPY VARCHAR2);
200
201 /*******************************************************
202 * Procedure to Firm Delivery for delivery merge purpose
203 *******************************************************/
204 PROCEDURE firm_delivery(
205 p_delivery_id IN NUMBER
206 , x_return_status OUT NOCOPY VARCHAR2
207 , x_proc_msg OUT NOCOPY VARCHAR2);
208
209 /**************************************
210 * Procedure to get kitting information
211 * for a item and quantity
212 **************************************/
213 PROCEDURE get_kitting_info(
214 x_return_status OUT NOCOPY VARCHAR2
215 , x_msg_data OUT NOCOPY VARCHAR2
216 , x_msg_count OUT NOCOPY VARCHAR2
217 , p_organization_id IN NUMBER
218 , p_inventory_item_id IN NUMBER
219 , p_quantity IN NUMBER);
220
221 /*************************************
222 * Function to indicate whether the kit
223 * has been identified as the current kit for packing
224 ************************************/
225 FUNCTION is_kit_identified(p_kit_id IN NUMBER) RETURN VARCHAR2;
226
227
228 /*************************************
229 * Function to indicate whether the item
230 * is unique across all the kits in the list of kits scanned so far
231 ************************************/
232
233 FUNCTION is_item_unique_existing_kit(p_component_id IN NUMBER) RETURN NUMBER;
234
235 /**********************************
236 * Procedure to issue savepoint
237 * This is called from the form/library
238 * The savepoint can be set currently are
239 * PACK_START
240 * BEFORE_TM
241 **************************************/
242 PROCEDURE issue_savepoint(p_savepoint VARCHAR2);
243 /**********************************
244 * Procedure to issue rollback to savepoint
245 * This is called from the form/library
246 * The savepoint can be rollback currently are
247 * PACK_START
248 * BEFORE_TM
249 * NULL : Rollback everything
250 **************************************/
251 PROCEDURE issue_rollback(p_savepoint VARCHAR2);
252 /**********************************
253 * Procedure to issue commit
254 * This is called from the form/library
255 **************************************/
256 PROCEDURE issue_commit;
257 /***********************************
258 * Global Variables *
259 **********************************/
260 /* Organization ID */
261 ORG_ID NUMBER;
262
263 /* For debug profile */
264 l_debug NUMBER := 0;
265
266
267
268 END WMS_PACKING_WORKBENCH_PVT;
[Home] [Help]