1 PACKAGE BODY WSH_CONT_PKG as
2 /* $Header: WSHCONTB.pls 115.5 99/07/26 11:08:01 porting ship $ */
3
4 -- Name get_master_container
5 -- Purpose get master container id and master serial number
6 -- Arguments
7 -- X_container_id
8 -- X_master_container_id
9 -- X_master_serial_number
10
11 PROCEDURE get_master_container(
12 X_container_id IN NUMBER,
13 X_master_container_id IN OUT NUMBER,
14 X_master_serial_number IN OUT VARCHAR2)
15 IS
16 CURSOR get_master_container(
17 P_container_id NUMBER)
18 IS
19 SELECT container_id, master_serial_number
20 FROM wsh_packed_containers
21 WHERE parent_container_id IS NULL
22 START WITH container_id = P_container_id
23 CONNECT BY PRIOR parent_container_id = container_id;
24
25 BEGIN
26 OPEN get_master_container(X_container_id);
27 FETCH get_master_container INTO X_master_container_id, X_master_serial_number;
28 IF (get_master_container%ISOPEN) THEN
29 CLOSE get_master_container;
30 END IF;
31 END get_master_container;
32
33
34 -- Name check_child_containers
35 -- Purpose find all child containers and update master container
36 -- id and master serial number
37 -- Arguments
38 -- X_delivery_id
39 -- X_container_id
40 -- X_master_container_id
41 -- X_master_serial_number
42 -- X_status
43
44 PROCEDURE check_child_containers(
45 X_delivery_id IN NUMBER,
46 X_container_id IN NUMBER,
47 X_master_container_id IN NUMBER,
48 X_master_serial_number IN VARCHAR2,
49 X_status IN OUT NUMBER)
50 IS
51 CURSOR get_child_cont(
52 P_container_id NUMBER)
53 IS
54 SELECT container_id
55 FROM wsh_packed_containers
56 START WITH parent_container_id = P_container_id
57 CONNECT BY PRIOR container_id = parent_container_id;
58
59 L_container_id NUMBER;
60
61 BEGIN
62 X_status := 0;
63
64 OPEN get_child_cont(X_container_id);
65 LOOP
66 FETCH get_child_cont INTO L_container_id;
67 EXIT WHEN get_child_cont%NOTFOUND;
68
69 UPDATE wsh_packed_containers
70 SET master_container_id = X_master_container_id,
71 master_serial_number = X_master_serial_number
72 WHERE container_id = L_container_id
73 AND delivery_id = X_delivery_id;
74
75 IF (SQL%ROWCOUNT > 0) THEN
76 X_status := 1;
77 END IF;
78 END LOOP;
79
80 IF (get_child_cont%ISOPEN) THEN
81 CLOSE get_child_cont;
82 END IF;
83 END check_child_containers;
84
85
86 -- Name validate_master_serial_number
87 -- Purpose Customizable API for validating master serial number.
88 -- This API by default will always return true.
89 -- It can be customized to perform validation according to the
90 -- customer business needs.
91 -- Arguments
92 -- X_delivery_id
93 -- X_container_sequence_number
94 -- X_status
95
96 PROCEDURE validate_master_serial_number(
97 X_delivery_id IN NUMBER,
98 X_container_sequence_number IN NUMBER,
99 X_status IN OUT NUMBER)
100 IS
101 BEGIN
102 null;
103 END validate_master_serial_number;
104
105
106 -- Name update_master_serial_number
107 -- Purpose update master serial number
108 -- Arguments
109 -- X_master_serial_number
110 -- X_container_id
111 -- X_delivery_id
112 -- X_status
113
114 PROCEDURE update_master_serial_number(
115 X_master_serial_number IN VARCHAR2,
116 X_container_id IN NUMBER,
117 X_delivery_id IN NUMBER,
118 X_status IN OUT NUMBER)
119 IS
120 CURSOR get_child_cont(
121 P_container_id NUMBER)
122 IS
123 SELECT container_id
124 FROM wsh_packed_containers
125 START WITH parent_container_id = P_container_id
126 CONNECT BY PRIOR container_id = parent_container_id;
127
128 L_container_id NUMBER;
129 BEGIN
130 X_status := 0;
131 OPEN get_child_cont(X_container_id);
132 LOOP
133 FETCH get_child_cont INTO L_container_id;
134 EXIT WHEN get_child_cont%NOTFOUND;
135
136 UPDATE wsh_packed_containers
137 SET master_serial_number = X_master_serial_number
138 WHERE container_id = L_container_id
139 AND delivery_id = X_delivery_id;
140
141 IF (SQL%ROWCOUNT > 0) THEN
142 X_status := 1;
143 END IF;
144 END LOOP;
145
146 IF (get_child_cont%ISOPEN) THEN
147 CLOSE get_child_cont;
148 END IF;
149 END update_master_serial_number;
150
151
152 PROCEDURE get_master_serial_number(
153 X_sequence_number IN NUMBER,
154 X_delivery_id IN NUMBER,
155 X_master_serial_number IN OUT VARCHAR2)
156 IS
157 CURSOR c1(
158 P_sequence_number NUMBER,
159 P_delivery_id NUMBER)
160 IS
161 SELECT master_serial_number
162 FROM wsh_packed_containers
163 WHERE parent_sequence_number IS NULL
164 START WITH sequence_number = p_sequence_number
165 AND delivery_id = p_delivery_id
166 CONNECT BY PRIOR parent_sequence_number = sequence_number
167 AND delivery_id = p_delivery_id;
168
169 BEGIN
170 OPEN c1(X_sequence_number, X_delivery_id);
171 FETCH c1 INTO X_master_serial_number;
172 CLOSE c1;
173 END get_master_serial_number;
174
175
176 END WSH_CONT_PKG;
[Home] [Help]