[Home] [Help]
PACKAGE BODY: APPS.RLM_AD_SV
Source
1 PACKAGE BODY RLM_AD_SV as
2 /*$Header: RLMDPARB.pls 120.1 2005/07/17 18:28:56 rlanka noship $ */
3 /*======================= RLM_AD_SV ============================*/
4 --
5 l_DEBUG NUMBER := NVL(fnd_profile.value('RLM_DEBUG_MODE'),-1);
6 --
7 /*===========================================================================
8
9 FUNCTION NAME: Archive_Headers
10
11 ===========================================================================*/
12 FUNCTION Archive_Headers (x_InterfaceHeaderId IN NUMBER,
13 x_RlmScheduleID OUT NOCOPY NUMBER) RETURN BOOLEAN
14
15 IS
16 --
17 v_RlmScheduleId number;
18 x_progress number;
19 e_NullOrgIDHdr EXCEPTION;
20 v_HdrRec RLM_INTERFACE_HEADERS_ALL%ROWTYPE;
21 --
22 CURSOR c_Hdr IS
23 SELECT *
24 FROM rlm_interface_headers_all
25 WHERE header_id = x_InterfaceHeaderId
26 AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
27 rlm_core_sv.k_PS_PARTIAL_PROCESSED);
28 --
29 BEGIN
30 --
31 IF (l_debug <> -1) THEN
32 rlm_core_sv.dpush(C_SDEBUG, 'Archive_Headers');
33 rlm_core_sv.dlog(C_SDEBUG, 'x_InterfaceHeaderId ',x_InterfaceHeaderId);
34 END IF;
35 --
36 SELECT rlm_schedule_headers_s.nextval
37 INTO v_RlmScheduleId
38 FROM dual;
39 --
40 x_progress :='020';
41 --
42 IF (l_debug <> -1) THEN
43 rlm_core_sv.dlog(C_SDEBUG, 'v_RlmScheduleId ',v_RlmScheduleId);
44 END IF;
45 --
46 x_RlmScheduleId := v_RlmScheduleId;
47 --
48 UPDATE rlm_interface_headers_all
49 SET schedule_header_id = v_RlmScheduleId
50 WHERE header_id = x_InterfaceHeaderId
51 AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
52 rlm_core_sv.k_PS_PARTIAL_PROCESSED);
53 --
54 x_progress :='030';
55 --
56 IF (l_debug <> -1) THEN
57 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Updated ',SQL%ROWCOUNT);
58 END IF;
59 --
60 OPEN c_Hdr;
61 FETCH c_Hdr INTO v_HdrRec;
62 CLOSE c_Hdr;
63 --
64 IF (l_debug <> -1) THEN
65 rlm_core_sv.dlog(C_DEBUG, 'Org ID at header level', v_HdrRec.org_id);
66 END IF;
67 --
68 -- Check if ORG_ID is null. Ideally, such a condition should not arise.
69 -- This is added only as a fail-safe mechanism.
70 --
71 IF (v_HdrRec.ORG_ID IS NULL) THEN
72 RAISE e_NullOrgIdHdr;
73 END IF;
74 --
75 INSERT INTO rlm_schedule_headers_all (
76 HEADER_ID,
77 INTERFACE_HEADER_ID,
78 CUSTOMER_ID,
79 SCHEDULE_TYPE,
80 SCHED_HORIZON_END_DATE,
81 SCHED_HORIZON_START_DATE,
82 SCHEDULE_SOURCE,
83 SCHEDULE_PURPOSE,
84 SCHEDULE_REFERENCE_NUM,
85 CUST_ADDRESS_1_EXT,
86 CUST_ADDRESS_2_EXT,
87 CUST_ADDRESS_3_EXT,
88 CUST_ADDRESS_4_EXT,
89 CUST_CITY_EXT,
90 CUST_COUNTRY_EXT,
91 CUST_COUNTY_EXT,
92 CUSTOMER_EXT,
93 CUST_NAME_EXT,
94 CUST_POSTAL_CD_EXT,
95 CUST_PROVINCE_EXT,
96 CUST_STATE_EXT,
97 ECE_TP_LOCATION_CODE_EXT,
98 ECE_TP_TRANSLATOR_CODE,
99 EDI_CONTROL_NUM_1,
100 EDI_CONTROL_NUM_2,
101 EDI_CONTROL_NUM_3,
102 EDI_TEST_INDICATOR,
103 process_status,
104 TP_ATTRIBUTE_CATEGORY,
105 TP_ATTRIBUTE1,
106 TP_ATTRIBUTE2,
107 TP_ATTRIBUTE3,
108 TP_ATTRIBUTE4,
109 TP_ATTRIBUTE5,
110 TP_ATTRIBUTE6,
111 TP_ATTRIBUTE7,
112 TP_ATTRIBUTE8,
113 TP_ATTRIBUTE9,
114 TP_ATTRIBUTE10,
115 TP_ATTRIBUTE11,
116 TP_ATTRIBUTE12,
117 TP_ATTRIBUTE13,
118 TP_ATTRIBUTE14,
119 TP_ATTRIBUTE15,
120 HEADER_CONTACT_CODE_1,
121 HEADER_CONTACT_CODE_2,
122 HEADER_CONTACT_VALUE_1,
123 HEADER_CONTACT_VALUE_2,
124 HEADER_NOTE_TEXT,
125 HEADER_REF_CODE_1,
126 HEADER_REF_CODE_2,
127 HEADER_REF_CODE_3,
128 HEADER_REF_VALUE_1,
129 HEADER_REF_VALUE_2,
130 HEADER_REF_VALUE_3,
131 SCHEDULE_TYPE_EXT,
132 SCHED_GENERATION_DATE,
133 SCHEDULE_PURPOSE_EXT,
134 LAST_UPDATE_DATE,
135 LAST_UPDATED_BY,
136 CREATION_DATE,
137 CREATED_BY,
138 ATTRIBUTE_CATEGORY,
139 ATTRIBUTE1,
140 ATTRIBUTE2,
141 ATTRIBUTE3,
142 ATTRIBUTE4,
143 ATTRIBUTE5,
144 ATTRIBUTE6,
145 ATTRIBUTE7,
146 ATTRIBUTE8,
147 ATTRIBUTE9,
148 ATTRIBUTE10,
149 ATTRIBUTE11,
150 ATTRIBUTE12,
151 ATTRIBUTE13,
152 ATTRIBUTE14,
153 ATTRIBUTE15,
154 LAST_UPDATE_LOGIN,
155 REQUEST_ID,
156 PROGRAM_APPLICATION_ID,
157 PROGRAM_ID,
158 PROGRAM_UPDATE_DATE,
159 ORG_ID)
160 VALUES
161 (
162 v_hdrRec.SCHEDULE_HEADER_ID,
163 v_hdrRec.HEADER_ID,
164 v_hdrRec.CUSTOMER_ID,
165 v_hdrRec.SCHEDULE_TYPE,
166 v_hdrRec.SCHED_HORIZON_END_DATE,
167 v_hdrRec.SCHED_HORIZON_START_DATE,
168 v_hdrRec.SCHEDULE_SOURCE,
169 v_hdrRec.SCHEDULE_PURPOSE,
170 v_hdrRec.SCHEDULE_REFERENCE_NUM,
171 v_hdrRec.CUST_ADDRESS_1_EXT,
172 v_hdrRec.CUST_ADDRESS_2_EXT,
173 v_hdrRec.CUST_ADDRESS_3_EXT,
174 v_hdrRec.CUST_ADDRESS_4_EXT,
175 v_hdrRec.CUST_CITY_EXT,
176 v_hdrRec.CUST_COUNTRY_EXT,
177 v_hdrRec.CUST_COUNTY_EXT,
178 v_hdrRec.CUSTOMER_EXT,
179 v_hdrRec.CUST_NAME_EXT,
180 v_hdrRec.CUST_POSTAL_CD_EXT,
181 v_hdrRec.CUST_PROVINCE_EXT,
182 v_hdrRec.CUST_STATE_EXT,
183 v_hdrRec.ECE_TP_LOCATION_CODE_EXT,
184 v_hdrRec.ECE_TP_TRANSLATOR_CODE,
185 v_hdrRec.EDI_CONTROL_NUM_1,
186 v_hdrRec.EDI_CONTROL_NUM_2,
187 v_hdrRec.EDI_CONTROL_NUM_3,
188 v_hdrRec.EDI_TEST_INDICATOR,
189 v_hdrRec.PROCESS_STATUS,
190 v_hdrRec.TP_ATTRIBUTE_CATEGORY,
191 v_hdrRec.TP_ATTRIBUTE1,
192 v_hdrRec.TP_ATTRIBUTE2,
193 v_hdrRec.TP_ATTRIBUTE3,
194 v_hdrRec.TP_ATTRIBUTE4,
195 v_hdrRec.TP_ATTRIBUTE5,
196 v_hdrRec.TP_ATTRIBUTE6,
197 v_hdrRec.TP_ATTRIBUTE7,
198 v_hdrRec.TP_ATTRIBUTE8,
199 v_hdrRec.TP_ATTRIBUTE9,
200 v_hdrRec.TP_ATTRIBUTE10,
201 v_hdrRec.TP_ATTRIBUTE11,
202 v_hdrRec.TP_ATTRIBUTE12,
203 v_hdrRec.TP_ATTRIBUTE13,
204 v_hdrRec.TP_ATTRIBUTE14,
205 v_hdrRec.TP_ATTRIBUTE15,
206 v_hdrRec.HEADER_CONTACT_CODE_1,
207 v_hdrRec.HEADER_CONTACT_CODE_2,
208 v_hdrRec.HEADER_CONTACT_VALUE_1,
209 v_hdrRec.HEADER_CONTACT_VALUE_2,
210 v_hdrRec.HEADER_NOTE_TEXT,
211 v_hdrRec.HEADER_REF_CODE_1,
212 v_hdrRec.HEADER_REF_CODE_2,
213 v_hdrRec.HEADER_REF_CODE_3,
214 v_hdrRec.HEADER_REF_VALUE_1,
215 v_hdrRec.HEADER_REF_VALUE_2,
216 v_hdrRec.HEADER_REF_VALUE_3,
217 v_hdrRec.SCHEDULE_TYPE_EXT,
218 v_hdrRec.SCHED_GENERATION_DATE,
219 v_hdrRec.SCHEDULE_PURPOSE_EXT,
220 v_hdrRec.LAST_UPDATE_DATE,
221 v_hdrRec.LAST_UPDATED_BY,
222 v_hdrRec.CREATION_DATE,
223 v_hdrRec.CREATED_BY,
224 v_hdrRec.ATTRIBUTE_CATEGORY,
225 v_hdrRec.ATTRIBUTE1,
226 v_hdrRec.ATTRIBUTE2,
227 v_hdrRec.ATTRIBUTE3,
228 v_hdrRec.ATTRIBUTE4,
229 v_hdrRec.ATTRIBUTE5,
230 v_hdrRec.ATTRIBUTE6,
231 v_hdrRec.ATTRIBUTE7,
232 v_hdrRec.ATTRIBUTE8,
233 v_hdrRec.ATTRIBUTE9,
234 v_hdrRec.ATTRIBUTE10,
235 v_hdrRec.ATTRIBUTE11,
236 v_hdrRec.ATTRIBUTE12,
237 v_hdrRec.ATTRIBUTE13,
238 v_hdrRec.ATTRIBUTE14,
239 v_hdrRec.ATTRIBUTE15,
240 v_hdrRec.LAST_UPDATE_LOGIN,
241 v_hdrRec.REQUEST_ID,
242 v_hdrRec.PROGRAM_APPLICATION_ID,
243 v_hdrRec.PROGRAM_ID,
244 v_hdrRec.PROGRAM_UPDATE_DATE,
245 v_hdrRec.ORG_ID
246 );
247 --
248 x_progress :='040';
249 --
250 IF (l_debug <> -1) THEN
251 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Inserted ',SQL%ROWCOUNT);
252 rlm_core_sv.dpop(C_SDEBUG);
253 END IF;
254 --
255 return TRUE;
256 --
257 EXCEPTION
258 --
259 WHEN e_NullOrgIDHdr THEN
260 --
261 IF (l_debug <> -1) THEN
262 rlm_core_sv.dlog(C_DEBUG, 'Null Org ID found at header level');
263 END IF;
264 --
265 rlm_message_sv.app_error(
266 x_ExceptionLevel => rlm_message_sv.k_error_level,
267 x_MessageName => 'RLM_OU_CONTEXT_NOT_SET',
268 x_InterfaceHeaderId => x_InterfaceHeaderId,
269 x_ScheduleHeaderId => x_RLMScheduleID);
270 --
271 IF (l_debug <> -1) THEN
272 rlm_core_sv.dpop(C_SDEBUG);
273 END IF;
274 --
275 RETURN FALSE;
276 --
277 WHEN OTHERS THEN
278 rlm_message_sv.sql_error ('rlm_archive_demand_sv.Archive_Headers',
279 x_progress);
280 IF (l_debug <> -1) THEN
281 --
282 rlm_core_sv.dlog(C_DEBUG,'x_progress',x_progress);
283 rlm_core_sv.dlog(C_DEBUG,'Error',sqlerrm);
284 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: OTHER - sql error');
285 --
286 END IF;
287 --
288 raise;
289 return FALSE;
290 END;
291
292 /*===========================================================================
293
294 FUNCTION NAME: Archive_Lines
295
296 ===========================================================================*/
297 FUNCTION Archive_Lines (x_InterfaceHeaderId IN NUMBER,
298 x_RlmScheduleId IN NUMBER)
299 RETURN BOOLEAN
300 IS
301 --
302 dup_rec number;
303 --
304 CURSOR c_cur IS
305 SELECT line_id interface_line_id,
306 header_id interface_header_id,
307 schedule_line_id,
308 item_detail_type,
309 order_header_id,
310 blanket_number,
311 org_id
312 FROM rlm_interface_lines_all
313 WHERE header_id = x_InterfaceHeaderId
314 AND process_status = rlm_core_sv.k_PS_AVAILABLE ;
315 --
316 v_count NUMBER := 0;
317 --
318 x_progress number;
319 e_NullOrgID EXCEPTION;
320 --
321 BEGIN
322 --
323 IF (l_debug <> -1) THEN
324 --
325 rlm_core_sv.dpush(C_SDEBUG, 'Archive_Lines');
326 rlm_core_sv.dlog(C_SDEBUG, 'x_InterfaceHeaderId ',x_InterfaceHeaderId);
327 rlm_core_sv.dlog(C_SDEBUG, 'x_RlmScheduleId ',x_RlmScheduleId);
328 --
329 END IF;
330 --
331 FOR c_rec IN c_cur LOOP
332 --
333 -- Raise error if ORG_ID is null. Ideally, such a condition should not
334 -- arise. This is added only as a fail safe mechanism.
335 --
336 IF c_rec.org_id IS NULL THEN
337 RAISE e_NullOrgID;
338 END IF;
339 --
340 UPDATE rlm_interface_lines_all
341 SET schedule_line_id = rlm_schedule_lines_s.nextval
342 WHERE header_id = c_rec.interface_header_id
343 AND line_id = c_rec.interface_line_id
344 AND schedule_line_id IS NULL
345 AND process_status = rlm_core_sv.k_PS_AVAILABLE ;
346 --
347 IF (l_debug <> -1) THEN
348 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Updated ',SQL%ROWCOUNT);
349 END IF;
350 --
351 v_count := v_count + 1;
352 --
353 x_progress :='020';
354 --
355 IF SQL%ROWCOUNT <> 0 THEN
356 --
357 INSERT INTO rlm_schedule_lines_all(
358 LINE_ID,
359 CUSTOMER_ITEM_EXT,
360 CUSTOMER_ITEM_ID,
361 DATE_TYPE_CODE,
362 INVENTORY_ITEM_ID,
363 ITEM_DETAIL_SUBTYPE,
364 ITEM_DETAIL_TYPE,
365 INTERFACE_LINE_ID,
366 QTY_TYPE_CODE,
367 HEADER_ID,
368 START_DATE_TIME,
369 UOM_CODE,
370 ATO_DATA_TYPE,
371 BILL_TO_ADDRESS_1_EXT,
372 BILL_TO_ADDRESS_2_EXT,
373 BILL_TO_ADDRESS_3_EXT,
374 BILL_TO_ADDRESS_4_EXT,
375 BILL_TO_ADDRESS_ID,
376 BILL_TO_CITY_EXT,
377 BILL_TO_COUNTRY_EXT,
378 BILL_TO_COUNTY_EXT,
379 BILL_TO_NAME_EXT,
380 BILL_TO_POSTAL_CD_EXT,
381 BILL_TO_PROVINCE_EXT,
382 BILL_TO_SITE_USE_ID,
383 BILL_TO_STATE_EXT,
384 CARRIER_ID_CODE_EXT,
385 CARRIER_QUALIFIER_EXT,
386 COMMODITY_EXT,
387 COUNTRY_OF_ORIGIN_EXT,
388 CUST_ASSEMBLY_EXT,
389 CUST_ASSIGNED_ID_EXT,
390 CUST_BILL_TO_EXT,
391 CUST_CONTRACT_NUM_EXT,
392 CUSTOMER_DOCK_CODE,
393 CUST_INTRMD_SHIP_TO_EXT,
394 CUST_ITEM_PRICE_EXT,
395 CUST_ITEM_PRICE_UOM_EXT,
396 CUSTOMER_ITEM_REVISION,
397 CUSTOMER_JOB,
398 CUST_MANUFACTURER_EXT,
399 CUST_MODEL_NUMBER_EXT,
400 CUST_MODEL_SERIAL_NUMBER,
401 CUST_ORDER_NUM_EXT,
402 CUST_PROCESS_NUM_EXT,
403 CUST_PRODUCTION_LINE,
404 CUST_SET_NUM_EXT,
405 CUST_SHIP_FROM_ORG_EXT,
406 CUST_SHIP_TO_EXT,
407 CUST_UOM_EXT,
408 END_DATE_TIME,
409 EQUIPMENT_CODE_EXT,
410 EQUIPMENT_NUMBER_EXT,
411 HANDLING_CODE_EXT,
412 HAZARD_CODE_EXT,
413 HAZARD_CODE_QUAL_EXT,
414 HAZARD_DESCRIPTION_EXT,
415 IMPORT_LICENSE_DATE_EXT,
416 IMPORT_LICENSE_EXT,
417 INDUSTRY_ATTRIBUTE1,
418 INDUSTRY_ATTRIBUTE10,
419 INDUSTRY_ATTRIBUTE11,
420 INDUSTRY_ATTRIBUTE12,
421 INDUSTRY_ATTRIBUTE13,
422 INDUSTRY_ATTRIBUTE14,
423 INDUSTRY_ATTRIBUTE15,
424 INDUSTRY_ATTRIBUTE2,
425 INDUSTRY_ATTRIBUTE3,
426 INDUSTRY_ATTRIBUTE4,
427 INDUSTRY_ATTRIBUTE5,
428 INDUSTRY_ATTRIBUTE6,
429 INDUSTRY_ATTRIBUTE7,
430 INDUSTRY_ATTRIBUTE8,
431 INDUSTRY_ATTRIBUTE9,
432 INDUSTRY_CONTEXT,
436 INTRMD_ST_ADDRESS_3_EXT,
433 INTRMD_SHIP_TO_ID,
434 INTRMD_ST_ADDRESS_1_EXT,
435 INTRMD_ST_ADDRESS_2_EXT,
437 INTRMD_ST_ADDRESS_4_EXT,
438 INTRMD_ST_CITY_EXT,
439 INTRMD_ST_COUNTRY_EXT,
440 INTRMD_ST_COUNTY_EXT,
441 INTRMD_ST_NAME_EXT,
442 INTRMD_ST_POSTAL_CD_EXT,
443 INTRMD_ST_PROVINCE_EXT,
444 INTRMD_ST_STATE_EXT,
445 ITEM_CONTACT_CODE_1,
446 ITEM_CONTACT_CODE_2,
447 ITEM_CONTACT_VALUE_1,
448 ITEM_CONTACT_VALUE_2,
449 ITEM_DESCRIPTION_EXT,
450 ITEM_DETAIL_QUANTITY,
451 ITEM_DETAIL_REF_CODE_1,
452 ITEM_DETAIL_REF_CODE_2,
453 ITEM_DETAIL_REF_CODE_3,
454 ITEM_DETAIL_REF_VALUE_1,
455 ITEM_DETAIL_REF_VALUE_2,
456 ITEM_DETAIL_REF_VALUE_3,
457 ITEM_ENG_CNG_LVL_EXT,
458 ITEM_MEASUREMENTS_EXT,
459 ITEM_NOTE_TEXT,
460 ITEM_REF_CODE_1,
461 ITEM_REF_CODE_2,
462 ITEM_REF_CODE_3,
463 ITEM_REF_VALUE_1,
464 ITEM_REF_VALUE_2,
465 ITEM_REF_VALUE_3,
466 ITEM_RELEASE_STATUS_EXT,
467 LADING_QUANTITY_EXT,
468 LETTER_CREDIT_EXPDT_EXT,
469 LETTER_CREDIT_EXT,
470 TP_ATTRIBUTE_CATEGORY,
471 TP_ATTRIBUTE1,
472 TP_ATTRIBUTE2,
473 TP_ATTRIBUTE3,
474 TP_ATTRIBUTE4,
475 TP_ATTRIBUTE5,
476 TP_ATTRIBUTE6,
477 TP_ATTRIBUTE7,
478 TP_ATTRIBUTE8,
479 TP_ATTRIBUTE9,
480 TP_ATTRIBUTE10,
481 TP_ATTRIBUTE11,
482 TP_ATTRIBUTE12,
483 TP_ATTRIBUTE13,
484 TP_ATTRIBUTE14,
485 TP_ATTRIBUTE15,
486 LINE_REFERENCE,
487 LINK_TO_LINE_REF,
488 OTHER_NAME_CODE_1,
489 OTHER_NAME_CODE_2,
490 OTHER_NAME_VALUE_1,
491 OTHER_NAME_VALUE_2,
492 PACK_SIZE_EXT,
493 PACK_UNITS_PER_PACK_EXT,
494 PACK_UOM_CODE_EXT,
495 PACKAGING_CODE_EXT,
496 PRIMARY_QUANTITY,
497 PRIMARY_UOM_CODE,
498 PRIME_CONTRCTR_PART_EXT,
499 /* line process status */
500 PROCESS_STATUS,
501 CUST_PO_RELEASE_NUM,
502 CUST_PO_DATE,
503 CUST_PO_LINE_NUM,
504 CUST_PO_NUMBER,
505 RETURN_CONTAINER_EXT,
506 ROUTING_DESC_EXT,
507 ROUTING_SEQ_CODE_EXT,
508 SCHEDULE_ITEM_NUM,
509 SHIP_DEL_PATTERN_EXT,
510 SHIP_DEL_TIME_CODE_EXT,
511 SHIP_FROM_ADDRESS_1_EXT,
512 SHIP_FROM_ADDRESS_2_EXT,
513 SHIP_FROM_ADDRESS_3_EXT,
514 SHIP_FROM_ADDRESS_4_EXT,
515 SHIP_FROM_CITY_EXT,
516 SHIP_FROM_COUNTRY_EXT,
517 SHIP_FROM_COUNTY_EXT,
518 SHIP_FROM_NAME_EXT,
519 SHIP_FROM_ORG_ID,
520 SHIP_FROM_POSTAL_CD_EXT,
521 SHIP_FROM_PROVINCE_EXT,
522 SHIP_FROM_STATE_EXT,
523 SHIP_LABEL_INFO_LINE_1,
524 SHIP_LABEL_INFO_LINE_10,
525 SHIP_LABEL_INFO_LINE_2,
526 SHIP_LABEL_INFO_LINE_3,
527 SHIP_LABEL_INFO_LINE_4,
528 SHIP_LABEL_INFO_LINE_5,
529 SHIP_LABEL_INFO_LINE_6,
530 SHIP_LABEL_INFO_LINE_7,
531 SHIP_LABEL_INFO_LINE_8,
532 SHIP_LABEL_INFO_LINE_9,
533 SHIP_TO_ADDRESS_1_EXT,
534 SHIP_TO_ADDRESS_2_EXT,
535 SHIP_TO_ADDRESS_3_EXT,
536 SHIP_TO_ADDRESS_4_EXT,
537 SHIP_TO_ADDRESS_ID,
538 SHIP_TO_CITY_EXT,
539 SHIP_TO_COUNTRY_EXT,
540 SHIP_TO_COUNTY_EXT,
541 SHIP_TO_NAME_EXT,
542 SHIP_TO_POSTAL_CD_EXT,
543 SHIP_TO_PROVINCE_EXT,
544 SHIP_TO_SITE_USE_ID,
545 SHIP_TO_STATE_EXT,
546 SUBLINE_ASSIGNED_ID_EXT,
547 SUBLINE_CONFIG_CODE_EXT,
548 SUBLINE_CUST_ITEM_EXT,
549 SUBLINE_CUST_ITEM_ID,
550 SUBLINE_MODEL_NUM_EXT,
551 SUBLINE_QUANTITY,
552 SUBLINE_UOM_CODE,
553 SUPPLIER_ITEM_EXT,
554 TRANSIT_TIME_EXT,
555 TRANSIT_TIME_QUAL_EXT,
556 CUST_PRODUCTION_SEQ_NUM,
557 TRANSPORT_LOC_QUAL_EXT,
558 TRANSPORT_LOCATION_EXT,
559 TRANSPORT_METHOD_EXT,
560 WEIGHT_EXT,
561 WEIGHT_QUALIFIER_EXT,
562 WEIGHT_UOM_EXT,
563 ITEM_DETAIL_SUBTYPE_EXT,
564 ITEM_DETAIL_TYPE_EXT,
565 QTY_TYPE_CODE_EXT,
566 DATE_TYPE_CODE_EXT,
567 LAST_UPDATE_DATE,
568 LAST_UPDATED_BY,
569 CREATION_DATE,
570 CREATED_BY,
571 ATTRIBUTE_CATEGORY,
572 ATTRIBUTE1,
573 ATTRIBUTE2,
574 ATTRIBUTE3,
575 ATTRIBUTE4,
576 ATTRIBUTE5,
577 ATTRIBUTE6,
578 ATTRIBUTE7,
579 ATTRIBUTE8,
580 ATTRIBUTE9,
581 ATTRIBUTE10,
582 ATTRIBUTE11,
583 ATTRIBUTE12,
584 ATTRIBUTE13,
585 ATTRIBUTE14,
586 ATTRIBUTE15,
587 LAST_UPDATE_LOGIN,
588 REQUEST_ID,
589 PROGRAM_APPLICATION_ID,
590 PROGRAM_ID,
591 LINE_NUMBER,
592 INTMED_SHIP_TO_ORG_ID,
593 SHIP_TO_ORG_ID,
594 DELIVER_TO_ORG_ID,
595 INVOICE_TO_ORG_ID,
596 ORDER_HEADER_ID,
597 SHIP_DEL_RULE_NAME,
598 BLANKET_NUMBER,
599 ORG_ID,
603 CUSTOMER_ITEM_EXT,
600 SHIP_TO_CUSTOMER_ID)
601 SELECT
602 SCHEDULE_LINE_ID,
604 CUSTOMER_ITEM_ID,
605 DATE_TYPE_CODE,
606 INVENTORY_ITEM_ID,
607 ITEM_DETAIL_SUBTYPE,
608 ITEM_DETAIL_TYPE,
609 LINE_ID,
610 QTY_TYPE_CODE,
611 x_RlmScheduleId,
612 START_DATE_TIME,
613 UOM_CODE,
614 ATO_DATA_TYPE,
615 BILL_TO_ADDRESS_1_EXT,
616 BILL_TO_ADDRESS_2_EXT,
617 BILL_TO_ADDRESS_3_EXT,
618 BILL_TO_ADDRESS_4_EXT,
619 BILL_TO_ADDRESS_ID,
620 BILL_TO_CITY_EXT,
621 BILL_TO_COUNTRY_EXT,
622 BILL_TO_COUNTY_EXT,
623 BILL_TO_NAME_EXT,
624 BILL_TO_POSTAL_CD_EXT,
625 BILL_TO_PROVINCE_EXT,
626 BILL_TO_SITE_USE_ID,
627 BILL_TO_STATE_EXT,
628 CARRIER_ID_CODE_EXT,
629 CARRIER_QUALIFIER_EXT,
630 COMMODITY_EXT,
631 COUNTRY_OF_ORIGIN_EXT,
632 CUST_ASSEMBLY_EXT,
633 CUST_ASSIGNED_ID_EXT,
634 CUST_BILL_TO_EXT,
635 CUST_CONTRACT_NUM_EXT,
636 CUSTOMER_DOCK_CODE,
637 CUST_INTRMD_SHIP_TO_EXT,
638 CUST_ITEM_PRICE_EXT,
639 CUST_ITEM_PRICE_UOM_EXT,
640 CUSTOMER_ITEM_REVISION,
641 CUSTOMER_JOB,
642 CUST_MANUFACTURER_EXT,
643 CUST_MODEL_NUMBER_EXT,
644 CUST_MODEL_SERIAL_NUMBER,
645 CUST_ORDER_NUM_EXT,
646 CUST_PROCESS_NUM_EXT,
647 CUST_PRODUCTION_LINE,
648 CUST_SET_NUM_EXT,
649 CUST_SHIP_FROM_ORG_EXT,
650 CUST_SHIP_TO_EXT,
651 CUST_UOM_EXT,
652 END_DATE_TIME,
653 EQUIPMENT_CODE_EXT,
654 EQUIPMENT_NUMBER_EXT,
655 HANDLING_CODE_EXT,
656 HAZARD_CODE_EXT,
657 HAZARD_CODE_QUAL_EXT,
658 HAZARD_DESCRIPTION_EXT,
659 IMPORT_LICENSE_DATE_EXT,
660 IMPORT_LICENSE_EXT,
661 INDUSTRY_ATTRIBUTE1,
662 INDUSTRY_ATTRIBUTE10,
663 INDUSTRY_ATTRIBUTE11,
664 INDUSTRY_ATTRIBUTE12,
665 INDUSTRY_ATTRIBUTE13,
666 INDUSTRY_ATTRIBUTE14,
667 INDUSTRY_ATTRIBUTE15,
668 INDUSTRY_ATTRIBUTE2,
669 INDUSTRY_ATTRIBUTE3,
670 INDUSTRY_ATTRIBUTE4,
671 INDUSTRY_ATTRIBUTE5,
672 INDUSTRY_ATTRIBUTE6,
673 INDUSTRY_ATTRIBUTE7,
674 INDUSTRY_ATTRIBUTE8,
675 INDUSTRY_ATTRIBUTE9,
676 INDUSTRY_CONTEXT,
677 INTRMD_SHIP_TO_ID,
678 INTRMD_ST_ADDRESS_1_EXT,
679 INTRMD_ST_ADDRESS_2_EXT,
680 INTRMD_ST_ADDRESS_3_EXT,
681 INTRMD_ST_ADDRESS_4_EXT,
682 INTRMD_ST_CITY_EXT,
683 INTRMD_ST_COUNTRY_EXT,
684 INTRMD_ST_COUNTY_EXT,
685 INTRMD_ST_NAME_EXT,
686 INTRMD_ST_POSTAL_CD_EXT,
687 INTRMD_ST_PROVINCE_EXT,
688 INTRMD_ST_STATE_EXT,
689 ITEM_CONTACT_CODE_1,
690 ITEM_CONTACT_CODE_2,
691 ITEM_CONTACT_VALUE_1,
692 ITEM_CONTACT_VALUE_2,
693 ITEM_DESCRIPTION_EXT,
694 ITEM_DETAIL_QUANTITY,
695 ITEM_DETAIL_REF_CODE_1,
696 ITEM_DETAIL_REF_CODE_2,
697 ITEM_DETAIL_REF_CODE_3,
698 ITEM_DETAIL_REF_VALUE_1,
699 ITEM_DETAIL_REF_VALUE_2,
700 ITEM_DETAIL_REF_VALUE_3,
701 ITEM_ENG_CNG_LVL_EXT,
702 ITEM_MEASUREMENTS_EXT,
703 ITEM_NOTE_TEXT,
704 ITEM_REF_CODE_1,
705 ITEM_REF_CODE_2,
706 ITEM_REF_CODE_3,
707 ITEM_REF_VALUE_1,
708 ITEM_REF_VALUE_2,
709 ITEM_REF_VALUE_3,
710 ITEM_RELEASE_STATUS_EXT,
711 LADING_QUANTITY_EXT,
712 LETTER_CREDIT_EXPDT_EXT,
713 LETTER_CREDIT_EXT,
714 TP_ATTRIBUTE_CATEGORY,
715 TP_ATTRIBUTE1,
716 TP_ATTRIBUTE2,
717 TP_ATTRIBUTE3,
718 TP_ATTRIBUTE4,
719 TP_ATTRIBUTE5,
720 TP_ATTRIBUTE6,
721 TP_ATTRIBUTE7,
722 TP_ATTRIBUTE8,
723 TP_ATTRIBUTE9,
724 TP_ATTRIBUTE10,
725 TP_ATTRIBUTE11,
726 TP_ATTRIBUTE12,
727 TP_ATTRIBUTE13,
728 TP_ATTRIBUTE14,
729 TP_ATTRIBUTE15,
730 LINE_REFERENCE,
731 LINK_TO_LINE_REF,
732 OTHER_NAME_CODE_1,
733 OTHER_NAME_CODE_2,
734 OTHER_NAME_VALUE_1,
735 OTHER_NAME_VALUE_2,
736 PACK_SIZE_EXT,
737 PACK_UNITS_PER_PACK_EXT,
738 PACK_UOM_CODE_EXT,
739 PACKAGING_CODE_EXT,
740 PRIMARY_QUANTITY,
741 PRIMARY_UOM_CODE,
742 PRIME_CONTRCTR_PART_EXT,
743 PROCESS_STATUS,
744 CUST_PO_RELEASE_NUM,
745 CUST_PO_DATE,
746 CUST_PO_LINE_NUM,
747 CUST_PO_NUMBER,
748 RETURN_CONTAINER_EXT,
749 ROUTING_DESC_EXT,
750 ROUTING_SEQ_CODE_EXT,
751 SCHEDULE_ITEM_NUM,
752 SHIP_DEL_PATTERN_EXT,
753 SHIP_DEL_TIME_CODE_EXT,
754 SHIP_FROM_ADDRESS_1_EXT,
755 SHIP_FROM_ADDRESS_2_EXT,
756 SHIP_FROM_ADDRESS_3_EXT,
757 SHIP_FROM_ADDRESS_4_EXT,
758 SHIP_FROM_CITY_EXT,
759 SHIP_FROM_COUNTRY_EXT,
760 SHIP_FROM_COUNTY_EXT,
761 SHIP_FROM_NAME_EXT,
762 SHIP_FROM_ORG_ID,
763 SHIP_FROM_POSTAL_CD_EXT,
764 SHIP_FROM_PROVINCE_EXT,
768 SHIP_LABEL_INFO_LINE_2,
765 SHIP_FROM_STATE_EXT,
766 SHIP_LABEL_INFO_LINE_1,
767 SHIP_LABEL_INFO_LINE_10,
769 SHIP_LABEL_INFO_LINE_3,
770 SHIP_LABEL_INFO_LINE_4,
771 SHIP_LABEL_INFO_LINE_5,
772 SHIP_LABEL_INFO_LINE_6,
773 SHIP_LABEL_INFO_LINE_7,
774 SHIP_LABEL_INFO_LINE_8,
775 SHIP_LABEL_INFO_LINE_9,
776 SHIP_TO_ADDRESS_1_EXT,
777 SHIP_TO_ADDRESS_2_EXT,
778 SHIP_TO_ADDRESS_3_EXT,
779 SHIP_TO_ADDRESS_4_EXT,
780 SHIP_TO_ADDRESS_ID,
781 SHIP_TO_CITY_EXT,
782 SHIP_TO_COUNTRY_EXT,
783 SHIP_TO_COUNTY_EXT,
784 SHIP_TO_NAME_EXT,
785 SHIP_TO_POSTAL_CD_EXT,
786 SHIP_TO_PROVINCE_EXT,
787 SHIP_TO_SITE_USE_ID,
788 SHIP_TO_STATE_EXT,
789 SUBLINE_ASSIGNED_ID_EXT,
790 SUBLINE_CONFIG_CODE_EXT,
791 SUBLINE_CUST_ITEM_EXT,
792 SUBLINE_CUST_ITEM_ID,
793 SUBLINE_MODEL_NUM_EXT,
794 SUBLINE_QUANTITY,
795 SUBLINE_UOM_CODE,
796 SUPPLIER_ITEM_EXT,
797 TRANSIT_TIME_EXT,
798 TRANSIT_TIME_QUAL_EXT,
799 CUST_PRODUCTION_SEQ_NUM,
800 TRANSPORT_LOC_QUAL_EXT,
801 TRANSPORT_LOCATION_EXT,
802 TRANSPORT_METHOD_EXT,
803 WEIGHT_EXT,
804 WEIGHT_QUALIFIER_EXT,
805 WEIGHT_UOM_EXT,
806 ITEM_DETAIL_SUBTYPE_EXT,
807 ITEM_DETAIL_TYPE_EXT,
808 QTY_TYPE_CODE_EXT,
809 DATE_TYPE_CODE_EXT,
810 sysdate,
811 LAST_UPDATED_BY,
812 sysdate,
813 CREATED_BY,
814 ATTRIBUTE_CATEGORY,
815 ATTRIBUTE1,
816 ATTRIBUTE2,
817 ATTRIBUTE3,
818 ATTRIBUTE4,
819 ATTRIBUTE5,
820 ATTRIBUTE6,
821 ATTRIBUTE7,
822 ATTRIBUTE8,
823 ATTRIBUTE9,
824 ATTRIBUTE10,
825 ATTRIBUTE11,
826 ATTRIBUTE12,
827 ATTRIBUTE13,
828 ATTRIBUTE14,
829 ATTRIBUTE15,
830 LAST_UPDATE_LOGIN,
831 REQUEST_ID,
832 PROGRAM_APPLICATION_ID,
833 PROGRAM_ID,
834 LINE_NUMBER,
835 INTMED_SHIP_TO_ORG_ID,
836 SHIP_TO_ORG_ID,
837 DELIVER_TO_ORG_ID,
838 INVOICE_TO_ORG_ID,
839 ORDER_HEADER_ID,
840 SHIP_DEL_RULE_NAME,
841 BLANKET_NUMBER,
842 ORG_ID,
843 SHIP_TO_CUSTOMER_ID
844 FROM rlm_interface_lines_all
845 WHERE header_id = x_InterfaceHeaderId
846 AND line_id = c_rec.interface_line_id
847 AND process_status = rlm_core_sv.k_PS_AVAILABLE ;
848 --
849 IF (l_debug <> -1) THEN
850 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Inserted ',SQL%ROWCOUNT);
851 END IF;
852 --
853 ELSE
854 --
855 UPDATE rlm_schedule_lines_all
856 SET request_id = rlm_message_sv.g_conc_req_id,
857 interface_line_id = c_rec.interface_line_id,
858 order_header_id = c_rec.order_header_id,
859 blanket_number = c_rec.blanket_number,
860 last_update_date = sysdate,
861 last_updated_by = fnd_global.user_id
862 WHERE header_id = x_RLMScheduleID
863 AND line_id = c_rec.schedule_line_id;
864 --
865 IF (l_debug <> -1) THEN
866 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Updated to new request id ', SQL%ROWCOUNT);
867 END IF;
868 --
869 END IF;
870
871 /*** Deleting Authorizations and Test transactions ****/
872 /*** Item Detail Type =3 means it is an Authorization ****/
873 /*** Item Detail Type =5 means it is Test Transaction ****/
874 --
875 IF c_rec.item_detail_type IN ('3','5') THEN
876 --
877 UPDATE rlm_interface_lines_all
878 SET process_status = rlm_core_sv.k_PS_PROCESSED
879 WHERE header_id = x_InterfaceHeaderId
880 AND line_id = c_rec.interface_line_id
881 AND process_status = rlm_core_sv.k_PS_AVAILABLE;
882 --
883 IF (l_debug <> -1) THEN
884 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Updated in interface for cum auth '
885 ,SQL%ROWCOUNT);
886 END IF;
887
888 --
889 UPDATE rlm_schedule_lines
890 SET process_status = rlm_core_sv.k_PS_PROCESSED
891 WHERE header_id = x_RLMScheduleID
892 AND interface_line_id = c_rec.interface_line_id
893 AND process_status = rlm_core_sv.k_PS_AVAILABLE;
894 --
895 IF (l_debug <> -1) THEN
896 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Updated in schedule for auth
897 cum and test',SQL%ROWCOUNT);
898 END IF;
899 --
900 END IF;
901 --
902 x_progress :='030';
903 --
904 END LOOP;
905 --
906 IF (l_debug <> -1) THEN
907 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Scanned ',v_count);
908 END IF;
909 --
910 x_progress :='040';
911 --
912 IF (l_debug <> -1) THEN
913 rlm_core_sv.dpop(C_SDEBUG);
914 END IF;
915 --
916 return TRUE;
917 --
918 EXCEPTION
919 --
920 WHEN e_NullOrgID THEN
921 --
922 IF (l_debug <> -1) THEN
923 rlm_core_sv.dlog(C_DEBUG, 'Null Org ID found');
924 END IF;
925 --
926 rlm_message_sv.app_error(
927 x_ExceptionLevel => rlm_message_sv.k_error_level,
928 x_MessageName => 'RLM_OU_CONTEXT_NOT_SET',
932 IF (l_debug <> -1) THEN
929 x_InterfaceHeaderId => x_InterfaceHeaderId,
930 x_ScheduleHeaderId => x_RLMScheduleID);
931 --
933 rlm_core_sv.dpop(C_SDEBUG);
934 END IF;
935 --
936 RETURN FALSE;
937 --
938 WHEN OTHERS THEN
939 rlm_message_sv.sql_error ('rlm_archive_demand_sv.Archive_Lines', x_progress);
940 IF (l_debug <> -1) THEN
941 rlm_core_sv.dlog(C_DEBUG,'Error',sqlerrm);
942 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: OTHER - sql error');
943 END IF;
944 raise;
945 return FALSE;
946 END;
947
948 /*===========================================================================
949
950 FUNCTION NAME: Archive_demand
951
952 ===========================================================================*/
953 FUNCTION Archive_Demand (x_InterfaceHeaderId IN NUMBER) RETURN BOOLEAN
954 IS
955 v_Count number;
956 v_RlmScheduleId number;
957 x_progress number;
958 CURSOR c_cur IS
959 SELECT schedule_header_id,
960 edi_test_indicator,
961 process_status
962 FROM rlm_interface_headers_all
963 WHERE header_id = x_InterfaceHeaderId;
964
965 BEGIN
966 --
967 IF (l_debug <> -1) THEN
968 rlm_core_sv.dpush(C_SDEBUG, 'Archive_demand');
969 rlm_core_sv.dlog(C_SDEBUG, 'x_InterfaceHeaderId ',x_InterfaceHeaderId);
970 END IF;
971 --
972 FOR c_rec IN c_cur LOOP
973 --
974 IF c_rec.schedule_header_id IS NOT NULL THEN
975 --
976 v_RlmScheduleID := c_rec.schedule_header_id;
977 --
978 IF (l_debug <> -1) THEN
979 rlm_core_sv.dlog(C_DEBUG, 'Schedule Header Already Archived',
980 c_rec.schedule_header_id);
981 rlm_core_sv.dlog(C_DEBUG, 'request_id ',rlm_message_sv.g_conc_req_id );
982 END IF;
983 --
984 Update rlm_schedule_headers_all
985 SET request_id = rlm_message_sv.g_conc_req_id ,
986 last_update_date = sysdate,
987 last_updated_by = fnd_global.user_id
988 WHERE header_id = c_rec.schedule_header_id;
989 --
990 IF (l_debug <> -1) THEN
991 rlm_core_sv.dlog(C_DEBUG, 'Schedule headers updated', SQL%ROWCOUNT);
992 END IF;
993 --
994 -- The lines are updated in Archive Lines no need to do this again
995 --
996 /*
997 Update rlm_schedule_lines
998 SET request_id = rlm_message_sv.g_conc_req_id ,
999 last_update_date = sysdate,
1000 last_updated_by = fnd_global.user_id
1001 WHERE header_id = c_rec.schedule_header_id;
1002 --
1003 IF (l_debug <> -1) THEN
1004 rlm_core_sv.dlog(C_DEBUG, 'Schedule lines updated', SQL%ROWCOUNT);
1005 END IF;
1006 --
1007 */
1008 ELSE
1009 --
1010 IF NOT(Archive_Headers(x_InterfaceHeaderId,v_RlmScheduleID)) then
1011 --
1012 IF (l_debug <> -1) THEN
1013 rlm_core_sv.dpop(C_SDEBUG);
1014 END IF;
1015 --
1016 return FALSE;
1017 --
1018 END IF;
1019 --
1020 IF (l_debug <> -1) THEN
1021 rlm_core_sv.dlog(C_SDEBUG, 'Archived Header with ', v_RlmScheduleId);
1022 END IF;
1023 --
1024 END IF;
1025 --
1026 IF (l_debug <> -1) THEN
1027 rlm_core_sv.dlog(C_SDEBUG, 'Archiving Schedule Lines');
1028 END IF;
1029 --
1030 IF NOT(Archive_lines(x_InterfaceHeaderId,v_RlmScheduleId)) then
1031 --
1032 IF (l_debug <> -1) THEN
1033 rlm_core_sv.dpop(C_SDEBUG);
1034 END IF;
1035 --
1036 return FALSE;
1037 --
1038 END IF;
1039 --
1040 IF c_rec.edi_test_indicator = 'T' THEN
1041 --
1042 UPDATE rlm_interface_lines
1043 SET process_status = rlm_core_sv.k_PS_PROCESSED
1044 WHERE header_id = x_InterfaceHeaderId
1045 AND process_status = rlm_core_sv.k_PS_AVAILABLE;
1046 --
1047 IF (l_debug <> -1) THEN
1048 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Updated in interface lines for test',SQL%ROWCOUNT);
1049 END IF;
1050 --
1051 UPDATE rlm_schedule_lines
1052 SET process_status = rlm_core_sv.k_PS_PROCESSED
1053 WHERE header_id = v_RlmScheduleID
1054 AND process_status = rlm_core_sv.k_PS_AVAILABLE;
1055 --
1056 IF (l_debug <> -1) THEN
1057 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Updated in schedule lines for test',SQL%ROWCOUNT);
1058 END IF;
1059 --
1060 UPDATE rlm_interface_headers_all
1061 SET process_status = rlm_core_sv.k_PS_PROCESSED
1062 WHERE header_id = x_InterfaceHeaderId
1063 AND process_status = rlm_core_sv.k_PS_AVAILABLE;
1064 --
1065 IF (l_debug <> -1) THEN
1066 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Updated in interface header for test',SQL%ROWCOUNT);
1067 END IF;
1068 --
1069 -- Update the schedule headers with v_RlmScheduleID instead of
1070 -- c_rec.header_id for new schedules bug 1085917
1071 --
1072 UPDATE rlm_schedule_headers_all
1073 SET process_status = rlm_core_sv.k_PS_PROCESSED
1074 WHERE header_id = v_RlmScheduleID
1075 AND process_status = rlm_core_sv.k_PS_AVAILABLE;
1076 --
1077 IF (l_debug <> -1) THEN
1078 rlm_core_sv.dlog(C_SDEBUG, 'No of Records Updated in schedule headers for test',SQL%ROWCOUNT);
1079 END IF;
1080 --
1081 END IF;
1082 END LOOP;
1083 --
1084 IF (l_debug <> -1) THEN
1088 return TRUE;
1085 rlm_core_sv.dpop(C_SDEBUG);
1086 END IF;
1087 --
1089 --
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 rlm_message_sv.sql_error ('rlm_archive_demand_sv.Archive_demand',
1093 x_progress);
1094 IF (l_debug <> -1) THEN
1095 rlm_core_sv.dlog(C_DEBUG,'x_progress',x_progress);
1096 rlm_core_sv.dlog(C_DEBUG,'Error',sqlerrm);
1097 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: OTHER - sql error');
1098 END IF;
1099 --
1100 raise;
1101 END;
1102
1103 END RLM_AD_SV;