DBA Data[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;