1 PACKAGE BODY PO_SHIPMENTS_SV5 as
2 /* $Header: POXPOS5B.pls 120.1 2005/07/19 14:41:56 pbamb noship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: val_source_line_num
7
8 ===========================================================================*/
9 PROCEDURE val_source_line_num
10 (X_entity_level IN VARCHAR2,
11 X_po_line_id IN NUMBER,
12 X_line_location_id IN NUMBER,
13 X_shipment_type IN VARCHAR2,
14 X_item_id IN NUMBER,
15 X_inventory_organization_id IN NUMBER,
16 X_line_type_id IN NUMBER,
17 X_quantity_ordered IN OUT NOCOPY NUMBER,
18 X_line_type IN OUT NOCOPY VARCHAR2,
19 X_outside_operation_flag IN OUT NOCOPY VARCHAR2,
20 X_receiving_flag IN OUT NOCOPY VARCHAR2,
21 X_planned_item_flag IN OUT NOCOPY VARCHAR2,
22 X_outside_op_uom_type IN OUT NOCOPY VARCHAR2,
23 X_invoice_close_tolerance IN OUT NOCOPY NUMBER,
24 X_receive_close_tolerance IN OUT NOCOPY NUMBER,
25 X_receipt_required_flag IN OUT NOCOPY VARCHAR2,
26 X_stock_enabled_flag IN OUT NOCOPY VARCHAR2,
27 X_total_line_quantity IN OUT NOCOPY NUMBER) IS
28
29 X_progress VARCHAR2(3) := '';
30 X_val_sched_released_qty VARCHAR2(1) := '';
31
32 X_outside_op_flag_msi VARCHAR2(1) := ''; -- get item place holder
33 X_internal_orderable VARCHAR2(1) := ''; -- get item place holder
34 X_purchasing_enabled VARCHAR2(1) := ''; -- get item place holder
35 X_inventory_asset_flag VARCHAR2(1) := ''; -- get item place holder
36
37 X_receipt_required_flag_temp VARCHAR2(1);
38
39 X_source_shipment_id NUMBER := ''; -- only relevant if entity is SHIP
40 X_planned_qty_ordered NUMBER := '';
41 X_quantity_released NUMBER := '';
42 X_receipt_close_tolerance_tmp NUMBER := '';
43
44 --<INVCONV R12 START>
45 X_secondary_default_ind MTL_SYSTEM_ITEMS.SECONDARY_DEFAULT_IND%TYPE;
46 X_grade_control_flag MTL_SYSTEM_ITEMS.GRADE_CONTROL_FLAG%TYPE;
47 X_secondary_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
48 --<INVCONV R12 END>
49
50 BEGIN
51
52 --dbms_output.put_line('010');
53 X_progress := '010';
54 /*
55 ** If the quantity ordered is less than the quantity released to date
56 ** for all scheduled releases against the planned shipment, display a
57 ** message that they have over released.
58 */
59 IF (X_shipment_type = 'SCHEDULED') THEN
60
61 SELECT quantity
62 INTO X_planned_qty_ordered
63 FROM po_lines
64 WHERE po_line_id = X_po_line_id;
65
66 SELECT sum(quantity - nvl(quantity_cancelled,0))
67 INTO X_quantity_released
68 FROM po_line_locations
69 WHERE po_line_id = X_po_line_id
70 AND shipment_type = 'SCHEDULED';
71
72 IF (X_planned_qty_ordered = X_quantity_released) THEN
73 po_message_s.app_error('PO_PO_ALL_SHIP_RELEASED');
74 END IF;
75
76 END IF;
77
78 /* Get the Total Line Quantity that can still be released */
79 IF X_shipment_type = 'SCHEDULED' then
80
81 --dbms_output.put_line('020');
82 X_progress := '020';
83
84 SELECT nvl(sum(nvl(quantity,0) - nvl(quantity_cancelled, 0)),0)
85 INTO X_total_line_quantity
86 FROM po_line_locations
87 WHERE po_line_id = X_po_line_id
88 AND shipment_type = 'SCHEDULED';
89
90 ELSIF (X_shipment_type = 'PLANNED') THEN
91
92 --dbms_output.put_line('030');
93 X_progress := '030';
94
95 SELECT sum(quantity - nvl(quantity_cancelled, 0))
96 INTO X_total_line_quantity
97 FROM po_line_locations
98 WHERE po_line_id = X_po_line_id
99 AND shipment_type <> 'PRICE BREAK';
100
101 END IF;
102
103 --dbms_output.put_line('040');
104 X_progress := '040';
105 /*
106 ** DEBUG. Call the routine to get the line type information
107 */
108 SELECT line_type,
109 nvl(outside_operation_flag,'N'),
110 receiving_flag,
111 receipt_close
112 INTO X_line_type,
113 X_outside_operation_flag,
114 X_receipt_required_flag_temp,
115 X_receipt_close_tolerance_tmp
116 FROM po_line_types_v
117 WHERE line_type_id = X_line_type_id;
118
119
120 /*
121 ** Call the routine to get the item information
122 ** If the item id is NOT NULL.
123 ** We do not use the OUTSIDE_OPERATION_FLAG from
124 ** MSI here. It is here as the procedure happens
125 ** to have it as a formal IN OUT parameter.
126 */
127 --dbms_output.put_line('050');
128 X_progress := '050';
129
130 If X_item_id is NOT NULL then
131
132 po_items_sv2.get_item_details(
133 X_item_id ,
134 X_inventory_organization_id ,
135 X_planned_item_flag ,
136 X_outside_op_flag_msi ,
137 X_outside_op_uom_type ,
138 X_invoice_close_tolerance ,
139 X_receive_close_tolerance ,
140 X_receipt_required_flag ,
141 X_stock_enabled_flag ,
142 X_internal_orderable ,
143 X_purchasing_enabled ,
144 X_inventory_asset_flag ,
145 --<INVCONV R12 START>
146 X_secondary_default_ind,
147 X_grade_control_flag,
148 X_secondary_unit_of_measure ) ;
149 --<INVCONV R12 END>
150 end if;
151
152 /*
153 ** Set the correct value of receipt_required_flag.
154 ** The receipt required flag value on the Item overrides
155 ** the value defined at the LINE TYPES level as long as
156 ** it is NOT NULL
157 */
158 --dbms_output.put_line('060');
159 if X_receipt_required_flag is NULL then
160 X_receipt_required_flag := X_receipt_required_flag_temp;
161 end if;
162
163 -- Bug: 1322342 set the correct value of receive close tolerance also.
164
165 if X_receive_close_tolerance is NULL then
166 X_receive_close_tolerance := X_receipt_close_tolerance_tmp;
167 end if;
168
169
170 EXCEPTION
171 when others then
172 --dbms_output.put_line('In exception');
173 po_message_s.sql_error('val_source_line_num', X_progress, sqlcode);
174 raise;
175 END val_source_line_num;
176
177
178 /*===========================================================================
179
180 PROCEDURE NAME: val_source_ship_num
181
182 ===========================================================================*/
183 PROCEDURE val_source_ship_num
184 (X_entity_level IN VARCHAR2,
185 X_set_of_books_id IN NUMBER,
186 X_line_id IN NUMBER,
187 X_line_location_id IN NUMBER,
188 X_shipment_type IN VARCHAR2,
189 X_quantity_ordered IN NUMBER,
190 X_source_shipment_id IN NUMBER,
191 X_ship_to_location_code IN OUT NOCOPY VARCHAR2,
192 X_ship_to_location_id IN OUT NOCOPY NUMBER,
193 X_ship_to_org_code IN OUT NOCOPY VARCHAR2,
194 X_ship_to_organization_id IN OUT NOCOPY NUMBER,
195 X_quantity IN OUT NOCOPY NUMBER,
196 X_price_override IN OUT NOCOPY NUMBER,
197 X_promised_date IN OUT NOCOPY DATE,
198 X_need_by_date IN OUT NOCOPY DATE,
199 X_taxable_flag IN OUT NOCOPY VARCHAR2,
200 X_tax_name IN OUT NOCOPY VARCHAR2,
201 X_enforce_ship_to_location IN OUT NOCOPY VARCHAR2,
202 X_allow_substitute_receipts IN OUT NOCOPY VARCHAR2,
203 X_receiving_routing_id IN OUT NOCOPY NUMBER ,
204 X_qty_rcv_tolerance IN OUT NOCOPY NUMBER ,
205 X_qty_rcv_exception_code IN OUT NOCOPY VARCHAR2 ,
206 X_days_early_receipt_allowed IN OUT NOCOPY NUMBER ,
207 X_last_accept_date IN OUT NOCOPY DATE,
208 X_days_late_receipt_allowed IN OUT NOCOPY NUMBER ,
209 X_receipt_days_exception_code IN OUT NOCOPY VARCHAR2 ,
210 X_invoice_close_tolerance IN OUT NOCOPY NUMBER,
211 X_receive_close_tolerance IN OUT NOCOPY NUMBER,
212 X_accrue_on_receipt_flag IN OUT NOCOPY VARCHAR2,
213 X_receipt_required_flag IN OUT NOCOPY VARCHAR2,
214 X_inspection_required_flag IN OUT NOCOPY VARCHAR2,
215 X_val_sched_released_qty IN OUT NOCOPY VARCHAR2) IS
216
217 X_progress VARCHAR2(3) := '';
218
219 BEGIN
220
221 /*
222 ** If the quantity ordered is less than the quantity released to date
223 ** for all scheduled releases against the planned shipment, display a
224 ** message that they have over released.
225 */
226 IF po_shipments_sv1.val_sched_released_qty
227 ('SHIPMENT',
228 X_line_id,
229 X_line_location_id,
230 'SCHEDULED',
231 X_quantity_ordered,
232 X_source_shipment_id ) THEN
233
234 X_val_sched_released_qty := 'Y';
235
236 ELSE
237 X_val_sched_released_qty := 'N';
238
239 END IF;
240
241 /*
242 ** Call the routine to get the information from the source
243 ** planned shipment.
244 */
245 po_shipments_sv1.get_planned_ship_info (
246 X_source_shipment_id,
247 X_set_of_books_id,
248 X_ship_to_location_code,
249 X_ship_to_location_id,
250 X_ship_to_org_code,
251 X_ship_to_organization_id,
252 X_quantity,
253 X_price_override,
254 X_promised_date,
255 X_need_by_date,
256 X_taxable_flag,
257 X_tax_name,
258 X_enforce_ship_to_location ,
259 X_allow_substitute_receipts ,
260 X_receiving_routing_id ,
261 X_qty_rcv_tolerance ,
262 X_qty_rcv_exception_code ,
263 X_days_early_receipt_allowed ,
264 X_last_accept_date ,
265 X_days_late_receipt_allowed ,
266 X_receipt_days_exception_code ,
267 X_invoice_close_tolerance,
268 X_receive_close_tolerance,
269 X_accrue_on_receipt_flag,
270 X_receipt_required_flag,
271 X_inspection_required_flag);
272
273
274 EXCEPTION
275 when others then
276 --dbms_output.put_line('In exception');
277 po_message_s.sql_error('source_ship_server_cover', X_progress, sqlcode);
278 raise;
279 END val_source_ship_num;
280
281
282
283
284 END PO_SHIPMENTS_SV5;