1 PACKAGE WSMPINVL AUTHID CURRENT_USER AS
2 /* $Header: WSMINVLS.pls 120.4 2006/03/28 03:50:09 sisankar noship $ */
3
4 USER NUMBER;
5 LOGIN NUMBER;
6 WSMISSUE NUMBER;
7 WSMRECEIPT NUMBER;
8 REQUEST NUMBER;
9 PROGRAM NUMBER;
10 PROGAPPL NUMBER;
11
12 /*BA#IIIP*/
13
14 /* Message type for errors in WSM_INTERFACE_ERRORS table
15 ** Message_type lookup in MFG_LOOKUPS.WIP_ML_ERROR_TYPE.
16 ** 1 - Error, 2 - Warning.
17 */
18 Message_Type_Error Number := 1;
19 Message_Type_Warning Number := 2;
20 /* Global Debug parameter and is set by MRP_DEBUG profile value */
21
22 G_DEBUG varchar2(1) := 'N';
23
24 /*EA#IIIP*/
25
26 /* Transaction Types */
27 SPLIT NUMBER := 1;
28 MERGE NUMBER := 2;
29 TRANSLATE NUMBER := 3;
30 TRANSFER NUMBER := 4;
31
32 /* Modes to call program */
33 ONLINE NUMBER := 1;
34 CONCURRENT NUMBER := 2;
35
36 /* Values for process status */
37 PENDING NUMBER := 1; -- changed from 2 to 1 by Bala Balakumar, Jul22,2000.
38 ERROR NUMBER := 3;
39 COMPLETE NUMBER := 4;
40
41 /* Added for bug fix 4958157 */
42 l_miss_char CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
43 l_miss_date CONSTANT DATE := FND_API.G_MISS_DATE;
44 l_miss_num CONSTANT NUMBER := FND_API.G_MISS_NUM;
45
46 /* This record type is added for maintaining lot attributes in Inv lot transactions */
47 /*
48 Type lot_attributes_rec_type is RECORD
49 (
50 l_mtli_txn_id NUMBER,
51 l_description VARCHAR2(256),
52 l_grade_code VARCHAR2(150),
53 l_origination_date DATE,
54 l_date_code VARCHAR2(150),
55 l_change_date DATE,
56 l_age NUMBER,
57 l_retest_date DATE,
58 l_maturity_date DATE,
59 l_item_size NUMBER,
60 l_color VARCHAR2(150),
61 l_volume NUMBER,
62 l_volume_uom VARCHAR2(3),
63 l_place_of_origin VARCHAR2(150),
64 l_best_by_date DATE,
65 l_length NUMBER,
66 l_length_uom VARCHAR2(3),
67 l_recycled_content NUMBER,
68 l_thickness NUMBER,
69 l_thickness_uom VARCHAR2(3),
70 l_width NUMBER,
71 l_width_uom VARCHAR2(3),
72 l_vendor_id NUMBER,
73 l_vendor_name VARCHAR2(240),
74 l_territory_code VARCHAR2(30),
75 l_supplier_lot_number VARCHAR2(150),
76 l_curl_wrinkle_fold VARCHAR2(150),
77 l_lot_attribute_category VARCHAR2(30),
78 l_attribute_category VARCHAR2(30)
79 ); */ -- commented out to change this based on MTL_LOT_NUMBERS table
80
81 Type lot_attributes_rec_type is RECORD
82 (
83 l_mtli_txn_id NUMBER,
84 l_description MTL_LOT_NUMBERS.DESCRIPTION%TYPE,
85 l_grade_code MTL_LOT_NUMBERS.GRADE_CODE%TYPE,
86 l_origination_date MTL_LOT_NUMBERS.ORIGINATION_DATE%TYPE,
87 l_date_code MTL_LOT_NUMBERS.DATE_CODE%TYPE,
88 l_change_date MTL_LOT_NUMBERS.CHANGE_DATE%TYPE,
89 l_age MTL_LOT_NUMBERS.AGE%TYPE,
90 l_retest_date MTL_LOT_NUMBERS.RETEST_DATE%TYPE,
91 l_maturity_date MTL_LOT_NUMBERS.MATURITY_DATE%TYPE,
92 l_item_size MTL_LOT_NUMBERS.ITEM_SIZE%TYPE,
93 l_color MTL_LOT_NUMBERS.COLOR%TYPE,
94 l_volume MTL_LOT_NUMBERS.VOLUME%TYPE,
95 l_volume_uom MTL_LOT_NUMBERS.VOLUME_UOM%TYPE,
96 l_place_of_origin MTL_LOT_NUMBERS.PLACE_OF_ORIGIN%TYPE,
97 l_best_by_date MTL_LOT_NUMBERS.BEST_BY_DATE%TYPE,
98 l_length MTL_LOT_NUMBERS.LENGTH%TYPE,
99 l_length_uom MTL_LOT_NUMBERS.LENGTH_UOM%TYPE,
100 l_recycled_content MTL_LOT_NUMBERS.RECYCLED_CONTENT%TYPE,
101 l_thickness MTL_LOT_NUMBERS.THICKNESS%TYPE,
102 l_thickness_uom MTL_LOT_NUMBERS.THICKNESS_UOM%TYPE,
103 l_width MTL_LOT_NUMBERS.WIDTH%TYPE,
104 l_width_uom MTL_LOT_NUMBERS.WIDTH_UOM%TYPE,
105 l_vendor_id MTL_LOT_NUMBERS.VENDOR_ID%TYPE,
106 l_vendor_name MTL_LOT_NUMBERS.VENDOR_NAME%TYPE,
107 l_territory_code MTL_LOT_NUMBERS.TERRITORY_CODE%TYPE,
108 l_supplier_lot_number MTL_LOT_NUMBERS.SUPPLIER_LOT_NUMBER%TYPE,
109 l_curl_wrinkle_fold MTL_LOT_NUMBERS.CURL_WRINKLE_FOLD%TYPE,
110 l_lot_attribute_category MTL_LOT_NUMBERS.LOT_ATTRIBUTE_CATEGORY%TYPE,
111 l_attribute_category MTL_LOT_NUMBERS.ATTRIBUTE_CATEGORY%TYPE
112 );
113
114 /* This is the main routing for processing inventory lot transactions
115 You can call the routine to process a group of rows by passing a group
116 id or just one row by passing a transaction id */
117
118 PROCEDURE Process_Interface_rows(
119 errbuf out NOCOPY varchar2,
123 P_Mode IN NUMBER);
120 retcode out NOCOPY number,
121 P_Group_Id IN NUMBER,
122 P_header_Id IN NUMBER,
124
125 PROCEDURE Transact(
126 P_Group_Id IN NUMBER,
127 P_header_Id IN NUMBER,
128 P_Mode IN NUMBER,
129 x_header_id out NOCOPY number,
130 o_err_code out NOCOPY number,
131 o_err_message out NOCOPY varchar2,
132 x_err_cnt out NOCOPY number);
133
134 /* Validates data in tables. Only needs to be called for records that
135 did not come in through the form */
136 --FUNCTION Validate(
137 --P_Transaction_Id IN NUMBER) return BOOLEAN;
138
139 PROCEDURE Validate_Merge(
140 P_header_Id IN NUMBER
141 ,err_status OUT NOCOPY NUMBER
142 ,o_err_message OUT NOCOPY VARCHAR);
143
144 PROCEDURE Validate_HEADER(
145 P_header_Id IN NUMBER,
146 err_status OUT NOCOPY NUMBER ,
147 o_err_message OUT NOCOPY VARCHAR);
148
149 PROCEDURE Validate_parent(
150 P_header_Id IN NUMBER,
151 err_status OUT NOCOPY NUMBER ,
152 o_err_message OUT NOCOPY VARCHAR);
153
154 PROCEDURE Validate_Starting(
155 P_header_Id IN NUMBER,
156 err_status OUT NOCOPY NUMBER ,
157 o_err_message OUT NOCOPY VARCHAR);
158
159 PROCEDURE Validate_resulting(
160 P_header_Id IN NUMBER,
161 err_status OUT NOCOPY NUMBER ,
162 o_err_message OUT NOCOPY VARCHAR);
163 /*
164 FUNCTION Validate_Header(
165 P_Transaction_Id IN NUMBER) return BOOLEAN;
166
167 FUNCTION Validate_Starting(
168 P_Transaction_Id IN NUMBER) return BOOLEAN;
169
170 FUNCTION Validate_Resulting(
171 P_Transaction_Id IN NUMBER) return BOOLEAN;
172 */
173
174 /* Gets a transaction header id from a sequence */
175 FUNCTION Get_Header_Id RETURN NUMBER;
176
177 /* Set packaged variables */
178 PROCEDURE Set_Vars;
179
180 /* Users might do a split in which the resulting quantities add up to
181 less than the starting quantities. In this case, we need an extra
182 resulting record */
183 PROCEDURE Create_Extra_Record(
184 P_header_Id IN NUMBER,
185 x_err_code OUT NOCOPY NUMBER,
186 x_err_msg OUT NOCOPY VARCHAR2 );
187
188 /* This procedure will populate MTL_MATERIAL_TRANSACTIONS_TEMP
189 and LOTS_TEMP */
190 PROCEDURE Create_Mtl_Records(
191 P_Header_id IN NUMBER, -- added by bala.
192 P_Header_Id1 IN NUMBER,
193 P_Transaction_Id IN NUMBER,
194 P_Transaction_Type IN NUMBER,
195 x_err_code OUT NOCOPY NUMBER,
196 x_err_msg OUT NOCOPY VARCHAR2
197 );
198
199 PROCEDURE Misc_Issue
200 (
201 X_Header_Id1 IN NUMBER,
202 X_Inventory_Item_Id IN NUMBER,
203 X_Organization_id IN NUMBER,
204 X_Quantity IN NUMBER,
205 X_Acct_Period_Id IN NUMBER,
206 X_Lot_Number IN VARCHAR2,
207 X_Subinventory IN VARCHAR2,
208 X_Locator_Id IN NUMBER,
209 X_Revision IN VARCHAR2,
210 X_Reason_Id IN NUMBER,
211 X_Reference IN VARCHAR2,
212 X_Transaction_Date IN DATE,
213 X_Source_Line_Id IN NUMBER,
214 X_Header_Id IN NUMBER, -- added by Bala
215 x_err_code OUT NOCOPY NUMBER,
216 x_err_msg OUT NOCOPY VARCHAR2
217 );
218
219 PROCEDURE Misc_Receipt
220 (
221 X_Header_Id1 IN NUMBER,
222 X_Inventory_Item_Id IN NUMBER,
223 X_Organization_id IN NUMBER,
224 X_Quantity IN NUMBER,
225 X_Acct_Period_Id IN NUMBER,
226 X_Lot_Number IN VARCHAR2,
227 X_Subinventory IN VARCHAR2,
228 X_Locator_Id IN NUMBER,
229 X_Revision IN VARCHAR2,
230 X_Reason_Id IN NUMBER,
231 X_Reference IN VARCHAR2,
232 X_Transaction_Date IN DATE,
233 X_Source_Line_Id IN NUMBER,
234 X_Header_Id IN NUMBER, -- added by Bala
235 x_lot_attributes_rec IN lot_attributes_rec_type, -- added by sisankar for bug 4920235
236 x_invattr_tbl IN inv_lot_api_pub.char_tbl,
237 x_Cattr_tbl IN inv_lot_api_pub.char_tbl,
238 x_Dattr_tbl IN inv_lot_api_pub.date_tbl,
239 x_Nattr_tbl IN inv_lot_api_pub.number_tbl,
240 x_err_code OUT NOCOPY NUMBER,
241 x_err_msg OUT NOCOPY VARCHAR2
242 );
243
244 /* Launches the inventory transaction worker to process transactions */
245 FUNCTION Launch_Worker (
246 X_Header_Id1 IN NUMBER,
247 X_Message OUT NOCOPY VARCHAR2
248 ) RETURN BOOLEAN;
249
250 /* Sets records to PROCESS_STATUS = COMPLETE */
251 PROCEDURE Success_All(
252 p_header_id NUMBER,
253 p_group_id NUMBER,
254 x_err_code OUT NOCOPY NUMBER,
255 x_err_msg OUT NOCOPY VARCHAR2,
256 p_mode NUMBER); /*Bug 4779518 fix*/
257
258 /* Sets records to PROCESS_STATUS = ERROR */
259 PROCEDURE Error_All(
260 p_header_id NUMBER,
261 p_group_id NUMBER,
262 p_message VARCHAR2);
263
264
265 /*BA#IIIP*/
266 /* Show progress in the logfile if Debug is ON */
267 PROCEDURE showProgress(
268 processingMode IN NUMBER,
269 headerId NUMBER,
270 procName IN VARCHAR2, -- Bug#1844972
271 procLocation IN NUMBER, -- Bug#1844972
272 showMessage VARCHAR2);
273
274 /* Create a Log for the user from wsm_interface_errors */
275 PROCEDURE writeToLog(
276 requestId NUMBER
277 , programId NUMBER
278 , programApplnId NUMBER
279 );
280 /*EA#IIIP*/
281
282 /*AM Genealogy Integration */
283
284 PROCEDURE enter_genealogy_records ( p_transaction_id NUMBER ,
285 p_transaction_type_id NUMBER,
286 p_header_id NUMBER,
287 p_process_status NUMBER ,
288 err_status OUT NOCOPY NUMBER ,
289 o_err_message OUT NOCOPY VARCHAR );
290
291 /*AM End Genealogy Integration */
292
293
294 END WSMPINVL;
[Home] [Help]