[Home] [Help]
PACKAGE: APPS.AP_ACCTG_PREPAY_DIST_PKG
Source
1 PACKAGE AP_ACCTG_PREPAY_DIST_PKG AS
2 /* $Header: appredis.pls 120.11.12010000.3 2008/09/26 23:41:01 gagrawal 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 CURSOR Prepay_History
36 (P_Invoice_ID NUMBER
37 ) IS
38 SELECT APH.Prepay_History_ID,
39 APH.Prepay_Invoice_ID,
40 APH.Invoice_ID,
41 APH.Invoice_Line_Number,
42 APH.Transaction_Type,
43 APH.Accounting_Date,
44 APH.Invoice_Adjustment_Event_ID,
45 APH.Related_Prepay_App_Event_ID
46 FROM AP_Prepay_History_All APH
47 WHERE APH.Invoice_ID = P_Invoice_ID
48 AND APH.Accounting_Event_ID IS NULL
49 ORDER BY transaction_type;
50
51
52 CURSOR Prepay_Dists
53 (P_Invoice_ID NUMBER,
54 P_Invoice_Line_Number NUMBER,
55 P_Accounting_Date DATE,
56 P_Prepay_History_ID NUMBER
57 ) IS
58 (SELECT AID.Invoice_ID,
59 AID.Invoice_Distribution_ID,
60 AID.Line_Type_Lookup_Code,
61 AID.Amount,
62 AID.Base_Amount,
63 AID.Accounting_Event_ID,
64 AID.Prepay_Distribution_ID,
65 AID.Prepay_Tax_Diff_Amount,
66 AID.Parent_Reversal_ID
67 FROM AP_Invoice_Distributions_All AID
68 WHERE Invoice_ID = P_Invoice_ID
69 AND Invoice_Line_Number = P_Invoice_Line_Number
70 AND Line_Type_Lookup_Code = 'PREPAY'
71 AND Accounting_Date = P_Accounting_Date
72 AND Accounting_Event_ID IS NULL
73 AND EXISTS (SELECT 'Prepay History'
74 FROM AP_Prepay_History_All APH,
75 AP_Invoice_Distributions_All AID1
76 WHERE APH.Prepay_History_ID = P_Prepay_History_ID
77 AND AID1.Invoice_Distribution_ID = AID.Prepay_Distribution_ID
78 AND AID1.Invoice_ID = APH.Prepay_Invoice_ID
79 AND AID1.Invoice_Line_Number = APH.Prepay_Line_Num
80 -- Bug 6718967
81 AND DECODE(APH.Transaction_Type, 'PREPAYMENT APPLIED', 1, 2) =
82 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2))
83 UNION ALL
84 SELECT AID.Invoice_ID,
85 AID.Invoice_Distribution_ID,
86 AID.Line_Type_Lookup_Code,
87 AID.Amount,
88 AID.Base_Amount,
89 AID.Accounting_Event_ID,
90 AID.Prepay_Distribution_ID,
91 AID.Prepay_Tax_Diff_Amount,
92 AID.Parent_Reversal_ID
93 FROM AP_Invoice_Distributions_All AID
94 WHERE Invoice_ID = P_Invoice_ID
95 AND Line_Type_Lookup_Code IN ('NONREC_TAX','REC_TAX')
96 AND Accounting_Event_ID IS NULL
97 AND Charge_Applicable_To_Dist_ID IN
98 (SELECT AID1.Invoice_Distribution_ID
99 FROM AP_Invoice_Distributions_All AID1
100 WHERE Invoice_ID = P_Invoice_ID
101 AND Invoice_Line_Number = P_Invoice_Line_Number
102 AND Line_Type_Lookup_Code = 'PREPAY'
103 AND Accounting_Date = P_Accounting_Date
104 AND Accounting_Event_ID IS NULL
105 AND EXISTS (SELECT 'Prepay History'
106 FROM AP_Prepay_History_All APH,
107 AP_Invoice_Distributions_All AID2
108 WHERE APH.Prepay_History_ID = P_Prepay_History_ID
109 AND AID2.Invoice_Distribution_ID = AID1.Prepay_Distribution_ID
110 AND AID2.Invoice_ID = APH.Prepay_Invoice_ID
111 AND AID2.Invoice_Line_Number = APH.Prepay_Line_Num
112 -- Bug 6718967
113 AND DECODE(APH.Transaction_Type, 'PREPAYMENT APPLIED', 1, 2) =
114 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2))));
115
116
117 CURSOR Invoice_Dists
118 (P_Invoice_ID NUMBER
119 ,P_Event_ID NUMBER DEFAULT NULL
120 ) IS
121 SELECT AID.Invoice_Distribution_ID,
122 AID.Line_Type_Lookup_Code,
123 AID.Amount,
124 AID.Base_Amount,
125 AID.PO_Distribution_ID,
126 AID.RCV_Transaction_ID,
127 NVL(AID.Reversal_Flag,'N'),
128 AID.Parent_Reversal_ID,
129 AID.AWT_Related_ID,
130 AID.AWT_Invoice_Payment_ID,
131 AID.Quantity_Variance,
132 AID.Base_Quantity_Variance,
133 AID.Amount_Variance,
134 AID.Base_Amount_Variance,
135 AID.Historical_Flag,
136 AID.Accounting_Event_Id
137 FROM AP_Invoice_Distributions_All AID
138 WHERE AID.Invoice_ID = P_Invoice_ID
139 AND NVL(AID.Accounting_Event_ID, -99) =
140 DECODE(P_Event_ID, NULL, NVL(AID.Accounting_Event_ID, -99),
141 P_Event_ID)
142 AND AID.Line_Type_Lookup_Code NOT IN ('PREPAY', 'ERV')
143 AND AID.Prepay_Distribution_ID IS NULL
144 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
145 AND NVL(AID.Cancellation_Flag,'N') <> 'Y' -- BUG 6513956
146 -- bug fix 6909150
147 AND NOT EXISTS (SELECT 1
148 FROM xla_events
149 WHERE event_id = AID.accounting_event_id
150 AND event_type_code IN ('INVOICE CANCELLED',
151 'CREDIT MEMO CANCELLED',
152 'DEBIT MEMO CANCELLED'))
153 ORDER BY AID.Amount;
154
155
156 CURSOR Payment_History
157 (P_Invoice_ID NUMBER
158 ,P_Transaction_Type VARCHAR2
159 ) IS
160 SELECT APH.Payment_History_ID,
161 APH.Pmt_Currency_Code,
162 APH.Pmt_To_Base_XRate_Type,
163 APH.Pmt_To_Base_XRate_Date,
164 APH.Pmt_To_Base_XRate,
165 APH.Bank_Currency_Code,
166 APH.Bank_To_Base_XRate_Type,
167 APH.Bank_To_Base_XRate_Date,
168 APH.Bank_To_Base_XRate,
169 APH.Errors_Bank_Amount,
170 APH.Charges_Bank_Amount,
171 APH.Rev_Pmt_Hist_ID,
172 APH.Related_Event_ID,
173 APH.Invoice_Adjustment_Event_ID
174 FROM AP_Payment_History_All APH,
175 AP_Invoice_Payments_All AIP
176 WHERE AIP.Invoice_ID = P_Invoice_ID
177 AND AIP.Check_ID = APH.Check_ID
178 AND APH.Transaction_Type = P_Transaction_Type
179 AND NVL(AIP.Reversal_Flag, 'N') <> 'Y'
180 AND NOT EXISTS (SELECT 'Reversal Exists'
181 FROM AP_Payment_History_All APH1
182 WHERE APH1.Rev_Pmt_Hist_ID = APH.Payment_History_ID
183 AND APH1.Check_ID = APH.Check_ID);
184
185
186 CURSOR Invoice_Header
187 (P_Invoice_ID NUMBER
188 ) IS
189 SELECT AI.Invoice_ID,
190 AI.Invoice_Amount,
191 AI.Invoice_Currency_Code,
192 AI.Payment_Currency_Code,
193 ASP.Base_Currency_Code,
194 AI.Pay_Curr_Invoice_Amount,
195 AI.Payment_Cross_Rate_Type,
196 AI.Payment_Cross_Rate_Date,
197 AI.Payment_Cross_Rate,
198 AI.Exchange_Rate_Type,
199 AI.Exchange_Date,
200 AI.Exchange_Rate,
201 NVL(AI.Disc_Is_Inv_Less_Tax_Flag, ASP.Disc_Is_Inv_Less_Tax_Flag)
202 Disc_Is_Inv_Less_Tax_Flag,
203 NVL(AI.Exclude_Freight_From_Discount, 'N')
204 Exclude_Freight_From_Discount
205 FROM AP_Invoices_All AI,
206 AP_System_Parameters_All ASP
207 WHERE AI.Invoice_ID = P_Invoice_ID
208 AND AI.Org_ID = ASP.Org_ID;
209
210
211 PROCEDURE Prepay_Dist_Appl
212 (P_Invoice_ID IN NUMBER
213 ,P_Calling_Sequence IN VARCHAR2
214 );
215
216
217 PROCEDURE Prepay_Dist_Cascade_Adj
218 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
219 ,P_Calling_Sequence IN VARCHAR2
220 );
221
222
223 -- Bug 6698125. Added p_xla_event_rec parameter
224 PROCEDURE Prepay_Dist_Proc
225 (P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
226 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
227 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
228 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
229 ,P_Prepay_Hist_Rec IN r_prepay_hist_info
230 ,P_Prepay_Dist_Rec IN r_prepay_dist_info
231 ,P_Inv_Dist_Rec IN ap_accounting_pay_pkg.r_inv_dist_info
232 ,P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
233 ,P_Calc_Mode IN VARCHAR2
234 ,P_Final_Payment IN BOOLEAN
235 ,P_Calling_Sequence IN VARCHAR2
236 );
237
238 PROCEDURE Prepay_Dist_Tax_Diff
239 (P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
240 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
241 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
242 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
243 ,P_Prepay_Hist_Rec IN r_prepay_hist_info
244 ,P_Prepay_Dist_Rec IN r_prepay_dist_info
245 ,P_Inv_Dist_Rec IN ap_accounting_pay_pkg.r_inv_dist_info
246 ,P_Calc_Mode IN VARCHAR2
247 ,P_Calling_Sequence IN VARCHAR2
248 );
249
250
251 PROCEDURE Prepay_Dist_ERV
252 (P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
253 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
254 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
255 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
256 ,P_Prepay_Hist_Rec IN r_prepay_hist_info
257 ,P_Prepay_Dist_Rec IN r_prepay_dist_info
258 ,P_Inv_Dist_Rec IN ap_accounting_pay_pkg.r_inv_dist_info
259 ,P_Prorated_Amount IN NUMBER
260 ,P_Calling_Sequence IN VARCHAR2
261 );
262
263
264 -- Bug 6698125. Added p_xla_event_rec parameter
265 -- Bug 7134020. Added p_inv_dist_id parameter
266 PROCEDURE Prepay_Dist_Reverse
267 (P_prepay_hist_rec IN r_prepay_hist_info
268 ,p_prepay_reversal_id IN NUMBER
269 ,P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
270 ,p_inv_reversal_id IN NUMBER
271 ,p_inv_dist_id IN NUMBER
272 ,p_prepay_inv_dist_id IN NUMBER
273 ,p_calling_sequence IN VARCHAR2
274 );
275
276
277 PROCEDURE Prepay_Dist_Insert
278 (P_PAD_Rec IN AP_PREPAY_APP_DISTS%ROWTYPE
279 ,P_Calling_Sequence IN VARCHAR2
280 );
281
282 --Bug 5373620 Added following Procedure
283 PROCEDURE Delete_Hist_Dists
284 (P_invoice_id IN NUMBER,
285 P_Calling_Sequence IN VARCHAR2
286 );
287
288 -- Bug 5394585 Added procedure
289 PROCEDURE Update_Gain_Loss_Ind
290 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
291 ,P_Calling_Sequence IN VARCHAR2
292 );
293
294
295 END AP_ACCTG_PREPAY_DIST_PKG;