1 PACKAGE OE_Bulk_Holds_PVT AS
2 /* $Header: OEBVHLDS.pls 120.0.12010000.7 2008/11/26 13:41:58 amallik 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 );
36 --ER#7479609 end
37 TYPE Hold_Entity_Tbl IS TABLE OF Hold_Entity_Rec
38 INDEX BY BINARY_INTEGER;
39
40 TYPE Line_Holds_Rec IS RECORD
41 (On_Generic_Hold VARCHAR2(1),
42 On_Scheduling_Hold VARCHAR2(1),
43 Any_ato_line_on_hold VARCHAR2(1),
44 Any_SMC_Line_on_hold VARCHAR2(1),
45 Hold_II_FLAG VARCHAR2(1)
46 );
47
48 TYPE Line_Holds_Tbl IS TABLE OF Line_Holds_Rec
49 INDEX BY BINARY_INTEGER;
50
51 ---------------------------------------------------------------------
52 -- GLOBAL RECORDS/TABLES
53 ---------------------------------------------------------------------
54
55 G_Line_Holds_Tbl Line_Holds_Tbl;
56
57 Customer_Pointer Hold_Tbl;
58 Item_Pointer Hold_Tbl;
59 ship_to_Pointer Hold_Tbl;
60 bill_to_Pointer Hold_Tbl;
61 Warehouse_Pointer Hold_Tbl;
62 --ER#7479609 start
63 PriceList_Pointer Hold_Tbl;
64 OrderType_Pointer Hold_Tbl;
65 CreationDate_Pointer Hold_Tbl;
66 SalesChannel_Pointer Hold_Tbl;
67 PaymentType_Pointer Hold_Tbl;
68 ShipMethod_Pointer Hold_Tbl;
69 deliver_to_Pointer Hold_Tbl;
70 --ER#7479609 end
71
72 Item_Customer_Pointer Hold_Tbl;
73 Item_shipto_Pointer Hold_Tbl;
74 Item_Billto_Pointer Hold_Tbl;
75 Item_Warehouse_Pointer Hold_Tbl;
76 --ER#7479609 Start
77 Item_ShipMethod_Pointer Hold_Tbl;
78 Item_Deliverto_Pointer Hold_Tbl;
79 Item_PriceList_Pointer Hold_Tbl;
80 Item_SourceType_Pointer Hold_Tbl;
81 Item_LineType_Pointer Hold_Tbl;
82 --ER#7479609 End
83
84 Warehouse_Customer_Pointer Hold_Tbl;
85 Warehouse_shipto_Pointer Hold_Tbl;
86 Warehouse_Billto_Pointer Hold_Tbl;
87 --ER#7479609 Start
88 Warehouse_LineType_Pointer Hold_Tbl;
89 Warehouse_ShipMethod_Pointer Hold_Tbl;
90 Warehouse_Deliverto_Pointer Hold_Tbl;
91 Warehouse_SourceType_Pointer Hold_Tbl;
92 --ER#7479609 End
93
94 --ER#7479609 start
95 Cust_SourceType_Pointer Hold_Tbl;
96 Cust_Billto_Pointer Hold_Tbl;
97 Cust_Shipto_Pointer Hold_Tbl;
98 Cust_Deliverto_Pointer Hold_Tbl;
99 Cust_PriceList_Pointer Hold_Tbl;
100 Cust_LineType_Pointer Hold_Tbl;
101 Cust_PayTerm_Pointer Hold_Tbl;
102 Cust_OrderType_Pointer Hold_Tbl;
103 Cust_PaymentType_Pointer Hold_Tbl;
104 Cust_Curr_Pointer Hold_Tbl;
105 Cust_SalesChannel_Pointer Hold_Tbl;
106
107
108 PriceList_Curr_Pointer Hold_Tbl;
109
110 OrderType_Curr_Pointer Hold_Tbl;
111 OrderType_LineType_Pointer Hold_Tbl;
112
113 CreDate_CreBy_Pointer Hold_Tbl;
114 --ER#7479609 End
115
116
117 Customer_Hold Hold_Entity_Tbl;
118 Item_Hold Hold_Entity_Tbl;
119 ship_to_Hold Hold_Entity_Tbl;
120 bill_to_Hold Hold_Entity_Tbl;
121 Warehouse_Hold Hold_Entity_Tbl;
122 --ER#7479609 start
123 PriceList_Hold Hold_Entity_Tbl;
124 OrderType_Hold Hold_Entity_Tbl;
125 CreationDate_Hold Hold_Entity_Tbl;
126 SalesChannel_Hold Hold_Entity_Tbl;
127 PaymentType_Hold Hold_Entity_Tbl;
128 ShipMethod_Hold Hold_Entity_Tbl;
129 deliver_to_Hold Hold_Entity_Tbl;
130 --ER#7479609 end
131
132 Item_Customer_Hold Hold_Entity_Tbl;
133 Item_shipto_Hold Hold_Entity_Tbl;
134 Item_Billto_Hold Hold_Entity_Tbl;
135 Item_Warehouse_Hold Hold_Entity_Tbl;
136 --ER#7479609 start
137 Item_ShipMethod_Hold Hold_Entity_Tbl;
138 Item_Deliverto_Hold Hold_Entity_Tbl;
139 Item_PriceList_Hold Hold_Entity_Tbl;
140 Item_SourceType_Hold Hold_Entity_Tbl;
141 Item_LineType_Hold Hold_Entity_Tbl;
142 --ER#7479609 end
143
144 Warehouse_Customer_Hold Hold_Entity_Tbl;
145 Warehouse_shipto_Hold Hold_Entity_Tbl;
146 Warehouse_Billto_Hold Hold_Entity_Tbl;
147 --ER#7479609 start
148 Warehouse_LineType_Hold Hold_Entity_Tbl;
149 Warehouse_ShipMethod_Hold Hold_Entity_Tbl;
150 Warehouse_Deliverto_Hold Hold_Entity_Tbl;
151 Warehouse_SourceType_Hold Hold_Entity_Tbl;
152 --ER#7479609 end
153
154 --ER#7479609 start
155 Cust_SourceType_Hold Hold_Entity_Tbl;
156 Cust_Billto_Hold Hold_Entity_Tbl;
157 Cust_Shipto_Hold Hold_Entity_Tbl;
158 Cust_Deliverto_Hold Hold_Entity_Tbl;
159 Cust_PriceList_Hold Hold_Entity_Tbl;
160 Cust_LineType_Hold Hold_Entity_Tbl;
161 Cust_PayTerm_Hold Hold_Entity_Tbl;
162 Cust_OrderType_Hold Hold_Entity_Tbl;
163 Cust_PaymentType_Hold Hold_Entity_Tbl;
164 Cust_Curr_Hold Hold_Entity_Tbl;
165 Cust_SalesChannel_Hold Hold_Entity_Tbl;
166
167 --ER#7479609 end
168
169 PriceList_Curr_Hold Hold_Entity_Tbl;
170
171 OrderType_Curr_Hold Hold_Entity_Tbl;
172 OrderType_LineType_Hold Hold_Entity_Tbl;
173
174 CreDate_CreBy_Hold Hold_Entity_Tbl;
175 --ER#7479609 end
176
177 g_hold_header_id OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM();
178 g_hold_line_id OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM();
179 g_hold_Source_Id OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM();
180 g_hold_ship_set OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30();
181 g_hold_arrival_set OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30();
182 g_hold_top_model_line_id OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM();
183 g_hold_activity_name OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30();
184
185 g_header_index NUMBER;
186
187 TYPE Hold_Source_Rec IS RECORD
188 (
189 HOLD_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
190 HOLD_SOURCE_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
191 HOLD_ENTITY_CODE OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30(),
192 HOLD_ENTITY_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
193 HOLD_UNTIL_DATE OE_WSH_BULK_GRP.T_DATE := OE_WSH_BULK_GRP.T_DATE(),
194 RELEASED_FLAG OE_WSH_BULK_GRP.T_V1 := OE_WSH_BULK_GRP.T_V1(),
195 HOLD_COMMENT OE_WSH_BULK_GRP.T_V2000 := OE_WSH_BULK_GRP.T_V2000(),
196 ORG_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
197 HOLD_RELEASE_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
198 HOLD_ENTITY_CODE2 OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30(),
199 HOLD_ENTITY_ID2 OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
200 HEADER_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM(),
201 LINE_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM()
202 );
203
204 g_hold_source_rec Hold_Source_Rec;
205 -----------------------------------------------------------------------
206 -- PROCEDURES/FUNCTIONS
207 -----------------------------------------------------------------------
208
209 -----------------------------------------------------------------------
210 -- PROCEDURE Load_Hold_Sources
211 --
212 -- This API caches existing hold sources in global tables:
213 -- Customer_Pointer etc. It is called from order import main package
214 -- (OEBVIMNB.pls) at the beginning of each order import request.
215 -----------------------------------------------------------------------
216
217 PROCEDURE Load_Hold_Sources;
218
219 -----------------------------------------------------------------------
220 -- PROCEDURE Initialize_Holds_Tbl
221 --
222 -- This API initializes the globals that store hold records for
223 -- a batch.
224 -----------------------------------------------------------------------
225
226 PROCEDURE Initialize_Holds_Tbl;
227
228 -----------------------------------------------------------------------
229 -- PROCEDURE Evaluate_Holds
230 --
231 -- This procedure is used to evaluate holds on the order or line being
232 -- processed in BULK order import.
233 --
234 -- If order or line attributes passed to this API (sold_to_org_id,
235 -- inventory_item_id etc.) match the hold source(s) criteria, this API
236 -- would mark that hold(s) needs to be applied by adding to the global
237 -- holds table.
238 --
239 -- In addition, it also populates OUT parameters to indicate the type
240 -- of hold (if any) that will be applied on this order or line.
241 -- Current BULK code needs to check for generic, booking or scheduling
242 -- holds only so there are 3 OUT parameters which will be set to TRUE
243 -- depending on types of holds that were applied.
244 -----------------------------------------------------------------------
245
246 PROCEDURE Evaluate_Holds(
247 p_header_id IN NUMBER,
248 p_line_id IN NUMBER,
249 p_line_number IN NUMBER,
250 p_sold_to_org_id IN NUMBER,
251 p_inventory_item_id IN NUMBER,
252 p_ship_from_org_id IN NUMBER,
253 p_invoice_to_org_id IN NUMBER,
254 p_ship_to_org_id IN NUMBER,
255 p_top_model_line_id IN NUMBER,
256 p_ship_set_name IN VARCHAR2,
257 p_arrival_set_name IN VARCHAR2,
258 p_check_only_warehouse_holds IN BOOLEAN := FALSE,
259 p_on_generic_hold OUT NOCOPY /* file.sql.39 change */ BOOLEAN,
260 p_on_booking_hold OUT NOCOPY /* file.sql.39 change */ BOOLEAN,
261 p_on_scheduling_hold OUT NOCOPY /* file.sql.39 change */ BOOLEAN
262 );
263
264
265 --ER#7479609 start
266 -----------------------------------------------------------------------
267 -- PROCEDURE Evaluate_Holds
268 --
269 -- This procedure is used to evaluate holds on the order or line being
270 -- processed in BULK order import.
271 --
272 -- If order or line attributes passed to this API (sold_to_org_id,
273 -- inventory_item_id etc.) match the hold source(s) criteria, this API
274 -- would mark that hold(s) needs to be applied by adding to the global
275 -- holds table.
276 --
277 -- In addition, it also populates OUT parameters to indicate the type
278 -- of hold (if any) that will be applied on this order or line.
279 -- Current BULK code needs to check for generic, booking or scheduling
280 -- holds only so there are 3 OUT parameters which will be set to TRUE
281 -- depending on types of holds that were applied.
282 -----------------------------------------------------------------------
283
284 PROCEDURE Evaluate_Holds(
285 p_header_rec IN OE_Order_PUB.Header_Rec_Type,
286 p_line_rec IN OE_Order_PUB.Line_Rec_Type,
287 p_check_only_warehouse_holds IN BOOLEAN := FALSE,
288 p_on_generic_hold OUT NOCOPY BOOLEAN,
289 p_on_booking_hold OUT NOCOPY BOOLEAN,
290 p_on_scheduling_hold OUT NOCOPY BOOLEAN
291 );
292 --ER#7479609 end
293
294 -----------------------------------------------------------------------
295 -- PROCEDURE Create_Holds
296 --
297 -- This API BULK inserts hold records into the database for orders
298 -- or lines processed in a single bulk order import batch. It uses
299 -- global hold records to create these records.
300 -----------------------------------------------------------------------
301
302 PROCEDURE Create_Holds;
303
304
305 ------------------------------------------------------------------------
306 --PROCEDURE Mark_Hols
307 --This for marking lines to hold in the memory.
308 ------------------------------------------------------------------------
309 PROCEDURE Mark_Hold(p_header_id IN NUMBER,
310 p_line_id IN NUMBER,
311 p_line_number IN NUMBER,
312 p_hold_source_id IN NUMBER,
313 p_ship_set_name IN VARCHAR2,
314 p_arrival_set_name IN VARCHAR2,
315 p_activity_name IN VARCHAR2,
316 p_attribute IN VARCHAR2,
317 p_top_model_line_id IN NUMBER
318 );
319
320 PROCEDURE Apply_GSA_Hold(p_header_id IN NUMBER,
321 p_line_id IN NUMBER,
322 p_line_number IN NUMBER,
323 p_hold_id IN NUMBER,
324 p_ship_set_name IN VARCHAR2,
325 p_arrival_set_name IN VARCHAR2,
326 p_activity_name IN VARCHAR2,
327 p_attribute IN VARCHAR2,
328 p_top_model_line_id IN NUMBER,
329 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 --bug 3735141
330 );
331
332 ------------------------------------------------------------------------
333 -- FUNCTION Check_for_Holds
334 -- This API is called by scheduling
335 ------------------------------------------------------------------------
336 FUNCTION Check_For_Holds(p_header_id IN NUMBER,
337 p_line_id IN NUMBER,
338 p_line_index IN NUMBER,
339 p_header_index IN NUMBER,
340 p_top_model_line_index IN NUMBER,
341 p_ship_model_complete_flag IN VARCHAR2,
342 p_ato_line_index IN NUMBER,
343 p_ii_parent_line_index IN NUMBER
344 ) RETURN BOOLEAN;
345
346 END OE_Bulk_Holds_PVT;
347
[Home] [Help]