1 PACKAGE BODY pa_po_integration_utils AS
2 /* $Header: PAPOUTLB.pls 120.8 2006/11/06 22:29:13 vgade noship $ */
3
4
5 /*
6 Function : Allow_Project_Info_Change.
7 Description: Checks to see if the sum of receipts interfaced to projects for the po distribution
8 is zero, if sum is zero return 'Y' else 'N'.
9 Further is there is any Un-InterfacedLine to Projects, Return 'N'.--bmurthy bug 4049925
10 Arguments : p_po_distribution_id - Purchase order distribution on which project information needs to be changed.
11 Return : 'Y', if project information on the purchase order distribution can be updated.
12 'N', if project information on the purchase order distribution cannot be updated.
13 */
14
15 FUNCTION Allow_Project_Info_Change ( p_po_distribution_id IN po_distributions_all.po_distribution_id%type)
16 RETURN varchar2 IS
17
18 l_sum_amount_interfaced number := 0;
19 l_uninterfaced_to_pa number := 0;
20 l_po_distribution_id number := 0;
21
22 BEGIN
23 l_po_distribution_id := nvl(p_po_distribution_id,-999);
24
25 BEGIN
26 select sum(nvl(entered_cr,0) - nvl(entered_dr,0))
27 into l_sum_amount_interfaced
28 from rcv_transactions rcv_txn,
29 rcv_receiving_sub_ledger rcv_sub
30 where rcv_txn.po_distribution_id = l_po_distribution_id
31 and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
32 and rcv_sub.pa_addition_flag in ('Y','I')
33 and ((rcv_txn.destination_type_code ='EXPENSE') OR
34 /*and ((rcv_txn.destination_type_code ='EXPENSE' AND rcv_txn.transaction_type <> 'RETURN TO RECEIVING') OR */--Bug4630478
35 (rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) );
36
37 IF l_sum_amount_interfaced <> 0 THEN
38 Return 'N';
39 END IF;
40
41 EXCEPTION
42
43 WHEN NO_DATA_FOUND THEN
44
45 BEGIN
46
47 select 1
48 into l_uninterfaced_to_pa
49 FROM dual
50 WHERE EXISTS
51 (SELECT 1 FROM rcv_transactions rcv_txn,
52 rcv_receiving_sub_ledger rcv_sub
53 ,po_distributions_all podist/*Bug 3905697*/
54 where rcv_txn.po_distribution_id = l_po_distribution_id
55 and podist.po_distribution_id=rcv_txn.po_distribution_id/*Bug 3905697*/
56 and rcv_sub.code_combination_id = podist.code_combination_id/*Bug 3905697*/
57 and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
58 and ((rcv_txn.destination_type_code ='EXPENSE') OR
59 (rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) )
60 and rcv_sub.pa_addition_flag ='N');
61
62 If l_uninterfaced_to_pa = 1 THEN
63 Return 'N';
64 Else
65 Return 'Y';
66 End If;
67
68 EXCEPTION
69
70 WHEN NO_DATA_FOUND THEN
71 Return 'Y';
72 WHEN OTHERS THEN
73 G_err_code := SQLCODE;
74 raise;
75
76 END;
77
78 END;
79
80
81
82 BEGIN
83
84 select 1
85 into l_uninterfaced_to_pa
86 FROM dual
87 WHERE EXISTS
88 (SELECT 1 FROM rcv_transactions rcv_txn,
89 rcv_receiving_sub_ledger rcv_sub
90 ,po_distributions_all podist/*Bug 3905697*/
91 where rcv_txn.po_distribution_id = l_po_distribution_id
92 and podist.po_distribution_id=rcv_txn.po_distribution_id/*Bug 3905697*/
93 and rcv_sub.code_combination_id = podist.code_combination_id/*Bug 3905697*/
94 and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
95 and ((rcv_txn.destination_type_code ='EXPENSE') OR
96 (rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) )
97 and rcv_sub.pa_addition_flag ='N');
98
99 If l_uninterfaced_to_pa = 1 OR l_sum_amount_interfaced <> 0 THEN
100 Return 'N';
101 Else
102 Return 'Y';
103 End If;
104
105
106 EXCEPTION
107
108 WHEN NO_DATA_FOUND THEN
109
110 If l_sum_amount_interfaced <> 0 THEN
111 Return 'N';
112 Else
113 Return 'Y';
114 End If;
115
116 WHEN OTHERS THEN
117 G_err_code := SQLCODE;
118 raise;
119 END;
120
121 Exception
122
123 WHEN OTHERS THEN
124 G_err_code := SQLCODE;
125 raise;
126
127 End Allow_Project_Info_Change;
128
129 --Added for bug 4407908
130 /*This is a public API, which will update PA_ADDITION_FLAG in
131 rcv_receiving_sub_ledger table. This API will be called from
132 purchasing module at the time of receipt creation.*/
133
134 PROCEDURE Update_PA_Addition_Flg (p_api_version IN NUMBER,
135 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
136 p_commit IN VARCHAR2 default FND_API.G_FALSE,
137 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_msg_count OUT NOCOPY NUMBER,
140 x_msg_data OUT NOCOPY VARCHAR2,
141 p_rcv_transaction_id IN NUMBER,
142 p_po_distribution_id IN NUMBER,
143 p_accounting_event_id IN NUMBER)
144 IS
145 /*l_project_id po_distributions_all.project_id%type; Bug 5585218 */
146 l_po_distribution_id po_distributions_all.po_distribution_id%type;
147 l_rcv_transaction_id rcv_receiving_sub_ledger.rcv_transaction_id%type;
148 l_processed Number := 0;
149
150 PROCEDURE net_zero_adj_po IS
151
152 l_old_stack VARCHAR2(630);
153 l_rcv_transaction_id1 NUMBER(15);
154 l_po_dist_id NUMBER(15);
155 l_num_rows NUMBER(15):=0;
156
157 l_rcv_txn_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
158 l_po_dist_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
159 l_rcv_acct_evt_tbl PA_PLSQL_DATATYPES.IdTabTyp; -- pricing changes
160
161
162 CURSOR net_zero_po_proj (p_transaction_id IN number) IS /* Modified the cursor query for Bug 5585218 */
163 SELECT /*+ leading(rcv_txn) index(rcvsub RCV_RECEIVING_SUB_LEDGER_N1) */ rcv_txn.transaction_id /*4338075*/
164 ,rcv_txn.po_distribution_id
165 ,rcvsub.accounting_event_id -- pricing changes
166 FROM rcv_transactions rcv_txn
167 ,po_distributions podist
168 ,rcv_receiving_sub_ledger rcvsub
169 WHERE rcv_txn.transaction_id = rcvsub.rcv_transaction_id
170 AND rcv_txn.parent_transaction_id = (SELECT parent_transaction_id
171 FROM rcv_transactions rcv_txn3
172 WHERE rcv_txn3.transaction_id = p_transaction_id)
173 and rcv_txn.po_distribution_id = podist.po_distribution_id
174 and podist.code_combination_id = rcvsub.code_combination_id
175 and rcvsub.actual_flag = 'A'
176 and podist.accrue_on_receipt_flag = 'Y'
177 /*and podist.project_id = p_project_id Bug 5585218 */
178 and rcvsub.pa_addition_flag = 'N' -- pricing changes
179 and ((rcv_txn.destination_type_code = 'EXPENSE' ) OR
180 (rcv_txn.destination_type_code = 'RECEIVING' AND
181 rcv_txn.transaction_type in ('RETURN TO VENDOR','RETURN TO RECEIVING')
182 ))
183 and 0 = (SELECT /*+ INDEX(RCV_TXN2 RCV_TRANSACTIONS_N1) */sum(nvl(rcvsub2.entered_dr,0)-nvl(rcvsub2.entered_cr,0))/*4338075*/
184 FROM rcv_transactions rcv_txn2
185 ,rcv_receiving_sub_ledger rcvsub2
186 ,po_distributions podist2
187 WHERE rcv_txn2.transaction_id = rcvsub2.rcv_transaction_id
188 and podist2.po_distribution_id = rcv_txn2.po_distribution_id
189 and podist2.code_combination_id = rcvsub2.code_combination_id
190 and rcvsub2.actual_flag = 'A'
191 and rcv_txn2.parent_transaction_id = rcv_txn.parent_transaction_id
192 and rcvsub2.code_combination_id = rcvsub.code_combination_id
193 and trunc(rcv_txn2.transaction_date) = trunc(rcv_txn.transaction_date)
194 and rcvsub2.pa_addition_flag = 'N' -- pricing changes
195 and rcv_txn2.po_distribution_id = rcv_txn.po_distribution_id
196 and ((rcv_txn2.destination_type_code = 'EXPENSE' ) OR
197 (rcv_txn2.destination_type_code = 'RECEIVING' AND
198 rcv_txn2.transaction_type in ('RETURN TO VENDOR','RETURN TO RECEIVING')
199 ))
200 );
201
202 BEGIN
203
204 l_po_dist_id := l_po_distribution_id;
205
206 -- l_old_stack := G_err_stack;
207 -- G_err_stack := G_err_stack || '->PAAPIMP_PKG.net_zero_adj_po';
208 -- G_err_code := 0;
209 -- G_err_stage := 'UPDATING RCV TRANSACTIONS FOR net_zero_adj_po';
210
211 -- write_log(LOG, G_err_stack);
212
213 OPEN net_zero_po_proj (l_rcv_transaction_id);
214
215 l_rcv_txn_id_tbl.delete;
216 l_po_dist_id_tbl.delete;
217 l_rcv_acct_evt_tbl.delete; -- pricing changes
218
219 FETCH net_zero_po_proj BULK COLLECT INTO l_rcv_txn_id_tbl
220 ,l_po_dist_id_tbl
221 ,l_rcv_acct_evt_tbl; -- pricing changes
222
223 IF l_rcv_txn_id_tbl.COUNT <> 0 THEN
224
225 FORALL i IN l_rcv_txn_id_tbl.FIRST..l_rcv_txn_id_tbl.LAST
226
227 UPDATE rcv_receiving_sub_ledger rcv_sub
228 SET rcv_sub.pa_addition_flag = 'Z'
229 WHERE rcv_sub.rcv_transaction_id = l_rcv_txn_id_tbl(i) --pricing changes
230 AND rcv_sub.pa_addition_flag = 'N'
231 AND (rcv_sub.accounting_event_id = l_rcv_acct_evt_tbl(i) --pricing changes
232 OR rcv_sub.accounting_event_id IS NULL); --pricing changes
233 l_num_rows := SQL%ROWCOUNT;
234
235 END IF;
236
237 -- write_log (LOG,'Total number of transctions updated to Z:'||l_num_rows);
238
239 l_rcv_txn_id_tbl.delete;
240 l_po_dist_id_tbl.delete;
241 l_rcv_acct_evt_tbl.delete; -- pricing changes
242
243 CLOSE net_zero_po_proj;
244
245 EXCEPTION
246 WHEN Others THEN
247
248 IF net_zero_po_proj%ISOPEN THEN
249 CLOSE net_zero_po_proj;
250 END IF;
251
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
253
254 -- G_err_stack := l_old_stack;
255 -- G_err_code := SQLCODE;
256 raise;
257 END net_zero_adj_po;
258
259 BEGIN
260
261 x_return_status := FND_API.G_RET_STS_SUCCESS;
262
263 l_po_distribution_id := p_po_distribution_id;
264 l_rcv_transaction_id := p_rcv_transaction_id;
265
266
267 UPDATE rcv_receiving_sub_ledger rcv_sub
268 SET rcv_sub.pa_addition_flag = NULL
269 WHERE rcv_sub.pa_addition_flag ='N'
270 AND rcv_sub.rcv_transaction_id = l_rcv_transaction_id
271 AND EXISTS
272 (
273 SELECT 'X'
274 FROM rcv_transactions rcv_txn
275 WHERE rcv_txn.TRANSACTION_ID = rcv_sub.RCV_TRANSACTION_ID
276 AND ((rcv_txn.destination_type_code IN ('INVENTORY','MULTIPLE','SHOP FLOOR')
277 OR
278 (rcv_txn.destination_type_code = 'RECEIVING'
279 AND
280 (rcv_txn.transaction_type NOT IN ('RETURN TO VENDOR','RETURN TO RECEIVING')
281 )
282 )
283 )
284 OR
285 (EXISTS
286 (SELECT po_distribution_id
287 FROM po_distributions po_dist
288 WHERE po_dist.po_distribution_id = rcv_txn.po_distribution_id
289 AND ((rcv_txn.destination_type_code = 'EXPENSE' AND
290 po_dist.project_id IS NULL)
291 OR
292 (rcv_txn.destination_type_code = 'EXPENSE' AND
293 nvl(po_dist.project_id,0) > 0 AND
294 po_dist.accrue_on_receipt_flag = 'N')
295 OR
296 (rcv_txn.destination_type_code = 'RECEIVING' AND
297 po_dist.project_id IS NULL)
298 OR
299 (rcv_txn.destination_type_code = 'RECEIVING' AND
300 po_dist.project_id IS NOT NULL AND
301 po_dist.accrue_on_receipt_flag = 'N')
302 )
303 )
304 ) OR
305 ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
306 AND EXISTS (SELECT 'X'
307 FROM mtl_system_items si,
308 po_lines_all pol,
309 po_distributions_all po_dist1
310 WHERE po_dist1.po_line_id = pol.po_line_id
311 AND po_dist1.po_distribution_id = rcv_txn.po_distribution_id
312 AND si.inventory_item_id = pol.item_id
313 AND po_dist1.project_id IS NOT NULL
314 AND si.comms_nl_trackable_flag = 'Y')
315 ) OR
316 (
317 rcv_sub.actual_flag <> 'A'
318 )
319 )
320 );
321
322 l_processed := SQL%ROWCOUNT ;
323
324 UPDATE rcv_receiving_sub_ledger rcv_sub
325 SET rcv_sub.pa_Addition_Flag = 'X'
326 WHERE rcv_sub.pa_addition_flag IN ('N','I')
327 AND rcv_sub.rcv_transaction_id = l_rcv_transaction_id
328 AND EXISTS
329 ( SELECT po_dist.code_combination_id
330 FROM Rcv_Transactions rcv_txn, PO_Distributions po_dist
331 WHERE
332 (
333 (rcv_txn.destination_type_code ='EXPENSE' )
334 OR (rcv_txn.destination_type_code = 'RECEIVING'
335 AND (rcv_txn.transaction_type
336 IN ('RETURN TO VENDOR','RETURN TO RECEIVING')))
337 )
338 -- AND rcv_txn.transaction_date <= nvl(G_GL_DATE,rcv_txn.transaction_date)
339 AND rcv_txn.PO_DISTRIBUTION_ID = po_dist.po_distribution_id
340 AND rcv_sub.code_combination_id <> po_dist.code_combination_id
341 AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
342 AND rcv_sub.actual_flag = 'A'
343 -- AND po_dist.expenditure_item_date <= nvl(G_TRANSACTION_DATE,po_dist.expenditure_item_date)
344 AND po_dist.project_ID > 0
345 AND po_dist.accrue_on_receipt_flag= 'Y') ;
346
347 l_processed := l_processed + SQL%ROWCOUNT ;
348
349 IF (l_processed = 0) THEN
350 net_zero_adj_po();
351 END IF ;
352
353 EXCEPTION
354 WHEN OTHERS THEN
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356 raise;
357 END Update_PA_Addition_Flg;
358
359 End pa_po_integration_utils;
[Home] [Help]