1 PACKAGE BODY RCV_CORRECTION_SV AS
2 /* $Header: RCVTXCOB.pls 115.5 2004/03/19 03:03:31 wkunz ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: post_query()
7
8 ===========================================================================*/
9
10 PROCEDURE POST_QUERY ( x_transaction_id IN NUMBER,
11 x_receipt_source_code IN VARCHAR2,
12 x_organization_id IN NUMBER,
13 x_hazard_class_id IN NUMBER,
14 x_un_number_id IN NUMBER,
15 x_shipment_line_id IN NUMBER,
16 x_po_line_location_id IN NUMBER,
17 x_rma_line_id IN NUMBER,
18 x_parent_transaction_type IN VARCHAR2,
19 x_grand_parent_id IN NUMBER,
20
21 x_source_document_code IN VARCHAR2,
22 x_destination_type_code IN VARCHAR2,
23 x_lpn_id IN VARCHAR2,
24 x_transfer_lpn_id IN VARCHAR2,
25 x_po_type IN VARCHAR2,
26
27 x_hazard_class OUT NOCOPY VARCHAR2,
28 x_un_number OUT NOCOPY VARCHAR2,
29 x_max_positive_qty IN OUT NOCOPY NUMBER,
30 x_max_negative_qty IN OUT NOCOPY NUMBER ,
31 x_max_tolerable_qty IN OUT NOCOPY NUMBER,
32 x_packing_slip OUT NOCOPY VARCHAR2,
33 x_max_supply_qty OUT NOCOPY NUMBER,
34
35 x_parent_transaction_type_dsp OUT NOCOPY VARCHAR2,
36 x_destination_type_dsp OUT NOCOPY VARCHAR2,
37 x_license_plate_number OUT NOCOPY VARCHAR2,
38 x_transfer_license_plate_num OUT NOCOPY VARCHAR2,
39
40 x_ordered_uom IN OUT NOCOPY VARCHAR2,
41 x_secondary_ordered_uom IN OUT NOCOPY VARCHAR2,
42 x_order_type IN OUT NOCOPY VARCHAR2
43 )
44 IS
45
46 cursor get_po_lookup_code(p_lookup_type in varchar2,p_lookup_code in varchar2) is
47 select displayed_field
48 from po_lookup_codes
49 where lookup_type = p_lookup_type
50 and lookup_code = p_lookup_code
51 AND ROWNUM<=1;
52
53 cursor get_uom_class(p_uom in varchar2) is
54 select uom_class
55 from mtl_units_of_measure
56 where unit_of_measure = p_uom
57 AND ROWNUM<=1;
58
59 cursor get_uom(p_uom_code in varchar2) is
60 select unit_of_measure
61 from mtl_units_of_measure
62 where uom_code = p_uom_code
63 AND ROWNUM<=1;
64
65 cursor get_license_plate_number(p_license_plate_id in varchar2) is
66 select LICENSE_PLATE_NUMBER
67 from WMS_LICENSE_PLATE_NUMBERS
68 where LPN_ID = p_license_plate_id
69 AND ROWNUM<=1;
70
71 x_progress VARCHAR2(3) := NULL;
72 unit_of_measure VARCHAR2(25);
73 tolerable_qty NUMBER := 0;
74 grand_parent_id NUMBER := 0;
75
76 /*Bug 1548597 */
77 x_secondary_available_qty NUMBER := 0;
78
79 BEGIN
80
81 if (x_parent_transaction_type is not null) then
82 open get_po_lookup_code('RCV TRANSACTION TYPE',x_parent_transaction_type);
83 fetch get_po_lookup_code into x_parent_transaction_type_dsp;
84 close get_po_lookup_code;
85 end if;
86
87 if (x_destination_type_code is not null) then
88 open get_po_lookup_code('RCV DESTINATION TYPE',x_destination_type_code);
89 fetch get_po_lookup_code into x_destination_type_dsp;
90 close get_po_lookup_code;
91 end if;
92
93 if (x_lpn_id is not null) then
94 open get_license_plate_number(x_lpn_id);
95 fetch get_license_plate_number into x_license_plate_number;
96 close get_license_plate_number;
97 end if;
98
99 if (x_transfer_lpn_id is not null) then
100 open get_license_plate_number(x_transfer_lpn_id);
101 fetch get_license_plate_number into x_transfer_license_plate_num;
102 close get_license_plate_number;
103 end if;
104
105 if (x_source_document_code = 'RMA') then
106 if (x_ordered_uom is not null) then
107 open get_uom(x_ordered_uom);
108 fetch get_uom into x_ordered_uom;
109 close get_uom;
110 end if;
111
112 if (x_secondary_ordered_uom is not null) then
113 open get_uom(x_secondary_ordered_uom);
114 fetch get_uom into x_secondary_ordered_uom;
115 close get_uom;
116 end if;
117 else --x_source_document_code <> 'RMA'
118 if (x_po_type is not null) then
119 if (x_source_document_code = 'PO') then
120 open get_po_lookup_code('PO TYPE',x_po_type);
121 else
122 open get_po_lookup_code('SHIPMENT SOURCE TYPE',x_po_type);
123 end if;
124 fetch get_po_lookup_code into x_order_type;
125 close get_po_lookup_code;
126 end if;
127 end if; --x_source_document_code = 'RMA'
128
129
130
131
132 /*
133 ** Get the max negative quantity
134 */
135 /*Bug 1548597 */
136 RCV_QUANTITIES_S.GET_AVAILABLE_QUANTITY ( 'CORRECT',
137 x_transaction_id,
138 x_receipt_source_code,
139 x_parent_transaction_type,
140 null,
141 'NEGATIVE',
142 x_max_negative_qty,
143 tolerable_qty,
144 unit_of_measure,
145 x_secondary_available_qty);
146 /*
147 ** For a transaction, the max supply quantity is the x_max_negative_quantity
148 */
149 x_max_supply_qty := x_max_negative_qty;
150
151 /*
152 ** Get the max positive quantity.
153 ** The grand_parent_id is either the po_line_location_id,
154 ** rcv_shipment_line_id or the x_grand_parent_id based
155 ** on the parent transaction type and the receipt source
156 ** code.
157 */
158
159 IF (x_parent_transaction_type not in ('RECEIVE', 'MATCH', 'UNORDERED')) THEN
160
161 /*
162 ** the grand parent transaction has to be a receiving transaction.
163 ** Hence, grand_parent_id = x_grand_parent_id
164 */
165
166 grand_parent_id := x_grand_parent_id;
167
168 ELSIF (x_parent_transaction_type = 'MATCH') THEN
169
170 /*
171 ** This is the same as a vendor receipt. Hence,
172 ** the grand_parent_id should be the po_line_location_id
173 */
174
175 IF (x_receipt_source_code = 'CUSTOMER') THEN
176 grand_parent_id := x_rma_line_id;
177 ELSE
178 grand_parent_id := x_po_line_location_id;
179 END IF;
180
181 ELSIF (x_parent_transaction_type = 'UNORDERED') THEN
182
183 /*
184 ** Since the unordered receipt is only backed by the
185 ** receipt transaction use the transaction_id as the
186 ** grandparent to get an infinite open ended positive
187 ** correction quantity
188 */
189
190 grand_parent_id := x_transaction_id;
191
192 ELSIF (x_parent_transaction_type = 'RECEIVE') THEN
193
194 /*
195 ** Depending on the receipt_source_code, the grand_parent_id
196 ** is either the po_line_location_id (for Vendor receipts) or
197 ** the rcv_shipment_line_id (for Internal receipts).
198 */
199
200 IF (x_receipt_source_code = 'VENDOR') THEN
201
202 grand_parent_id := x_po_line_location_id;
203
204 ELSIF (x_receipt_source_code = 'CUSTOMER') THEN
205 grand_parent_id := x_rma_line_id;
206
207 ELSE
208
209 grand_parent_id := x_shipment_line_id;
210
211 END IF;
212
213 END IF;
214
215 /*
216 ** Get the max positive quantity
217 */
218 /*Bug 1548597 */
219 RCV_QUANTITIES_S.GET_AVAILABLE_QUANTITY ( 'CORRECT',
220 x_transaction_id,
221 x_receipt_source_code,
222 x_parent_transaction_type,
223 grand_parent_id,
224 'POSITIVE',
225 x_max_positive_qty,
226 x_max_tolerable_qty,
227 unit_of_measure,
228 x_secondary_available_qty);
229
230 /*
231 ** Get the hazard class information if the hazard class id is
232 ** not null
233 */
234
235 IF (x_hazard_class_id is NOT NULL) THEN
236
237 x_progress := 10;
238
239 SELECT hazard_class
240 INTO x_hazard_class
241 FROM po_hazard_classes
242 WHERE hazard_class_id = x_hazard_class_id;
243
244 END IF;
245
246 /*
247 ** Get the UN Number info if the un number id is not null
248 */
249
250 IF (x_un_number_id is NOT NULL) THEN
251
252 x_progress := 20;
253
254 SELECT un_number
255 INTO x_un_number
256 FROM po_un_numbers
257 WHERE un_number_id = x_un_number_id;
258
259 END IF;
260
261 /*
262 * BUG NO 782779.
263 * We select the packing_slip for the block rcv_transaction
264 * in the Enter Corrections form from the rcv_shipment_lines.
265 */
266 SELECT nvl(packing_slip,' ')
267 INTO x_packing_slip
268 FROM rcv_shipment_lines
269 WHERE shipment_line_id = x_shipment_line_id;
270
271 EXCEPTION
272
273 WHEN OTHERS THEN
274 po_message_s.sql_error('post_query', x_progress, sqlcode);
275 RAISE;
276
277 END post_query;
278
279
280 END RCV_CORRECTION_SV;
[Home] [Help]