1 Package Body Pa_Tieback_Adj_Costs As
2 /* $Header: PAXAPJTB.pls 115.1 2002/08/22 19:15:16 eyefimov noship $ */
3
4 G_ParentTxnTab PA_TIEBACK_ADJ_COSTS.ParentTxnTab;
5
6 G_Success_Count NUMBER := 0;
7 G_Reject_Count NUMBER := 0;
8
9 /*----------------------------------------------------------------------------------------+
10 | Procedure : Is_Adjusted |
11 | Purpose : Receipt corrections and returns whose original item has been adjusted |
12 | in Projects will be displayed in a different section of the |
13 | AUD: Payables Costs Interface Audit report. |
14 | This function is called from the receipt portion of the query in |
15 | PAAPIMPR.rdf |
16 | Caching is done in this API based on the parent transaction id |
17 | (P_Parent_Txn_Id) |
18 +----------------------------------------------------------------------------------------*/
19
20 Function Is_Adjusted(P_Po_Dist IN Number,
21 P_Txn_Id IN Number,
22 P_Parent_Txn_Id IN Number,
23 P_Trx_Type IN Varchar2) Return Varchar2
24 Is
25
26 l_Adjusted Varchar2(1) := 'N';
27 l_Found Boolean;
28
29 Cursor c_Rcvtxns Is
30 Select Transaction_Id
31 From Rcv_Transactions
32 Where (Transaction_Id = P_Parent_Txn_Id OR
33 Parent_Transaction_Id = P_Parent_Txn_Id)
34 And Nvl(Pa_Addition_Flag,'N') IN ('Y', 'I');
35
36 Begin
37
38 If P_Trx_Type IN ('CORRECT', 'RETURN TO VENDOR', 'RETURN TO RECEIVING') Then
39
40 l_Found := FALSE;
41
42 -- Check if there are any records in the pl/sql table.
43 If G_ParentTxnTab.COUNT > 0 Then
44
45 Begin
46
47 -- Get the value of the record for the given parent_txn_id
48 -- If there is no index with the value of the parent_txn_id passed
49 -- in then an ORA-1403: No_Data_Found is generated.
50 l_Adjusted := G_ParentTxnTab(P_Parent_Txn_Id);
51 l_Found := TRUE;
52
53 Exception
54 When No_Data_Found Then
55 l_Found := FALSE;
56 When Others Then
57 Raise;
58
59 End;
60
61 End If;
62
63 If Not l_Found Then
64
65 -- Since the Rcpt has not been cached yet, will need to add it.
66 -- So check to see if there are already 200 records in the pl/sql table.
67 If G_ParentTxnTab.COUNT > 199 Then
68
69 G_ParentTxnTab.Delete;
70
71 End If;
72
73 For EiRec IN c_Rcvtxns Loop
74
75 Begin
76
77 Select 'Y'
78 Into l_Adjusted
79 From Dual
80 Where Exists
81 (Select 1
82 From Pa_Expenditure_Items_All Ei,
83 Pa_Cost_Distribution_Lines_All Cdl
84 Where Nvl(Ei.Adjusted_Expenditure_Item_Id,
85 Ei.Transferred_From_Exp_Item_Id) =
86 Cdl.Expenditure_Item_Id
87 And Nvl(Cdl.System_Reference4,'X') = To_Char(EiRec.Transaction_Id)
88 And Cdl.Line_Num = 1);
89
90 If l_Adjusted = 'Y' Then
91
92 G_ParentTxnTab(P_Parent_Txn_Id) := l_Adjusted;
93
94 End If;
95
96 Exception
97 When No_Data_Found Then
98 -- Add if the Rcpt is adjusted into the pl/sql table using the Parent_Txn_Id
99 -- as the index value. This makes things fast.
100 l_Adjusted := 'N';
101 G_ParentTxnTab(P_Parent_Txn_Id) := l_Adjusted;
102 End;
103
104 End Loop;
105
106 End If;
107
108 End If;
109
110 Return l_Adjusted;
111
112 EXCEPTION
113 When No_Data_Found Then
114 Return l_Adjusted;
115
116 END Is_Adjusted;
117
118 /*----------------------------------------------------------------------------------------+
119 | Procedure : Init |
120 | Purpose : To initialize all required params/debug requirements |
121 +----------------------------------------------------------------------------------------*/
122
123 Procedure Init Is
124
125 l_Debug_Mode VARCHAR2(10);
126
127 Begin
128
129 Fnd_Profile.Get('PA_DEBUG_MODE',l_Debug_Mode);
130 l_Debug_Mode := Nvl(l_Debug_Mode, 'N');
131
132 Pa_Debug.G_Err_Stage := 'Entering Init';
133 Log_Message(P_Message => Pa_Debug.G_Err_Stage);
134
135 Pa_Debug.Set_Curr_Function( P_Function => 'Init',
136 P_Debug_Mode => l_Debug_Mode);
137
138 G_REQUEST_ID := Fnd_Global.Conc_Request_Id;
139 G_PROGRAM_APPLICATION_ID := Fnd_Global.Prog_Appl_Id;
140 G_PROGRAM_ID := Fnd_Global.Conc_Program_Id;
141 G_DEBUG_MODE := l_Debug_Mode;
142
143 End Init;
144
145 /*----------------------------------------------------------------------------------------+
146 | Procedure : Log_Message |
147 | Purpose : To write log message as supplied by the process |
148 | Parameters : |
149 | ================================================================================== |
150 | Name Mode Description |
151 | ================================================================================== |
152 | P_Message IN Message to be logged |
153 | ================================================================================== |
154 +----------------------------------------------------------------------------------------*/
155
156 Procedure Log_Message(P_Message IN Varchar2) Is
157
158 Begin
159
160 Pa_Debug.Log_Message (P_Message => P_Message);
161
162 End Log_Message;
163
164 Procedure Write_Output (X_Return_Status OUT Varchar2,
165 X_Error_Message_Code OUT Varchar2)
166
167 Is
168
169 Begin
170
174
171 /* This has to be changed. Cannot hard code text for a report. Need to use messages that will be
172 * translated.
173 */
175 Fnd_File.Put_Line(FND_FILE.OUTPUT, ' Tieback Adjustment Invoices Output ');
176 Fnd_File.Put_Line(FND_FILE.OUTPUT, '----------------------------------------------------------------');
177 Fnd_File.Put_Line(FND_FILE.OUTPUT, 'Number of Records Successfully Processed = ' || G_Success_Count);
178 Fnd_File.Put_Line(FND_FILE.OUTPUT, 'Number of Records Rejected = ' || G_Reject_Count);
179 Fnd_File.Put_Line(FND_FILE.OUTPUT, '----------------------------------------------------------------');
180
181 End Write_Output;
182
183 /*----------------------------------------------------------------------------------------+
184 | Procedure : TiebackAdjCosts |
185 | Purpose : To tieback adjustment invoices from Payables |
186 | Parameters : |
190 | X_Return_Status OUT Return Status |
187 | ================================================================================== |
188 | Name Mode Description |
189 | ================================================================================== |
191 | X_Error_Message_Code OUT Error Message Code (if any) |
192 | ================================================================================== |
193 | Called By : PRC: Tieback Adjustment Invoices from Payables |
194 | Process : |
195 | 1. Fetch a new set of CDL's to tie back - Transfer Status Code of 'I' |
196 | 2. Retrieve status from ap_invoices_interface, depending on the reference_2 and |
197 | reference_2 columns in ap_invoice_lines_interface. |
198 | 3. If status is PROCESSED then update transfer status code to 'A', |
199 | Else if REJECTED then update to 'R' |
200 | 4. Write to OUTPUT file how many were rejected/succesfully processed |
201 | 5. Deleting rejected from ap_invoices_interface |
202 | 6. Deleting rejected from ap_invoice_lines_interface |
203 | 7. Commit |
204 +----------------------------------------------------------------------------------------*/
205
206 Procedure TiebackAdjCosts(X_Return_Status OUT Varchar2,
207 X_Error_Message_Code OUT Varchar2)
208
209 Is
210
211 Cursor C_TiebackRecs Is
212 Select Cdl.RowId,
213 Cdl.Expenditure_Item_Id,
214 Cdl.Line_Num,
215 Inv.Status
216 From Pa_Cost_Distribution_Lines Cdl,
217 Ap_Invoices_Interface Inv,
218 Ap_Invoice_Lines_Interface Lines
219 Where Cdl.Transfer_Status_Code = 'I'
220 And Inv.Source = 'PA_COST_ADJUSTMENTS'
221 And Inv.Invoice_Id = Lines.Invoice_Id
222 And Lines.Reference_2 = To_Char(Cdl.Expenditure_Item_Id) || '-' || To_Char(Cdl.Line_Num);
223
224 /* Need new index on Ap_Invoice_Lines_Interface (reference_2) */
225
226 l_RowIdTab PA_PLSQL_DATATYPES.RowidTabTyp;
227 l_EiTab PA_PLSQL_DATATYPES.IdTabTyp;
228 l_LineNumTab PA_PLSQL_DATATYPES.IdTabTyp;
229 l_InvStatusTab PA_PLSQL_DATATYPES.Char25TabTyp;
230
231 L_ROWS BINARY_INTEGER := 200;
232
233 BEGIN
234
235 Init;
236
237 PA_DEBUG.Set_Curr_Function( 'TiebackAdjCosts', G_debug_mode);
238
239 PA_DEBUG.g_err_stage := 'Start of Tieback Adjustment Invoices program';
240 Log_Message(PA_DEBUG.g_err_stage);
241
242 Open C_TiebackRecs;
243
244 Loop
245
246 Pa_Debug.G_Err_Stage := 'Inside Loop';
247 Log_Message(Pa_Debug.G_Err_Stage);
248
249 l_RowIdTab.Delete;
250 l_EiTab.Delete;
251 l_LineNumTab.Delete;
252 l_InvStatusTab.Delete;
253
254 Fetch C_TiebackRecs Bulk Collect Into
255 l_RowIdTab,
256 l_EiTab,
257 l_LineNumTab,
258 l_InvStatusTab
259 Limit L_ROWS;
260
261 Pa_Debug.G_Err_Stage := 'Log: No. of records Selected ' || l_RowIdTab.Count;
262 Log_Message(Pa_Debug.G_Err_Stage);
263
264 If l_RowIdTab.Count = 0 Then
265
266 Pa_Debug.G_Err_Stage := 'Log: No records in C_TiebackRecs to process, exiting.';
267 Log_Message(Pa_Debug.G_Err_Stage);
268 Exit;
269
270 End If;
271
272 -- ForAll i IN l_RowIdTab.First .. l_RowIdTab.Last
273 For i In l_RowIdTab.First .. l_RowIdTab.Last Loop
274
275 Log_Message('Ei = ' || L_EiTab(i) ||
276 ' Line = ' || L_LineNumTab(i) ||
277 ' InvStatus = ' || l_InvStatusTab(i));
278
279 Update PA_Cost_Distribution_Lines Cdl
280 Set (Cdl.System_Reference2,
281 Cdl.System_Reference3,
282 Cdl.gl_date,
283 Cdl.gl_period_name) =
284 (Select Decode(l_InvStatusTab(i),'PROCESSED',
285 Dist.Invoice_Id,CDL.System_Reference2),
286 Decode(l_InvStatusTab(i),'PROCESSED',
287 Dist.Distribution_Line_Number,Cdl.System_Reference3),
288 Decode(l_InvStatusTab(i),'PROCESSED',
289 Dist.Accounting_Date,Cdl.Gl_Date),
290 Decode(l_InvStatusTab(i),'PROCESSED',
291 Pa_Utils2.Get_Gl_Period_Name(Dist.Accounting_Date,Dist.Org_Id),
292 Cdl.Gl_Period_Name)
293 From Ap_Invoice_Distributions Dist
294 Where Dist.Reference_2 = To_Char(Cdl.Expenditure_Item_Id) || '-' ||
295 to_char(Cdl.Line_Num)
296 And Pa_Addition_Flag = 'T'),
297 Cdl.Transfer_Status_Code = Decode(l_InvStatusTab(i), 'PROCESSED','A',
298 'REJECTED' ,'R',
299 Cdl.Transfer_Status_Code),
300 Cdl.Request_Id = G_REQUEST_ID,
301 Cdl.Program_Application_Id = G_PROGRAM_APPLICATION_ID,
302 Cdl.Program_Id = G_PROGRAM_ID,
303 Cdl.Program_Update_Date = sysdate
304 Where Cdl.RowId = l_RowIdTab(i);
305
306 /* Need new index in ap_invoice_distributions(reference_2) */
307
308 End Loop;
309
310 For i in l_RowIdTab.first..l_RowIdTab.last Loop
311
312 If l_InvStatusTab(i) = 'PROCESSED' Then
313
314 G_Success_Count := G_Success_Count + 1;
315
316 ElsIf l_InvStatusTab(i) = 'REJECTED' Then
317
318 G_Reject_Count := G_Reject_Count + 1;
319
320 End If;
321
322 End Loop;
323
324 Commit;
325
326 End loop;
327
328 Close C_TiebackRecs;
329
330 Delete From Ap_Invoice_Lines_Interface
331 Where Invoice_Id in (
332 Select
333 Invoice_Id
334 From
335 Ap_Invoices_Interface
336 Where
337 Source = 'PA_COST_ADJUSTMENTS'
338 And Status = 'REJECTED');
339
340 Delete From Ap_Invoices_Interface
341 Where Source = 'PA_COST_ADJUSTMENTS'
342 And Status = 'REJECTED';
343
344 Commit;
345
346 Write_Output(X_Return_Status, X_Error_Message_Code );
347
348 Pa_Debug.Reset_Curr_Function;
349
350 Exception
351
352 When Others Then
353 Raise;
354
355 End TiebackAdjCosts;
356
357 End Pa_Tieback_Adj_Costs;
[Home] [Help]