1 Package body ONT_UPGRADE_EXCEPTION_REPORT As
2 /* $Header: OEXNUPUB.pls 120.0 2005/06/01 03:02:11 appldev noship $ */
3 function BASE_LINE_NUMBER(
4 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
5 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
6 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
7 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
8 )
9 return NUMBER
10 IS
11 V_SHIPMENT_SCHEDULE_LINE_ID NUMBER := NULL; -- default is NULL
12 V_PARENT_LINE_ID NUMBER := NULL; -- default is NULL
13 V_LINE_NUMBER NUMBER := NULL; -- default is NULL
14 BASE_LINE_NUMBER NUMBER := NULL; -- default is NULL
15 BEGIN
16 IF ( P_SERVICE_PARENT_LINE_ID IS NULL ) THEN
17 V_SHIPMENT_SCHEDULE_LINE_ID := P_SHIPMENT_SCHEDULE_LINE_ID;
18 V_PARENT_LINE_ID := P_PARENT_LINE_ID;
19 V_LINE_NUMBER := P_LINE_NUMBER;
20 ELSE
21 SELECT SHIPMENT_SCHEDULE_LINE_ID,
22 PARENT_LINE_ID,
23 LINE_NUMBER
24 INTO V_SHIPMENT_SCHEDULE_LINE_ID,
25 V_PARENT_LINE_ID,
26 V_LINE_NUMBER
27 FROM SO_LINES_ALL
28 WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
29 END IF;
30 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NULL) THEN
31 IF (V_PARENT_LINE_ID IS NULL) THEN
32 BASE_LINE_NUMBER := V_LINE_NUMBER;
33 ELSE
34 SELECT LINE_NUMBER
35 INTO BASE_LINE_NUMBER
36 FROM SO_LINES_ALL
37 WHERE LINE_ID = V_PARENT_LINE_ID;
38 END IF;
39 ELSE
40 SELECT LINE_NUMBER
41 INTO BASE_LINE_NUMBER
42 FROM SO_LINES_ALL
43 WHERE LINE_ID = V_SHIPMENT_SCHEDULE_LINE_ID;
44 END IF;
45
46 RETURN(BASE_LINE_NUMBER);
47
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 RETURN( NULL );
51 END BASE_LINE_NUMBER;
52
53
54 function SHIPMENT_SCHEDULE_NUMBER(
55 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
56 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
57 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
58 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
59 )
60 return NUMBER
61 IS
62 V_SHIPMENT_SCHEDULE_LINE_ID NUMBER := NULL; -- default is NULL
63 V_PARENT_LINE_ID NUMBER := NULL; -- default is NULL
64 V_LINE_NUMBER NUMBER := NULL; -- default is NULL
65 SCHEDULE_LINE_NUMBER NUMBER := NULL; -- default is NULL
66 BEGIN
67 IF ( P_SERVICE_PARENT_LINE_ID IS NULL ) THEN
68 V_SHIPMENT_SCHEDULE_LINE_ID := P_SHIPMENT_SCHEDULE_LINE_ID;
69 V_PARENT_LINE_ID := P_PARENT_LINE_ID;
70 V_LINE_NUMBER := P_LINE_NUMBER;
71 ELSE
72 SELECT SHIPMENT_SCHEDULE_LINE_ID,
73 PARENT_LINE_ID,
74 LINE_NUMBER
75 INTO V_SHIPMENT_SCHEDULE_LINE_ID,
76 V_PARENT_LINE_ID,
77 V_LINE_NUMBER
78 FROM SO_LINES_ALL
79 WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
80 END IF;
81 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NOT NULL) THEN
82 IF (V_PARENT_LINE_ID IS NULL) THEN
83 SCHEDULE_LINE_NUMBER := V_LINE_NUMBER;
84 ELSE
85 SELECT LINE_NUMBER
86 INTO SCHEDULE_LINE_NUMBER
87 FROM SO_LINES_ALL
88 WHERE LINE_ID = V_PARENT_LINE_ID;
89 END IF;
90 END IF;
91
92 RETURN(SCHEDULE_LINE_NUMBER);
93
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 RETURN( NULL );
97 END SHIPMENT_SCHEDULE_NUMBER;
98
99
100 function OPTION_LINE_NUMBER(
101 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
102 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
103 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
104 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
105 )
106 return NUMBER
107 IS
108 V_SHIPMENT_SCHEDULE_LINE_ID NUMBER := NULL; -- default is NULL
109 V_PARENT_LINE_ID NUMBER := NULL; -- default is NULL
110 V_LINE_NUMBER NUMBER := NULL; -- default is NULL
111 OPTION_LINE_NUMBER NUMBER := NULL; -- default is NULL
112 BEGIN
113 IF ( P_SERVICE_PARENT_LINE_ID IS NULL ) THEN
114 V_SHIPMENT_SCHEDULE_LINE_ID := P_SHIPMENT_SCHEDULE_LINE_ID;
115 V_PARENT_LINE_ID := P_PARENT_LINE_ID;
116 V_LINE_NUMBER := P_LINE_NUMBER;
117 ELSE
118 SELECT SHIPMENT_SCHEDULE_LINE_ID,
119 PARENT_LINE_ID,
120 LINE_NUMBER
121 INTO V_SHIPMENT_SCHEDULE_LINE_ID,
122 V_PARENT_LINE_ID,
123 V_LINE_NUMBER
124 FROM SO_LINES_ALL
125 WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
126 END IF;
127 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NULL) THEN
128 IF (V_PARENT_LINE_ID IS NOT NULL) THEN
129 OPTION_LINE_NUMBER := V_LINE_NUMBER;
130 END IF;
131 ELSE
132 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NOT NULL) THEN
133 IF (V_PARENT_LINE_ID IS NOT NULL) THEN
134 OPTION_LINE_NUMBER := V_LINE_NUMBER;
135 END IF;
136 END IF;
137 END IF;
138
139 RETURN(OPTION_LINE_NUMBER);
140
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 RETURN( NULL );
144 END OPTION_LINE_NUMBER;
145
146 END ONT_UPGRADE_EXCEPTION_REPORT;
[Home] [Help]