[Home] [Help]
PACKAGE: APPS.OE_BULK_HOLDS_PVT
Source
1 PACKAGE OE_Bulk_Holds_PVT AUTHID CURRENT_USER AS
2 /* $Header: OEBVHLDS.pls 120.9 2011/12/27 14:56:08 sujithku ship $ */
3
4
5 -----------------------------------------------------------------
6 -- DATA TYPES (RECORD/TABLE TYPES)
7 -----------------------------------------------------------------
8
9 /*ER#7479609 start
10 TYPE Hold_Tbl IS TABLE OF NUMBER
11 INDEX BY BINARY_INTEGER;
12 ER#7479609 end*/
13
14 --ER#7479609 start
15 TYPE Hold_Tbl IS TABLE OF OE_HOLD_SOURCES_ALL.HOLD_ENTITY_ID%TYPE
16 INDEX BY VARCHAR2(250);
17 -- OE_HOLD_SOURCES_ALL.HOLD_ENTITY_ID%TYPE; -- Modified INDEX BY TO VARCHAR2 for bug 7589328
18 --ER#7479609 end
19
20 /*ER#7479609 start
21 TYPE Hold_Entity_Rec IS RECORD
22 (Entity_Id1 NUMBER,
23 Entity_Id2 NUMBER,
24 Hold_Source_Id NUMBER,
25 Activity_name VARCHAR2(30)
26 );
27 ER#7479609 end*/
28
29 --ER#7479609 start
30 TYPE Hold_Entity_Rec IS RECORD
31 (Entity_Id1 OE_HOLD_SOURCES_ALL.HOLD_ENTITY_ID%TYPE,
32 Entity_Id2 OE_HOLD_SOURCES_ALL.HOLD_ENTITY_ID2%TYPE,
33 Hold_Source_Id NUMBER,
34 Activity_name VARCHAR2(30),
35 Hold_id NUMBER --ER# 3667551
36 );
37 --ER#7479609 end
38 TYPE Hold_Entity_Tbl IS TABLE OF Hold_Entity_Rec
39 INDEX BY VARCHAR2(250);
40
41 TYPE Line_Holds_Rec IS RECORD
42 (On_Generic_Hold VARCHAR2(1),
43 On_Scheduling_Hold VARCHAR2(1),
44 Any_ato_line_on_hold VARCHAR2(1),
45 Any_SMC_Line_on_hold VARCHAR2(1),
46 Hold_II_FLAG VARCHAR2(1)
47 );
48
49 TYPE Line_Holds_Tbl IS TABLE OF Line_Holds_Rec
50 INDEX BY VARCHAR2(250);
51
52 ---------------------------------------------------------------------
53 -- GLOBAL RECORDS/TABLES
54 ---------------------------------------------------------------------
55
56 G_Line_Holds_Tbl Line_Holds_Tbl;
57
58 Customer_Pointer Hold_Tbl;
59 Item_Pointer Hold_Tbl;
60 ship_to_Pointer Hold_Tbl;
61 bill_to_Pointer Hold_Tbl;
62 Warehouse_Pointer Hold_Tbl;
63 --ER#7479609 start
64 PriceList_Pointer Hold_Tbl;
65 OrderType_Pointer Hold_Tbl;
66 CreationDate_Pointer Hold_Tbl;
67 SalesChannel_Pointer Hold_Tbl;
68 PaymentType_Pointer Hold_Tbl;
69 ShipMethod_Pointer Hold_Tbl;
70 deliver_to_Pointer Hold_Tbl;
71 --ER#7479609 end
72
73 Item_Customer_Pointer Hold_Tbl;
74 Item_shipto_Pointer Hold_Tbl;
75 Item_Billto_Pointer Hold_Tbl;
76 Item_Warehouse_Pointer Hold_Tbl;
77 --ER#7479609 Start
78 Item_ShipMethod_Pointer Hold_Tbl;
79 Item_Deliverto_Pointer Hold_Tbl;
80 Item_PriceList_Pointer Hold_Tbl;
81 Item_SourceType_Pointer Hold_Tbl;
82 Item_LineType_Pointer Hold_Tbl;
83 --ER#7479609 End
84
85 Warehouse_Customer_Pointer Hold_Tbl;
86 Warehouse_shipto_Pointer Hold_Tbl;
87 Warehouse_Billto_Pointer Hold_Tbl;
88 --ER#7479609 Start
89 Warehouse_LineType_Pointer Hold_Tbl;
90 Warehouse_ShipMethod_Pointer Hold_Tbl;
91 Warehouse_Deliverto_Pointer Hold_Tbl;
92 Warehouse_SourceType_Pointer Hold_Tbl;
93 --ER#7479609 End
94
95 --ER#7479609 start
96 Cust_SourceType_Pointer Hold_Tbl;
97 Cust_Billto_Pointer Hold_Tbl;
98 Cust_Shipto_Pointer Hold_Tbl;
99 Cust_Deliverto_Pointer Hold_Tbl;
100 Cust_PriceList_Pointer Hold_Tbl;
101 Cust_LineType_Pointer Hold_Tbl;
102 Cust_PayTerm_Pointer Hold_Tbl;
103 Cust_OrderType_Pointer Hold_Tbl;
104 Cust_PaymentType_Pointer Hold_Tbl;
105 Cust_Curr_Pointer Hold_Tbl;
106 Cust_SalesChannel_Pointer Hold_Tbl;
107
108
109 PriceList_Curr_Pointer Hold_Tbl;
110
111 OrderType_Curr_Pointer Hold_Tbl;
112 OrderType_LineType_Pointer Hold_Tbl;
113
114 CreDate_CreBy_Pointer Hold_Tbl;
115 --ER#7479609 End
116
117
118 Customer_Hold Hold_Entity_Tbl;
119 Item_Hold Hold_Entity_Tbl;
120 ship_to_Hold Hold_Entity_Tbl;
121 bill_to_Hold Hold_Entity_Tbl;
122 Warehouse_Hold Hold_Entity_Tbl;
123 --ER#7479609 start
124 PriceList_Hold Hold_Entity_Tbl;
125 OrderType_Hold Hold_Entity_Tbl;
126 CreationDate_Hold Hold_Entity_Tbl;
127 SalesChannel_Hold Hold_Entity_Tbl;
128 PaymentType_Hold Hold_Entity_Tbl;
129 ShipMethod_Hold Hold_Entity_Tbl;
130 deliver_to_Hold Hold_Entity_Tbl;
131 --ER#7479609 end
132
133 Item_Customer_Hold Hold_Entity_Tbl;
134 Item_shipto_Hold Hold_Entity_Tbl;
135 Item_Billto_Hold Hold_Entity_Tbl;
136 Item_Warehouse_Hold Hold_Entity_Tbl;
137 --ER#7479609 start
138 Item_ShipMethod_Hold Hold_Entity_Tbl;
139 Item_Deliverto_Hold Hold_Entity_Tbl;
140 Item_PriceList_Hold Hold_Entity_Tbl;
141 Item_SourceType_Hold Hold_Entity_Tbl;
142 Item_LineType_Hold Hold_Entity_Tbl;
143 --ER#7479609 end
144
145 Warehouse_Customer_Hold Hold_Entity_Tbl;
146 Warehouse_shipto_Hold Hold_Entity_Tbl;
147 Warehouse_Billto_Hold Hold_Entity_Tbl;
148 --ER#7479609 start
149 Warehouse_LineType_Hold Hold_Entity_Tbl;
150 Warehouse_ShipMethod_Hold Hold_Entity_Tbl;
151 Warehouse_Deliverto_Hold Hold_Entity_Tbl;
152 Warehouse_SourceType_Hold Hold_Entity_Tbl;
153 --ER#7479609 end
154
155 --ER#7479609 start
156 Cust_SourceType_Hold Hold_Entity_Tbl;
157 Cust_Billto_Hold Hold_Entity_Tbl;
158 Cust_Shipto_Hold Hold_Entity_Tbl;
159 Cust_Deliverto_Hold Hold_Entity_Tbl;
160 Cust_PriceList_Hold Hold_Entity_Tbl;
161 Cust_LineType_Hold Hold_Entity_Tbl;
162 Cust_PayTerm_Hold Hold_Entity_Tbl;
163 Cust_OrderType_Hold Hold_Entity_Tbl;
164 Cust_PaymentType_Hold Hold_Entity_Tbl;
165 Cust_Curr_Hold Hold_Entity_Tbl;
166 Cust_SalesChannel_Hold Hold_Entity_Tbl;
167
168 --ER#7479609 end
169
170 PriceList_Curr_Hold Hold_Entity_Tbl;
171
172 OrderType_Curr_Hold Hold_Entity_Tbl;
173 OrderType_LineType_Hold Hold_Entity_Tbl;
174
175 CreDate_CreBy_Hold Hold_Entity_Tbl;
176 --ER#7479609 end
177
178 --ER# 12571983 start added for 'EC'
179 EndCust_Pointer Hold_Tbl;
180 Item_EndCust_Pointer Hold_Tbl;
181 Warehouse_EndCust_Pointer Hold_Tbl;
182 EndCust_SourceType_Pointer Hold_Tbl;
183 EndCust_Billto_Pointer Hold_Tbl;
184 EndCust_Shipto_Pointer Hold_Tbl;
185 EndCust_Deliverto_Pointer Hold_Tbl;
186 EndCust_PriceList_Pointer Hold_Tbl;
187 EndCust_LineType_Pointer Hold_Tbl;
188 EndCust_PayTerm_Pointer Hold_Tbl;
189 EndCust_OrderType_Pointer Hold_Tbl;
190 EndCust_PaymentType_Pointer Hold_Tbl;
191 EndCust_Curr_Pointer Hold_Tbl;
192 EndCust_SalesChannel_Pointer Hold_Tbl;
193 EndCust_EndCustLoc_Pointer Hold_Tbl;
194
195 EndCust_Hold Hold_Entity_Tbl;
196 Item_EndCust_Hold Hold_Entity_Tbl;
197 Warehouse_EndCust_Hold Hold_Entity_Tbl;
198 EndCust_SourceType_Hold Hold_Entity_Tbl;
199 EndCust_Billto_Hold Hold_Entity_Tbl;
200 EndCust_Shipto_Hold Hold_Entity_Tbl;
201 EndCust_Deliverto_Hold Hold_Entity_Tbl;
202 EndCust_PriceList_Hold Hold_Entity_Tbl;
203 EndCust_LineType_Hold Hold_Entity_Tbl;
204 EndCust_PayTerm_Hold Hold_Entity_Tbl;
205 EndCust_OrderType_Hold Hold_Entity_Tbl;
206 EndCust_PaymentType_Hold Hold_Entity_Tbl;
207 EndCust_Curr_Hold Hold_Entity_Tbl;
208 EndCust_SalesChannel_Hold Hold_Entity_Tbl;
209 EndCust_EndCustLoc_Hold Hold_Entity_Tbl;
210
211 --ER# 12571983 end added for 'EC'
212
213 --ER# 13331078 start added for 'IC'
214
215 ItemCat_Pointer Hold_Tbl;
216 ItemCat_Customer_Pointer Hold_Tbl;
217 ItemCat_shipto_Pointer Hold_Tbl;
218 ItemCat_Billto_Pointer Hold_Tbl;
219 ItemCat_Warehouse_Pointer Hold_Tbl;
220 ItemCat_ShipMethod_Pointer Hold_Tbl;
221 ItemCat_Deliverto_Pointer Hold_Tbl;
222 ItemCat_PriceList_Pointer Hold_Tbl;
223 ItemCat_SourceType_Pointer Hold_Tbl;
224 ItemCat_LineType_Pointer Hold_Tbl;
225 ItemCat_EndCust_Pointer Hold_Tbl;
226
227 ItemCat_Hold Hold_Entity_Tbl;
228 ItemCat_Customer_Hold Hold_Entity_Tbl;
229 ItemCat_shipto_Hold Hold_Entity_Tbl;
230 ItemCat_Billto_Hold Hold_Entity_Tbl;
231 ItemCat_Warehouse_Hold Hold_Entity_Tbl;
232 ItemCat_ShipMethod_Hold Hold_Entity_Tbl;
233 ItemCat_Deliverto_Hold Hold_Entity_Tbl;
234 ItemCat_PriceList_Hold Hold_Entity_Tbl;
235 ItemCat_SourceType_Hold Hold_Entity_Tbl;
236 ItemCat_LineType_Hold Hold_Entity_Tbl;
237 ItemCat_EndCust_Hold Hold_Entity_Tbl;
238
239 --ER# 13331078 end added for 'IC'
240
241 g_hold_header_id OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM();
242 g_hold_line_id OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM();
243 g_hold_Source_Id OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM();
244 g_hold_ship_set OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30();
245 g_hold_arrival_set OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30();
246 g_hold_top_model_line_id OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM();
247 g_hold_activity_name OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30();
248
249 g_header_index NUMBER;
250
251 TYPE Hold_Source_Rec IS RECORD
252 (
253 HOLD_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
254 HOLD_SOURCE_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
255 HOLD_ENTITY_CODE OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30(),
256 HOLD_ENTITY_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
257 HOLD_UNTIL_DATE OE_WSH_BULK_GRP.T_DATE := OE_WSH_BULK_GRP.T_DATE(),
258 RELEASED_FLAG OE_WSH_BULK_GRP.T_V1 := OE_WSH_BULK_GRP.T_V1(),
259 HOLD_COMMENT OE_WSH_BULK_GRP.T_V2000 := OE_WSH_BULK_GRP.T_V2000(),
260 ORG_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
261 HOLD_RELEASE_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
262 HOLD_ENTITY_CODE2 OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30(),
263 HOLD_ENTITY_ID2 OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
264 HEADER_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
265 LINE_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM()
266 );
267
268 g_hold_source_rec Hold_Source_Rec;
269 -----------------------------------------------------------------------
270 -- PROCEDURES/FUNCTIONS
271 -----------------------------------------------------------------------
272
273 -----------------------------------------------------------------------
274 -- PROCEDURE Load_Hold_Sources
275 --
276 -- This API caches existing hold sources in global tables:
277 -- Customer_Pointer etc. It is called from order import main package
278 -- (OEBVIMNB.pls) at the beginning of each order import request.
279 -----------------------------------------------------------------------
280
281 PROCEDURE Load_Hold_Sources;
282
283 -----------------------------------------------------------------------
284 -- PROCEDURE Initialize_Holds_Tbl
285 --
286 -- This API initializes the globals that store hold records for
287 -- a batch.
288 -----------------------------------------------------------------------
289
290 PROCEDURE Initialize_Holds_Tbl;
291
292 -----------------------------------------------------------------------
293 -- PROCEDURE Evaluate_Holds
294 --
295 -- This procedure is used to evaluate holds on the order or line being
296 -- processed in BULK order import.
297 --
298 -- If order or line attributes passed to this API (sold_to_org_id,
299 -- inventory_item_id etc.) match the hold source(s) criteria, this API
300 -- would mark that hold(s) needs to be applied by adding to the global
301 -- holds table.
302 --
303 -- In addition, it also populates OUT parameters to indicate the type
307 -- depending on types of holds that were applied.
304 -- of hold (if any) that will be applied on this order or line.
305 -- Current BULK code needs to check for generic, booking or scheduling
306 -- holds only so there are 3 OUT parameters which will be set to TRUE
308 -----------------------------------------------------------------------
309
310 PROCEDURE Evaluate_Holds(
311 p_header_id IN NUMBER,
312 p_line_id IN NUMBER,
313 p_line_number IN NUMBER,
314 p_sold_to_org_id IN NUMBER,
315 p_inventory_item_id IN NUMBER,
316 p_ship_from_org_id IN NUMBER,
317 p_invoice_to_org_id IN NUMBER,
318 p_ship_to_org_id IN NUMBER,
319 p_top_model_line_id IN NUMBER,
320 p_ship_set_name IN VARCHAR2,
321 p_arrival_set_name IN VARCHAR2,
322 p_check_only_warehouse_holds IN BOOLEAN := FALSE,
323 p_on_generic_hold OUT NOCOPY /* file.sql.39 change */ BOOLEAN,
324 p_on_booking_hold OUT NOCOPY /* file.sql.39 change */ BOOLEAN,
325 p_on_scheduling_hold OUT NOCOPY /* file.sql.39 change */ BOOLEAN
326 );
327
328
329 --ER#7479609 start
330 -----------------------------------------------------------------------
331 -- PROCEDURE Evaluate_Holds
332 --
333 -- This procedure is used to evaluate holds on the order or line being
334 -- processed in BULK order import.
335 --
336 -- If order or line attributes passed to this API (sold_to_org_id,
337 -- inventory_item_id etc.) match the hold source(s) criteria, this API
338 -- would mark that hold(s) needs to be applied by adding to the global
339 -- holds table.
340 --
341 -- In addition, it also populates OUT parameters to indicate the type
342 -- of hold (if any) that will be applied on this order or line.
343 -- Current BULK code needs to check for generic, booking or scheduling
344 -- holds only so there are 3 OUT parameters which will be set to TRUE
345 -- depending on types of holds that were applied.
346 -----------------------------------------------------------------------
347
348 PROCEDURE Evaluate_Holds(
349 p_header_rec IN OE_Order_PUB.Header_Rec_Type,
350 p_line_rec IN OE_Order_PUB.Line_Rec_Type,
351 p_check_only_warehouse_holds IN BOOLEAN := FALSE,
352 p_on_generic_hold OUT NOCOPY BOOLEAN,
353 p_on_booking_hold OUT NOCOPY BOOLEAN,
354 p_on_scheduling_hold OUT NOCOPY BOOLEAN
355 );
356 --ER#7479609 end
357
358 -----------------------------------------------------------------------
359 -- PROCEDURE Create_Holds
360 --
361 -- This API BULK inserts hold records into the database for orders
362 -- or lines processed in a single bulk order import batch. It uses
363 -- global hold records to create these records.
364 -----------------------------------------------------------------------
365
366 PROCEDURE Create_Holds;
367
368
369 ------------------------------------------------------------------------
370 --PROCEDURE Mark_Hols
371 --This for marking lines to hold in the memory.
372 ------------------------------------------------------------------------
373 PROCEDURE Mark_Hold(p_header_id IN NUMBER,
374 p_line_id IN NUMBER,
375 p_line_number IN NUMBER,
376 p_hold_source_id IN NUMBER,
377 p_ship_set_name IN VARCHAR2,
378 p_arrival_set_name IN VARCHAR2,
379 p_activity_name IN VARCHAR2,
380 p_attribute IN VARCHAR2,
381 p_top_model_line_id IN NUMBER
382 );
383
384 PROCEDURE Apply_GSA_Hold(p_header_id IN NUMBER,
385 p_line_id IN NUMBER,
386 p_line_number IN NUMBER,
387 p_hold_id IN NUMBER,
388 p_ship_set_name IN VARCHAR2,
389 p_arrival_set_name IN VARCHAR2,
390 p_activity_name IN VARCHAR2,
391 p_attribute IN VARCHAR2,
392 p_top_model_line_id IN NUMBER,
393 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 --bug 3735141
394 );
395
396 ------------------------------------------------------------------------
397 -- FUNCTION Check_for_Holds
398 -- This API is called by scheduling
399 ------------------------------------------------------------------------
400 FUNCTION Check_For_Holds(p_header_id IN NUMBER,
401 p_line_id IN NUMBER,
402 p_line_index IN NUMBER,
403 p_header_index IN NUMBER,
404 p_top_model_line_index IN NUMBER,
405 p_ship_model_complete_flag IN VARCHAR2,
406 p_ato_line_index IN NUMBER,
407 p_ii_parent_line_index IN NUMBER
408 ) RETURN BOOLEAN;
409
410 ------------------------------------------------------------------------
411 ----ER# 3667551 new Function added to return Customer Account ID
412 ----for the Site ID passed in
413 ------------------------------------------------------------------------
414 FUNCTION CustAcctID_func
415 (
416 p_in_site_id IN NUMBER,
417 p_out_IDfound OUT NOCOPY VARCHAR2) RETURN NUMBER;
418
419 END OE_Bulk_Holds_PVT;
420