1 PACKAGE AP_ACCTG_PAY_DIST_PKG AUTHID CURRENT_USER AS
2 /* $Header: appaydis.pls 120.27 2010/11/10 10:55:10 rseeta ship $ */
3
4
5 TYPE r_inv_pay_info IS RECORD
6 (Invoice_ID AP_Invoices.Invoice_ID%TYPE
7 ,Invoice_Payment_ID AP_Invoice_Payments.Invoice_Payment_ID%TYPE
8 ,Amount AP_Invoice_Payments.Amount%TYPE
9 ,Discount_Taken AP_Invoice_Payments.Discount_Taken%TYPE
10 ,Payment_Base_Amount AP_Invoice_Payments.Payment_Base_Amount%TYPE
11 ,Invoice_Base_Amount AP_Invoice_Payments.Invoice_Base_Amount%TYPE
12 ,Exchange_Rate_Type AP_Invoice_Payments.Exchange_Rate_Type%TYPE
13 ,Exchange_Date AP_Invoice_Payments.Exchange_Date%TYPE
14 ,Exchange_Rate AP_Invoice_Payments.Exchange_Rate%TYPE
15 ,Reversal_Flag AP_Invoice_Payments.Reversal_Flag%TYPE
16 ,Reversal_Inv_Pmt_ID AP_Invoice_Payments.Reversal_Inv_Pmt_ID%TYPE
17 );
18
19
20 -- Cursor to get invoice payments for the given event id
21 CURSOR Invoice_Payments
22 (P_Event_ID NUMBER
23 ,P_Invoice_ID NUMBER DEFAULT NULL
24 ) IS
25 SELECT AIP.Invoice_ID,
26 AIP.Invoice_Payment_ID,
27 AIP.Amount,
28 AIP.Discount_Taken,
29 AIP.Payment_Base_Amount,
30 AIP.Invoice_Base_Amount,
31 AIP.Exchange_Rate_Type,
32 AIP.Exchange_Date,
33 AIP.Exchange_Rate,
34 NVL(AIP.Reversal_Flag,'N'),
35 AIP.Reversal_Inv_Pmt_ID
36 FROM AP_Invoice_Payments_All AIP
37 WHERE Accounting_Event_ID = P_Event_ID
38 AND AIP.Invoice_ID = DECODE(P_Invoice_ID, NULL, AIP.Invoice_ID,
39 P_Invoice_ID);
40
41 -- Cursor to get all the invoice payments that has been paid by
42 -- the check being passed. This cursor will also get all the
43 -- invoice payments that have been adjusted after the payment has
44 -- been cleared.
45 /*Added 'REFUND CANCELLED' for bug 9531314 */
46 CURSOR Clrg_Invoice_Payments
47 (P_Check_ID NUMBER
48 ) IS
49 SELECT AIP.Invoice_ID,
50 AIP.Invoice_Payment_ID,
51 AIP.Amount,
52 AIP.Discount_Taken,
53 AIP.Payment_Base_Amount,
54 AIP.Invoice_Base_Amount,
55 AIP.Exchange_Rate_Type,
56 AIP.Exchange_Date,
57 AIP.Exchange_Rate,
58 NVL(AIP.Reversal_Flag,'N'),
59 AIP.Reversal_Inv_Pmt_ID
60 FROM AP_Invoice_Payments_All AIP
61 WHERE AIP.Check_ID = P_Check_ID
62 AND NVL(AIP.Reversal_Flag, 'N') <> 'Y' --bug 9072782, uncomment the change done in bug 7029334
63 AND AIP.Accounting_Event_ID NOT IN
64 (SELECT APH.Accounting_Event_ID
65 FROM AP_Payment_History_All APH
66 WHERE APH.Check_ID = P_Check_ID
67 AND APH.Transaction_Type in ('PAYMENT CANCELLED', 'REFUND CANCELLED')); --bug 7029334 'PAYMENT CANCELLATION'
68
69 -- Cursor to get payment history information
70 -- bug 5623129
71 -- add Nvl bank_To_Base_XRate to 1 when bank currency code
72 -- is the same as base currency code. pmt exchange rate
73 -- is the clear exchange rate.
74 CURSOR Payment_History
75 (P_Event_ID NUMBER
76 ) IS
77 SELECT APH.Payment_History_ID,
78 APH.Accounting_Date, -- bug9271242
79 APH.Pmt_Currency_Code,
80 APH.Pmt_To_Base_XRate_Type,
81 APH.Pmt_To_Base_XRate_Date,
82 APH.Pmt_To_Base_XRate,
83 APH.Bank_Currency_Code,
84 APH.Bank_To_Base_XRate_Type,
85 APH.Bank_To_Base_XRate_Date,
86 NVL(APH.Bank_To_Base_XRate,1),
87 APH.Errors_Bank_Amount,
88 APH.Charges_Bank_Amount,
89 APH.Rev_Pmt_Hist_ID,
90 APH.Related_Event_ID,
91 APH.Invoice_Adjustment_Event_ID
92 FROM AP_Payment_History_All APH
93 WHERE APH.Accounting_Event_ID = P_Event_ID;
94
95
96 -- Cursor to get the invoice header information for the given
97 -- invoice id
98 CURSOR Invoice_Header
99 (P_Invoice_ID NUMBER
100 ) IS
101 SELECT AI.Invoice_ID,
102 AI.GL_Date, --bug9271242
103 AI.Invoice_Amount,
104 AI.Invoice_Currency_Code,
105 AI.Payment_Currency_Code,
106 ASP.Base_Currency_Code,
107 AI.Pay_Curr_Invoice_Amount,
108 AI.Payment_Cross_Rate_Type,
109 AI.Payment_Cross_Rate_Date,
110 AI.Payment_Cross_Rate,
111 AI.Exchange_Rate_Type,
112 AI.Exchange_Date,
113 AI.Exchange_Rate,
114 NVL(AI.Disc_Is_Inv_Less_Tax_Flag, ASP.Disc_Is_Inv_Less_Tax_Flag)
115 Disc_Is_Inv_Less_Tax_Flag,
116 NVL(AI.Exclude_Freight_From_Discount, 'N')
117 Exclude_Freight_From_Discount
118 FROM AP_Invoices_All AI,
119 AP_System_Parameters_All ASP
120 WHERE AI.Org_ID = ASP.Org_ID
121 AND AI.Invoice_ID = P_Invoice_ID;
122
123
124 -- Cursor to get all the invoice distributions for a particular
125 -- invoice
126 -- bug 5570002 need to exclude TERV/ERV/PREPAY
127 CURSOR Invoice_Dists
128 (P_Invoice_ID NUMBER
129 ,P_Event_ID NUMBER DEFAULT NULL
130 ) IS
131 SELECT AID.Invoice_Distribution_ID,
132 AID.Line_Type_Lookup_Code,
133 AID.Amount,
134 AID.Base_Amount,
135 AID.PO_Distribution_ID,
136 AID.RCV_Transaction_ID,
137 NVL(AID.Reversal_Flag,'N'),
138 AID.Parent_Reversal_ID,
139 AID.AWT_Related_ID,
140 AID.AWT_Invoice_Payment_ID,
141 AID.Quantity_Variance,
142 AID.Base_Quantity_Variance,
143 AID.Amount_Variance,
144 AID.Base_Amount_Variance,
145 AID.historical_flag,
146 AID.accounting_event_id
147 FROM AP_Invoice_Distributions_All AID,
148 Financials_System_Params_All FSP
149 WHERE AID.Invoice_ID = P_Invoice_ID
150 AND NVL(AID.Accounting_Event_ID,-99) = DECODE(P_Event_ID, NULL,
151 NVL(AID.Accounting_Event_ID,-99), P_Event_ID)
152 AND AID.Line_Type_Lookup_Code NOT IN ('PREPAY', 'ERV', 'TERV')
153 AND AID.Prepay_Distribution_ID IS NULL
154 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
155 AND AID.Org_ID = FSP.Org_ID
156 --bug 6147546
157 /*AND 'INVOICE CANCELLED' <> (SELECT event_type_code
158 FROM xla_events
159 WHERE event_id = AID.accounting_event_id)*/
160 --bug6614371
161 -- Bug 6712649. Added Credit and Debit memo cancelled
162 AND NOT EXISTS (SELECT 1
163 FROM xla_events
164 WHERE event_id = AID.accounting_event_id
165 AND application_id = 200 --bug 7308385
166 AND event_type_code IN ('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
167 'CREDIT MEMO CANCELLED',
168 'DEBIT MEMO CANCELLED'))
169 AND ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'N'
170 AND AID.Match_Status_Flag IN ('T','A'))
171 OR
172 ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'Y'
173 AND AID.Match_Status_Flag = 'A')))
174 ORDER BY DECODE(AID.Line_Type_Lookup_Code, 'AWT', 1, 2), --bug 9670808
175 abs(AID.Amount), AID.Invoice_Distribution_ID; --bug 5410819
176 --Bug 8208856 added AID.Invoice_Distribution_ID to the above order by.
177
178
179 PROCEDURE Primary_Pay_Events
180 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
181 ,P_Calling_Sequence IN VARCHAR2
182 );
183
184 PROCEDURE Manual_Pay_Adj_Events
185 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
186 ,P_Calling_Sequence IN VARCHAR2
187 );
188
189 PROCEDURE Cancel_Primary_Pay_Events
190 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
191 ,P_Calling_Sequence IN VARCHAR2
192 );
193
194 PROCEDURE Pay_Dist_Cascade_Adj_Events
195 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
196 ,P_Calling_Sequence IN VARCHAR2
197 );
198
199
200 PROCEDURE Pay_Dist_Proc
201 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
202 ,P_Inv_Pay_Rec IN r_inv_pay_info
203 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
204 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
205 ,P_Inv_Dist_Rec IN ap_accounting_pay_pkg.r_inv_dist_info
206 ,P_Calc_Mode IN VARCHAR2
207 ,P_Final_Payment IN BOOLEAN
208 ,P_Calling_Sequence IN VARCHAR2
209 );
210
211
212 PROCEDURE Pay_Dist_Discount
213 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
214 ,P_Inv_Pay_Rec IN r_inv_pay_info
215 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
216 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
217 ,P_Inv_Dist_Rec IN ap_accounting_pay_pkg.r_inv_dist_info
218 ,P_Calc_Mode IN VARCHAR2
219 ,P_Disc_Pay_Amount IN OUT NOCOPY NUMBER
220 ,P_Disc_Dist_Amount IN OUT NOCOPY NUMBER
221 ,P_Disc_Bank_Amount IN OUT NOCOPY NUMBER
222 ,P_Calling_Sequence IN VARCHAR2
223 );
224
225 PROCEDURE Pay_Dist_ERV
226 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
227 ,P_Inv_Pay_Rec IN r_inv_pay_info
228 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
229 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
230 ,P_Inv_Dist_Rec IN ap_accounting_pay_pkg.r_inv_dist_info
231 ,P_Prorated_Amount IN NUMBER
232 ,P_Calling_Sequence IN VARCHAR2
233 );
234
235 -- bug 5659368
236 PROCEDURE Pay_Dist_Err_Chrg
237 (p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
238 ,p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
239 ,p_distribute_mode IN VARCHAR2
240 ,p_calling_sequence IN VARCHAR2
241 );
242
243 -- Bug 6887295. Added parameter p_inv_dist_rec
244 PROCEDURE Pay_Dist_Reverse
245 (p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
246 ,p_inv_pay_rec IN r_inv_pay_info
247 ,p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
248 ,p_reversal_inv_pmt_id IN NUMBER
249 ,p_related_event_id IN NUMBER
250 ,p_invoice_dist_id IN NUMBER
251 ,p_inv_dist_rec IN ap_accounting_pay_pkg.r_inv_dist_info
252 ,p_calling_sequence IN VARCHAR2
253 );
254
255
256 PROCEDURE Pay_Dist_Insert
257 (P_PD_Rec IN AP_PAYMENT_HIST_DISTS%ROWTYPE
258 ,P_Calling_Sequence IN VARCHAR2
259 );
260
261 PROCEDURE Upg_Dist_Links_Insert
262 (p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
263 ,p_payment_history_id IN NUMBER
264 ,p_accounting_event_id IN NUMBER
265 ,p_calling_sequence IN VARCHAR2
266 );
267
268
269 END AP_ACCTG_PAY_DIST_PKG;
[Home] [Help]