1 PACKAGE jai_ap_tds_generation_pkg
2 /* $Header: jai_ap_tds_gen.pls 120.5 2007/05/03 13:48:44 csahoo ship $ */
3 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 ---------------------------------------------------------------------------- */
69 g_inr_currency_rounding NUMBER := 0;
70 g_fcy_currency_rounding NUMBER := 2;
71 -- added, Harshita for Bug#5131075(5346558)
72 gn_invoice_id NUMBER ;
73 gv_request_id NUMBER ;
74
75 -- Bug 5722028. Added by csahoo
76 gn_tds_rounding_factor NUMBER;
77 gd_tds_rounding_effective_date DATE;
78
79 FUNCTION get_rnded_value (p_tax_amount in number )
80 RETURN NUMBER ;
81 -- End for bug 5722028.
82
83 procedure status_update_chk_validate
84 (
85 p_invoice_id in number,
86 p_invoice_line_number in number default null, /* AP lines uptake */
87 p_invoice_distribution_id in number default null,
88 p_match_status_flag in varchar2 default null,
89 p_is_invoice_validated out nocopy varchar2,
90 p_process_flag out nocopy varchar2,
91 p_process_message out nocopy varchar2,
92 p_codepath in out nocopy varchar2
93 );
94
95
96 procedure process_tds_at_inv_validate
97 (
98 p_invoice_id in number,
99 p_vendor_id in number,
100 p_vendor_site_id in number,
101 p_accounting_date in date,
102 p_invoice_currency_code in varchar2,
103 p_exchange_rate in number,
104 p_set_of_books_id in number,
105 p_org_id in number,
106 p_call_from in varchar2,
107 -- Bug 5722028. Added by csahoo
108 p_creation_date in date,
109 p_process_flag out nocopy varchar2,
110 p_process_message out nocopy varchar2,
111 p_codepath in out nocopy varchar2
112 );
113
114
115 procedure generate_tds_invoices
116 (
117 pn_invoice_id in number,
118 pn_invoice_line_number in number default null, /* AP lines */
119 pn_invoice_distribution_id in number default null, /* Prepayment apply / unapply scenario */
120 pv_invoice_num_prepay_apply in varchar2 default null, /* Prepayment application secanrio */
121 pv_invoice_num_to_tds_apply in varchar2 default null, /* Prepayment unapplication secanrio */
122 pv_invoice_num_to_vendor_apply in varchar2 default null, /* Prepayment unapplication secanrio */
123 pv_invoice_num_to_vendor_can in varchar2 default null, /* Invoice Cancel Secnario */
124 pn_threshold_hdr_id in number default null, /* For validate scenario only */
125 pn_taxable_amount in number,
126 pn_tax_amount in number,
127 pn_tax_id in number,
128 pd_accounting_date in date,
129 pv_tds_event in varchar2,
130 pn_threshold_grp_id in number,
131 pv_tds_invoice_num out nocopy varchar2,
132 pv_cm_invoice_num out nocopy varchar2,
133 pn_threshold_trx_id out nocopy number,
134 -- Bug 5722028. Added by csahoo
135 pd_creation_date in date,
136 p_process_flag out nocopy varchar2,
137 p_process_message out nocopy varchar2
138 );
139
140 procedure process_threshold_transition
141 (
142 p_threshold_grp_id in number,
143 p_threshold_slab_id in number,
144 p_invoice_id in number,
145 p_vendor_id in number,
146 p_vendor_site_id in number,
147 p_accounting_date in date,
148 p_tds_event in varchar2,
149 p_org_id in number,
150 pv_tds_invoice_num out nocopy varchar2,
151 pv_cm_invoice_num out nocopy varchar2,
152 p_process_flag out nocopy varchar2,
153 p_process_message out nocopy varchar2
154 );
155
156 procedure import_and_approve
157 (
158 p_invoice_id in number,
159 p_start_thhold_trx_id in number,
160 p_tds_event in varchar2,
161 p_process_flag out nocopy varchar2,
162 p_process_message out nocopy varchar2
163 ) ;
164
165 procedure approve_tds_invoices
166 (
167 errbuf out nocopy varchar2,
168 retcode out nocopy varchar2,
169 p_parent_request_id in number,
170 p_invoice_id in number,
171 p_vendor_id in number,
172 p_vendor_site_id in number,
173 p_start_thhold_trx_id in number
174 );
175
176 /* Following procedure is called from ja_in_ap_aia_before_trg to update the ids of the TDS invoices */
177 procedure populate_tds_invoice_id
178 (
179 p_invoice_id in number,
180 p_invoice_num in varchar2,
181 p_vendor_id in number,
182 p_vendor_site_id in number,
183 p_process_flag out nocopy varchar2,
184 p_process_message out nocopy varchar2
185 );
186
187 procedure maintain_thhold_grps
188 (
189 p_threshold_grp_id in out nocopy number ,
190 p_vendor_id in number default null,
191 p_org_tan_num in varchar2 default null,
192 p_vendor_pan_num in varchar2 default null,
193 p_section_type in varchar2 default null,
194 p_section_code in varchar2 default null,
195 p_fin_year in number default null,
196 p_org_id in number default null,
197 p_trx_invoice_amount in number default null,
198 p_trx_invoice_cancel_amount in number default null,
199 p_trx_invoice_apply_amount in number default null,
200 p_trx_invoice_unapply_amount in number default null,
201 p_trx_tax_paid in number default null,
202 p_trx_thhold_change_tax_paid in number default null,
203 p_trx_threshold_slab_id in number default null,
204 p_tds_event in varchar2,
205 p_invoice_id in number default null,
206 p_invoice_line_number in number default null, /* AP lines Uptake */
207 p_invoice_distribution_id in number default null,
208 p_remarks in varchar2 default null,
209 -- Bug 5722028. Added by csahoo
210 p_creation_date in date default sysdate,
211 p_threshold_grp_audit_id out nocopy number,
212 p_process_flag out nocopy varchar2,
213 P_process_message out nocopy varchar2,
214 p_codepath in out nocopy varchar2
215 );
216
217 procedure insert_tds_thhold_trxs --4333449
218 (
219 p_invoice_id in number,
220 p_tds_event in varchar2,
221 p_tax_id in number default null,
222 p_tax_rate in number default null,
223 p_taxable_amount in number default null,
224 p_tax_amount in number default null,
225 p_tds_authority_vendor_id in number default null,
226 p_tds_authority_vendor_site_id in number default null,
227 p_invoice_tds_authority_num in varchar2 default null,
228 p_invoice_tds_authority_type in varchar2 default null,
229 p_invoice_tds_authority_curr in varchar2 default null,
230 p_invoice_tds_authority_amt in number default null,
231 p_invoice_tds_authority_id in number default null,
232 p_vendor_id in number default null,
233 p_vendor_site_id in number default null,
234 p_invoice_vendor_num in varchar2 default null,
235 p_invoice_vendor_type in varchar2 default null,
236 p_invoice_vendor_curr in varchar2 default null,
237 p_invoice_vendor_amt in number default null,
238 p_invoice_vendor_id in number default null,
239 p_parent_inv_payment_priority in number default null,
240 p_parent_inv_exchange_rate in number default null
241 );
242
243 -- added, Harshita for Bug 5096787
244 Procedure create_tds_after_holds_release
245 (
246 errbuf out nocopy varchar2,
247 retcode out nocopy varchar2,
248 p_invoice_id IN number ,
249 p_invoice_amount IN number ,
250 p_payment_status_flag IN varchar2 ,
251 p_invoice_type_lookup_code IN varchar2 ,
252 p_vendor_id IN number ,
253 p_vendor_site_id IN number ,
254 p_accounting_date IN DATE ,
255 p_invoice_currency_code IN varchar2 ,
256 p_exchange_rate IN number ,
257 p_set_of_books_id IN number ,
258 p_org_id IN number ,
262 p_codepath IN varchar2 ,
259 p_call_from IN varchar2 ,
260 p_process_flag IN varchar2 ,
261 p_process_message IN varchar2 ,
263 p_request_id IN number default null-- added, Harshita for Bug#5131075(5346558)
264 ) ;
265 -- ended, Harshita for Bug 5096787
266
267 --Procedure Added by Sanjikum for Bug#5131075(4718907)
268 PROCEDURE get_tds_threshold_slab(
269 p_prepay_distribution_id IN NUMBER,
270 p_threshold_grp_id IN OUT NOCOPY NUMBER,
271 p_threshold_hdr_id IN OUT NOCOPY NUMBER,
272 p_threshold_slab_id OUT NOCOPY NUMBER,
273 p_threshold_type OUT NOCOPY VARCHAR2,
274 p_process_flag OUT NOCOPY VARCHAR2,
275 p_process_message OUT NOCOPY VARCHAR2,
276 p_codepath IN OUT NOCOPY VARCHAR2);
277
278 --Procedure Added by Sanjikum for Bug#5131075(4718907)
279 PROCEDURE process_threshold_rollback(
280 p_invoice_id IN VARCHAR2,
281 p_before_threshold_type IN VARCHAR2,
282 p_after_threshold_type IN VARCHAR2,
283 p_before_threshold_slab_id IN NUMBER,
284 p_after_threshold_slab_id IN NUMBER,
285 p_threshold_grp_id IN NUMBER,
286 p_org_id IN NUMBER,
287 p_accounting_date IN DATE,
288 p_invoice_distribution_id IN NUMBER DEFAULT NULL,
289 p_prepay_distribution_id IN NUMBER DEFAULT NULL,
290 p_process_flag OUT NOCOPY VARCHAR2,
291 p_process_message OUT NOCOPY VARCHAR2,
292 p_codepath IN OUT NOCOPY VARCHAR2);
293
294
295 end jai_ap_tds_generation_pkg;
[Home] [Help]