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