1 PACKAGE msc_sce_loads_pkg AS
2 /* $Header: MSCXLDS.pls 120.3 2007/07/04 10:33:27 vsiyer noship $ */
3
4 /*Constants*/
5 G_NULL_STRING CONSTANT VARCHAR2(10) := '-234567';
6 G_PLAN_ID CONSTANT NUMBER := -1;
7 G_SR_INSTANCE_ID CONSTANT NUMBER := -1;
8 G_OPERATOR CONSTANT NUMBER := 1;
9 G_BATCH_SIZE CONSTANT NUMBER := 500;
10 G_UOM CONSTANT VARCHAR2(3) := 'Ea';
11
12 /* Order types */
13 G_SALES_FORECAST CONSTANT NUMBER := 1;
14 G_SUPPLY_SCHEDULE CONSTANT NUMBER := 2;
15 G_ORDER_FORECAST CONSTANT NUMBER := 2;
16 G_SUPPLY_COMMIT CONSTANT NUMBER := 3;
17 G_ALLOCATED_SUPPLY CONSTANT NUMBER := 3;
18 G_HIST_SALES CONSTANT NUMBER := 4;
19 G_SELL_THRO_FCST CONSTANT NUMBER := 5;
20 G_SUPPLIER_CAP CONSTANT NUMBER := 6;
21 G_SAFETY_STOCK CONSTANT NUMBER := 7;
22 G_PROJ_SS CONSTANT NUMBER := 8;
23 G_ALLOC_ONHAND CONSTANT NUMBER := 9;
24 G_UNALLOCATED_ONHAND CONSTANT NUMBER := 10;
25 G_PROJ_UNALLOC_AVAIL CONSTANT NUMBER := 11;
26 G_PROJ_ALLOC_AVAIL CONSTANT NUMBER := 12;
27 G_PURCHASE_ORDER CONSTANT NUMBER := 13;
28 G_SALES_ORDER CONSTANT NUMBER := 14;
29 G_ASN CONSTANT NUMBER := 15;
30 G_SHIP_RECEIPT CONSTANT NUMBER := 16;
31 G_INTRANSIT CONSTANT NUMBER := 17;
32 G_WORK_ORDER CONSTANT NUMBER := 45;
33 G_REPLENISHMENT CONSTANT NUMBER := 19;
34 G_REQUISITION CONSTANT NUMBER := 20;
35 G_POA CONSTANT NUMBER := 21;
36 g_proj_avai_bal CONSTANT NUMBER := 27;
37 G_CONS_ADVICE CONSTANT NUMBER := 28;
38
39 G_PO_ACKNOWLEDGEMENT CONSTANT NUMBER := 21;
40 G_NEGOTIATED_CAPACITY CONSTANT NUMBER := 6;
41
42 /* Bucket type */
43 G_DAY CONSTANT NUMBER := 1;
44 G_WEEK CONSTANT NUMBER := 2;
45 G_MONTH CONSTANT NUMBER := 3;
46
47 /* Row statuses */
48 G_HEADER_FAILURE CONSTANT NUMBER := 1;
49 G_PROCESS CONSTANT NUMBER := 2;
50 G_SUCCESS CONSTANT NUMBER := 3;
51 G_FAILURE CONSTANT NUMBER := 4;
52 G_DELETED CONSTANT NUMBER := -99;
53
54 /* Serial_Number_Control_code */
55 G_SERIAL_ITEM CONSTANT NUMBER := 1;
56
57 /* PL/SQL table types */
58 TYPE headeridList IS TABLE OF msc_supdem_lines_interface.parent_header_id%TYPE;
59 TYPE lineidList IS TABLE OF msc_supdem_lines_interface.line_id%TYPE;
60 TYPE publisherList IS TABLE OF msc_companies.company_name%TYPE;
61 TYPE publishidList IS TABLE OF msc_companies.company_id%TYPE;
62 TYPE pubsiteList IS TABLE OF msc_company_sites.company_site_name%TYPE;
63 TYPE pubsiteidList IS TABLE OF msc_company_sites.company_site_id%TYPE;
64 TYPE pubaddrList IS TABLE OF msc_sup_dem_entries.publisher_address%TYPE;
65 TYPE customerList IS TABLE OF msc_companies.company_name%TYPE;
66 TYPE custidList IS TABLE OF msc_companies.company_id%TYPE;
67 TYPE custsiteList IS TABLE OF msc_company_sites.company_site_name%TYPE;
68 TYPE custsiteidList IS TABLE OF msc_company_sites.company_site_id%TYPE;
69 TYPE custaddrList IS TABLE OF msc_sup_dem_entries.customer_address%TYPE;
70 TYPE supplierList IS TABLE OF msc_companies.company_name%TYPE;
71 TYPE suppidList IS TABLE OF msc_companies.company_id%TYPE;
72 TYPE suppsiteList IS TABLE OF msc_company_sites.company_site_name%TYPE;
73 TYPE suppsiteidList IS TABLE OF msc_company_sites.company_site_id%TYPE;
74 TYPE suppaddrList IS TABLE OF msc_sup_dem_entries.supplier_address%TYPE;
75 TYPE shipfromList IS TABLE OF msc_companies.company_name%TYPE;
76 TYPE shipfromidList IS TABLE OF msc_companies.company_id%TYPE;
77 TYPE shipfromsiteList IS TABLE OF msc_company_sites.company_site_name%TYPE;
78 TYPE shipfromsidList IS TABLE OF msc_company_sites.company_site_id%TYPE;
79 TYPE shipfromaddrList IS TABLE OF msc_sup_dem_entries.ship_from_address%TYPE;
80 TYPE shiptoList IS TABLE OF msc_companies.company_name%TYPE;
81 TYPE shiptoidList IS TABLE OF msc_companies.company_id%TYPE;
82 TYPE shiptositeList IS TABLE OF msc_company_sites.company_site_name%TYPE;
83 TYPE shiptosidList IS TABLE OF msc_company_sites.company_site_id%TYPE;
84 TYPE shiptopaddrList IS TABLE OF msc_sup_dem_entries.ship_to_address%TYPE;
85 TYPE shiptoaddrList IS TABLE OF msc_supdem_lines_interface.ship_to_address%TYPE;
86 TYPE endordpubList IS TABLE OF msc_companies.company_name%TYPE;
87 TYPE endordpubidList IS TABLE OF msc_companies.company_id%TYPE;
88 TYPE endordpubsiteList IS TABLE OF msc_company_sites.company_site_name%TYPE;
89 TYPE endordpubsidList IS TABLE OF msc_company_sites.company_site_id%TYPE;
90 TYPE ordertypeList IS TABLE OF fnd_lookup_values.lookup_code%TYPE;
91 TYPE otdescList IS TABLE OF fnd_lookup_values.meaning%TYPE;
92 TYPE endordertypeList IS TABLE OF fnd_lookup_values.lookup_code%TYPE;
93 TYPE endotdescList IS TABLE OF fnd_lookup_values.meaning%TYPE;
94 TYPE bktypedescList IS TABLE OF fnd_lookup_values.meaning%TYPE;
95 TYPE bktypeList IS TABLE OF fnd_lookup_values.lookup_code%TYPE;
96 TYPE itemList IS TABLE OF msc_system_items.item_name%TYPE;
97 TYPE itemidList IS TABLE OF msc_system_items.inventory_item_id%TYPE;
98 TYPE itemdescList IS TABLE OF msc_system_items.description%TYPE;
99 TYPE categoryList IS TABLE OF msc_system_items.category_name%TYPE;
100 TYPE ordernumList IS TABLE OF msc_supdem_lines_interface.order_identifier%TYPE;
101 TYPE linenumList IS TABLE OF msc_supdem_lines_interface.line_number%TYPE;
102 TYPE relnumList IS TABLE OF msc_supdem_lines_interface.release_number%TYPE;
103 TYPE endordList IS TABLE OF msc_supdem_lines_interface.pegging_order_identifier%TYPE;
104 TYPE endlineList IS TABLE OF msc_supdem_lines_interface.ref_line_number%TYPE;
105 TYPE endrelList IS TABLE OF msc_supdem_lines_interface.ref_release_number%TYPE;
106 TYPE keydateList IS TABLE OF msc_sup_dem_entries.key_date%TYPE;
107 TYPE newschedList IS TABLE OF msc_sup_dem_entries.new_schedule_date%TYPE;
108 TYPE shipdateList IS TABLE OF msc_sup_dem_entries.ship_date%TYPE;
109 TYPE receiptdateList IS TABLE OF msc_sup_dem_entries.receipt_date%TYPE;
110 TYPE newordplaceList IS TABLE OF msc_sup_dem_entries.new_order_placement_date%TYPE;
111 TYPE origpromList IS TABLE OF msc_sup_dem_entries.original_promised_date%TYPE;
112 TYPE reqdateList IS TABLE OF msc_sup_dem_entries.request_date%TYPE;
113 TYPE wipstdatelist IS TABLE OF msc_sup_dem_entries.wip_start_date%TYPE;
114 TYPE wipenddatelist IS TABLE OF msc_sup_dem_entries.wip_end_date%TYPE;
115 TYPE qtyList IS TABLE OF msc_supdem_lines_interface.quantity%TYPE;
116 TYPE uomList IS TABLE OF msc_sup_dem_entries.uom_code%TYPE;
117 TYPE commentList IS TABLE OF msc_supdem_lines_interface.comments%TYPE;
118 TYPE carrierList IS TABLE OF msc_supdem_lines_interface.carrier_code%TYPE;
119 TYPE billofladList IS TABLE OF msc_supdem_lines_interface.bill_of_lading_number%TYPE;
120 TYPE trackingList IS TABLE OF msc_supdem_lines_interface.tracking_number%TYPE;
121 TYPE vehicleList IS TABLE OF msc_supdem_lines_interface.vehicle_number%TYPE;
122 TYPE containerList IS TABLE OF msc_supdem_lines_interface.container_type%TYPE;
123 TYPE contqtyList IS TABLE OF msc_supdem_lines_interface.container_qty%TYPE;
124 TYPE serialnumList IS TABLE OF msc_supdem_lines_interface.serial_number%TYPE;
125 TYPE attachurlList IS TABLE OF msc_supdem_lines_interface.attachment_url%TYPE;
126 TYPE errmsgList IS TABLE OF msc_supdem_lines_interface.err_msg%TYPE;
127 TYPE versionList IS TABLE OF msc_supdem_lines_interface.version%TYPE;
128 TYPE designatorList IS TABLE OF msc_supdem_lines_interface.designator%TYPE;
129 TYPE contextList IS TABLE OF msc_supdem_lines_interface.context%TYPE;
130 TYPE attributeList IS TABLE OF msc_supdem_lines_interface.attribute1%TYPE;
131 TYPE postingpartyList IS TABLE OF msc_supdem_lines_interface.posting_party_name%TYPE;
132 TYPE lastupdatedateList IS TABLE OF msc_supdem_lines_interface.last_update_date%TYPE; -- Bug # 5599903
133 TYPE lastupdatedbyList IS TABLE OF msc_supdem_lines_interface.last_updated_by%TYPE;
134 TYPE syncList IS TABLE OF msc_supdem_lines_interface.sync_indicator%TYPE; --Fix for bug 6147298
135 TYPE delqtyList IS TABLE OF msc_supdem_lines_interface.quantity%TYPE; --Fix for bug 6147298
136 TYPE numList IS TABLE OF NUMBER;
137 TYPE usernameList IS TABLE OF fnd_user.user_name%TYPE;
138 TYPE eventkeyList IS TABLE OF VARCHAR2(30);
139 TYPE serialTxnId IS TABLE OF msc_st_serial_numbers.serial_txn_id%TYPE;
140 TYPE serialNumber IS TABLE OF msc_st_serial_numbers.serial_number%TYPE;
141 TYPE attachmentUrl IS TABLE OF msc_st_serial_numbers.attachment_url%TYPE;
142 TYPE userDefined IS TABLE OF msc_st_serial_numbers.user_defined1%TYPE;
143 TYPE creationDate IS TABLE OF msc_st_serial_numbers.creation_date%TYPE;
144 TYPE createdBy IS TABLE OF msc_st_serial_numbers.created_by%TYPE;
145 TYPE lastUpdateDate IS TABLE OF msc_st_serial_numbers.last_update_date%TYPE;
146 TYPE lastUpdatedBy IS TABLE OF msc_st_serial_numbers.last_updated_by%TYPE;
147 TYPE lastUpdateLogin IS TABLE OF msc_st_serial_numbers.last_update_login%TYPE;
148 TYPE context IS TABLE OF msc_st_serial_numbers.context%TYPE;
149 TYPE attribute IS TABLE OF msc_st_serial_numbers.attribute1%TYPE;
150 TYPE syncIndicator IS TABLE OF msc_st_serial_numbers.sync_indicator%TYPE;
151 TYPE rowStatus IS TABLE OF msc_st_serial_numbers.row_status%TYPE;
152 TYPE errMsg IS TABLE OF msc_st_serial_numbers.err_msg%TYPE;
153 TYPE planId IS TABLE OF msc_serial_numbers.plan_id%TYPE;
154 TYPE disableDate IS TABLE OF msc_serial_numbers.disable_date%TYPE;
155 TYPE rowidList IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
156 TYPE serialLineId IS TABLE OF msc_st_serial_numbers.line_id%TYPE;
157 TYPE serialErrMsg IS TABLE OF msc_st_serial_numbers.err_msg%TYPE;
158 TYPE serialOrderType IS TABLE OF msc_st_serial_numbers.order_type%TYPE;
159 TYPE transactionIdList IS TABLE OF msc_sup_dem_entries.transaction_id%TYPE;
160 TYPE shipCtrlList IS TABLE OF msc_sup_dem_entries.shipping_control%TYPE;
161 TYPE plannerCode IS TABLE OF msc_sup_dem_entries.planner_code%TYPE;--Bug 4424426
162
163
164 PROCEDURE get_user_id(
165 p_int_control_number IN NUMBER,
166 p_user_id OUT NOCOPY NUMBER
167 );
168
169 PROCEDURE update_errors (
170 p_header_id IN NUMBER,
171 p_language IN VARCHAR2,
172 p_build_err IN NUMBER,
173 p_date_format IN VARCHAR2
174 , p_consumption_advice_exists OUT NOCOPY BOOLEAN -- bug 3551850
175 );
176
177 PROCEDURE send_ntf (
178 p_header_id IN NUMBER,
179 p_file_name IN VARCHAR2,
180 p_status IN NUMBER,
181 p_user_name IN VARCHAR2,
182 p_event_key IN VARCHAR2
183 );
184
185 FUNCTION get_message (
186 p_app IN VARCHAR2,
187 p_name IN VARCHAR2,
188 p_lang IN VARCHAR2
189 ) RETURN VARCHAR2;
190
191 FUNCTION build_error_string (
192 p_header_id IN NUMBER,
193 p_lang IN VARCHAR2
194 ) RETURN VARCHAR2;
195
196
197
198 --=====================================================================
199 -- This procedure is called by the Flat file UI, XML Map and
200 -- Manual Order Entry UI. The input and output parameters are
201 -- explained below:
202 --
203 -- p_header_id => MSC_SUPDEM_HDRS_INTERFACE.HEADER_ID
204 -- p_build_err => 1 when the API is called by the FF-UI and XML
205 -- 2 when the API is called by the manual order entry UI
206 --
207 -- The OUT parameters are populated only when the API is called by
208 -- the manual order entry UI
209 --
210 -- p_status => 0 if the manual order entry was successful
211 -- 1 if the manual order entry fails
212 -- p_err_msg => Contains the the error message string
213 --
214 --======================================================================
215
216 PROCEDURE validate (
217
218 p_err_msg OUT NOCOPY VARCHAR2,
219 p_status OUT NOCOPY NUMBER,
220 p_header_id IN NUMBER,
221 p_build_err IN NUMBER
222 );
223
224 FUNCTION checkdates (
225 p_header_id IN NUMBER,
226 p_line_id IN NUMBER,
227 p_date_format IN VARCHAR2
228 ) RETURN NUMBER;
229
230 PROCEDURE get_optional_info (
231 p_header_id IN Number,
232 p_language_code IN Varchar2,
233 t_line_id IN lineidList,
234 t_end_order_pub IN endordpubList,
235 t_end_ord_pub_site IN endordpubsiteList,
236 t_shipfrom IN shipfromList,
237 t_shipfrom_site IN shipfromsiteList,
238 t_shipto IN shiptoList,
239 t_shipto_site IN shiptositeList,
240 t_end_ot_desc IN endotdescList,
241 t_posting_party_name IN postingpartyList,
242 t_cust_id IN OUT NOCOPY custidList,
243 t_cust_site_id IN OUT NOCOPY custsiteidList,
244 t_supp_id IN suppidList,
245 t_supp_site_id IN suppsiteidList,
246 t_item_id IN itemidList,
247 t_order_type IN ordertypeList,
248 t_ship_date IN OUT NOCOPY shipdateList,
249 t_receipt_date IN OUT NOCOPY receiptdateList,
250 t_end_order_type IN OUT NOCOPY endordertypeList,
251 t_end_ord_pub_id IN OUT NOCOPY endordpubidList,
252 t_end_ord_pub_site_id IN OUT NOCOPY endordpubsidList,
253 t_shipfrom_id IN OUT NOCOPY shipfromidList,
254 t_shipfrom_site_id IN OUT NOCOPY shipfromsidList,
255 t_shipto_id IN OUT NOCOPY shiptoidList,
256 t_shipto_site_id IN OUT NOCOPY shiptosidList,
257 t_posting_party_id IN OUT NOCOPY numList,
258 t_cust IN OUT NOCOPY customerList,
259 t_cust_site IN OUT NOCOPY custsiteList,
260 t_key_date IN OUT NOCOPY keydateList
261 );
262
263 PROCEDURE replace_supdem_entries (
264 p_header_id IN Number,
265 t_line_id IN lineidList,
266 t_pub IN publisherList,
267 t_pub_id IN publishidList,
268 t_pub_site IN pubsiteList,
269 t_pub_site_id IN pubsiteidList,
270 t_pub_addr IN pubaddrList,
271 t_cust IN customerList,
272 t_cust_id IN custidList,
273 t_cust_site IN custsiteList,
274 t_cust_site_id IN custsiteidList,
275 t_cust_addr IN custaddrList,
276 t_supp IN supplierList,
277 t_supp_id IN suppidList,
278 t_supp_site IN suppsiteList,
279 t_supp_site_id IN suppsiteidList,
280 t_supp_addr IN suppaddrList,
281 t_shipfrom IN shipfromList,
282 t_shipfrom_id IN shipfromidList,
283 t_shipfrom_site IN shipfromsiteList,
284 t_shipfrom_site_id IN shipfromsidList,
285 t_shipfrom_addr IN shipfromaddrList,
286 t_shipto IN shiptoList,
290 t_shipto_party_addr IN shiptopaddrList,
287 t_shipto_id IN shiptoidList,
288 t_shipto_site IN shiptositeList,
289 t_shipto_site_id IN shiptosidList,
291 t_shipto_addr IN shiptoaddrList,
292 t_end_order_pub IN endordpubList,
293 t_end_ord_pub_id IN endordpubidList,
294 t_end_ord_pub_site IN endordpubsiteList,
295 t_end_ord_pub_site_id IN endordpubsidList,
296 t_order_type IN ordertypeList,
297 t_ot_desc IN otdescList,
298 t_end_order_type IN endordertypeList,
299 t_end_ot_desc IN endotdescList,
300 t_bkt_type_desc IN bktypedescList,
301 t_bkt_type IN bktypeList,
302 t_item_id IN itemidList,
303 t_ord_num IN ordernumList,
304 t_line_num IN linenumList,
305 t_rel_num IN relnumList,
306 t_end_ord IN endordList,
307 t_end_line IN endlineList,
308 t_end_rel IN endrelList,
309 t_key_date IN keydateList,
310 t_new_sched_date IN newschedList,
311 t_ship_date IN shipdateList,
312 t_receipt_date IN receiptdateList,
313 t_new_ord_plac_date IN newordplaceList,
314 t_orig_prom_date IN origpromList,
315 t_req_date IN reqdateList,
316 /* Added for work order support */
317 t_wip_st_date IN wipstdatelist,
318 t_wip_end_date IN wipenddatelist,
319 t_uom IN uomList,
320 t_quantity IN qtyList,
321 t_comments IN commentList,
322 t_carrier_code IN carrierList,
323 t_bill_of_lading IN billofladList,
324 t_tracking_number IN trackingList,
325 t_vehicle_number IN vehicleList,
326 t_container_type IN containerList,
327 t_container_qty IN contqtyList,
328 t_serial_number IN serialnumList,
329 t_attach_url IN attachurlList,
330 t_version IN versionList,
331 t_designator IN designatorList,
332 t_context IN contextList,
333 t_attribute1 IN attributeList,
334 t_attribute2 IN attributeList,
335 t_attribute3 IN attributeList,
336 t_attribute4 IN attributeList,
337 t_attribute5 IN attributeList,
338 t_attribute6 IN attributeList,
339 t_attribute7 IN attributeList,
340 t_attribute8 IN attributeList,
341 t_attribute9 IN attributeList,
342 t_attribute10 IN attributeList,
343 t_attribute11 IN attributeList,
344 t_attribute12 IN attributeList,
345 t_attribute13 IN attributeList,
346 t_attribute14 IN attributeList,
347 t_attribute15 IN attributeList,
348 --p_posting_party_name IN VARCHAR2,
349 --p_posting_party_id IN NUMBER,
350 t_posting_party_name IN postingpartyList,
351 t_posting_party_id IN numList,
352 p_user_id IN NUMBER,
353 p_language_code IN VARCHAR2
354 );
355
356 --===========================================================================
357 --This proc is called in the XML Gateway Maps. The XML gateway action type
358 --converts the OAG date format into 'YYYYMMDD HH24MISS' format. The proc
359 --takes in a string containing a date in the 'YYYYMMDD HH24MISS' format and
360 --converts it to 'DD-MON-YYYY HH24:MI:SS' format.
361 --===========================================================================
362 PROCEDURE change_date_format (
363 p_string IN OUT NOCOPY VARCHAR2
364 );
365
366 PROCEDURE LOG_MESSAGE(
367 p_string IN VARCHAR2
368 );
369
370 PROCEDURE POST_PROCESS(
371 p_header_id IN NUMBER
372 );
373
374 PROCEDURE UPDATE_QTY_FROM_UI(
375 p_item_id IN number,
376 p_qty IN number,
377 p_uom IN varchar2,
378 p_pri_uom IN varchar2,
379 p_tp_uom IN varchar2,
380 p_pri_qty OUT NOCOPY number,
381 p_tp_qty OUT NOCOPY number
382 );
383
384 FUNCTION GET_QUANTITY(
385 p_qty IN NUMBER,
386 p_uom IN VARCHAR2,
387 p_uom1 IN VARCHAR2,
388 p_item_id IN NUMBER
389 ) RETURN NUMBER;
390
391 --=======================================================================
392 -- These procedure are used to verify and load serial Number information
393 -- To improve the perfomance it creates indexes on MSC_ST_SERIAL_NUMBERS
394 -- abd drops it at the before exiting from the code
395 --====================================================================
396
397 PROCEDURE serial_validation(
398 p_header_id IN NUMBER,
399 p_language IN VARCHAR2
400 );
401
402 PROCEDURE drop_index(
403 v_applsys_schema IN VARCHAR2
404 );
405
406 PROCEDURE create_index (
407 v_applsys_schema IN VARCHAR2
408 );
409
410 -- API to validate receipt/ship date for TP as a customer
411 PROCEDURE validate_rs_dates_supplier(
412 t_line_id IN lineidList
413 , p_header_id IN NUMBER
414 , p_language IN VARCHAR2
415 );
416
417 -- API to validate receipt/ship date for TP as a customer
418 PROCEDURE validate_rs_dates_customer(
419 t_line_id IN lineidList
423
420 , p_header_id IN NUMBER
421 , p_language IN VARCHAR2
422 );
424 END msc_sce_loads_pkg;
[Home] [Help]