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