1 PACKAGE BODY rcv_rma_headers
2 /* $Header: RCVRMAHB.pls 120.3 2006/04/20 16:27:30 pjiang noship $ */
3 AS
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
6
7 /*===========================================================================+
8 | |
9 | PROCEDURE NAME: derive_rma_header() |
10 | |
11 +===========================================================================*/
12 PROCEDURE derive_rma_header(
13 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
14 ) IS
15 BEGIN
16 IF (g_asn_debug = 'Y') THEN
17 asn_debug.put_line('In derive_rma_header');
18 END IF;
19
20 rcv_roi_header_common.derive_ship_to_org_info(p_header_record);
21 rcv_roi_header_common.derive_from_org_info(p_header_record);
22 rcv_roi_header_common.derive_location_info(p_header_record);
23 rcv_roi_header_common.derive_payment_terms_info(p_header_record);
24 rcv_roi_header_common.derive_receiver_info(p_header_record);
25 derive_customer_info(p_header_record);
26 derive_customer_site_info(p_header_record);
27
28 IF (g_asn_debug = 'Y') THEN
29 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
30 asn_debug.put_line('Error in derive_rma_header');
31 asn_debug.put_line('status = ' || p_header_record.error_record.error_status);
32 asn_debug.put_line('message = ' || p_header_record.error_record.error_message);
33 END IF;
34
35 asn_debug.put_line('Done derive_rma_header');
36 END IF;
37 END derive_rma_header;
38
39 /*===========================================================================+
40 | |
41 | PROCEDURE NAME: default_rma_header() |
42 | |
43 +===========================================================================*/
44 PROCEDURE default_rma_header(
45 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
46 ) IS
47 BEGIN
48 IF (g_asn_debug = 'Y') THEN
49 asn_debug.put_line('In default_rma_header');
50 END IF;
51
52 default_customer_info(p_header_record);
53 default_customer_site_info(p_header_record);
54 default_trx_info(p_header_record);
55 rcv_roi_header_common.default_last_update_info(p_header_record);
56 rcv_roi_header_common.default_creation_info(p_header_record);
57 rcv_roi_header_common.default_asn_type(p_header_record);
58 default_shipment_num(p_header_record);
59 rcv_roi_header_common.default_shipment_header_id(p_header_record);
60 rcv_roi_header_common.default_receipt_info(p_header_record);
61 rcv_roi_header_common.default_ship_to_location_info(p_header_record);
62
63 IF (g_asn_debug = 'Y') THEN
64 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
65 asn_debug.put_line('Error in default_rma_header');
66 asn_debug.put_line('status = ' || p_header_record.error_record.error_status);
67 asn_debug.put_line('message = ' || p_header_record.error_record.error_message);
68 END IF;
69
70 asn_debug.put_line('Out of default_rma_header');
71 END IF;
72 END default_rma_header;
73
74 /*===========================================================================+
75 | |
76 | PROCEDURE NAME: validate_rma_header() |
77 | |
78 +===========================================================================*/
79 PROCEDURE validate_rma_header(
80 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
81 ) IS
82 BEGIN
83 IF (g_asn_debug = 'Y') THEN
84 asn_debug.put_line('In validate_rma_header');
85 END IF;
86
87 validate_receipt_source_code(p_header_record);
88 validate_customer_info(p_header_record);
89 validate_customer_site_info(p_header_record);
90 rcv_roi_header_common.validate_trx_type(p_header_record);
91 rcv_roi_header_common.validate_expected_receipt_date(p_header_record);
92 rcv_roi_header_common.validate_receipt_num(p_header_record);
93 rcv_roi_header_common.validate_ship_to_org_info(p_header_record);
94 rcv_roi_header_common.validate_from_org_info(p_header_record);
95 rcv_roi_header_common.validate_location_info(p_header_record);
96 rcv_roi_header_common.validate_payment_terms_info(p_header_record);
97 rcv_roi_header_common.validate_receiver_info(p_header_record);
98 rcv_roi_header_common.validate_freight_carrier_info(p_header_record);
99
100 IF (g_asn_debug = 'Y') THEN
101 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
102 asn_debug.put_line('Error in validate_rma_header');
103 asn_debug.put_line('status = ' || p_header_record.error_record.error_status);
104 asn_debug.put_line('message = ' || p_header_record.error_record.error_message);
105 END IF;
106
107 asn_debug.put_line('Out of validate_rma_header');
108 END IF;
109 END validate_rma_header;
110
111 PROCEDURE insert_rma_header(
112 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
113 ) IS
114 x_sysdate DATE := SYSDATE;
115 BEGIN
116 -- Set asn_type to null if asn_type is STD as the UI gets confused
117
118 IF NVL(p_header_record.header_record.asn_type, 'STD') = 'STD' THEN
119 p_header_record.header_record.asn_type := NULL;
120 END IF;
121
122 /* Bug - 1086088 - Ship_to_org_id needs to get populated in the
123 * RCV_SHIPMENT_HEADERS table */
124 INSERT INTO rcv_shipment_headers
125 (shipment_header_id,
126 last_update_date,
127 last_updated_by,
128 creation_date,
129 created_by,
130 last_update_login,
131 receipt_source_code,
132 vendor_id,
133 vendor_site_id,
134 organization_id,
135 shipment_num,
136 receipt_num,
137 ship_to_location_id,
138 ship_to_org_id,
139 bill_of_lading,
140 packing_slip,
141 shipped_date,
142 freight_carrier_code,
143 expected_receipt_date,
144 employee_id,
145 num_of_containers,
146 waybill_airbill_num,
147 comments,
148 attribute_category,
149 attribute1,
150 attribute2,
151 attribute3,
152 attribute4,
153 attribute5,
154 attribute6,
155 attribute7,
156 attribute8,
157 attribute9,
158 attribute10,
159 attribute11,
160 attribute12,
161 attribute13,
162 attribute14,
163 attribute15,
164 ussgl_transaction_code,
165 government_context,
166 request_id,
167 program_application_id,
168 program_id,
169 program_update_date,
170 asn_type,
171 edi_control_num,
172 notice_creation_date,
173 gross_weight,
174 gross_weight_uom_code,
175 net_weight,
176 net_weight_uom_code,
177 tar_weight,
178 tar_weight_uom_code,
179 packaging_code,
180 carrier_method,
181 carrier_equipment,
182 carrier_equipment_num,
183 carrier_equipment_alpha,
184 special_handling_code,
185 hazard_code,
186 hazard_class,
187 hazard_description,
188 freight_terms,
189 freight_bill_number,
190 invoice_date,
191 invoice_amount,
192 tax_name,
193 tax_amount,
194 freight_amount,
195 invoice_status_code,
196 asn_status,
197 currency_code,
198 conversion_rate_type,
199 conversion_rate,
200 conversion_date,
201 payment_terms_id,
202 invoice_num,
203 customer_id,
204 customer_site_id,
205 ship_from_location_id
206 )
207 VALUES (p_header_record.header_record.receipt_header_id,
208 p_header_record.header_record.last_update_date,
209 p_header_record.header_record.last_updated_by,
210 p_header_record.header_record.creation_date,
211 p_header_record.header_record.created_by,
212 p_header_record.header_record.last_update_login,
213 p_header_record.header_record.receipt_source_code,
214 p_header_record.header_record.vendor_id,
215 p_header_record.header_record.vendor_site_id,
216 p_header_record.header_record.ship_to_organization_id,
217 p_header_record.header_record.shipment_num,
218 p_header_record.header_record.receipt_num,
219 p_header_record.header_record.location_id,
220 p_header_record.header_record.ship_to_organization_id,
221 p_header_record.header_record.bill_of_lading,
222 p_header_record.header_record.packing_slip,
223 p_header_record.header_record.shipped_date,
224 p_header_record.header_record.freight_carrier_code,
225 p_header_record.header_record.expected_receipt_date,
226 p_header_record.header_record.employee_id,
227 p_header_record.header_record.num_of_containers,
228 p_header_record.header_record.waybill_airbill_num,
229 p_header_record.header_record.comments,
230 p_header_record.header_record.attribute_category,
231 p_header_record.header_record.attribute1,
232 p_header_record.header_record.attribute2,
233 p_header_record.header_record.attribute3,
234 p_header_record.header_record.attribute4,
235 p_header_record.header_record.attribute5,
236 p_header_record.header_record.attribute6,
237 p_header_record.header_record.attribute7,
238 p_header_record.header_record.attribute8,
239 p_header_record.header_record.attribute9,
240 p_header_record.header_record.attribute10,
241 p_header_record.header_record.attribute11,
242 p_header_record.header_record.attribute12,
243 p_header_record.header_record.attribute13,
244 p_header_record.header_record.attribute14,
245 p_header_record.header_record.attribute15,
246 p_header_record.header_record.usggl_transaction_code,
247 NULL, -- p_header_record.header_record.Government_Context
248 fnd_global.conc_request_id,
249 fnd_global.prog_appl_id,
250 fnd_global.conc_program_id,
251 x_sysdate,
252 p_header_record.header_record.asn_type,
253 p_header_record.header_record.edi_control_num,
254 p_header_record.header_record.notice_creation_date,
255 p_header_record.header_record.gross_weight,
256 p_header_record.header_record.gross_weight_uom_code,
257 p_header_record.header_record.net_weight,
258 p_header_record.header_record.net_weight_uom_code,
259 p_header_record.header_record.tar_weight,
260 p_header_record.header_record.tar_weight_uom_code,
261 p_header_record.header_record.packaging_code,
262 p_header_record.header_record.carrier_method,
263 p_header_record.header_record.carrier_equipment,
264 NULL, -- p_header_record.header_record.Carrier_Equipment_Num
265 NULL, -- p_header_record.header_record.Carrier_Equipment_Alpha
266 p_header_record.header_record.special_handling_code,
267 p_header_record.header_record.hazard_code,
268 p_header_record.header_record.hazard_class,
269 p_header_record.header_record.hazard_description,
270 p_header_record.header_record.freight_terms,
271 p_header_record.header_record.freight_bill_number,
272 p_header_record.header_record.invoice_date,
273 p_header_record.header_record.total_invoice_amount,
274 p_header_record.header_record.tax_name,
275 p_header_record.header_record.tax_amount,
276 p_header_record.header_record.freight_amount,
277 p_header_record.header_record.invoice_status_code,
278 'NEW_SHIP', -- p_header_record.header_record.Asn_Status
279 p_header_record.header_record.currency_code,
280 p_header_record.header_record.conversion_rate_type,
281 p_header_record.header_record.conversion_rate,
282 p_header_record.header_record.conversion_rate_date,
283 p_header_record.header_record.payment_terms_id,
284 p_header_record.header_record.invoice_num,
285 p_header_record.header_record.customer_id,
286 p_header_record.header_record.customer_site_id,
287 p_header_record.header_record.ship_from_location_id
288 );
289 EXCEPTION
290 WHEN OTHERS THEN
291 rcv_error_pkg.set_sql_error_message('insert_rma_header', '000');
292 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
293 p_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
294 END insert_rma_header;
295
296 /* Private helper procedures */
297 PROCEDURE derive_customer_info(
298 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
299 ) IS
300 BEGIN
301 IF p_header_record.error_record.error_status IN('S', 'W')
302 AND p_header_record.header_record.customer_id IS NULL THEN
303 -- derive customer name from customer id
304 IF p_header_record.header_record.customer_party_name IS NULL
305 AND p_header_record.header_record.customer_id IS NOT NULL THEN
306 SELECT party.party_name
307 INTO p_header_record.header_record.customer_party_name
308 FROM hz_parties party,
309 hz_cust_accounts acct
310 WHERE acct.party_id = party.party_id
311 AND acct.cust_account_id = p_header_record.header_record.customer_id;
312 END IF;
313
314 -- derive customer id from customer name and account number
315 /* Bug 3648886.
316 * The sql below had the where condition as
317 * AND party.party_name = party_name
318 * AND acct.account_number = account_number;
319 * This needs to be
320 * AND party.party_name = p_header_record.header_record.customer_party_name
321 * AND acct.account_number = p_header_record.header_record.customer_account_number;
322 * This will give error and also there was a performance problem.
323 */
324 IF p_header_record.header_record.customer_id IS NULL
325 AND p_header_record.header_record.customer_account_number IS NOT NULL
326 AND p_header_record.header_record.customer_party_name IS NOT NULL THEN
327 SELECT acct.cust_account_id
328 INTO p_header_record.header_record.customer_id
329 FROM hz_parties party,
330 hz_cust_accounts acct
331 WHERE party.party_id = acct.party_id
332 AND party.party_name = p_header_record.header_record.customer_party_name
333 AND acct.account_number = p_header_record.header_record.customer_account_number;
334 END IF;
335 END IF;
336 EXCEPTION
337 WHEN OTHERS THEN
338 rcv_error_pkg.set_sql_error_message('derive_customer_info', '000');
339 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
340 p_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
341 END derive_customer_info;
342
343 /* We do not insert customer_site_id now from forms */
344 /* customer_site_id = from_org_id */
345 PROCEDURE derive_customer_site_info(
346 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
347 ) IS
348 BEGIN
349 NULL;
350 END derive_customer_site_info;
351
352 PROCEDURE default_customer_info(
353 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
354 ) IS
355 BEGIN
356 NULL;
357 -- For RMA, we can default customer_id which is actually the cust_account_id in hz_cust_accounts.
358 -- If party_name is given and for that party_id, if there exists only one account_id in the accounts table
359 -- then we can default that value.
360 -- If both party_id and party_name is given, then we consider only party_id. If party_id has a wrong value but party_name has a correct value what do we do? Ask PM. In validate
361 -- we use all the values for vendors .Do we need to do the same here also?
362 -- Then it does not make sense to use party_name since even if we derive it will fail validation later when we use both party_id and party_name value.
363
364
365 -- If (customer_id is null and (party_id is not null) then
366 -- select count(*) into l_count
367 -- from hz_cust_accounts acct
368 -- where acct.party_id = acct.party_id;
369
370 -- If (l_count = 1) then /* There is only one acct for this party hence default*/
371 -- select acct.cust_account_id
372 -- from hz_cust_accounts acct
373 -- where acct.party_id = party_id;
374 -- end if;
375 -- end if;
376
377 -- If (customer_id is null and (party_name is not null) then
378 -- select count(*)
379 -- from hz_parties party, hz_cust_accounts acct
380 -- where acct.party_id = party.party_id and
381 -- party.party_name = party_name;
382
383 -- If (l_count = 1) then /* There is only one acct for this party hence default*/
384 -- select acct.cust_account_id
385 -- from hz_parties party, hz_cust_accounts acct
386 -- where acct.party_id = party.party_id and
387 -- party.party_name = party_name;
388 -- end if;
389
390 -- end if;
391 END default_customer_info;
392
393 PROCEDURE default_customer_site_info(
394 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
395 ) IS
396 BEGIN
397 IF ( p_header_record.header_record.customer_site_id IS NULL
398 AND p_header_record.header_record.from_organization_id IS NOT NULL) THEN
399 p_header_record.header_record.customer_site_id := p_header_record.header_record.from_organization_id;
400
401 IF (g_asn_debug = 'Y') THEN
402 asn_debug.put_line('defaulted customer_site_id');
403 END IF;
404 END IF;
405 END default_customer_site_info;
406
407 PROCEDURE default_trx_info(
408 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
409 ) IS
410 BEGIN
411 IF p_header_record.header_record.transaction_type IS NULL THEN
412 p_header_record.header_record.transaction_type := 'NEW';
413
414 IF (g_asn_debug = 'Y') THEN
415 asn_debug.put_line('defaulted transaction_type');
416 END IF;
417 END IF;
418 END default_trx_info;
419
420 PROCEDURE default_shipment_num(
421 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
422 ) IS
423 BEGIN
424 /* SHIPMENT NUMBER FOR ASBN/ASN if shipment_num IS NULL */
425 /* First choice for ASN/ Second Choice for ASN */
426 IF p_header_record.header_record.shipment_num IS NULL THEN
427 p_header_record.header_record.shipment_num := p_header_record.header_record.packing_slip;
428
429 IF (g_asn_debug = 'Y') THEN
430 asn_debug.put_line('defaulted shipment number');
431 END IF;
432 END IF;
433 END default_shipment_num;
434
435 PROCEDURE validate_receipt_source_code(
436 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
437 ) IS
438 BEGIN
439 -- validate that the receipt source code is CUSTOMER
440 -- and that the txn type and asn type makes sense
441 -- Do we need to do this?
442 NULL;
443 END validate_receipt_source_code;
444
445 PROCEDURE validate_customer_info(
446 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
447 ) IS
448 l_status hz_cust_accounts.status%TYPE;
449 l_validation_failed BOOLEAN; /*Bug 4344351*/
450 BEGIN
451 l_validation_failed := FALSE;
452
453 IF p_header_record.header_record.customer_id IS NOT NULL THEN
454
455 SELECT status
456 INTO l_status
457 FROM hz_cust_accounts acct
458 WHERE acct.cust_account_id = p_header_record.header_record.customer_id;
459
460 IF l_status <> 'A' THEN
461 IF (g_asn_debug = 'Y') THEN
462 asn_debug.put_line('Customer status is not ACTIVE');
463 END IF;
464 l_validation_failed := TRUE;
465 END IF;
466 ELSE
467 IF (g_asn_debug = 'Y') THEN
468 asn_debug.put_line('Customer_id is null in header record');
469 END IF;
470
471 -- Bug 4344351: The header record should be errored out if customer_id is null.
472 l_validation_failed := TRUE;
473
474 END IF;
475
476 IF ( l_validation_failed ) THEN
477 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
478 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', p_header_record.error_record.error_message);
479 rcv_error_pkg.set_token('COLUMN_NAME', 'CUSTOMER_ID');
480 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.customer_id);
481 END IF;
482 EXCEPTION
483 WHEN OTHERS THEN
484 rcv_error_pkg.set_sql_error_message('validate_customer_info', '000');
485 /* Bug 4344351: Setting error staus to Error instead of Unexpected Error.
486 ** This is to ensure that we error out rti and stop further processing.
487 */
488 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
489 p_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
490 END validate_customer_info;
491
492 PROCEDURE validate_customer_site_info(
493 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
494 ) IS
495 BEGIN
496 NULL;
497 END validate_customer_site_info;
498 END rcv_rma_headers;
[Home] [Help]