1 PACKAGE BODY PO_DIST_S as
2 /* $Header: POXPOPDB.pls 120.1.12000000.2 2007/10/17 11:54:28 ppadilam ship $ */
3 /*======================== PO_DIST_S ====================================*/
4 /*===========================================================================
5 PROCEDURE NAME: test_get_total_dist_qty
6 ===========================================================================*/
7 PROCEDURE test_get_total_dist_qty(X_po_line_location_id IN NUMBER) IS
8 X_total_quantitya NUMBER;
9 BEGIN
10 -- dbms_output.put_line('before call');
11 po_dist_s.get_total_dist_qty(X_po_line_location_id, X_total_quantitya);
12
13
14 -- dbms_output.put_line('after call');
15 -- dbms_output.put_line(X_total_quantitya);
16 END test_get_total_dist_qty;
17 /*===========================================================================
18 PROCEDURE NAME: get_total_dist_qty
19 ===========================================================================*/
20 PROCEDURE get_total_dist_qty
21 (X_po_line_location_id IN NUMBER,
22 X_total_quantity IN OUT NOCOPY NUMBER) IS
23 X_progress varchar2(3) := '';
24 CURSOR C is
25 SELECT sum(POD.quantity_ordered)
26 FROM po_distributions POD
27 WHERE POD.po_distribution_id = X_po_line_location_id;
28 BEGIN
29 -- dbms_output.put_line('Before open cursor');
30 if (X_po_line_location_id is not null) then
31 X_progress := '010';
32 OPEN C;
33 X_progress := '020';
34 FETCH C into X_total_quantity;
35 CLOSE C;
36 else
37 X_progress := '030';
38 po_message_s.sql_error('get_total_dist_qty', X_progress, sqlcode);
39 end if;
40 EXCEPTION
41 when others then
42 -- dbms_output.put_line('In exception');
43 po_message_s.sql_error('get_total_dist_qty', X_progress, sqlcode);
44 END get_total_dist_qty;
45 /*===========================================================================
46 FUNCTION NAME: test_val_distribution_exists
47 ===========================================================================*/
48 PROCEDURE test_val_distribution_exists(X_po_line_location_id IN NUMBER) IS
49 X_val_dist BOOLEAN;
50 BEGIN
51 -- dbms_output.put_line('before call');
52 X_val_dist := po_dist_s.val_distribution_exists(X_po_line_location_id);
53
54
55 -- dbms_output.put_line('after call');
56 END test_val_distribution_exists;
57 /*===========================================================================
58 FUNCTION NAME: val_distribution_exists
59 ===========================================================================*/
60 FUNCTION val_distribution_exists
61 (X_po_line_location_id IN NUMBER) RETURN BOOLEAN IS
62 X_progress VARCHAR2(3) := '';
63 X_max_distribution_id NUMBER := '';
64 BEGIN
65 SELECT max(POD.po_distribution_id)
66 INTO X_max_distribution_id
67 FROM po_distributions POD
68 WHERE POD.line_location_id = X_po_line_location_id;
69 IF (X_max_distribution_id is null) THEN
70 -- dbms_output.put_line('returned false');
71 return(FALSE);
72 ELSE
73 -- dbms_output.put_line('returned true');
74 return(TRUE);
75 END IF;
76 EXCEPTION
77 when others then
78 -- dbms_output.put_line('In exception');
79 po_message_s.sql_error('val_distribution_exists', X_progress, sqlcode);
80 END val_distribution_exists;
81
82 /*===========================================================================
83
84 FUNCTION NAME: val_approval_status
85
86 ===========================================================================*/
87 FUNCTION val_approval_status
88 (X_distribution_id IN NUMBER,
89 X_distribution_num IN NUMBER,
90 X_deliver_to_person_id IN NUMBER,
91 X_quantity_ordered IN NUMBER,
92 X_amount_ordered IN NUMBER,
93 X_rate IN NUMBER,
94 X_rate_date IN DATE,
95 X_gl_encumbered_date IN DATE,
96 X_charge_account_id IN NUMBER,
97 X_project_id IN NUMBER, -- Bug # 6408034
98 --< Shared Proc FPJ Start >
99 p_dest_charge_account_id IN NUMBER,
100 --< Shared Proc FPJ End >
101
102 X_recovery_rate IN NUMBER,
103 X_destination_subinventory IN VARCHAR2) RETURN NUMBER IS
104
105 X_temp_deliver_to_person_id NUMBER;
106 X_temp_quantity_ordered NUMBER;
107 X_temp_amount_ordered NUMBER;
108 X_temp_rate NUMBER;
109 X_temp_rate_date DATE;
110 X_temp_gl_encumbered_date DATE;
111 X_temp_charge_account_id NUMBER;
112 X_temp_project_id NUMBER; -- Bug # 6408034
113 X_temp_recovery_rate NUMBER;
114 X_temp_distribution_num NUMBER;
115 X_need_to_approve NUMBER := NULL;
116 X_temp_dest_subinventory VARCHAR2(10);
117 X_progress VARCHAR2(3) := '';
118
119 --< Shared Proc FPJ Start >
120 l_temp_dest_charge_account_id NUMBER;
121 --< Shared Proc FPJ End >
122
123 BEGIN
124
125 X_progress := '010';
126
127 SELECT
128 deliver_to_person_id,
129 quantity_ordered,
130 amount_ordered,
131 rate,
132 rate_date,
133 gl_encumbered_date,
134 code_combination_id,
135 project_id, -- Bug # 6408034
136 --< Shared Proc FPJ Start >
137 dest_charge_account_id,
138 --< Shared Proc FPJ End >
139
140 recovery_rate,
141 distribution_num,
142 destination_subinventory
143 INTO
144 X_temp_deliver_to_person_id,
145 X_temp_quantity_ordered,
146 X_temp_amount_ordered,
147 X_temp_rate,
148 X_temp_rate_date,
149 X_temp_gl_encumbered_date,
150 X_temp_charge_account_id,
151 X_temp_project_id, -- Bug # 6408034
152 --< Shared Proc FPJ Start >
153 l_temp_dest_charge_account_id,
154 --< Shared Proc FPJ End >
155
156 X_temp_recovery_rate,
157 X_temp_distribution_num,
158 X_temp_dest_subinventory
159 FROM po_distributions
160 WHERE po_distribution_id = X_distribution_id;
161
162 -- Bug 5409088: Added check for amount ordered
163 IF ((X_temp_quantity_ordered <> X_quantity_ordered )
164 OR (X_temp_quantity_ordered is NULL
165 AND
166 X_quantity_ordered is NOT NULL)
167 OR (X_temp_quantity_ordered is NOT NULL
168 AND
169 X_quantity_ordered is NULL)
170 OR (X_temp_amount_ordered <> X_amount_ordered )
171 OR (X_temp_amount_ordered is NULL
172 AND
173 X_amount_ordered is NOT NULL)
174 OR (X_temp_amount_ordered is NOT NULL
175 AND
176 X_amount_ordered is NULL)
177 OR (X_temp_deliver_to_person_id <> X_deliver_to_person_id)
178 OR (X_temp_deliver_to_person_id is NULL
179 AND
180 X_deliver_to_person_id IS NOT NULL)
181 OR (X_temp_deliver_to_person_id IS NOT NULL
182 AND
183 X_deliver_to_person_id IS NULL)
184 OR (X_temp_rate_date <> X_rate_date)
185 OR (X_temp_rate_date IS NULL
186 AND
187 X_rate_date IS NOT NULL)
188 OR (X_temp_rate_date IS NOT NULL
189 AND
190 X_rate_date IS NULL)
191 OR (X_temp_rate <> X_rate)
192 OR (X_temp_rate IS NULL
193 AND
194 X_rate IS NOT NULL)
195 OR (X_temp_rate IS NOT NULL
196 AND
197 X_rate IS NULL)
198 -- Bug 3268649
199 -- OR (X_gl_encumbered_date <> X_gl_encumbered_date)
200 OR (X_temp_gl_encumbered_date <> X_gl_encumbered_date)
201 OR (X_temp_gl_encumbered_date IS NULL
202 AND
203 X_gl_encumbered_date IS NOT NULL)
204 OR (X_temp_gl_encumbered_date IS NOT NULL
205 AND
206 X_gl_encumbered_date IS NULL)
207 OR (X_temp_recovery_rate <> X_recovery_rate)
208 OR (X_temp_recovery_rate IS NULL
209 AND
210 X_recovery_rate IS NOT NULL)
211 OR (X_temp_recovery_rate IS NOT NULL
212 AND
213 X_recovery_rate IS NULL)
214 OR (X_temp_dest_subinventory <> X_destination_subinventory)
215 OR (X_temp_dest_subinventory IS NULL
216 AND
217 X_destination_subinventory IS NOT NULL)
218 OR (X_temp_dest_subinventory IS NOT NULL
219 AND
220 X_destination_subinventory IS NULL)
221
222 OR (X_temp_charge_account_id <> X_charge_account_id)
223 OR (X_temp_charge_account_id IS NULL
224 AND
225 X_charge_account_id IS NOT NULL)
226 OR (X_temp_charge_account_id IS NOT NULL
227 AND
228 X_charge_account_id IS NULL)
229
230 /* start Bug # 6408034 */
231 OR (X_temp_project_id <> X_project_id)
232 OR (X_temp_project_id IS NULL
233 AND
234 X_project_id IS NOT NULL)
235 OR (X_temp_project_id IS NOT NULL
236 AND
237 X_project_id IS NULL)
238
239 /* end Bug # 6408034 */
240
241 --< Shared Proc FPJ Start >
242 OR (l_temp_dest_charge_account_id <> p_dest_charge_account_id)
243 OR (l_temp_dest_charge_account_id IS NULL AND
244 p_dest_charge_account_id IS NOT NULL)
245 OR (l_temp_dest_charge_account_id IS NOT NULL AND
246 p_dest_charge_account_id IS NULL)
247 --< Shared Proc FPJ End >
248
249 ) then
250
251 /* Unapprove Both the doc and the shipment */
252
253 X_need_to_approve := 2;
254
255 END IF;
256
257 /* Bug 3268649: The code below was moved to before the return.
258 * Otherwise, it would never be run!
259 */
260
261 /* bug 1046786 added the distribution_num check to unapprove the PO header */
262
263 if
264 ((X_temp_distribution_num <> X_distribution_num)
265 OR (X_temp_distribution_num IS NULL
266 AND
267 X_distribution_num IS NOT NULL)
268 OR (X_temp_distribution_num IS NOT NULL
269 AND
270 X_distribution_num IS NULL)) then
271
272
273 /* Unapprove Only the Doc if this is the only change.
274 ** If the document already needs to be re-approved due to other
275 ** changes above, leave it at 2. */
276
277 if X_need_to_approve is NULL then
278 X_need_to_approve := 1;
279 end if;
280
281 end if; /* Dist num Check */
282
283 /* End Bug 3268649 */
284
285 return(X_need_to_approve);
286
287
288 EXCEPTION
289 WHEN NO_DATA_FOUND THEN
290 -- dbms_output.put_line('No data found');
291 return(0);
292 WHEN OTHERS THEN
293 -- dbms_output.put_line('In UPDATE exception');
294 po_message_s.sql_error('val_approval_status', X_progress, sqlcode);
295 raise;
296
297 END val_approval_status;
298
299
300 END PO_DIST_S;
[Home] [Help]