1 PACKAGE BODY po_calculatereqtotal_pvt AS
2 /* $Header: POXVRTCB.pls 120.0.12020000.2 2013/02/10 13:03:55 vegajula ship $ */
3
4 /**
5 * Public FUNCTION get_req_distribution_total
6 * Requires: Requisition Header Id
7 * Requisition Line Id
8 * Requisition Distribution Id
9 * Modifies: None.
10 * Effects: Return updated distribution qty X new price
11 from po_change_requests if any changes were made
12 * Else return req_line_quantity X unit price
13 * from po_req_distributions and po_requisition_lines
14 * If line was cancelled return 0
15 * Returns:
16 * Revised req total coming from a distribution
17 * If something fails returns 0
18 */
19 FUNCTION get_req_distribution_total(p_header_id IN NUMBER,
20 p_line_id IN NUMBER,
21 p_distribution_id IN NUMBER)
22 RETURN NUMBER
23 IS
24 l_matching_basis po_requisition_lines_all.matching_basis%type;
25 l_action_type po_change_requests.action_type%type;
26 l_dist_total NUMBER := 0;
27 l_change_request_group_id po_change_requests.change_request_group_id%type;
28 BEGIN
29
30 SELECT prl.matching_basis, pcr.action_type,
31 decode (prl.matching_basis, 'AMOUNT' , prd.req_line_amount,
32 prd.req_line_quantity*prl.unit_price) +
33 nvl(prd.nonrecoverable_tax,0),
34 pcr.change_request_group_id
35 INTO l_matching_basis, l_action_type, l_dist_total,
36 l_change_request_group_id
37 FROM po_requisition_lines_all prl,
38 po_req_distributions_all prd,
39 po_change_requests pcr
40 WHERE prl.requisition_line_id = p_line_id
41 AND prl.requisition_line_id = prd.requisition_line_id
42 AND prd.distribution_id = p_distribution_id
43 AND nvl(prl.cancel_flag,'N') = 'N'
44 AND nvl(prl.modified_by_agent_flag, 'N') = 'N'
45 AND pcr.document_line_id(+) = prl.requisition_line_id
46 AND pcr.document_type(+) = 'REQ'
47 AND pcr.action_type(+) <> 'DERIVED'
48 AND pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
49 AND rownum =1;
50
51 IF (l_action_type is null) THEN
52 RETURN l_dist_total;
53 ELSIF (l_action_type = 'CANCELLATION') THEN
54 RETURN 0;
55 ELSE
56 RETURN get_new_distribution_total(p_header_id, p_line_id,
57 p_distribution_id, l_matching_basis,
58 l_change_request_group_id);
59 END IF;
60
61 EXCEPTION WHEN OTHERS THEN
62 RETURN 0;
63 END get_req_distribution_total;
64
65 -- Bug 16168687 start
66
67 /* The following function is same as get_req_distribution_total except that
68 query for getting dist total is modified to exclude SYSTEMSAVE records
69 from po_change_requests table. This is required as these records causing
70 issue in approving the requisition. Creating new function as existing
71 function is required in AME where SYSTEMSAVE records also needs to be
72 considered.*/
73
74 /**
75 * Public FUNCTION get_req_dist_total
76 * Requires: Requisition Header Id
77 * Requisition Line Id
78 * Requisition Distribution Id
79 * Modifies: None.
80 * Effects: Return updated distribution qty X new price
81 from po_change_requests if any changes were made
82 * Else return req_line_quantity X unit price
83 * from po_req_distributions and po_requisition_lines
84 * If line was cancelled return 0
85 * Returns:
86 * Revised req total coming from a distribution
87 * If something fails returns 0
88 */
89 FUNCTION get_req_dist_total(p_header_id IN NUMBER,
90 p_line_id IN NUMBER,
91 p_distribution_id IN NUMBER)
92 RETURN NUMBER
93 IS
94 l_matching_basis po_requisition_lines_all.matching_basis%type;
95 l_action_type po_change_requests.action_type%type;
96 l_dist_total NUMBER := 0;
97 l_change_request_group_id po_change_requests.change_request_group_id%type;
98 BEGIN
99
100 SELECT prl.matching_basis, pcr.action_type,
101 decode (prl.matching_basis, 'AMOUNT' , prd.req_line_amount,
102 prd.req_line_quantity*prl.unit_price) +
103 nvl(prd.nonrecoverable_tax,0),
104 pcr.change_request_group_id
105 INTO l_matching_basis, l_action_type, l_dist_total,
106 l_change_request_group_id
107 FROM po_requisition_lines_all prl,
108 po_req_distributions_all prd,
109 po_change_requests pcr
110 WHERE prl.requisition_line_id = p_line_id
111 AND prl.requisition_line_id = prd.requisition_line_id
112 AND prd.distribution_id = p_distribution_id
113 AND nvl(prl.cancel_flag,'N') = 'N'
114 AND nvl(prl.modified_by_agent_flag, 'N') = 'N'
115 AND pcr.document_line_id(+) = prl.requisition_line_id
116 AND pcr.document_type(+) = 'REQ'
117 AND pcr.action_type(+) <> 'DERIVED'
118 AND pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED','SYSTEMSAVE') /* added SYSTEMSAVE condition */
119 AND rownum =1;
120
121 IF (l_action_type is null) THEN
122 RETURN l_dist_total;
123 ELSIF (l_action_type = 'CANCELLATION') THEN
124 RETURN 0;
125 ELSE
126 RETURN get_new_distribution_total(p_header_id, p_line_id,
127 p_distribution_id, l_matching_basis,
128 l_change_request_group_id);
129 END IF;
130
131 EXCEPTION WHEN OTHERS THEN
132 RETURN 0;
133 END get_req_dist_total;
134
135 -- Bug 16168687 End
136
137 /**
138 * Public FUNCTION get_new_distribution_total
139 * Requires: Requisition Header Id
140 * Requisition Line Id
141 * Distribution Id
142 * Assumes that the line is not cancelled
143 * Modifies: None.
144 * Effects: Return updated requisition total
145 * for a distribution
146 * using revised values of distribution qty and line price
147 * Returns:
148 * Revised req total coming from a distribution
149 * If something fails returns 0
150 */
151
152 FUNCTION get_new_distribution_total(p_header_id IN NUMBER,
153 p_line_id IN NUMBER,
154 p_distribution_id IN NUMBER,
155 p_matching_basis IN VARCHAR2,
156 p_change_request_group_id IN NUMBER)
157 RETURN NUMBER
158 IS
159 l_nonrec_tax NUMBER := 0;
160 l_old_dist_total NUMBER := 0;
161 l_new_dist_total NUMBER := 0;
162 BEGIN
163 IF (p_matching_basis = 'AMOUNT') THEN
164
165 -- get updated amount
166 SELECT prd.req_line_amount,
167 nvl(pcr.new_amount, prd.req_line_amount),
168 prd.nonrecoverable_tax
169 INTO l_old_dist_total, l_new_dist_total, l_nonrec_tax
170 FROM po_change_requests pcr,
171 po_req_distributions_all prd
172 WHERE prd.distribution_id = p_distribution_id
173 AND pcr.document_line_id(+) = prd.requisition_line_id
174 AND pcr.document_distribution_id(+) = prd.distribution_id
175 AND pcr.request_status(+) NOT IN ('ACCEPTED','REJECTED');
176
177 ELSE
178
179 -- get updated line price and quantity
180 SELECT prd.req_line_quantity*prl.unit_price,
181 nvl(pcr1.new_quantity, prd.req_line_quantity)*nvl(pcr.new_price, prl.unit_price),
182 prd.nonrecoverable_tax
183 INTO l_old_dist_total, l_new_dist_total, l_nonrec_tax
184 FROM po_change_requests pcr,
185 po_change_requests pcr1,
186 po_requisition_lines_all prl,
187 po_req_distributions_all prd
188 WHERE prd.distribution_id = p_distribution_id
189 AND pcr1.document_distribution_id(+) = prd.distribution_id
190 AND pcr1.document_line_id(+) = prd.requisition_line_id
191 AND pcr1.request_status(+) NOT IN ('ACCEPTED','REJECTED')
192 AND prd.requisition_line_id = prl.requisition_line_id
193 AND pcr.document_line_id(+) = prl.requisition_line_id
194 AND pcr.new_price(+) IS NOT NULL
195 AND pcr.old_price(+) IS NOT NULL
196 AND pcr.document_type(+) = 'REQ'
197 AND pcr.request_status(+) NOT IN ('ACCEPTED','REJECTED')
198 AND pcr.request_level(+) = 'LINE'
199 AND pcr.action_type(+) <> 'CANCELLATION'
200 AND pcr.change_request_group_id(+) = p_change_request_group_id;
201
202 END IF;
203
204 IF (l_old_dist_total IS NOT NULL AND l_old_dist_total > 0) THEN
205 l_nonrec_tax := NVL((l_nonrec_tax / l_old_dist_total) * l_new_dist_total, 0);
206 RETURN NVL(l_nonrec_tax + l_new_dist_total, 0);
207 ELSE
208 RETURN NVL(l_new_dist_total, 0);
209 END IF;
210
211 EXCEPTION WHEN OTHERS THEN
212 RETURN 0;
213 END get_new_distribution_total;
214
215 END PO_CALCULATEREQTOTAL_PVT;
[Home] [Help]