[Home] [Help]
PACKAGE BODY: APPS.PA_AP_XFER_PKG
Source
1 PACKAGE BODY pa_ap_xfer_pkg AS
2 /* $Header: PAAPXFRB.pls 115.4 2003/08/15 02:14:24 vgade noship $ */
3
4
5 PROCEDURE upd_cdl_xfer_status( p_request_id IN NUMBER
6 ,x_return_status OUT NOCOPY NUMBER
7 ,x_error_code OUT NOCOPY VARCHAR2
8 ,x_error_stage OUT NOCOPY NUMBER
9 )
10 IS
11
12 g_request_id pa_cost_distribution_lines.request_id%TYPE ;
13 l_expenditure_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
14 l_line_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
15 l_line_num_reversed_tab PA_PLSQL_DATATYPES.NumTabTyp;
16 l_dr_code_combination_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
17 l_return_status NUMBER := -1;
18 l_error_code VARCHAR2(30):= FND_API.G_RET_STS_ERROR;
19 l_error_stage VARCHAR2(30);
20 l_debug_mode VARCHAR2(1);
21 l_stage NUMBER ;
22 l_this_fetch PLS_INTEGER := 0;
23 l_totally_fetched PLS_INTEGER := 0;
24 l_accrue_on_receipt_num NUMBER := 0;
25 l_system_reference2 pa_cost_distribution_lines_all.system_reference2%TYPE;
26
27 /*Cursor to select the invoices for the newly created cdls after recalc*/
28 CURSOR Inv_stat_cur
29 IS
30 Select distinct system_reference2
31 from pa_cost_distribution_lines cdl,
32 pa_expenditure_items ei
33 where ei.cost_distributed_flag ='S'
34 AND ei.request_id = g_request_id
35 AND ei.system_linkage_function = 'VI'
36 AND cdl.transfer_status_code = 'P'
37 AND cdl.line_type ='R'
38 AND cdl.request_id = g_request_id
39 AND cdl.expenditure_item_id = ei.expenditure_item_id;
40
41 /*Cursor to select all the reversal cdls (line_type 'R') for the expenditure_item_ids
42 marked with cost_distributed_flag as 'S' */
43
44 CURSOR rev_cdl_cur
45 IS
46 SELECT cdl.expenditure_item_id
47 ,cdl.line_num
48 ,cdl.line_num_reversed
49 ,cdl.dr_code_combination_id
50 FROM pa_expenditure_items_all ei
51 ,pa_cost_distribution_lines_all cdl
52 WHERE ei.cost_distributed_flag = 'S'
53 AND ei.request_id = g_request_id
54 AND ei.system_linkage_function = 'VI'
55 AND cdl.transfer_status_code = 'P'
56 AND cdl.request_id = g_request_id
57 AND cdl.line_type = 'R'
58 AND cdl.expenditure_item_id = ei.expenditure_item_id
59 AND cdl.line_num_reversed is NOT NULL
60 ORDER BY cdl.expenditure_item_id
61 ,cdl.line_num;
62
63 BEGIN
64 pa_debug.init_err_stack('pa_ap_xfer_pkg.upd_cdl_xfer_status');
65
66 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
67 l_debug_mode := NVL(l_debug_mode, 'Y');
68
69 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
70
71 l_stage := 10;
72 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':From upd_cdl_xfer_status';
73 pa_debug.write_file(pa_debug.g_err_stage);
74
75 g_request_id := p_request_id ;
76
77 l_stage := 20;
78 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Opening Cursor Inv_stat_cur';
79 pa_debug.write_file(pa_debug.g_err_stage);
80
81 OPEN Inv_stat_cur;
82
83 LOOP
84 Fetch Inv_stat_cur
85 into l_system_reference2;
86 EXIT WHEN Inv_stat_cur%NOTFOUND;
87
88 l_stage := 30;
89 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Checking for Inv Status';
90 pa_debug.write_file(pa_debug.g_err_stage);
91
92 /*Bug 3094341. Added an NVL condition to system_reference2, as it was calling
93 AP_PA_API_PKG, which was erroring out when NULL was passed. No records were retrieved when NULL is passed. */
94 IF pa_integration.check_ap_invoices(nvl(l_system_reference2,0),'ADJUSTMENTS') <> 'N'
95 THEN
96 UPDATE PA_COST_DISTRIBUTION_LINES
97 SET transfer_status_code ='B'
98 WHERE system_reference2 =l_system_reference2
99 AND transfer_status_code = 'P'
100 AND line_type ='R'
101 AND request_id = g_request_id ;
102
103 l_stage := 40;
104 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After updating cdls for restricted invoice';
105 pa_debug.write_file(pa_debug.g_err_stage);
106
107 END IF;
108
109 l_stage := 50;
110 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After check for Inv status';
111 pa_debug.write_file(pa_debug.g_err_stage);
112
113 END LOOP;
114 CLOSE Inv_stat_cur;
115
116 l_stage :=60 ;
117 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Closing Cursor Inv_stat_cur';
118 pa_debug.write_file(pa_debug.g_err_stage);
119
120 OPEN rev_cdl_cur;
121 l_this_fetch := 0;
122 l_totally_fetched := 0;
123 l_stage := 70;
124 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ':Fetching a Set of reversal CDLs to Process.';
125 PA_DEBUG.write_file(PA_DEBUG.g_err_stage);
126
127 l_stage := 80;
128
129 LOOP
130
131 FETCH rev_cdl_cur
132 BULK COLLECT
133 INTO l_expenditure_item_id_tab
134 ,l_line_num_tab
135 ,l_line_num_reversed_tab
136 ,l_dr_code_combination_id_tab;
137
138 l_this_fetch := rev_cdl_cur%ROWCOUNT - l_totally_fetched;
139 l_totally_fetched := rev_cdl_cur%ROWCOUNT;
140
141 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ':Fetched [' || l_this_fetch || '] CDLs to process.';
142 PA_DEBUG.write_file(PA_DEBUG.g_err_stage);
143
144
145 IF (l_this_fetch = 0) THEN
146 l_stage := 90;
147 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ':No more Reversal CDLs to process. Exiting';
148 PA_DEBUG.write_file(PA_DEBUG.g_err_stage);
149
150 x_return_status := 0;
151 x_error_code := FND_API.G_RET_STS_SUCCESS;
152 x_error_stage := l_stage;
153 EXIT;
154 END IF;
155
156 l_stage :=100;
157
158 FORALL i IN l_expenditure_item_id_tab.FIRST..l_expenditure_item_id_tab.LAST
159 UPDATE PA_COST_DISTRIBUTION_LINES_ALL a
160 SET a.TRANSFER_STATUS_CODE ='B'
161 WHERE a.expenditure_item_id = l_expenditure_item_id_tab(i)
162 AND a.line_num in (l_line_num_tab(i) ,l_line_num_tab(i)+ 1)
163 AND EXISTS (SELECT 1
164 FROM PA_COST_DISTRIBUTION_LINES_ALL cdl
165 WHERE cdl.expenditure_item_id = a.expenditure_item_id
166 AND cdl.line_num = l_line_num_tab(i) +1
167 AND cdl.dr_code_combination_id =l_dr_code_combination_id_tab(i));
168
169 END LOOP;
170 l_stage := 110;
171 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ': Closing the Cursor';
172 PA_DEBUG.write_file(PA_DEBUG.g_err_stage);
173 pa_debug.reset_err_stack; --Added for Bug#3094341
174 CLOSE rev_cdl_cur;
175 EXCEPTION
176 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
177 THEN
178 l_stage := 120 ;
179 pa_debug.write_file(pa_debug.g_err_stage);
180 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) ||'In UnExpected Exception';
181 pa_debug.write_file(pa_debug.g_err_stage);
182
183 x_return_status := -1;
184 x_error_code := FND_API.G_RET_STS_ERROR;
185 x_error_stage := l_stage ;
186 pa_debug.reset_err_stack;
187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
188 WHEN OTHERS
189 THEN
190 l_stage := 130 ;
191 pa_debug.write_file(pa_debug.g_err_stage);
192 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) ||'In Others Exception';
193 pa_debug.write_file(pa_debug.g_err_stage);
194 pa_debug.g_err_stage := TO_CHAR(SQLCODE) || SQLERRM ;
195 pa_debug.write_file(pa_debug.g_err_stage);
196
197 x_return_status := -1;
198 x_error_code := TO_CHAR(SQLCODE) || SQLERRM ;
199 x_error_stage := l_stage ;
200 pa_debug.reset_err_stack;
201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
202 END upd_cdl_xfer_status;
203 END pa_ap_xfer_pkg;