[Home] [Help]
PACKAGE: APPS.AP_ACCTG_PREPAY_DIST_PKG
Source
1 PACKAGE AP_ACCTG_PREPAY_DIST_PKG AUTHID CURRENT_USER AS
2 /* $Header: appredis.pls 120.25 2010/10/14 04:22:45 rseeta ship $ */
3
4
5 TYPE r_prepay_hist_info IS RECORD
6 (Prepay_History_ID AP_Prepay_History.Prepay_History_ID%TYPE
7 ,Prepay_Invoice_ID AP_Prepay_History.Prepay_Invoice_ID%TYPE
8 ,Invoice_ID AP_Prepay_History.Invoice_ID%TYPE
9 ,Invoice_Line_Number AP_Prepay_History.Invoice_Line_Number%TYPE
10 ,Transaction_Type AP_Prepay_History.Transaction_Type%TYPE
11 ,Accounting_Date AP_Prepay_History.Accounting_Date%TYPE
12 ,Invoice_Adjustment_Event_ID AP_Prepay_History.Invoice_Adjustment_Event_ID%TYPE
13 ,Related_Prepay_App_Event_ID AP_Prepay_History.Related_Prepay_App_Event_ID%TYPE
14 );
15
16
17 TYPE r_prepay_dist_info IS RECORD
18 (Invoice_ID AP_Invoice_Distributions.Invoice_ID%TYPE
19 ,Invoice_Distribution_ID AP_Invoice_Distributions.Invoice_Distribution_ID%TYPE
20 ,Line_Type_Lookup_Code AP_Invoice_Distributions.Line_Type_Lookup_Code%TYPE
21 ,Amount AP_Invoice_Distributions.Amount%TYPE
22 ,Base_Amount AP_Invoice_Distributions.Base_Amount%TYPE
23 ,Accounting_Event_ID AP_Invoice_Distributions.Accounting_Event_ID%TYPE
24 ,Prepay_Distribution_ID AP_Invoice_Distributions.Prepay_Distribution_ID%TYPE
25 ,Prepay_Tax_Diff_Amount AP_Invoice_Distributions.Prepay_Tax_Diff_Amount%TYPE
26 ,Parent_Reversal_ID AP_Invoice_Distributions.Parent_Reversal_ID%TYPE
27 );
28
29
30 -- bug7349279, added the order by condition
31 -- to ensure that the prepayment unapplications
32 -- are always fetched after the prepayment
33 -- applications
34 --
35 --
36 -- bug9038462, changing this cursor to pick up
37 -- Prepay History records which have not yet
38 -- been Accounted or Encumbered
39 --
40 CURSOR Prepay_History
41 (P_Invoice_ID NUMBER
42 ) IS
43 SELECT APH.Prepay_History_ID,
44 APH.Prepay_Invoice_ID,
45 APH.Invoice_ID,
46 APH.Invoice_Line_Number,
47 APH.Transaction_Type,
48 APH.Accounting_Date,
49 APH.Invoice_Adjustment_Event_ID,
50 APH.Related_Prepay_App_Event_ID
51 FROM AP_Prepay_History_All APH,
52 XLA_Events XE
53 WHERE APH.Invoice_ID = P_Invoice_ID
54 --AND APH.Accounting_Event_ID IS NULL
55 AND nvl(APH.Posted_flag, 'N') <> 'Y'
56 AND XE.application_id(+) = 200
57 AND APH.bc_event_id = XE.event_id(+)
58 AND (nvl(XE.event_id, -99) = -99 OR
59 XE.event_status_code <> 'P')
60 AND (nvl(XE.event_id, -99) = -99 OR
61 XE.budgetary_control_flag = 'Y')
62 ORDER BY transaction_type, Prepay_History_ID; --Bug 10173936 added Prepay_History_ID
63
64
65 -- bug9038462, changing this cursor to pick up
66 -- Invoice Distributions for Prepayment Applications
67 -- which have not yet been Accounted or Encumbered
68 --
69 CURSOR Prepay_Dists
70 (P_Invoice_ID NUMBER,
71 P_Invoice_Line_Number NUMBER,
72 P_Accounting_Date DATE,
73 P_Prepay_History_ID NUMBER
74 ) IS
75 (SELECT AID.Invoice_ID,
76 AID.Invoice_Distribution_ID Invoice_Distribution_ID,
77 AID.Line_Type_Lookup_Code,
78 AID.Amount,
79 AID.Base_Amount,
80 AID.Accounting_Event_ID,
81 AID.Prepay_Distribution_ID,
82 AID.Prepay_Tax_Diff_Amount,
83 AID.Parent_Reversal_ID
84 FROM AP_Invoice_Distributions_All AID
85 WHERE Invoice_ID = P_Invoice_ID
86 AND Invoice_Line_Number = P_Invoice_Line_Number
87 AND Line_Type_Lookup_Code = 'PREPAY'
88 AND Accounting_Date = P_Accounting_Date
89 --AND Accounting_Event_ID IS NULL
90 AND nvl(AID.Posted_flag, 'N') <> 'Y'
91 AND nvl(AID.Encumbered_flag, 'N') <> 'Y'
92 AND EXISTS (SELECT 'Prepay History'
93 FROM AP_Prepay_History_All APH,
94 AP_Invoice_Distributions_All AID1
95 WHERE APH.Prepay_History_ID = P_Prepay_History_ID
96 AND AID1.Invoice_Distribution_ID = AID.Prepay_Distribution_ID
97 AND AID1.Invoice_ID = APH.Prepay_Invoice_ID
98 AND AID1.Invoice_Line_Number = APH.Prepay_Line_Num
99 -- Bug 6718967
100 AND DECODE(APH.Transaction_Type, 'PREPAYMENT APPLIED', 1, 2) =
101 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2))
102 UNION ALL
103 SELECT AID.Invoice_ID,
104 AID.Invoice_Distribution_ID Invoice_Distribution_ID,
105 AID.Line_Type_Lookup_Code,
106 AID.Amount,
107 AID.Base_Amount,
108 AID.Accounting_Event_ID,
109 AID.Prepay_Distribution_ID,
110 AID.Prepay_Tax_Diff_Amount,
111 AID.Parent_Reversal_ID
112 FROM AP_Invoice_Distributions_All AID
113 WHERE Invoice_ID = P_Invoice_ID
114 AND Line_Type_Lookup_Code IN ('NONREC_TAX','REC_TAX')
115 --AND Accounting_Event_ID IS NULL
116 AND nvl(AID.Posted_flag, 'N') <> 'Y'
117 AND nvl(AID.Encumbered_flag, 'N') <> 'Y'
118 AND Charge_Applicable_To_Dist_ID IN
119 (SELECT AID1.Invoice_Distribution_ID
120 FROM AP_Invoice_Distributions_All AID1
121 WHERE Invoice_ID = P_Invoice_ID
122 AND Invoice_Line_Number = P_Invoice_Line_Number
123 AND Line_Type_Lookup_Code = 'PREPAY'
124 AND Accounting_Date = P_Accounting_Date
125 --AND Accounting_Event_ID IS NULL Bug 10184420
126 AND EXISTS (SELECT 'Prepay History'
127 FROM AP_Prepay_History_All APH,
128 AP_Invoice_Distributions_All AID2
129 WHERE APH.Prepay_History_ID = P_Prepay_History_ID
130 AND AID2.Invoice_Distribution_ID = AID1.Prepay_Distribution_ID
131 AND AID2.Invoice_ID = APH.Prepay_Invoice_ID
132 AND AID2.Invoice_Line_Number = APH.Prepay_Line_Num
133 -- Bug 6718967
134 AND DECODE(APH.Transaction_Type, 'PREPAYMENT APPLIED', 1, 2) =
135 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2))))
136 ORDER BY Invoice_Distribution_ID; --bug 7614480, order by invoice_distribution_id
137
138
139 CURSOR Invoice_Dists
140 (P_Invoice_ID NUMBER
141 ,P_Event_ID NUMBER DEFAULT NULL
142 ) IS
143 SELECT AID.Invoice_Distribution_ID,
144 AID.Line_Type_Lookup_Code,
145 AID.Amount,
146 AID.Base_Amount,
147 AID.PO_Distribution_ID,
148 AID.RCV_Transaction_ID,
149 NVL(AID.Reversal_Flag,'N'),
150 AID.Parent_Reversal_ID,
151 AID.AWT_Related_ID,
152 AID.AWT_Invoice_Payment_ID,
153 AID.Quantity_Variance,
154 AID.Base_Quantity_Variance,
155 AID.Amount_Variance,
156 AID.Base_Amount_Variance,
157 AID.Historical_Flag,
158 AID.Accounting_Event_Id
159 FROM AP_Invoice_Distributions_All AID
160 WHERE AID.Invoice_ID = P_Invoice_ID
161 AND NVL(AID.Accounting_Event_ID, -99) =
162 DECODE(P_Event_ID, NULL, NVL(AID.Accounting_Event_ID, -99),
163 P_Event_ID)
164 AND AID.Line_Type_Lookup_Code NOT IN ('PREPAY', 'ERV','TERV') --Bug 10181254 added TERV
165 AND AID.Prepay_Distribution_ID IS NULL
166 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
167 AND NVL(AID.Cancellation_Flag,'N') <> 'Y' -- BUG 6513956
168 -- bug fix 6909150
169 AND NOT EXISTS (SELECT 1
170 FROM xla_events
171 WHERE event_id = AID.accounting_event_id
172 AND event_type_code IN ('INVOICE CANCELLED',
173 'CREDIT MEMO CANCELLED',
174 'DEBIT MEMO CANCELLED'))
175 ORDER BY decode(aid.line_type_lookup_code,'AWT',1,2), --Bug 9166188
176 abs(AID.Amount),
177 AID.Invoice_Distribution_ID;
178
179
180 CURSOR Payment_History
181 (P_Invoice_ID NUMBER
182 ,P_Transaction_Type VARCHAR2
183 ) IS
184 SELECT APH.Payment_History_ID,
185 APH.Accounting_Date, -- bug9271242
186 APH.Pmt_Currency_Code,
187 APH.Pmt_To_Base_XRate_Type,
188 APH.Pmt_To_Base_XRate_Date,
189 APH.Pmt_To_Base_XRate,
190 APH.Bank_Currency_Code,
191 APH.Bank_To_Base_XRate_Type,
192 APH.Bank_To_Base_XRate_Date,
193 APH.Bank_To_Base_XRate,
194 APH.Errors_Bank_Amount,
195 APH.Charges_Bank_Amount,
196 APH.Rev_Pmt_Hist_ID,
197 APH.Related_Event_ID,
198 APH.Invoice_Adjustment_Event_ID
199 FROM AP_Payment_History_All APH,
200 AP_Invoice_Payments_All AIP
201 WHERE AIP.Invoice_ID = P_Invoice_ID
202 AND AIP.Check_ID = APH.Check_ID
203 AND APH.Transaction_Type = P_Transaction_Type
204 AND NVL(AIP.Reversal_Flag, 'N') <> 'Y'
205 AND NOT EXISTS (SELECT 'Reversal Exists'
206 FROM AP_Payment_History_All APH1
207 WHERE APH1.Rev_Pmt_Hist_ID = APH.Payment_History_ID
208 AND APH1.Check_ID = APH.Check_ID);
209
210
211 CURSOR Invoice_Header
212 (P_Invoice_ID NUMBER
213 ) IS
214 SELECT AI.Invoice_ID,
215 AI.GL_Date, -- bug9271242
216 AI.Invoice_Amount,
217 AI.Invoice_Currency_Code,
218 AI.Payment_Currency_Code,
219 ASP.Base_Currency_Code,
220 AI.Pay_Curr_Invoice_Amount,
221 AI.Payment_Cross_Rate_Type,
222 AI.Payment_Cross_Rate_Date,
223 AI.Payment_Cross_Rate,
224 AI.Exchange_Rate_Type,
225 AI.Exchange_Date,
226 AI.Exchange_Rate,
227 NVL(AI.Disc_Is_Inv_Less_Tax_Flag, ASP.Disc_Is_Inv_Less_Tax_Flag)
228 Disc_Is_Inv_Less_Tax_Flag,
229 NVL(AI.Exclude_Freight_From_Discount, 'N')
230 Exclude_Freight_From_Discount
231 FROM AP_Invoices_All AI,
232 AP_System_Parameters_All ASP
233 WHERE AI.Invoice_ID = P_Invoice_ID
234 AND AI.Org_ID = ASP.Org_ID;
235
236
237 PROCEDURE Prepay_Dist_Appl
238 (P_Invoice_ID IN NUMBER
239 ,P_Calling_Sequence IN VARCHAR2
240 );
241
242
243 PROCEDURE Prepay_Dist_Cascade_Adj
244 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
245 ,P_Calling_Sequence IN VARCHAR2
246 );
247
248
249 -- Bug 6698125. Added p_xla_event_rec parameter
250 PROCEDURE Prepay_Dist_Proc
251 (P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
252 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
253 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
254 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
255 ,P_Prepay_Hist_Rec IN r_prepay_hist_info
256 ,P_Prepay_Dist_Rec IN r_prepay_dist_info
257 ,P_Inv_Dist_Rec IN ap_accounting_pay_pkg.r_inv_dist_info
258 ,P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
259 ,P_Calc_Mode IN VARCHAR2
260 ,P_Final_Payment IN BOOLEAN
261 ,P_Calling_Sequence IN VARCHAR2
262 );
263
264 PROCEDURE Prepay_Dist_Tax_Diff
265 (P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
266 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
267 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
268 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
269 ,P_Prepay_Hist_Rec IN r_prepay_hist_info
270 ,P_Prepay_Dist_Rec IN r_prepay_dist_info
271 ,P_Inv_Dist_Rec IN ap_accounting_pay_pkg.r_inv_dist_info
272 ,P_Calc_Mode IN VARCHAR2
273 ,P_Calling_Sequence IN VARCHAR2
274 );
275
276
277 PROCEDURE Prepay_Dist_ERV
278 (P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
279 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
280 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
281 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
282 ,P_Prepay_Hist_Rec IN r_prepay_hist_info
283 ,P_Prepay_Dist_Rec IN r_prepay_dist_info
284 ,P_Inv_Dist_Rec IN ap_accounting_pay_pkg.r_inv_dist_info
285 ,P_Prorated_Amount IN NUMBER
286 ,P_Calling_Sequence IN VARCHAR2
287 );
288
289
290 -- Bug 6698125. Added p_xla_event_rec parameter
291 -- Bug 7134020. Added p_inv_dist_id parameter
292 PROCEDURE Prepay_Dist_Reverse
293 (P_prepay_hist_rec IN r_prepay_hist_info
294 ,p_prepay_reversal_id IN NUMBER
295 ,P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
296 ,p_inv_reversal_id IN NUMBER
297 ,p_inv_dist_id IN NUMBER
298 ,p_prepay_inv_dist_id IN NUMBER
299 ,p_calling_sequence IN VARCHAR2
300 );
301
302 -- 9322009, added the following procedure to recreate the incorrect dist
303 -- links for the upgraded prepayment application events, so the the
304 -- prepayment unapplication for the same created in R12 can get successfully
305 -- accounted
306 --
307 PROCEDURE Upg_Dist_Links_Insert
308 (P_Invoice_ID IN NUMBER
309 ,p_prepay_history_id IN NUMBER
310 ,p_accounting_event_id IN NUMBER
311 ,p_calling_sequence IN VARCHAR2
312 );
313
314 PROCEDURE Prepay_Dist_Insert
315 (P_PAD_Rec IN AP_PREPAY_APP_DISTS%ROWTYPE
316 ,P_Calling_Sequence IN VARCHAR2
317 );
318
319 --Bug 5373620 Added following Procedure
320 PROCEDURE Delete_Hist_Dists
321 (P_invoice_id IN NUMBER,
322 P_Calling_Sequence IN VARCHAR2
323 );
324
325 -- Bug 5394585 Added procedure
326 PROCEDURE Update_Gain_Loss_Ind
327 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
328 ,P_Calling_Sequence IN VARCHAR2
329 );
330
331
332 END AP_ACCTG_PREPAY_DIST_PKG;