1 PACKAGE WSH_BULK_PROCESS_PVT AUTHID CURRENT_USER as
2 /* $Header: WSHBLPRS.pls 120.1.12000000.1 2007/01/16 05:42:09 appldev ship $ */
3
4
5 TYPE additional_line_info_rec_type IS RECORD (
6 Released_status WSH_BULK_TYPES_GRP.char1_nested_tab_TYPE :=
7 WSH_BULK_TYPES_GRP.char1_nested_tab_TYPE(),
8 Source_code varchar2(30),
9 inv_interfaced_flag WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type :=
10 WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type(),
11 attribute_category WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
12 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
13 attribute1 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
14 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
15 attribute2 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
16 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
17 attribute3 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
18 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
19 attribute4 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
20 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
21 attribute5 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
22 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
23 attribute6 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
24 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
25 attribute7 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
26 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
27 attribute8 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
28 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
29 attribute9 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
30 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
31 attribute10 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
32 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
33 attribute11 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
34 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
35 attribute12 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
36 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
37 attribute13 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
38 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
39 attribute14 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
40 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
41 attribute15 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type :=
42 WSH_BULK_TYPES_GRP.char150_Nested_Tab_Type(),
43 ignore_for_planning WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type :=
47 latest_pickup_date WSH_BULK_TYPES_GRP.date_Nested_Tab_Type :=
44 WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type(),
45 earliest_pickup_date WSH_BULK_TYPES_GRP.date_Nested_Tab_Type :=
46 WSH_BULK_TYPES_GRP.date_Nested_Tab_Type(),
48 WSH_BULK_TYPES_GRP.date_Nested_Tab_Type(),
49 earliest_dropoff_date WSH_BULK_TYPES_GRP.date_Nested_Tab_Type :=
50 WSH_BULK_TYPES_GRP.date_Nested_Tab_Type(),
51 latest_dropoff_date WSH_BULK_TYPES_GRP.date_Nested_Tab_Type :=
52 WSH_BULK_TYPES_GRP.date_Nested_Tab_Type(),
53 service_level WSH_BULK_TYPES_GRP.char30_Nested_Tab_Type :=
54 WSH_BULK_TYPES_GRP.char30_Nested_Tab_Type(),
55 mode_of_transport WSH_BULK_TYPES_GRP.char30_Nested_Tab_Type :=
56 WSH_BULK_TYPES_GRP.char30_Nested_Tab_Type(),
57 cancelled_quantity2 WSH_BULK_TYPES_GRP.number_Nested_Tab_Type :=
58 WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
59 cancelled_quantity WSH_BULK_TYPES_GRP.number_Nested_Tab_Type :=
60 WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
61 master_container_item_id WSH_BULK_TYPES_GRP.number_Nested_Tab_Type :=
62 WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
63 detail_container_item_id WSH_BULK_TYPES_GRP.number_Nested_Tab_Type :=
64 WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
65 latest_pickup_tpdate_excep WSH_BULK_TYPES_GRP.tbl_date,
66 latest_dropoff_tpdate_excep WSH_BULK_TYPES_GRP.tbl_date
67 );
68
69
70
71 --========================================================================
72 -- PROCEDURE : Create_delivery_details
73 --
74 -- PARAMETERS: p_action_prms Additional attributes needed
75 -- p_line_rec Line record
76 -- x_return_status return status
77 -- COMMENT : This API is called from the wrapper API:
78 -- WSH_BULK_PROCESS_GRP.Create_update_delivery_details
79 -- It imports the order lines into Shipping tables
80 --========================================================================
81 -- Made the parameter p_action_prms IN OUT
82 PROCEDURE Create_delivery_details(
83 p_action_prms IN OUT NOCOPY
84 WSH_BULK_TYPES_GRP.action_parameters_rectype,
85 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_rec_type,
86 x_return_status OUT NOCOPY VARCHAR2
87 );
88
89
90 --========================================================================
91 -- PROCEDURE : Extend_tables
92 --
93 -- PARAMETERS: p_line_rec Line record
94 -- p_action_prms Additional attributes needed
95 -- x_table_count Size of each table
96 -- x_additional_line_info_rec Local record that is extended
97 -- and ready to use to store additional
98 -- information for line record.
99 -- x_return_status return status
100 -- COMMENT : This procedure extends all the common table in p_line_rec
101 -- It also extends the local tables used during the import.
102 --========================================================================
103
104
105 PROCEDURE Extend_tables (
106 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
107 p_action_prms IN WSH_BULK_TYPES_GRP.action_parameters_rectype ,
108 x_table_count OUT NOCOPY NUMBER ,
109 x_additional_line_info_rec OUT NOCOPY
110 additional_line_info_rec_type ,
111 x_return_status OUT NOCOPY VARCHAR2
112 );
113
114
115
116
117 --========================================================================
118 -- PROCEDURE : Validate_lines
119 --
120 -- PARAMETERS: p_line_rec Line record
121 -- p_action_prms Additional attributes needed
122 -- x_table_count Size of each table
123 -- x_additional_line_info_rec Local record that is extended
124 -- and ready to use to store additional
125 -- information for line record.
126 -- x_valid_rec_exist set to 1, if any record was validated
127 -- successfully
128 -- x_eligible_rec_exist set to 1, if any eligible record exists.
129 -- x_return_status return status
130 -- COMMENT : This procedure goes through the tables in p_line_rec and
131 -- validates them. If the validation is successful, a 'Y' will
132 -- be set in the table p_line_rec.shipping_interfaced_flag
133 --========================================================================
134
135
136 PROCEDURE Validate_lines(
137 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
138 P_action_prms IN OUT NOCOPY
139 WSH_BULK_TYPES_GRP.action_parameters_rectype,
140 p_additional_line_info_rec IN OUT NOCOPY
141 additional_line_info_rec_type ,
142 x_valid_rec_exist OUT NOCOPY NUMBER ,
143 x_eligible_rec_exist OUT NOCOPY NUMBER ,
144 X_return_status OUT NOCOPY VARCHAR2
145 );
146
147
148 --========================================================================
149 -- PROCEDURE : bulk_insert_details
150 --
151 -- PARAMETERS: p_line_rec Line record
155 -- information for line record.
152 -- p_action_prms Additional attributes needed
153 -- p_additional_line_info_rec Local record that is extended
154 -- and ready to use to store additional
156 -- x_return_status return status
157 -- COMMENT : This procedure will bulk insert the records into tables
158 -- wsh_delivery_details and wsh_delivery_assignments_v
159 --========================================================================
160
161
162 PROCEDURE bulk_insert_details (
163 P_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
164 p_index IN NUMBER,
165 p_action_prms IN WSH_BULK_TYPES_GRP.action_parameters_rectype,
166 p_additional_line_info_rec IN additional_line_info_rec_type ,
167 X_return_status OUT NOCOPY VARCHAR2
168 );
169
170
171
172
173 --========================================================================
174 -- PROCEDURE : Set_message
175 -- PARAMETERS: p_line_rec Line record
176 -- p_index index for the line record
177 -- p_caller caller OM, PO, OKE
178 -- p_first_call pass 'T' to this API for the first time
179 -- this API is called.
180 -- X_stack_size_start this will return the fnd message
181 -- stack size, if a 'T' is passed to
182 -- the parameter p_first_call
183 -- x_return_status return status
184 -- COMMENT : This API should be called twice, once at the begin of
185 -- the validation and once at the end. If the caller is OM
186 -- this API will calculate the number of the messages that have
187 -- been added to the fnd_message stack and put this number into
188 -- table p_line_rec.error_message_count.
189 -- If the caller is not OM, then if any errors should be logged
190 -- for a certain line, this API would put one message at the begin
191 -- saying which line, header number is being processed and once
192 -- all the messages for this line has been put on the stack,
193 -- another message indicates that the validation has finished for
194 -- the line, header number.
195 --
196 --========================================================================
197
198
199 PROCEDURE Set_message(
200 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_rec_type,
201 p_index IN NUMBER,
202 p_caller IN VARCHAR2,
203 P_first_call IN VARCHAR2,
204 X_stack_size_start IN OUT NOCOPY NUMBER,
205 X_return_status OUT NOCOPY VARCHAR2
206 );
207
208 -- Added for Inbound Logistics
209 --========================================================================
210 -- PROCEDURE : validate_mandatory_info
211 --
212 -- PARAMETERS: p_line_rec IN OUT OE_WSH_BULK_GRP.line_rec_type,
213 -- p_index IN NUMBER
214 -- x_return_status return status
215 --
216 -- COMMENT : This API checks for the validity of mandatory fields like
217 -- 1.ordered_quantity
218 -- 2.order_quantity_uom
219 -- 3.organization_id
220 -- 4.po_shipment_line_id
221 -- 5.header_id
222 -- 6.line_id
223 -- 7.source_header_number
224 -- 8.source_line_number
225 -- 9.po_shipment_line_number
226 -- 10.source_header_type_id
227 -- 11.source_blanket_reference_id
228 -- 12.source_blanket_reference_num
229 -- 13.source_line_type_code
230 --
231 --========================================================================
232 PROCEDURE validate_mandatory_info(
233 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
234 p_index IN NUMBER,
235 x_return_status OUT NOCOPY VARCHAR2);
236
237 --========================================================================
238 -- PROCEDURE : get_opm_quantity
239 --
240 -- PARAMETERS: p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
241 -- p_index IN NUMBER,
242 -- x_return_status OUT NOCOPY VARCHAR2
243 --
244 -- COMMENT :
245 --========================================================================
246 /* HW OPMCONV. No need to use this routine
247 PROCEDURE get_opm_quantity(
248 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
249 p_index IN NUMBER,
250 x_return_status OUT NOCOPY VARCHAR2);
251 */--========================================================================
252 -- PROCEDURE : calc_service_mode
253 --
254 -- PARAMETERS: p_line_rec
255 -- p_cache_tbl used to store the cache info
256 -- p_cache_ext_tbl used to store the cache info
257 -- p_index current index for tables in
258 -- p_additional_line_info_rec
259 -- p_additional_line_info_rec
260 -- x_return_status return status
261 -- COMMENT : The service_level and mode_of_transport is calculated and
262 -- populated to the p_additional_line_info_rec.mode_of_transport
263 -- and p_additional_line_info_rec.service_level tables.
264 -- The index of these tables are cached, so that the for the same
265 -- ship_method_code these information is reused.
266 --========================================================================
267 PROCEDURE calc_service_mode(
268 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
269 p_cache_tbl IN OUT NOCOPY
270 wsh_util_core.char500_tab_type,
271 p_cache_ext_tbl IN OUT NOCOPY
272 wsh_util_core.char500_tab_type,
273 p_index IN NUMBER,
274 p_additional_line_info_rec IN OUT NOCOPY
275 additional_line_info_rec_type, x_return_status OUT NOCOPY VARCHAR2);
276
277 END WSH_BULK_PROCESS_PVT;