1 PACKAGE BODY RLM_RLMSCHRL_XMLP_PKG AS
2 /* $Header: RLMSCHRLB.pls 120.0 2008/01/25 09:42:56 krreddy noship $ */
3 FUNCTION CF_CUSTOMER_NUMFORMULA(CUSTOMER_ID IN NUMBER) RETURN CHAR IS
4 CNUM VARCHAR2(30);
5 BEGIN
6 SELECT
7 CUST_ACCT.ACCOUNT_NUMBER
8 INTO CNUM
9 FROM
10 HZ_PARTIES PARTY,
11 HZ_CUST_ACCOUNTS CUST_ACCT
12 WHERE PARTY.PARTY_ID = CUST_ACCT.PARTY_ID
13 AND CUST_ACCT.CUST_ACCOUNT_ID = CUSTOMER_ID;
14 RETURN (CNUM);
15 EXCEPTION
16 WHEN OTHERS THEN
17 RETURN (NULL);
18 END CF_CUSTOMER_NUMFORMULA;
19
20 FUNCTION CF_SHIP_FROM_ORGFORMULA(SCHEDULE_SOURCE IN VARCHAR2
21 ,SHIP_FROM_ORG_ID IN NUMBER
22 ,CUST_SHIP_FROM_ORG_EXT IN VARCHAR2) RETURN VARCHAR IS
23 CODE VARCHAR2(10);
24 BEGIN
25 IF SCHEDULE_SOURCE = 'MANUAL' THEN
26 SELECT
27 ORGANIZATION_CODE
28 INTO CODE
29 FROM
30 ORG_ORGANIZATION_DEFINITIONS
31 WHERE ORGANIZATION_ID = SHIP_FROM_ORG_ID;
32 ELSE
33 CODE := CUST_SHIP_FROM_ORG_EXT;
34 END IF;
35 RETURN (CODE);
36 EXCEPTION
37 WHEN OTHERS THEN
38 RETURN (NULL);
39 END CF_SHIP_FROM_ORGFORMULA;
40
41 FUNCTION CF_SHIP_TO_LOCFORMULA(SCHEDULE_SOURCE IN VARCHAR2
42 ,SHIP_TO_ADDRESS_ID IN NUMBER
43 ,CUST_SHIP_TO_EXT IN VARCHAR2) RETURN VARCHAR IS
44 LCODE VARCHAR2(10);
45 BEGIN
46 IF SCHEDULE_SOURCE = 'MANUAL' THEN
47 SELECT
48 ECE_TP_LOCATION_CODE
49 INTO LCODE
50 FROM
51 HZ_CUST_ACCT_SITES
52 WHERE CUST_ACCT_SITE_ID = SHIP_TO_ADDRESS_ID;
53 ELSE
54 LCODE := CUST_SHIP_TO_EXT;
55 END IF;
56 RETURN (LCODE);
57 EXCEPTION
58 WHEN OTHERS THEN
59 RETURN (NULL);
60 END CF_SHIP_TO_LOCFORMULA;
61
62 FUNCTION CF_CUST_ITEM_NUMFORMULA(SCHEDULE_SOURCE IN VARCHAR2
63 ,CUSTOMER_ITEM_ID IN NUMBER
64 ,CUSTOMER_ID IN NUMBER
65 ,CUSTOMER_ITEM_EXT IN VARCHAR2) RETURN VARCHAR IS
66 CNUM VARCHAR2(30);
67 BEGIN
68 IF SCHEDULE_SOURCE = 'MANUAL' THEN
69 SELECT
70 CUSTOMER_ITEM_NUMBER
71 INTO CNUM
72 FROM
73 MTL_CUSTOMER_ITEMS
74 WHERE CUSTOMER_ITEM_ID = CF_CUST_ITEM_NUMFORMULA.CUSTOMER_ITEM_ID
75 AND CUSTOMER_ID = CF_CUST_ITEM_NUMFORMULA.CUSTOMER_ID;
76 ELSE
77 CNUM := CUSTOMER_ITEM_EXT;
78 END IF;
79 RETURN (CNUM);
80 EXCEPTION
81 WHEN OTHERS THEN
82 RETURN (NULL);
83 END CF_CUST_ITEM_NUMFORMULA;
84
85 FUNCTION AFTERPFORM RETURN BOOLEAN IS
86 STATEMENT0 VARCHAR2(100);
87 STATEMENT9 VARCHAR2(100);
88 STATEMENT1 VARCHAR2(1000);
89 STATEMENT10 VARCHAR2(100);
90 STATEMENT2 VARCHAR2(100);
91 STATEMENT11 VARCHAR2(100);
92 STATEMENT3 VARCHAR2(100);
93 STATEMENT12 VARCHAR2(100);
94 STATEMENT4 VARCHAR2(100);
95 STATEMENT13 VARCHAR2(100);
96 STATEMENT5 VARCHAR2(100);
97 STATEMENT14 VARCHAR2(100);
98 STATEMENT6 VARCHAR2(100);
99 STATEMENT15 VARCHAR2(100);
100 STATEMENT7 VARCHAR2(100);
101 STATEMENT16 VARCHAR2(100);
102 STATEMENT8 VARCHAR2(100);
103 STATEMENT17 VARCHAR2(3000);
104 V_ORIGIN_TABLE VARCHAR2(1);
105 V_HEADER_ID NUMBER;
106 CURSOR C_GET_SCHEDULE_REF_NUM(P_HEADER_ID IN NUMBER) IS
107 SELECT
108 SCHEDULE_REFERENCE_NUM
109 FROM
110 RLM_SCHEDULE_HEADERS_ALL
111 WHERE HEADER_ID = P_HEADER_ID;
112 CURSOR C_GET_SCHEDULE_REF_NUM_INT(P_HEADER_ID IN NUMBER) IS
113 SELECT
114 SCHEDULE_REFERENCE_NUM
115 FROM
116 RLM_INTERFACE_HEADERS_ALL
117 WHERE HEADER_ID = P_HEADER_ID;
118 BEGIN
119 IF P_CUSTOMER IS NOT NULL THEN
120 STATEMENT0 := ' And sih.customer_id = :P_CUSTOMER';
121 ELSE
122 STATEMENT0 := NULL;
123 END IF;
124 IF P_SCHEDULE_NUM IS NOT NULL THEN
125 V_ORIGIN_TABLE := SUBSTR(P_SCHEDULE_NUM
126 ,1
127 ,1);
128 V_HEADER_ID := TO_NUMBER(SUBSTR(P_SCHEDULE_NUM
129 ,2));
130 IF V_ORIGIN_TABLE = 'S' THEN
131 OPEN C_GET_SCHEDULE_REF_NUM(V_HEADER_ID);
132 FETCH C_GET_SCHEDULE_REF_NUM
133 INTO P_REF_NUM;
134 CLOSE C_GET_SCHEDULE_REF_NUM;
135 ELSIF V_ORIGIN_TABLE = 'I' THEN
136 OPEN C_GET_SCHEDULE_REF_NUM_INT(V_HEADER_ID);
137 FETCH C_GET_SCHEDULE_REF_NUM_INT
138 INTO P_REF_NUM;
139 CLOSE C_GET_SCHEDULE_REF_NUM_INT;
140 END IF;
141 STATEMENT1 := ' And sih.schedule_reference_num = :P_REF_NUM ';
142 ELSE
143 STATEMENT1 := NULL;
144 END IF;
145 IF P_SHIP_FROM IS NOT NULL THEN
146 STATEMENT2 := ' And sil.ship_from_org_id = :P_SHIP_FROM';
147 ELSE
148 STATEMENT2 := NULL;
149 END IF;
150 IF P_SHIP_TO IS NOT NULL THEN
151 STATEMENT3 := ' And sil.ship_to_address_id = :P_SHIP_TO';
152 ELSE
153 STATEMENT3 := NULL;
154 END IF;
155 IF P_SCHEDULE_TYPE IS NOT NULL THEN
156 STATEMENT4 := ' And sih.schedule_type = :P_SCHEDULE_TYPE';
157 ELSE
158 STATEMENT4 := NULL;
159 END IF;
160 IF P_SCHEDULE_SOURCE IS NOT NULL THEN
161 STATEMENT5 := ' And sih.schedule_source = :P_SCHEDULE_SOURCE';
162 ELSE
163 STATEMENT5 := NULL;
164 END IF;
165 IF P_SCHEDULE_PURPOSE IS NOT NULL THEN
166 STATEMENT6 := ' And sih.schedule_purpose = :P_SCHEDULE_PURPOSE';
167 ELSE
168 STATEMENT6 := NULL;
169 END IF;
170 IF P_PROCESS_DATE_FROM IS NOT NULL THEN
171 STATEMENT7 := ' And sih.creation_date >= to_date(:P_PROCESS_DATE_FROM,''yyyy/MM/DD HH24:MI:SS'')';
172 ELSE
173 STATEMENT7 := NULL;
174 END IF;
175 IF P_PROCESS_DATE_TO IS NOT NULL THEN
176 STATEMENT8 := ' And sih.creation_date <= to_date(:P_PROCESS_DATE_TO,''yyyy/MM/DD HH24:MI:SS'')';
177 ELSE
178 STATEMENT8 := NULL;
179 END IF;
180 IF P_ISSUE_DATE_FROM IS NOT NULL THEN
181 STATEMENT9 := ' And sih.SCHED_GENERATION_DATE >= to_date(:P_ISSUE_DATE_FROM,''yyyy/MM/DD HH24:MI:SS'')';
182 ELSE
183 STATEMENT9 := NULL;
184 END IF;
185 IF P_ISSUE_DATE_TO IS NOT NULL THEN
186 STATEMENT10 := ' And sih.SCHED_GENERATION_DATE <= to_date(:P_ISSUE_DATE_TO,''yyyy/MM/DD HH24:MI:SS'')';
187 ELSE
188 STATEMENT10 := NULL;
189 END IF;
190 IF P_TP_FROM IS NOT NULL THEN
191 STATEMENT11 := ' And sih.ECE_TP_TRANSLATOR_CODE >= :P_TP_FROM';
192 ELSE
193 STATEMENT11 := NULL;
194 END IF;
195 IF (P_TP_TO IS NOT NULL) THEN
196 STATEMENT12 := ' And sih.ECE_TP_TRANSLATOR_CODE <= :P_TP_TO';
197 ELSE
198 STATEMENT12 := NULL;
199 END IF;
200 IF (P_TP_LOC_FROM IS NOT NULL) THEN
201 STATEMENT13 := ' And sih.ECE_TP_LOCATION_CODE_EXT >= :P_TP_LOC_FROM';
202 ELSE
203 STATEMENT13 := NULL;
204 END IF;
205 IF (P_TP_LOC_TO IS NOT NULL) THEN
206 STATEMENT14 := ' And sih.ECE_TP_LOCATION_CODE_EXT <= :P_TP_LOC_TO';
207 ELSE
208 STATEMENT14 := NULL;
209 END IF;
210 IF P_HORIZON_START_DATE IS NOT NULL THEN
211 STATEMENT15 := ' And sih.SCHED_HORIZON_START_DATE >= to_date(:P_HORIZON_START_DATE,''yyyy/MM/DD HH24:MI:SS'')';
212 ELSE
213 STATEMENT15 := NULL;
214 END IF;
215 IF P_HORIZON_END_DATE IS NOT NULL THEN
216 STATEMENT16 := ' And sih.SCHED_HORIZON_END_DATE <= to_date(:P_HORIZON_END_DATE,''yyyy/MM/DD HH24:MI:SS'')';
217 ELSE
218 STATEMENT16 := NULL;
219 END IF;
220 IF P_PROCESS_STATUS IS NOT NULL THEN
221 IF P_PROCESS_STATUS = 2 OR P_PROCESS_STATUS = 4 THEN
222 STATEMENT17 := ' And sih.process_status = :P_PROCESS_STATUS
223 and sih.origin_table = ''INTERFACE''
224 and sil.origin_table = ''INTERFACE'' ';
225 ELSE
226 IF P_PROCESS_STATUS = 5 THEN
227 STATEMENT17 := ' And sih.process_status = :P_PROCESS_STATUS and sih.origin_table = ''SCHEDULE'' and sil.origin_table = ''SCHEDULE'' ';
228 ELSE
229 IF P_PROCESS_STATUS = 7 THEN
230 STATEMENT17 := ' And sih.process_status = :P_PROCESS_STATUS';
231 END IF;
232 END IF;
233 END IF;
234 ELSE
235 STATEMENT17 := ' and sih.origin_table = decode(sih.process_status,2,''INTERFACE'',4,''INTERFACE'',5,''SCHEDULE'',7,sih.origin_table)
236 and (sil.origin_table = decode(sil.process_status,2,''INTERFACE'',4,''INTERFACE'',5,''INTERFACE'')
237 or sil.origin_table = decode(sil.process_status,2,''INTERFACE'',4,''INTERFACE'',5,''SCHEDULE''))
238 and sih.origin_table = decode(sih.process_status,2,sil.origin_table,4,sil.origin_table,5,sil.origin_table,7,sih.origin_table) ';
239 END IF;
240 P_WHERE_CLAUSE := STATEMENT0 || STATEMENT1 || STATEMENT2 || STATEMENT3 || STATEMENT4 || STATEMENT5 || STATEMENT6 || STATEMENT7 || STATEMENT8 || STATEMENT9 || STATEMENT10 || STATEMENT11 || STATEMENT12 || STATEMENT13 ||
241 STATEMENT14 || STATEMENT15 || STATEMENT16 || STATEMENT17;
242 RETURN (TRUE);
243 END AFTERPFORM;
244
245 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
246 CNAME VARCHAR2(50);
247 ORGCODE VARCHAR2(10);
248 LCODE VARCHAR2(10);
249 STYPE VARCHAR2(30);
250 SPURPOSE VARCHAR2(30);
251 PSTATUS VARCHAR2(40);
252 PSCHREFNUM VARCHAR2(35);
253 L_CURRENT_ORG_ID NUMBER;
254 L_OU_NAME VARCHAR2(240);
255 BEGIN
256 BEGIN
257 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
258 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
259 EXCEPTION
260 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
261 /*SRW.MESSAGE(1000
262 ,'Failed in BEFORE REPORT trigger')*/NULL;
263 RETURN (FALSE);
264 END;
265 L_CURRENT_ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
266 IF (L_CURRENT_ORG_ID IS NULL AND P_ORG_ID IS NOT NULL) THEN
267 MO_GLOBAL.SET_POLICY_CONTEXT(P_ACCESS_MODE => 'S'
268 ,P_ORG_ID => P_ORG_ID);
269 L_CURRENT_ORG_ID := P_ORG_ID;
270 END IF;
271 L_OU_NAME := FND_ACCESS_CONTROL_UTIL.GET_ORG_NAME(L_CURRENT_ORG_ID);
272 CP_DEFAULT_OU := L_OU_NAME;
273 IF P_CUSTOMER IS NOT NULL THEN
274 SELECT
275 SUBSTRB(PARTY.PARTY_NAME
276 ,1
277 ,50)
278 INTO CNAME
279 FROM
280 HZ_PARTIES PARTY
281 WHERE PARTY.PARTY_ID = (
282 SELECT
283 CUST_ACCT.PARTY_ID
284 FROM
285 HZ_CUST_ACCOUNTS CUST_ACCT
286 WHERE CUST_ACCT.CUST_ACCOUNT_ID = P_CUSTOMER );
287 CP_CUSTOMER := CNAME;
288 ELSE
289 CP_CUSTOMER := P_CUSTOMER;
290 END IF;
291 IF P_SHIP_FROM IS NOT NULL THEN
292 SELECT
293 ORGANIZATION_CODE
294 INTO ORGCODE
295 FROM
296 ORG_ORGANIZATION_DEFINITIONS
297 WHERE ORGANIZATION_ID = P_SHIP_FROM;
298 CP_SHIP_FROM_ORG := ORGCODE;
299 ELSE
300 CP_SHIP_FROM_ORG := P_SHIP_FROM;
301 END IF;
302 IF P_SHIP_TO IS NOT NULL THEN
303 SELECT
304 ECE_TP_LOCATION_CODE
305 INTO LCODE
306 FROM
307 HZ_CUST_ACCT_SITES
308 WHERE CUST_ACCT_SITE_ID = P_SHIP_TO;
309 CP_SHIP_TO := LCODE;
310 ELSE
311 CP_SHIP_TO := P_SHIP_TO;
312 END IF;
313 IF P_SCHEDULE_TYPE IS NOT NULL THEN
314 SELECT
315 MEANING
316 INTO STYPE
317 FROM
318 FND_LOOKUP_VALUES_VL
319 WHERE LOOKUP_TYPE = 'RLM_SCHEDULE_TYPE'
320 AND LOOKUP_CODE = P_SCHEDULE_TYPE;
321 CP_SCHEDULE_TYPE := STYPE;
322 ELSE
323 CP_SCHEDULE_TYPE := NULL;
324 END IF;
325 IF P_SCHEDULE_PURPOSE IS NOT NULL THEN
326 SELECT
327 MEANING
328 INTO SPURPOSE
329 FROM
330 FND_LOOKUP_VALUES_VL
331 WHERE LOOKUP_TYPE = 'RLM_SCHEDULE_PURPOSE'
332 AND LOOKUP_CODE = P_SCHEDULE_PURPOSE;
333 CP_SCHEDULE_PURPOSE := SPURPOSE;
334 ELSE
335 CP_SCHEDULE_PURPOSE := NULL;
336 END IF;
337 IF P_PROCESS_STATUS IS NOT NULL THEN
338 SELECT
339 MEANING
340 INTO PSTATUS
341 FROM
342 FND_LOOKUP_VALUES_VL FLV
343 WHERE FLV.LOOKUP_TYPE = 'RLM_PROCESS_STATUS'
344 AND FLV.LOOKUP_CODE = P_PROCESS_STATUS;
345 CP_PROCESS_STATUS := PSTATUS;
346 ELSE
347 CP_PROCESS_STATUS := NULL;
348 END IF;
349 CP_SCHEDULE_REF_NUM := P_REF_NUM;
350 CP_SCHEDULE_SOURCE := P_SCHEDULE_SOURCE;
351 CP_TITLE := P_TITLE;
352 CP_HORIZON_START_DATE := P_HORIZON_START_DATE;
353 CP_HORIZON_END_DATE := P_HORIZON_END_DATE;
354 CP_ISSUE_DATE_FROM := P_ISSUE_DATE_FROM;
355 CP_ISSUE_DATE_TO := P_ISSUE_DATE_TO;
356 CP_TP_FROM := P_TP_FROM;
357 CP_TP_TO := P_TP_TO;
358 CP_TP_LOC_CODE_FROM := P_TP_LOC_FROM;
359 CP_TP_LOC_CODE_TO := P_TP_LOC_TO;
360 CP_PROCESS_DATE_FROM := P_PROCESS_DATE_FROM;
361 CP_PROCESS_DATE_TO := P_PROCESS_DATE_TO;
362 RETURN (TRUE);
363 EXCEPTION
364 WHEN OTHERS THEN
365 RETURN (NULL);
366 END BEFOREREPORT;
367
368 FUNCTION CF_TEST_INDICATORFORMULA(EDI_TEST_INDICATOR IN VARCHAR2) RETURN CHAR IS
369 TEST_INDICATOR VARCHAR2(10);
370 BEGIN
371 IF EDI_TEST_INDICATOR = 'T' THEN
372 SELECT
373 MEANING
374 INTO TEST_INDICATOR
375 FROM
376 FND_LOOKUPS
377 WHERE LOOKUP_CODE = 'Y'
378 AND LOOKUP_TYPE = 'YES_NO';
379 ELSE
380 SELECT
381 MEANING
382 INTO TEST_INDICATOR
383 FROM
384 FND_LOOKUPS
385 WHERE LOOKUP_CODE = 'N'
386 AND LOOKUP_TYPE = 'YES_NO';
387 END IF;
388 RETURN (TEST_INDICATOR);
389 EXCEPTION
390 WHEN OTHERS THEN
391 RETURN (NULL);
392 END CF_TEST_INDICATORFORMULA;
393
394 FUNCTION CF_SHIP_DEL_PATTERNFORMULA(SHIP_DEL_PATTERN_EXT IN VARCHAR2) RETURN CHAR IS
395 SDPDESC VARCHAR2(100);
396 BEGIN
397 SELECT
398 DESCRIPTION
399 INTO SDPDESC
400 FROM
401 RLM_SHIP_DELIVERY_CODES
402 WHERE SHIP_DELIVERY_RULE_NAME = SHIP_DEL_PATTERN_EXT;
403 RETURN (SDPDESC);
404 EXCEPTION
405 WHEN OTHERS THEN
406 RETURN (NULL);
407 END CF_SHIP_DEL_PATTERNFORMULA;
408
409 FUNCTION CF_INV_ITEM_CONC_SEGMENTFORMUL(INVENTORY_ITEM_ID IN NUMBER
410 ,SHIP_FROM_ORG_ID IN NUMBER) RETURN CHAR IS
411 CONC_SEG VARCHAR2(40);
412 BEGIN
413 SELECT
414 CONCATENATED_SEGMENTS
415 INTO CONC_SEG
416 FROM
417 MTL_SYSTEM_ITEMS_KFV MSI
418 WHERE CF_INV_ITEM_CONC_SEGMENTFORMUL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
419 AND MSI.ORGANIZATION_ID = SHIP_FROM_ORG_ID;
420 RETURN (CONC_SEG);
421 EXCEPTION
422 WHEN OTHERS THEN
423 RETURN (NULL);
424 END CF_INV_ITEM_CONC_SEGMENTFORMUL;
425
429 CODE VARCHAR2(10);
426 FUNCTION CF_SHIP_FROM_ORG1FORMULA(SCHEDULE_SOURCE IN VARCHAR2
427 ,SHIP_FROM_ORG_ID1 IN NUMBER
428 ,CUST_SHIP_FROM_ORG_EXT1 IN VARCHAR2) RETURN VARCHAR IS
430 BEGIN
431 IF SCHEDULE_SOURCE = 'MANUAL' THEN
432 SELECT
433 ORGANIZATION_CODE
434 INTO CODE
435 FROM
436 ORG_ORGANIZATION_DEFINITIONS
437 WHERE ORGANIZATION_ID = SHIP_FROM_ORG_ID1;
438 ELSE
439 CODE := CUST_SHIP_FROM_ORG_EXT1;
440 END IF;
441 RETURN (CODE);
442 EXCEPTION
443 WHEN OTHERS THEN
444 RETURN (NULL);
445 END CF_SHIP_FROM_ORG1FORMULA;
446
447 FUNCTION CF_SHIP_TO_LOC1FORMULA(SCHEDULE_SOURCE IN VARCHAR2
448 ,SHIP_TO_ADDRESS_ID1 IN NUMBER
449 ,CUST_SHIP_TO_EXT1 IN VARCHAR2) RETURN VARCHAR IS
450 LCODE VARCHAR2(10);
451 BEGIN
452 IF SCHEDULE_SOURCE = 'MANUAL' THEN
453 SELECT
454 ECE_TP_LOCATION_CODE
455 INTO LCODE
456 FROM
457 HZ_CUST_ACCT_SITES
458 WHERE CUST_ACCT_SITE_ID = SHIP_TO_ADDRESS_ID1;
459 ELSE
460 LCODE := CUST_SHIP_TO_EXT1;
461 END IF;
462 RETURN (LCODE);
463 EXCEPTION
464 WHEN OTHERS THEN
465 RETURN (NULL);
466 END CF_SHIP_TO_LOC1FORMULA;
467
468 FUNCTION CF_SHIP_DEL_PATTERN1FORMULA(SHIP_DEL_PATTERN_EXT1 IN VARCHAR2) RETURN CHAR IS
469 SDPDESC VARCHAR2(100);
470 BEGIN
471 SELECT
472 DESCRIPTION
473 INTO SDPDESC
474 FROM
475 RLM_SHIP_DELIVERY_CODES
476 WHERE SHIP_DELIVERY_RULE_NAME = SHIP_DEL_PATTERN_EXT1;
477 RETURN (SDPDESC);
478 EXCEPTION
479 WHEN OTHERS THEN
480 RETURN (NULL);
481 END CF_SHIP_DEL_PATTERN1FORMULA;
482
483 FUNCTION CF_INV_CONC_SEGMENT1FORMULA(INVENTORY_ITEM_ID1 IN NUMBER
484 ,SHIP_FROM_ORG_ID1 IN NUMBER) RETURN CHAR IS
485 CONC_SEG VARCHAR2(40);
486 BEGIN
487 SELECT
488 CONCATENATED_SEGMENTS
489 INTO CONC_SEG
490 FROM
491 MTL_SYSTEM_ITEMS_KFV MSI
492 WHERE INVENTORY_ITEM_ID1 = MSI.INVENTORY_ITEM_ID
493 AND MSI.ORGANIZATION_ID = SHIP_FROM_ORG_ID1;
494 RETURN (CONC_SEG);
495 EXCEPTION
496 WHEN OTHERS THEN
497 RETURN (NULL);
498 END CF_INV_CONC_SEGMENT1FORMULA;
499
500 FUNCTION CF_CUST_ITEM_NUM1FORMULA(SCHEDULE_SOURCE IN VARCHAR2
501 ,CUSTOMER_ITEM_ID1 IN NUMBER
502 ,CUSTOMER_ID IN NUMBER
503 ,CUSTOMER_ITEM_EXT1 IN VARCHAR2) RETURN CHAR IS
504 CNUM VARCHAR2(30);
505 BEGIN
506 IF SCHEDULE_SOURCE = 'MANUAL' THEN
507 SELECT
508 CUSTOMER_ITEM_NUMBER
509 INTO CNUM
510 FROM
511 MTL_CUSTOMER_ITEMS
512 WHERE CUSTOMER_ITEM_ID = CUSTOMER_ITEM_ID1
513 AND CUSTOMER_ID = CUSTOMER_ID;
514 ELSE
515 CNUM := CUSTOMER_ITEM_EXT1;
516 END IF;
517 RETURN (CNUM);
518 EXCEPTION
519 WHEN OTHERS THEN
520 RETURN (NULL);
521 END CF_CUST_ITEM_NUM1FORMULA;
522
523 FUNCTION CF_BILL_TO_LOCFORMULA(BILL_TO_ADDRESS_ID IN NUMBER) RETURN CHAR IS
524 LCODE VARCHAR2(10);
525 BEGIN
526 SELECT
527 ECE_TP_LOCATION_CODE
528 INTO LCODE
529 FROM
530 HZ_CUST_ACCT_SITES
531 WHERE CUST_ACCT_SITE_ID = BILL_TO_ADDRESS_ID;
532 RETURN (LCODE);
533 EXCEPTION
534 WHEN OTHERS THEN
535 RETURN (NULL);
536 END CF_BILL_TO_LOCFORMULA;
537
538 FUNCTION CF_BILL_TO_LOC1FORMULA(BILL_TO_ADDRESS_ID1 IN NUMBER) RETURN CHAR IS
539 LCODE VARCHAR2(10);
540 BEGIN
541 SELECT
542 ECE_TP_LOCATION_CODE
543 INTO LCODE
544 FROM
545 HZ_CUST_ACCT_SITES
546 WHERE CUST_ACCT_SITE_ID = BILL_TO_ADDRESS_ID1;
547 RETURN (LCODE);
548 EXCEPTION
549 WHEN OTHERS THEN
550 RETURN (NULL);
551 END CF_BILL_TO_LOC1FORMULA;
552
553 FUNCTION CF_SCHEDULE_SOURCEFORMULA(SCHEDULE_SOURCE IN VARCHAR2) RETURN CHAR IS
554 SSOURCE VARCHAR2(10);
555 BEGIN
556 IF SCHEDULE_SOURCE = 'MANUAL' THEN
557 SELECT
558 MEANING
559 INTO SSOURCE
560 FROM
561 FND_LOOKUP_VALUES_VL
562 WHERE LOOKUP_TYPE = 'RLM_SCHEDULE_SOURCE'
563 AND LOOKUP_CODE = 'MANUAL';
564 ELSE
565 SSOURCE := SCHEDULE_SOURCE;
566 END IF;
567 RETURN (SSOURCE);
568 EXCEPTION
569 WHEN OTHERS THEN
570 RETURN (NULL);
571 END CF_SCHEDULE_SOURCEFORMULA;
572
573 FUNCTION CF_ADDRESS1FORMULA(CUST_ADDRESS_1_EXT IN VARCHAR2
574 ,CUST_ADDRESS_2_EXT IN VARCHAR2
575 ,CUST_ADDRESS_3_EXT IN VARCHAR2
576 ,CUST_ADDRESS_4_EXT IN VARCHAR2) RETURN CHAR IS
577 BEGIN
578 IF CUST_ADDRESS_1_EXT IS NOT NULL THEN
579 RETURN (CUST_ADDRESS_1_EXT);
580 ELSIF CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NOT NULL THEN
581 RETURN (CUST_ADDRESS_2_EXT);
582 ELSIF CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NOT NULL THEN
583 RETURN (CUST_ADDRESS_3_EXT);
584 ELSIF CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NOT NULL THEN
585 RETURN (CUST_ADDRESS_4_EXT);
586 ELSE
587 RETURN (NULL);
588 END IF;
589 EXCEPTION
590 WHEN OTHERS THEN
594 FUNCTION CF_ADDRESS2FORMULA(CUST_ADDRESS_1_EXT IN VARCHAR2
591 RETURN (NULL);
592 END CF_ADDRESS1FORMULA;
593
595 ,CUST_ADDRESS_2_EXT IN VARCHAR2
596 ,CUST_ADDRESS_3_EXT IN VARCHAR2
597 ,CUST_ADDRESS_4_EXT IN VARCHAR2) RETURN CHAR IS
598 BEGIN
599 IF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NOT NULL) THEN
600 RETURN (CUST_ADDRESS_2_EXT);
601 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NOT NULL) THEN
602 RETURN (CUST_ADDRESS_3_EXT);
603 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
604 RETURN (CUST_ADDRESS_4_EXT);
605 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
606 RETURN (NULL);
607 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NOT NULL) THEN
608 RETURN (CUST_ADDRESS_3_EXT);
609 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
610 RETURN (CUST_ADDRESS_4_EXT);
611 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
612 RETURN NULL;
613 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
614 RETURN NULL;
615 END IF;
616 EXCEPTION
617 WHEN OTHERS THEN
618 RETURN (NULL);
619 END CF_ADDRESS2FORMULA;
620
621 FUNCTION CF_ADDRESS3FORMULA(CUST_ADDRESS_1_EXT IN VARCHAR2
622 ,CUST_ADDRESS_2_EXT IN VARCHAR2
623 ,CUST_ADDRESS_3_EXT IN VARCHAR2
624 ,CUST_ADDRESS_4_EXT IN VARCHAR2) RETURN CHAR IS
625 BEGIN
626 IF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NOT NULL) THEN
627 RETURN (CUST_ADDRESS_3_EXT);
628 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
629 RETURN (CUST_ADDRESS_4_EXT);
630 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
631 RETURN NULL;
632 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
633 RETURN (NULL);
634 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NOT NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
635 RETURN (CUST_ADDRESS_4_EXT);
636 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
637 RETURN NULL;
638 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NOT NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
639 RETURN NULL;
640 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NOT NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
641 RETURN NULL;
642 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NOT NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
643 RETURN CUST_ADDRESS_4_EXT;
644 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
645 RETURN NULL;
646 ELSE
647 RETURN (NULL);
648 END IF;
649 EXCEPTION
650 WHEN OTHERS THEN
651 RETURN (NULL);
652 END CF_ADDRESS3FORMULA;
653
654 FUNCTION CF_ADDRESS4FORMULA(CUST_ADDRESS_1_EXT IN VARCHAR2
655 ,CUST_ADDRESS_2_EXT IN VARCHAR2
656 ,CUST_ADDRESS_3_EXT IN VARCHAR2
657 ,CUST_ADDRESS_4_EXT IN VARCHAR2) RETURN CHAR IS
658 BEGIN
659 IF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NOT NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
660 RETURN (CUST_ADDRESS_4_EXT);
661 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
662 RETURN NULL;
663 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
664 RETURN NULL;
665 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
666 RETURN NULL;
667 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
668 RETURN (NULL);
669 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NOT NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
670 RETURN NULL;
671 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NOT NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
672 RETURN NULL;
673 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NOT NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
674 RETURN NULL;
675 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NOT NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
676 RETURN NULL;
677 ELSIF (CUST_ADDRESS_1_EXT IS NOT NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NOT NULL AND CUST_ADDRESS_4_EXT IS NOT NULL) THEN
678 RETURN NULL;
679 ELSIF (CUST_ADDRESS_1_EXT IS NULL AND CUST_ADDRESS_2_EXT IS NULL AND CUST_ADDRESS_3_EXT IS NULL AND CUST_ADDRESS_4_EXT IS NULL) THEN
680 RETURN NULL;
681 ELSE
682 RETURN NULL;
683 END IF;
684 EXCEPTION
688
685 WHEN OTHERS THEN
686 RETURN (NULL);
687 END CF_ADDRESS4FORMULA;
689 FUNCTION AFTERREPORT RETURN BOOLEAN IS
690 BEGIN
691 BEGIN
692 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
693 EXCEPTION
694 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
695 /*SRW.MESSAGE(1
696 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
697 RETURN (FALSE);
698 END;
699 RETURN (TRUE);
700 END AFTERREPORT;
701
702 FUNCTION CP_CUSTOMER_P RETURN VARCHAR2 IS
703 BEGIN
704 RETURN CP_CUSTOMER;
705 END CP_CUSTOMER_P;
706
707 FUNCTION CP_SCHEDULE_REF_NUM_P RETURN VARCHAR2 IS
708 BEGIN
709 RETURN CP_SCHEDULE_REF_NUM;
710 END CP_SCHEDULE_REF_NUM_P;
711
712 FUNCTION CP_SHIP_FROM_ORG_P RETURN VARCHAR2 IS
713 BEGIN
714 RETURN CP_SHIP_FROM_ORG;
715 END CP_SHIP_FROM_ORG_P;
716
717 FUNCTION CP_SHIP_TO_P RETURN VARCHAR2 IS
718 BEGIN
719 RETURN CP_SHIP_TO;
720 END CP_SHIP_TO_P;
721
722 FUNCTION CP_SCHEDULE_TYPE_P RETURN VARCHAR2 IS
723 BEGIN
724 RETURN CP_SCHEDULE_TYPE;
725 END CP_SCHEDULE_TYPE_P;
726
727 FUNCTION CP_SCHEDULE_PURPOSE_P RETURN VARCHAR2 IS
728 BEGIN
729 RETURN CP_SCHEDULE_PURPOSE;
730 END CP_SCHEDULE_PURPOSE_P;
731
732 FUNCTION CP_SCHEDULE_SOURCE_P RETURN VARCHAR2 IS
733 BEGIN
734 RETURN CP_SCHEDULE_SOURCE;
735 END CP_SCHEDULE_SOURCE_P;
736
737 FUNCTION CP_PROCESS_STATUS_P RETURN VARCHAR2 IS
738 BEGIN
739 RETURN CP_PROCESS_STATUS;
740 END CP_PROCESS_STATUS_P;
741
742 FUNCTION CP_HORIZON_START_DATE_P RETURN VARCHAR2 IS
743 BEGIN
744 RETURN CP_HORIZON_START_DATE;
745 END CP_HORIZON_START_DATE_P;
746
747 FUNCTION CP_HORIZON_END_DATE_P RETURN VARCHAR2 IS
748 BEGIN
749 RETURN CP_HORIZON_END_DATE;
750 END CP_HORIZON_END_DATE_P;
751
752 FUNCTION CP_ISSUE_DATE_FROM_P RETURN VARCHAR2 IS
753 BEGIN
754 RETURN CP_ISSUE_DATE_FROM;
755 END CP_ISSUE_DATE_FROM_P;
756
757 FUNCTION CP_ISSUE_DATE_TO_P RETURN VARCHAR2 IS
758 BEGIN
759 RETURN CP_ISSUE_DATE_TO;
760 END CP_ISSUE_DATE_TO_P;
761
762 FUNCTION CP_TP_FROM_P RETURN VARCHAR2 IS
763 BEGIN
764 RETURN CP_TP_FROM;
765 END CP_TP_FROM_P;
766
767 FUNCTION CP_TP_TO_P RETURN VARCHAR2 IS
768 BEGIN
769 RETURN CP_TP_TO;
770 END CP_TP_TO_P;
771
772 FUNCTION CP_TP_LOC_CODE_FROM_P RETURN VARCHAR2 IS
773 BEGIN
774 RETURN CP_TP_LOC_CODE_FROM;
775 END CP_TP_LOC_CODE_FROM_P;
776
777 FUNCTION CP_TP_LOC_CODE_TO_P RETURN VARCHAR2 IS
778 BEGIN
779 RETURN CP_TP_LOC_CODE_TO;
780 END CP_TP_LOC_CODE_TO_P;
781
782 FUNCTION CP_PROCESS_DATE_FROM_P RETURN VARCHAR2 IS
783 BEGIN
784 RETURN CP_PROCESS_DATE_FROM;
785 END CP_PROCESS_DATE_FROM_P;
786
787 FUNCTION CP_PROCESS_DATE_TO_P RETURN VARCHAR2 IS
788 BEGIN
789 RETURN CP_PROCESS_DATE_TO;
790 END CP_PROCESS_DATE_TO_P;
791
792 FUNCTION CP_TITLE_P RETURN VARCHAR2 IS
793 BEGIN
794 RETURN CP_TITLE;
795 END CP_TITLE_P;
796
797 FUNCTION CP_DEFAULT_OU_P RETURN VARCHAR2 IS
798 BEGIN
799 RETURN CP_DEFAULT_OU;
800 END CP_DEFAULT_OU_P;
801
802 END RLM_RLMSCHRL_XMLP_PKG;
[Home] [Help]