1 PACKAGE BODY PO_CONTRACTS_S AS
2 /* $Header: pocontvb.pls 120.1 2007/10/08 13:04:35 lswamina ship $ */
3
4
5
6 /*===========================================================================
7
8 FUNCTION NAME: val_contract_amount
9
10 ===========================================================================*/
11
12 FUNCTION val_contract_amount (X_po_header_id IN NUMBER) RETURN NUMBER IS
13
14 X_current_amount NUMBER := 0;
15 X_purchased_amount NUMBER := 0;
16 X_contract_id NUMBER := 0;
17 X_progress NUMBER := 10;
18 X_currency_code PO_HEADERS_ALL.CURRENCY_CODE%TYPE; --Bug# 4685260
19 X_rate PO_HEADERS_ALL.RATE%TYPE; --Bug# 4685260
20
21 l_amount_limit_func NUMBER; -- bug3673292
22 x_diff_curr VARCHAR2(1); --Bug# 4685260
23
24 -- bug3673292
25 -- get_contracts will return only the contracts that have amount limits
26
27 CURSOR get_contracts IS
28 SELECT distinct contracts.po_header_id,
29 contracts.amount_limit ,
30 NVL(contracts.rate, 1), -- Bug# 4685260
31 contracts.currency_code --Bug# 4685260
32 FROM po_lines_gt pol, -- <GC FPJ>
33 po_headers contracts --<BUG 3209400> -- <GC FPJ>
34 WHERE contracts.po_header_id = pol.contract_id
35 AND NVL(contracts.global_agreement_flag, 'N') = 'N' -- <GC FPJ>
36 AND pol.po_header_id = X_po_header_id
37 AND contracts.amount_limit IS NOT NULL; -- bug3673292
38
39
40 BEGIN
41
42 /* Main loop to get all po_lines that you're trying to insert and then
43 go get the total po amounts that you've created to see if it's okay to
44 insert this po
45 */
46 X_progress := 10;
47
48 OPEN get_contracts;
49
50 /* Loop through the lines and check to see how much of the contract has
51 been used up by other po's that have been created
52 */
53 LOOP
54
55 FETCH get_contracts INTO X_contract_id,
56 l_amount_limit_func, -- bug3673292
57 x_rate, -- Bug# 4685260
58 x_currency_code; --Bug# 4685260
59 EXIT WHEN get_contracts%NOTFOUND;
60
61 --dbms_output.put_line('X_contract_id = ' || X_contract_id);
62
63 X_progress := 20;
64
65 /* Go get the amounts that have been used on other po's for
66 this contract. Need an NVL rather than an exception handler
67 for no data found since SUM always returns a row but it could be
68 blank. If it's null then the tally in the select 1 check will
69 always fail even though it should pass
70 */
71
72 /* Bug# 2362213: kagarwal
73 ** Desc: When getting the amount on Std PO referencing the
74 ** contract, consider the rate on the Std PO and not that in the Contract.
75 */
76
77 --<BUG 3209400>
78 --1) Need an NVL since sum can return NULL.
79 --2) SELECT list cannot include both the group function SUM and
80 -- an individual column expression.
81
82 -- bug3673292
83 -- Removed table poh from the FROM clause.
84
85 --Bug# 4685260 Start
86 -- Checking if there are lines referring this contract PO
87 -- with currency different to the Contract. We will do the
88 -- conversion to the base currency only if there is atleast one
89 -- line which are referring this contract with a different currency
90 -- that the Contract currency. If all line referring this contrat
91 -- are in the same currecny as this contract we will compare the
92 -- amount directly without considering the rate.
93
94 x_diff_curr:='N';
95 Begin
96
97 SELECT 'Y'
98 INTO x_diff_curr
99 from dual
100 where exists (
101 SELECT 'Exists'
102 FROM po_lines pol,
103 po_line_locations pll,
104 po_headers poh2
105 WHERE pol.contract_id = X_contract_id -- <GC FPJ>
106 AND pol.po_line_id = pll.po_line_id
107 AND poh2.po_header_id = pol.po_header_id
108 AND (( poh2.authorization_status = 'APPROVED')
109 or (poh2.authorization_status = 'IN PROCESS'))
110 AND pol.po_header_id <> X_po_header_id
111 AND poh2.currency_code <> x_currency_code
112 UNION ALL
113 SELECT 'Exists'
114 FROM po_lines_gt pol, -- <GC FPJ>
115 po_line_locations_gt pll, -- <GC FPJ>
116 po_headers_gt potoapp -- <GC FPJ>
117 WHERE pol.contract_id = X_contract_id -- <GC FPJ>
118 AND pol.po_line_id = pll.po_line_id
119 AND pol.po_header_id = potoapp.po_header_id
120 AND potoapp.po_header_id = X_po_header_id
121 AND potoapp.currency_code <> x_currency_code
122 );
123
124 Exception
125
126 When Others then
127 x_diff_curr:='N';
128
129 End;
130
131
132 if nvl(x_diff_curr,'N') = 'Y' then
133 l_amount_limit_func := l_amount_limit_func * X_rate;
134 End if;
135
136 --Bug# 4685260 End
137 --Bug# 4685260, added the rate conversion to the below sql
138
139
140 SELECT ( nvl( --<BUG 3209400> -- <SERVICES FPJ>
141 sum ( decode ( PLL.quantity
142 , NULL , PLL.amount - nvl(PLL.amount_cancelled,0)
143 , ( ( PLL.quantity
144 - nvl(PLL.quantity_cancelled,0) )
145 * PLL.price_override )
146 )
147 * decode(nvl(x_diff_curr,'N'),
148 'Y', nvl(POH2.rate, 1),1) --Bug4685260
149 ),
150 0
151 )
152 )
153 INTO X_purchased_amount
154 FROM po_lines pol,
155 po_line_locations pll,
156 po_headers poh2
157 WHERE pol.contract_id = X_contract_id -- <GC FPJ>
158 AND pol.po_line_id = pll.po_line_id
159 AND poh2.po_header_id = pol.po_header_id
160 AND (( poh2.authorization_status = 'APPROVED')
161 or (poh2.authorization_status = 'IN PROCESS'))
162 AND pol.po_header_id <> X_po_header_id;
163
164 --dbms_output.put_line('X_purchased_amount = ' || X_purchased_amount);
165
166 /* Go get the amount that is trying to be approved for this contract
167 on this po
168 */
169 X_progress := 30;
170
171 /* A nvl would definately be a bad thing if this occurred
172 since you better have records that you're trying to approve */
173
174 --<BUG 3209400>
175 --SELECT list cannot include both the group function SUM and
176 --an individual column expression.
177
178 -- bug3673292
179 -- Removd poh from the FROM clause
180
181 --Bug# 4685260, added the rate conversion to the below sql
182
183 SELECT ( -- <SERVICES FPJ>
184 sum ( decode ( PLL.quantity
185 , NULL , PLL.amount - nvl(PLL.amount_cancelled,0)
186 , ( ( PLL.quantity
187 - nvl(PLL.quantity_cancelled,0) )
188 * PLL.price_override )
189 )
190 * decode(nvl(x_diff_curr,'N'),
191 'Y', nvl(POTOAPP.rate, 1),1) --Bug4685260
192 )
193 )
194 INTO X_current_amount
195 FROM po_lines_gt pol, -- <GC FPJ>
196 po_line_locations_gt pll, -- <GC FPJ>
197 po_headers_gt potoapp -- <GC FPJ>
198 WHERE pol.contract_id = X_contract_id -- <GC FPJ>
199 AND pol.po_line_id = pll.po_line_id
200 AND pol.po_header_id = potoapp.po_header_id
201 AND potoapp.po_header_id = X_po_header_id;
202
203 --dbms_output.put_line('X_current_amount = ' || X_current_amount);
204
205 /* Check to see that the amount used on other po's + the amount
206 you're trying to use on this po is less than what's been approved
207 for the po.
208 */
209 X_progress := 40;
210
211 -- bug3673292
212 -- Use pl/sql code to replace a query to check whether we have funds
213 -- available, as we got all the necessary values we need already
214
215 IF ( l_amount_limit_func < X_current_amount + X_purchased_amount ) THEN
216 RETURN (0);
217 END IF;
218
219 END LOOP;
220
221 RETURN (1);
222
223 /* If something fails then return a 0 as failure */
224 EXCEPTION
225 WHEN OTHERS THEN
226 RAISE; --<BUG 3209400>
227
228
229 END val_contract_amount;
230
231 /*===========================================================================
232
233 PROCEDURE NAME: test_val_contract_amount
234
235 ===========================================================================*/
236
237 PROCEDURE test_val_contract_amount (X_po_header_id IN NUMBER) IS
238
239 X_contract_funds_available NUMBER := 0;
240
241 BEGIN
242
243 X_contract_funds_available := po_contracts_s.val_contract_amount(X_po_header_id);
244
245 --dbms_output.put_line('Return value is = ' || X_contract_funds_available);
246
247 END test_val_contract_amount;
248
249
250 END po_contracts_s;
[Home] [Help]