1 PACKAGE OE_Set_Util AUTHID CURRENT_USER AS
2 /* $Header: OEXUSETS.pls 120.5.12020000.1 2012/06/26 09:47:35 appldev ship $ */
3
4 g_delivery_set_rec OE_ORDER_PUB.set_rec_type;
5 g_invoice_set_rec OE_ORDER_PUB.set_rec_type;
6 g_ship_set_rec OE_ORDER_PUB.set_rec_type;
7 g_fullfillment_set_rec OE_ORDER_PUB.set_rec_type;
8 G_Create_Ship_set VARCHAR2(30) := FND_API.G_FALSE;
9 G_Create_Arrival_set VARCHAR2(30) := FND_API.G_FALSE;
10 G_Set_Request_Tbl OE_ORDER_PUB.Request_Tbl_Type;
11 G_Set_Recursive_Flag BOOLEAN := FALSE;
12
13 --Bug 4080531
14 g_set_rec OE_ORDER_CACHE.set_rec_type;
15
16 Type Set_rec IS RECORD
17 (
18 Line_id number := FND_api.g_miss_num,
19 Set_name varchar2(80) := FND_Api.g_miss_char,
20 set_type varchar2(30),
21 process_flag varchar2(1):= 'N',
22 operation varchar2(1):= 'U');
23
24 Type Set_opt_rec IS RECORD
25 (
26 Line_id number := FND_api.g_miss_num,
27 Set_id number := FND_Api.g_miss_num,
28 set_type varchar2(30));
29
30 Type auto_set_rec IS RECORD
31 (
32 Line_id number := FND_api.g_miss_num);
33
34 Type Set_opt_line_Tbl IS TABLE OF set_opt_rec
35 index by binary_integer;
36
37 Type Set_line_Tbl IS TABLE OF set_rec
38 index by binary_integer;
39
40 Type auto_set_tbl IS TABLE OF auto_set_rec
41 index by binary_integer;
42
43 g_set_tbl Set_line_Tbl;
44 g_set_opt_tbl Set_opt_line_Tbl;
45 g_auto_set_tbl auto_set_tbl;
46
47
48 -- Function to find if set exists
49 FUNCTION Set_Exist(p_set_name IN VARCHAR2,
50 p_set_type IN VARCHAR2,
51 p_Header_Id IN NUMBER,
52 x_set_id OUT NOCOPY NUMBER)
53
54 RETURN BOOLEAN;
55
56 -- Function to get Fulfillment sets for a line
57
58 FUNCTION Get_Fulfillment_List(p_line_id IN NUMBER)
59 RETURN VARCHAR2;
60
61 -- Function to find if set exists for a set id
62
63 FUNCTION Set_Exist(p_set_id IN NUMBER,
64 p_header_id IN NUMBER := FND_API.G_MISS_NUM)
65 RETURN BOOLEAN;
66
67 -- This procedure is first thing to get called in Lines procedure
68 -- to figure out if there are any set requests and populate the
69 -- global tables that get accessed in post lines process
70
71 PROCEDURE get_set_id(
72 p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type,
73 p_old_line_rec IN OE_ORDER_PUB.line_rec_type,
74 p_Index IN NUmber);
75
76 -- This Procedure is designed to be called from form controller for a
77 -- multiselect case and user using the right mouse click button to do
78 -- set operations
79
80 Procedure Process_Sets
81 ( p_selected_line_tbl IN OE_GLOBALS.Selected_Record_Tbl, --R12.MOAC
82 p_record_count IN NUMBER,
83 p_set_name IN VARCHAR2,
84 p_set_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
85 p_operation IN VARCHAR2,
86 p_header_id IN VARCHAR2 := FND_API.G_MISS_CHAR,
87 x_Set_Id OUT NOCOPY NUMBER,
88
89 x_return_status OUT NOCOPY VARCHAR2,
90
91 x_msg_count OUT NOCOPY NUMBER,
92
93 x_msg_data OUT NOCOPY VARCHAR2);
94
95
96 -- This api is called to create set passing the required set attributes
97 -- Validates if all the required set attributes set correctly and creates
98 -- the set and returns the set id
99 Procedure Create_Set
100 (p_Set_Name IN VARCHAR2,
101 p_Set_Type IN VARCHAR2,
102 p_Header_Id IN NUMBER := NULL,
103 p_Ship_From_Org_Id IN NUMBER := NULL,
104 p_Ship_To_Org_Id IN NUMBER := NULL,
105 p_shipment_priority_code IN VARCHAR2 := NULL,
106 p_Schedule_Ship_Date IN DATE := NULL,
107 p_Schedule_Arrival_Date IN DATE := NULL,
108 p_Freight_Carrier_Code IN VARCHAR2 := NULL,
109 p_Shipping_Method_Code IN VARCHAR2 := NULL,
110 p_system_set IN VARCHAR2 DEFAULT 'N',
111 x_Set_Id OUT NOCOPY NUMBER,
112
113 x_return_status OUT NOCOPY VARCHAR2,
114
115 x_msg_count OUT NOCOPY NUMBER,
116
117 x_msg_data OUT NOCOPY VARCHAR2
118
119 );
120 -- Insert Into Set is called from a delayed request for a case where
121 -- User chooses to default the preference of set from the header rec
122 -- This dependes on the customer preference set code
123
124 Procedure Insert_Into_Set
125 (p_Set_request_tbl oe_order_pub.Request_Tbl_Type,
126 p_Push_Set_Date IN VARCHAR2 := FND_API.G_FALSE,
127 X_Return_Status OUT NOCOPY VARCHAR2,
128
129 x_msg_count OUT NOCOPY NUMBER,
130
131 x_msg_data OUT NOCOPY VARCHAR2
132
133 );
134
135 -- This is desinged with the intention to push new lines of the split that
136 -- are part of the ship or arrival set
137 -- into a different new set
138
139 Procedure Split_Set
140 (p_set_id IN NUMBER,
141 p_set_name IN VARCHAR2,
142 X_Return_Status OUT NOCOPY VARCHAR2,
143
144 x_msg_count OUT NOCOPY NUMBER,
145
146 x_msg_data OUT NOCOPY VARCHAR2
147
148 );
149
150 -- Update set is called from scheduling when any of the set attributes
151 -- changes and result in cascading on the lines of the set.
152 Procedure Update_Set
153 (p_Set_Id IN NUMBER,
154 p_Ship_From_Org_Id IN NUMBER := FND_API.G_MISS_NUM,
155 p_Ship_To_Org_Id IN NUMBER := FND_API.G_MISS_NUM,
156 p_Schedule_Ship_Date IN DATE := FND_API.G_MISS_DATE,
157 p_Schedule_Arrival_Date IN DATE := FND_API.G_MISS_DATE,
158 p_Freight_Carrier_Code IN VARCHAR2 := FND_API.G_MISS_CHAR,
159 p_Shipping_Method_Code IN VARCHAR2 := FND_API.G_MISS_CHAR,
160 p_shipment_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
161 X_Return_Status OUT NOCOPY VARCHAR2,
162
163 x_msg_count OUT NOCOPY NUMBER,
164
165 x_msg_data OUT NOCOPY VARCHAR2
166
167 );
168
169 -- This is use to cache the set record for performance reasons
170 FUNCTION get_set_rec(p_set_id IN NUMBER)
171 RETURN OE_ORDER_CACHE.set_rec_type;
172
173 -- This api checks if all the set attributes on the set are same
174
175 PROCEDURE Validate_set_attributes(p_set_id IN NUMBER ,
176 p_Ship_From_Org_Id IN NUMBER := FND_API.G_MISS_NUM,
177 p_Ship_To_Org_Id IN NUMBER := FND_API.G_MISS_NUM,
178 p_Schedule_Ship_Date IN DATE := FND_API.G_MISS_DATE,
179 p_Schedule_Arrival_Date IN DATE := FND_API.G_MISS_DATE,
180 p_Freight_Carrier_Code IN VARCHAR2 := FND_API.G_MISS_CHAR,
181 p_Shipping_Method_Code IN VARCHAR2 := FND_API.G_MISS_CHAR,
182 p_shipment_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
183 X_Return_Status OUT NOCOPY VARCHAR2);
184
185
186 -- This is to check if an arrival set already exists in a ship set and
187 -- if so to restrict user from creating a second one
188
189 PROCEDURE Validate_Multi_Arr_Set(p_header_id IN NUMBER,
190 p_ship_Set_id IN NUMBER ,
191 x_arrival_set_id OUT NOCOPY NUMBER);
192
193
194 -- Updates the options if a model is changed
195
196 Procedure Update_Options(p_ato_line_id IN NUMBER := FND_API.G_MISS_NUM,
197 p_config_line_id IN NUMBER := FND_API.G_MISS_NUM,
198 p_set_id IN NUMBER,
199 p_set_type IN VARCHAR2 );
200
201 -- To query all the lines in the set
202
203 PROCEDURE Query_Set_Rows(p_set_id IN NUMBER,
204 x_line_tbl OUT NOCOPY OE_Order_PUB.Line_Tbl_Type);
205
206 -- To create a line set
207 Procedure Create_line_Set(p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.LINE_REC_TYPE);
208
209 -- To process the sets from the global table populated by get set id api
210 -- this is called from post line process and get executed when the proces is
211 -- set to true
212 Procedure Process_Sets(p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.LINE_TBL_TYPE);
213
214 -- To remove from fullifllment set
215 Procedure Remove_from_fulfillment(p_line_id NUMBER);
216
217 -- To create fulfillment set
218 Procedure Create_Fulfillment_Set(p_line_id NUMBER,
219 -- 4925992
220 p_top_model_line_id NUMBER := NULL,
221 p_set_id NUMBER);
222
223 -- Add new procedure Default line set for set and scheduling revamping
224 Procedure Default_Line_Set(p_x_line_rec IN OUT NOCOPY
225 oe_order_pub.line_rec_type,
226 p_old_line_rec IN oe_order_pub.line_rec_type);
227 -- 4026756
228 -- To delete set
229 Procedure Delete_Set(p_request_rec IN OE_ORDER_PUB.request_rec_type,
230 x_return_status OUT NOCOPY VARCHAR2);
231
232 --Standalone
233 FUNCTION Stand_Alone_set_exists (p_ship_set_id IN NUMBER,
234 p_arrival_set_id IN NUMBER,
235 p_header_id IN NUMBER,
236 p_line_id IN NUMBER,
237 p_sch_level IN VARCHAR2)
238 RETURN BOOLEAN;
239
240 END OE_Set_Util;