1 PACKAGE INV_TRANSACTIONS AS
2 /* $Header: INVVMWAS.pls 120.1 2005/06/11 07:21:42 appldev $ */
3
4
5
6 --The Header id variable keeps track of the header id of a transaction across several serials
7
8 G_Header_Id NUMBER;
9
10 --Counts number of units being transacted. so we can update total quantity of transaction
11
12 G_Interface_Id NUMBER;
13
14
15 G_Serial_Id NUMBER;
16
17 PROCEDURE LINE_INTERFACE_INSERT( p_Inventory_Item_Id IN NUMBER,
18 p_Item_Revision IN VARCHAR2,
19 p_Organization_Id IN NUMBER,
20 p_Transaction_Source_Id IN NUMBER,
21 p_Transaction_Action_Id IN NUMBER,
22 p_From_Subinventory_Code IN VARCHAR2,
23 p_To_Subinventory_Code IN VARCHAR2,
24 p_From_Locator_Id IN NUMBER,
25 p_To_Locator_Id IN NUMBER,
26 p_To_Organization IN NUMBER,
27 p_Transaction_Type_Id IN NUMBER,
28 p_Transaction_Source_Type_Id IN NUMBER,
29 p_Transaction_Quantity IN NUMBER,
30 p_Transaction_UOM IN VARCHAR2,
31 p_Transaction_Date IN DATE,
32 p_Reason_Id IN NUMBER,
33 p_User_Id IN NUMBER,
34 x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
35 x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
36
37 PROCEDURE LOT_INTERFACE_INSERT(p_Transaction_Quantity IN NUMBER,
38 p_Lot_Number IN VARCHAR2,
39 p_User_Id IN NUMBER,
40 p_serial_number_control_code IN NUMBER);
41
42
43 PROCEDURE SERIAL_INTERFACE_INSERT(p_From_Serial IN VARCHAR2,
44 p_To_Serial IN VARCHAR2,
45 p_User_Id IN NUMBER,
46 p_lot_control_code IN NUMBER);
47
48 PROCEDURE PROCESS(x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
49 x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
50
51
52 PROCEDURE GET_ACCT_ID(x_Transaction_Source_Id OUT NOCOPY /* file.sql.39 change */ NUMBER,
53 p_Transaction_Source_Id_AA IN NUMBER,
54 p_Transaction_Source_Id_Inv IN NUMBER,
55 p_Transaction_Source_Type_Id IN NUMBER);
56
57
58
59
60
61
62 /* We need the following Data type API's to use as wrappers on top of QM
63
64 1) When Serial Entered: if acct txn, Validate serial number, insert serial number into serial interface,
65 and call quantity manager to update the quantities.
66 if not acct txn, validate serial numer, check if From Sub,From Loc have changed.
67 if so insert new row (same header id but different interface id) in lines table,
68 if applicable enter new row in lots table, insert into serial interface table with
69 new header id, call quantity manager.
70 --we can afford to call quantity manager at the end, because an error cannot be made here regarding
71 quantity.
72
73 2) When Lot Number Changed: Call Quantity manager.
74
75 3) When Txn UOM Entered: Insert into MTI (with qty = 0) new record and get new header id, interface id.
76
77 4) When Lot Qty Changed: Call quantity manager. If there are no errors, Check lot number against existing
78 lots with given header id. If lot number exists then update existing row with
79 new quantity, otherwise validate lot number and insert new lot row with
80 existing header id.
81
82 5) When Quantity Changed: Call quantity manager. If there are no errors, Update the row with the correct
83 header and interface ids with the new quantity.
84
85 6) When SUBMIT Pressed: call process procedure with appropriate header and interface ids.
86
87
88 */
89
90
91
92
93
94 /*
95 PROCEDURE Validate_Serial_Trigger(x_Message OUT VARCHAR2,
96 x_Status OUT VARCHAR2,
97 p_Transaction_Quantity IN NUMBER,
98 p_Lot_Control_Code IN NUMBER,
99 p_From_Serial IN VARCHAR2,
100 p_Current_Organization IN NUMBER,
101 p_Inventory_Item_Id IN NUMBER,
102 p_User_Id IN NUMBER);
103
104 */
105
106 PROCEDURE SUBMIT_PRESSED(x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
107 x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
108 p_Transaction_Action_Id IN NUMBER,
109 p_To_Sub IN VARCHAR2,
110 p_To_Loc_Id IN NUMBER);
111
112
113
114
115 PROCEDURE UOM_ENTERED(x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
116 x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
117 p_Inventory_Item_Id IN NUMBER,
118 p_Item_Revision IN VARCHAR2,
119 p_Organization_Id IN NUMBER,
120 p_Transaction_Source_Id_AA IN NUMBER,
121 p_Transaction_Source_Id_Inv IN NUMBER,
122 p_Transaction_Action_Id IN NUMBER,
123 p_From_Subinventory_Code IN VARCHAR2,
124 p_To_Subinventory_Code IN VARCHAR2,
125 p_From_Locator_Id IN NUMBER,
126 p_To_Locator_Id IN NUMBER,
127 p_Transfer_Organization IN NUMBER,
128 p_Transaction_Type_Id IN NUMBER,
129 p_Transaction_Source_Type_Id IN NUMBER,
130 p_Transaction_UOM IN VARCHAR2,
131 p_Transaction_Date IN DATE,
132 p_Reason_Id IN NUMBER,
133 p_User_Id IN NUMBER);
134
135
136 PROCEDURE TXN_QTY_CHANGED(p_txn_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
137 p_txn_uom_code IN VARCHAR2,
138 p_prev_txn_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
139 p_serial_number_control_code IN NUMBER,
140 p_lot_control_code IN NUMBER,
141 p_primary_uom_code IN NUMBER,
142 p_organization_id IN NUMBER,
143 p_inventory_item_id IN NUMBER,
144 p_total_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
145 p_total_serial_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
146 p_transaction_action_id IN NUMBER,
147 x_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
148 x_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
149 x_message OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
150
151
152 PROCEDURE LOT_QTY_CHANGED(p_Lot_Number IN VARCHAR2,
153 p_txn_qty IN NUMBER,
154 p_txn_uom_code IN VARCHAR2,
155 p_inventory_item_id IN NUMBER,
156 p_lot_control_code IN NUMBER,
157 p_serial_control_code IN NUMBER,
158 p_current_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
159 p_prev_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
160 p_total_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
161 p_total_serial_qty IN NUMBER,
162 x_done IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
163 x_lot_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
164 x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
165 x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
166 p_Organization_Id IN NUMBER);
167
168 PROCEDURE LOT_CHANGED( p_total_serial_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
169 p_serial_number_control_code IN NUMBER,
170 p_Lot_Number IN VARCHAR2,
171 p_User_Id IN NUMBER);
172
173
174 PROCEDURE SERIAL_ENTERED( x_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
175 x_lot_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
176 x_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
177 x_Message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
178 p_txn_qty IN NUMBER,
179 p_txn_uom_code IN VARCHAR2,
180 p_Organization_Id IN NUMBER,
181 p_inventory_item_id IN NUMBER,
182 p_current_lot_qty IN NUMBER,
183 p_lot_control_code IN NUMBER,
184 p_serial_control_code IN NUMBER,
185 p_from_serial IN VARCHAR2,
186 p_to_serial IN VARCHAR2,
187 p_total_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
188 p_total_serial_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
189 p_transaction_action_id IN NUMBER,
190 p_User_Id IN NUMBER);
191
192
193 END Inv_Transactions;