[Home] [Help]
PACKAGE BODY: APPS.PO_REQ_DIST_SV1
Source
1 PACKAGE BODY po_req_dist_sv1 AS
2 /* $Header: POXRQD2B.pls 115.6 2003/12/19 18:57:52 jskim ship $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: get_dist_num_account()
7
8 ===========================================================================*/
9
10 PROCEDURE get_dist_num_account(x_requisition_line_id IN OUT NOCOPY NUMBER,
11 x_num_of_dist IN OUT NOCOPY NUMBER,
12 x_code_combination_id IN OUT NOCOPY NUMBER)
13 IS
14
15 x_progress VARCHAR2(3) := NULL;
16
17 BEGIN
18 x_progress := '001';
19
20 SELECT count(distribution_id)
21 INTO x_num_of_dist
22 FROM po_req_distributions
23 WHERE requisition_line_id = x_requisition_line_id;
24
25 --
26 -- If there is one distribution then obtain
27 -- the code combination id for the distribution.
28 --
29 IF (x_num_of_dist <> 1) THEN
30 x_code_combination_id := null;
31 return;
32
33 ELSE
34
35 x_progress := '020';
36
37 SELECT code_combination_id
38 INTO x_code_combination_id
39 FROM po_req_distributions
40 WHERE requisition_line_id = x_requisition_line_id;
41
42 END IF;
43
44
45 EXCEPTION
46 WHEN NO_DATA_FOUND THEN
47 x_num_of_dist := 0;
48
49 WHEN OTHERS THEN
50 --dbms_output.put_line('In Exception');
51 po_message_s.sql_error('get_max_dist_num', x_progress, sqlcode);
52 raise;
53
54 END get_dist_num_account;
55
56 /*===========================================================================
57
58 PROCEDURE NAME: get_dist_account()
59
60 ===========================================================================*/
61
62 FUNCTION get_dist_account(x_requisition_line_id IN NUMBER) return NUMBER
63 IS
64
65 x_progress VARCHAR2(3) := NULL;
66 x_num_of_dist NUMBER;
67 x_code_combination_id NUMBER;
68
69 BEGIN
70 x_progress := '001';
71 -- Bug: 1702850 use _all table instead of stripe table
72 SELECT count(distribution_id)
73 INTO x_num_of_dist
74 FROM po_req_distributions_all
75 WHERE requisition_line_id = x_requisition_line_id;
76
77 --
78 -- If there is one distribution then obtain
79 -- the code combination id for the distribution.
80 --
81 IF (x_num_of_dist = 0) THEN
82 return(null);
83
84 ELSIF (x_num_of_dist > 1) THEN
85
86 return(-11); /* Use -11 to signal multiple distributions. POXRQLNS.pld uses
87 ** this to display "Multiple" in the charge_account field */
88
89 ELSIF (x_num_of_dist = 1) THEN
90 x_progress := '020';
91
92 -- Bug: 1702850 use _all table instead of stripe table
93
94 SELECT code_combination_id
95 INTO x_code_combination_id
96 FROM po_req_distributions_all
97 WHERE requisition_line_id = x_requisition_line_id;
98
99 return(x_code_combination_id);
100
101 END IF;
102
103
104 EXCEPTION
105
106 WHEN OTHERS THEN
107 /* dbms_output.put_line('In Exception'); */
108 /* po_message_s.sql_error('get_dist_account', x_progress, sqlcode); */
109 raise;
110
111 END get_dist_account;
112
113
114 /*===========================================================================
115
116 PROCEDURE NAME: update_dist_quantity()
117
118 ===========================================================================*/
119
120
121 PROCEDURE update_dist_quantity(x_requisition_line_id NUMBER,
122 x_line_quantity NUMBER)
123
124 IS
125
126 x_progress VARCHAR2(3) := NULL;
127 x_num_of_dist NUMBER := NULL;
128 Recinfo po_req_distributions%rowtype;
129
130 BEGIN
131 x_progress := '001';
132
133 SELECT count(distribution_id)
134 INTO x_num_of_dist
135 FROM po_req_distributions
136 WHERE requisition_line_id = x_requisition_line_id
137 AND NOT EXISTS (SELECT 'there are encumbered distributions'
138 FROM po_req_distributions prd2
139 WHERE prd2.requisition_line_id = x_requisition_line_id
140 AND ( nvl(prd2.encumbered_flag, 'N') <> 'N')
141 );
142
143 --
144 -- If there is one distribution then obtain
145 -- update the distribution quantity.
146 --
147 IF ((x_num_of_dist <> 1) OR
148 (x_num_of_dist is null))THEN
149 return;
150
151 ELSE
152
153 x_progress := '020';
154
155 --
156 -- Lock the distribution.
157 --
158
159
160 SELECT *
161 INTO Recinfo
162 FROM po_req_distributions
163 WHERE requisition_line_id = x_requisition_line_id
164 FOR UPDATE OF req_line_quantity NOWAIT;
165
166 x_progress := '030';
167
168 UPDATE po_req_distributions prd
169 SET req_line_quantity = x_line_quantity
170 WHERE prd.requisition_line_id = x_requisition_line_id;
171
172 END IF;
173
174
175 EXCEPTION
176 WHEN NO_DATA_FOUND THEN
177 return;
178
179 WHEN OTHERS THEN
180 --dbms_output.put_line('In Exception');
181 po_message_s.sql_error('update_dist_quantity', x_progress, sqlcode);
182 raise;
183
184 END update_dist_quantity;
185
186 --< Bug 3265539 > Removed unused function get_project_num.
187
188 END po_req_dist_sv1;