1 PACKAGE jai_ap_tds_generation_pkg
2 /* $Header: jai_ap_tds_gen.pls 120.13 2012/06/06 09:30:54 mmurtuza ship $ */
3 AUTHID CURRENT_USER AS
4 /* ----------------------------------------------------------------------------
5 FILENAME : jai_ap_inv_tds_generation_pkg_s.sql
6
7 Created By : Aparajita
8
9 Created Date : 19-feb-2005
10
11 Bug :
12
13 Purpose : Implementation of tax defaultation functionality on AP invoice.
14
15 Called from : Trigger ja_in_ap_aia_after_trg
16 Trigger ja_in_ap_aida_after_trg
17
18 CHANGE HISTORY:
19 -------------------------------------------------------------------------------
20 S.No Date Author and Details
21 -------------------------------------------------------------------------------
22 1. 24/12/2004 Aparajita for bug#4088186. version#115.0. TDS Clean Up.
23
24 Created this package for implementing the TDS generation
25 functionality onto AP invoice.
26
27 2. 2/05/2005 rchandan for bug#4333449. Version 116.1
28 A new procedure to insert into jai_ap_tds_thhold_trxs table
29 is added.
30
31 3. 08-Jun-2005 Version 116.1 jai_ap_tds_gen -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
32 as required for CASE COMPLAINCE.
33
34 4. 19-Jan-2006 avallabh for bug 4926736. File version 120.2
35 Removed procedure process_tds_batch since it is no longer used.
36
37 5 27/03/2006 Hjujjuru for Bug 5096787 , File Version 120.3
38 Spec changes have been made in this file as a part og Bug 5096787.
39 Now, the r12 Procedure/Function specs is in this file are in
40 sync with their corrsponding 11i counterparts
41
42 6. 03/11/2006 Sanjikum for Bug#5131075, File Version 120.4
43 1) Changes are done for forward porting of bugs - 4718907, 5346558
44
45 Dependency Due to this Bug
46 --------------------------
47 Yes, as Package spec is changed and there are multiple files changed as part of current
48
49 7. 03/05/2007 Bug 5722028. Added by csahoo file version 120.5
50 Forward Porting to R12.
51 Added parameter p_creation_date for the follownig procedures
52 process_tds_at_inv_validate
53 maintain_thhold_grps
54 and pd_creation_date in generate_tds_invoices.
55 Added global variables
56 gn_tds_rounding_factor
57 gd_tds_rounding_effective_date and function get_rnded_value
58
59
60 Depedencies:
61 =============
62 jai_ap_tds_gen.pls - 120.5
63 jai_ap_tds_gen.plb - 120.19
64 jai_ap_tds_ppay.pls - 120.2
65 jai_ap_tds_ppay.plb - 120.5
66 jai_ap_tds_can.plb - 120.6
67
68 8. 06/06/2012 Arcs'ed out version before DTC and arcs'ed in changes with CFE
69
70 ---------------------------------------------------------------------------- */
71 g_inr_currency_rounding NUMBER := 0;
72 g_fcy_currency_rounding NUMBER := 2;
73 -- added, Harshita for Bug#5131075(5346558)
74 gn_invoice_id NUMBER ;
75 gv_request_id NUMBER ;
76
77 -- Bug 5722028. Added by csahoo
78 gn_tds_rounding_factor NUMBER;
79 gd_tds_rounding_effective_date DATE;
80
81 FUNCTION get_rnded_value (p_tax_amount in number )
82 RETURN NUMBER ;
83 -- End for bug 5722028.
84
85 procedure status_update_chk_validate
86 (
87 p_invoice_id in number,
88 p_invoice_line_number in number default null, /* AP lines uptake */
89 p_invoice_distribution_id in number default null,
90 p_match_status_flag in varchar2 default null,
91 p_is_invoice_validated out nocopy varchar2,
92 p_process_flag out nocopy varchar2,
93 p_process_message out nocopy varchar2,
94 p_codepath in out nocopy varchar2
95 );
96
97
98 procedure process_tds_at_inv_validate
99 (
100 p_invoice_id in number,
101 p_vendor_id in number,
102 p_vendor_site_id in number,
103 p_accounting_date in date,
104 p_invoice_currency_code in varchar2,
105 p_exchange_rate in number,
106 p_set_of_books_id in number,
107 p_org_id in number,
108 p_call_from in varchar2,
109 -- Bug 5722028. Added by csahoo
110 p_creation_date in date,
111 p_process_flag out nocopy varchar2,
112 p_process_message out nocopy varchar2,
113 p_codepath in out nocopy varchar2
114 );
115
116
117 procedure generate_tds_invoices
118 (
119 pn_invoice_id in number,
120 pn_invoice_line_number in number default null, /* AP lines */
121 pn_invoice_distribution_id in number default null, /* Prepayment apply / unapply scenario */
122 pv_invoice_num_prepay_apply in varchar2 default null, /* Prepayment application secanrio */
123 pv_invoice_num_to_tds_apply in varchar2 default null, /* Prepayment unapplication secanrio */
124 pv_invoice_num_to_vendor_apply in varchar2 default null, /* Prepayment unapplication secanrio */
125 pv_invoice_num_to_vendor_can in varchar2 default null, /* Invoice Cancel Secnario */
126 pn_threshold_hdr_id in number default null, /* For validate scenario only */
127 pn_taxable_amount in number,
128 pn_tax_amount in number,
129 pn_tax_id in number,
130 pd_accounting_date in date,
131 pv_tds_event in varchar2,
132 pn_threshold_grp_id in number,
133 pv_tds_invoice_num out nocopy varchar2,
134 pv_cm_invoice_num out nocopy varchar2,
135 pn_threshold_trx_id out nocopy number,
136 -- Bug 5722028. Added by csahoo
137 pd_creation_date in date,
138 pn_calc_tax_amount in number default 0, /* Bug 12965614 */
139 p_process_flag out nocopy varchar2,
140 p_process_message out nocopy varchar2
141 );
142
143 procedure process_threshold_transition
144 (
145 p_threshold_grp_id in number,
146 p_threshold_slab_id in number,
147 p_invoice_id in number,
148 p_vendor_id in number,
149 p_vendor_site_id in number,
150 p_accounting_date in date,
151 p_tds_event in varchar2,
152 p_org_id in number,
153 pv_tds_invoice_num out nocopy varchar2,
154 pv_cm_invoice_num out nocopy varchar2,
155 p_process_flag out nocopy varchar2,
156 p_process_message out nocopy varchar2
157 );
158
159 procedure import_and_approve
160 (
161 p_invoice_id in number,
162 p_start_thhold_trx_id in number,
163 p_tds_event in varchar2,
164 p_process_flag out nocopy varchar2,
165 p_process_message out nocopy varchar2
166 ) ;
167
168 procedure approve_tds_invoices
169 (
170 errbuf out nocopy varchar2,
171 retcode out nocopy varchar2,
172 p_parent_request_id in number,
173 p_invoice_id in number,
174 p_vendor_id in number,
175 p_vendor_site_id in number,
176 p_start_thhold_trx_id in number
177 );
178
179 /* Following procedure is called from ja_in_ap_aia_before_trg to update the ids of the TDS invoices */
180 procedure populate_tds_invoice_id
181 (
182 p_invoice_id in number,
183 p_invoice_num in varchar2,
184 p_vendor_id in number,
185 p_vendor_site_id in number,
186 p_process_flag out nocopy varchar2,
187 p_process_message out nocopy varchar2
188 );
189
190 procedure maintain_thhold_grps
191 (
192 p_threshold_grp_id in out nocopy number ,
193 p_vendor_id in number default null,
194 p_org_tan_num in varchar2 default null,
195 p_vendor_pan_num in varchar2 default null,
196 p_section_type in varchar2 default null,
197 p_section_code in varchar2 default null,
198 p_fin_year in number default null,
199 p_org_id in number default null,
200 p_trx_invoice_amount in number default null,
201 p_trx_invoice_cancel_amount in number default null,
202 p_trx_invoice_apply_amount in number default null,
203 p_trx_invoice_unapply_amount in number default null,
204 p_trx_tax_paid in number default null,
205 p_trx_thhold_change_tax_paid in number default null,
206 p_trx_threshold_slab_id in number default null,
207 p_tds_event in varchar2,
208 p_invoice_id in number default null,
209 p_invoice_line_number in number default null, /* AP lines Uptake */
210 p_invoice_distribution_id in number default null,
211 p_remarks in varchar2 default null,
212 -- Bug 5722028. Added by csahoo
213 p_creation_date in date default sysdate,
214 p_threshold_grp_audit_id out nocopy number,
215 p_process_flag out nocopy varchar2,
216 P_process_message out nocopy varchar2,
217 p_codepath in out nocopy varchar2
218 );
219
220 procedure insert_tds_thhold_trxs --4333449
221 (
222 p_invoice_id in number,
223 p_tds_event in varchar2,
224 p_tax_id in number default null,
225 p_tax_rate in number default null,
226 p_taxable_amount in number default null,
227 p_tax_amount in number default null,
228 p_tds_authority_vendor_id in number default null,
229 p_tds_authority_vendor_site_id in number default null,
230 p_invoice_tds_authority_num in varchar2 default null,
231 p_invoice_tds_authority_type in varchar2 default null,
232 p_invoice_tds_authority_curr in varchar2 default null,
233 p_invoice_tds_authority_amt in number default null,
234 p_invoice_tds_authority_id in number default null,
235 p_vendor_id in number default null,
236 p_vendor_site_id in number default null,
237 p_invoice_vendor_num in varchar2 default null,
238 p_invoice_vendor_type in varchar2 default null,
239 p_invoice_vendor_curr in varchar2 default null,
240 p_invoice_vendor_amt in number default null,
241 p_invoice_vendor_id in number default null,
242 p_parent_inv_payment_priority in number default null,
243 p_parent_inv_exchange_rate in number default null
244 );
245
246 -- added, Harshita for Bug 5096787
247 Procedure create_tds_after_holds_release
248 (
249 errbuf out nocopy varchar2,
250 retcode out nocopy varchar2,
251 p_invoice_id IN number ,
252 p_invoice_amount IN number ,
253 p_payment_status_flag IN varchar2 ,
254 p_invoice_type_lookup_code IN varchar2 ,
255 p_vendor_id IN number ,
256 p_vendor_site_id IN number ,
257 p_accounting_date IN DATE ,
258 p_invoice_currency_code IN varchar2 ,
259 p_exchange_rate IN number ,
260 p_set_of_books_id IN number ,
261 p_org_id IN number ,
262 p_call_from IN varchar2 ,
263 p_process_flag IN varchar2 ,
264 p_process_message IN varchar2 ,
265 p_codepath IN varchar2 ,
266 p_request_id IN number default null-- added, Harshita for Bug#5131075(5346558)
267 ) ;
268 -- ended, Harshita for Bug 5096787
269
270 --Procedure Added by Sanjikum for Bug#5131075(4718907)
271 PROCEDURE get_tds_threshold_slab(
272 p_prepay_distribution_id IN NUMBER,
273 p_threshold_grp_id IN OUT NOCOPY NUMBER,
274 p_threshold_hdr_id IN OUT NOCOPY NUMBER,
275 p_threshold_slab_id OUT NOCOPY NUMBER,
276 p_threshold_type OUT NOCOPY VARCHAR2,
277 p_process_flag OUT NOCOPY VARCHAR2,
278 p_process_message OUT NOCOPY VARCHAR2,
279 p_codepath IN OUT NOCOPY VARCHAR2);
280
281 --Procedure Added by Sanjikum for Bug#5131075(4718907)
282 PROCEDURE process_threshold_rollback(
286 p_before_threshold_slab_id IN NUMBER,
283 p_invoice_id IN VARCHAR2,
284 p_before_threshold_type IN VARCHAR2,
285 p_after_threshold_type IN VARCHAR2,
287 p_after_threshold_slab_id IN NUMBER,
288 p_threshold_grp_id IN NUMBER,
289 p_org_id IN NUMBER,
290 p_accounting_date IN DATE,
291 p_invoice_distribution_id IN NUMBER DEFAULT NULL,
292 p_prepay_distribution_id IN NUMBER DEFAULT NULL,
293 p_process_flag OUT NOCOPY VARCHAR2,
294 p_process_message OUT NOCOPY VARCHAR2,
295 p_codepath IN OUT NOCOPY VARCHAR2);
296
297
298 end jai_ap_tds_generation_pkg;
[Home] [Help]