[Home] [Help]
PACKAGE: APPS.JAI_ETCS_PKG
Source
1 PACKAGE jai_etcs_pkg AUTHID CURRENT_USER AS
2 /* $Header: jai_ar_etcs_prc.pls 120.6 2011/08/19 05:45:00 amandali ship $ */
3
4 /***************************************************************************************************
5 CREATED BY : CSahoo
6 CREATED DATE : 01-FEB-2007
7 ENHANCEMENT BUG : 5631784
8 PURPOSE : NEW ENH: TAX COLLECTION AT SOURCE IN RECEIVABLES
9
10
11 -- #
12 -- # Change History -
13
14
15 1. 01/02/2007 CSahoo for bug#5631784. File Version 120.0
16 Forward Porting of 11i BUG#4742259 (TAX COLLECTION AT SOURCE IN RECEIVABLES)
17
18 2. 26.06.2007 sacsethi for bug 6153881 file version 120.2
19
20 Problem - R12RUP03-ST1:UNABLE TO RUN TCS RETURN REPORTS
21
22 Solution - According to R12 Standard we should not use legal entity in out code , so
23 removing legal_entity_id from code ...
24
25 3. 18-Aug-2011 amandali for Bug:12597773
26 Description:ETCS Quarterly reports not triggering Form 27B
27 Fix:Added parameters p_RespPers_flat_no,p_RespPers_prem_bldg,p_RespPers_rd_st_lane,p_RespPers_area_loc,
28 p_RespPers_tn_cty_dt and commented p_RespPersAddress in the procedures generate_etcs_returns,
29 quarterly_returns, create_quarterly_batch_header, validate_batch_header, create_quarterly_fh
30
31 *******************************************************************************************************/
32
33 -- debug variables
34 v_pad_date VARCHAR2(1) := ' ';
35 v_pad_char VARCHAR2(1) := ' ';
36 v_pad_number VARCHAR2(1) := '0';
37
38 -- File Header Size Variables
39 s_line_number NUMBER(2) := 9;
40 s_record_type NUMBER(2) := 2;
41 s_file_type NUMBER(2) := 3;
42 s_upload_type NUMBER(2) := 1;
43 s_file_sequence_number NUMBER(2) := 9;
44 s_deductor_tan NUMBER(2) := 10;
45 s_number_of_batches NUMBER(2) := 9;
46 v_underline_char VARCHAR2(1) := '-';
47
48 -- Challan Detail
49 s_batch_number NUMBER(2) := 9;
50 s_challan_slno NUMBER(2) := 9;
51 s_challan_section NUMBER(2) := 5;
52 s_amount_deducted NUMBER(2) := 14;
53 s_amount_sur NUMBER(2) := 14;
54 s_amount_cess NUMBER(2) := 14;
55 s_amount_tcs NUMBER(2) := 14;
56 s_chq_dd_num NUMBER(2) := 14;
57 s_challan_num NUMBER(2) := 9;
58 s_bank_branch_code NUMBER(2) := 7;
59 s_tds_dep_book_ent NUMBER(2) := 1;
60 s_filler4 NUMBER(2) := 1;
61
62 -- Deductee Detail
63 s_deductee_slno NUMBER(2) := 9;
64 s_deductee_section NUMBER(2) := 5;
65 s_deductee_code NUMBER(2) := 2;
66 s_deductee_pan NUMBER(2) := 10;
67 s_deductee_name NUMBER(2) := 75;
68 s_deductee_address1 NUMBER(2) := 25;
69 s_deductee_address2 NUMBER(2) := 25;
70 s_deductee_address3 NUMBER(2) := 25;
71 s_deductee_address4 NUMBER(2) := 25;
72 s_deductee_address5 NUMBER(2) := 25;
73 s_deductee_state NUMBER(2) := 2;
74 s_deductee_pin NUMBER(2) := 6;
75 s_payment_amount NUMBER(2) := 14;
76 s_tax_rate NUMBER(2) := 4;
77 s_grossing_up_factor NUMBER(2) := 1;
78 s_tax_deducted NUMBER(2) := 14;
79 s_challan_no NUMBER(2) := 9;
80 s_reason_for_nDeduction NUMBER(2) := 1;
81 s_filler NUMBER(2) := 14;
82 s_filler6 NUMBER(2) := 1;
83 s_book_ent_oth NUMBER(2) := 1;
84 s_date NUMBER(1) := 8;
85
86
87 ---ADDED BY VASAVI---
88 G_DATE_DUMMY CONSTANT VARCHAR2(1) := '-';
89 v_delimeter VARCHAR2(1) := '^' ;
90 v_quart_len NUMBER := 15 ;
91
92 sq_len_1 NUMBER := 1 ;
93 sq_len_2 NUMBER :=2 ;
94 sq_len_3 NUMBER :=3 ;
95 sq_len_4 NUMBER :=4 ;
96 sq_len_5 NUMBER :=5 ;
97 sq_len_6 NUMBER :=6 ;
98 sq_len_7 NUMBER :=7 ;
99 sq_len_8 NUMBER :=8 ;
100 sq_len_9 NUMBER :=9 ;
101 sq_len_10 NUMBER :=10 ;
102 sq_len_12 NUMBER :=12 ;
103 sq_len_14 NUMBER :=14 ;
104 sq_len_15 NUMBER :=15 ;
105 sq_len_20 NUMBER :=20 ;
106 sq_len_25 NUMBER :=25 ;
107 sq_len_75 NUMBER :=75 ;
108 sq_len_150 NUMBER :=150 ; /*Bug 8880543 - Added length 150*/
109
110 v_quart_pad VARCHAR2(1) := ' ';
111 v_q_noval_filler VARCHAR2(1) := '-';
112 v_q_null_filler VARCHAR2(1) := '*';
113 v_quart_numfill NUMBER := 0 ;
114 v_format_amount VARCHAR2(17) := 'FM999999999990D00' ;
115 v_format_rate VARCHAR2(9) := 'FM90D0000';
116 ln_batch_id NUMBER ;
117 lv_action VARCHAR2(1) ;
118
119
120 PROCEDURE openFile(
121 p_directory IN VARCHAR2,
122 p_filename IN VARCHAR2
123 ) ;
124
125 PROCEDURE closeFile ;
126
127 PROCEDURE create_fh(p_batch_id IN NUMBER) ;
128
129 PROCEDURE create_quarterly_fh
130 (p_batch_id IN NUMBER,
131 p_period IN VARCHAR2,
132 --p_RespPersAddress IN VARCHAR2,
133 p_RespPers_flat_no IN VARCHAR2 , -- Bug 12597773
134 p_RespPers_prem_bldg IN VARCHAR2 , -- Bug 12597773
135 p_RespPers_rd_st_lane IN VARCHAR2 , -- Bug 12597773
136 p_RespPers_area_loc IN VARCHAR2 , -- Bug 12597773
137 p_RespPers_tn_cty_dt IN VARCHAR2 , -- Bug 12597773
138 p_RespPersState IN VARCHAR2,
139 p_RespPersPin IN NUMBER,
140 p_RespPersAddrChange IN VARCHAR2
141 );
142
143
144 PROCEDURE generate_etcs_returns(
145 p_err_buf OUT NOCOPY VARCHAR2,
146 p_ret_code OUT NOCOPY NUMBER,
147 p_tan_number IN VARCHAR2,
148 p_organization_id IN NUMBER,
149 p_fin_year IN NUMBER,
150 p_tax_authority_id IN NUMBER,
151 p_tax_authority_site_id IN NUMBER,
152 p_seller_name IN VARCHAR2,
153 p_seller_state IN VARCHAR2,
154 p_addrChangedSinceLastRet IN VARCHAR2,
155 p_persRespForCollection IN VARCHAR2,
156 p_desgOfPersResponsible IN VARCHAR2,
157 p_Start_Date IN DATE,
158 p_End_Date IN DATE,
159 p_pro_rcpt_num_org_ret IN NUMBER,
160 p_file_path IN VARCHAR2,
161 p_filename IN VARCHAR2,
162 p_collection_code IN VARCHAR2,
163 p_generate_headers IN VARCHAR2 DEFAULT NULL,
164 p_period IN VARCHAR2 DEFAULT NULL,
165 /*Bug 12597773 -Start*/
166 p_RespPers_flat_no IN VARCHAR2 DEFAULT NULL,
167 p_RespPers_prem_bldg IN VARCHAR2 DEFAULT NULL,
168 p_RespPers_rd_st_lane IN VARCHAR2 DEFAULT NULL,
169 p_RespPers_area_loc IN VARCHAR2 DEFAULT NULL,
170 p_RespPers_tn_cty_dt IN VARCHAR2 DEFAULT NULL,
171 --p_RespPersAddress IN VARCHAR2 DEFAULT NULL,
172 /*Bug 12597773 -End*/
173 p_RespPersState IN VARCHAR2 DEFAULT NULL,
174 p_RespPersPin IN NUMBER DEFAULT NULL,
175 p_RespPersAddrChange IN VARCHAR2 DEFAULT NULL,
176 p_action IN VARCHAR2 DEFAULT NULL
177 --p_collector_status IN VARCHAR2 DEFAULT NULL
178 ) ;
179
180 PROCEDURE yearly_returns
181 (
182 p_err_buf OUT NOCOPY VARCHAR2,
183 p_ret_code OUT NOCOPY NUMBER,
184 p_tan_number IN VARCHAR2,
185 p_organization_id IN NUMBER,
186 p_fin_year IN NUMBER,
187 p_collection_code IN VARCHAR2,
188 p_tax_authority_id IN NUMBER,
189 p_tax_authority_site_id IN NUMBER,
190 p_seller_name IN VARCHAR2,
191 p_seller_state IN VARCHAR2,
192 p_addrChangedSinceLastRet IN VARCHAR2,
193 p_persRespForCollection IN VARCHAR2,
194 p_desgOfPersResponsible IN VARCHAR2,
195 p_start_date IN VARCHAR2,
196 p_end_date IN VARCHAR2,
197 p_pro_rcpt_num_org_ret IN NUMBER,
198 p_file_path IN VARCHAR2,
199 p_filename IN VARCHAR2,
200 p_generate_headers IN VARCHAR2 DEFAULT 'N'
201 ) ;
202
203 PROCEDURE quarterly_returns
204 (
205 p_err_buf OUT NOCOPY VARCHAR2,
206 p_ret_code OUT NOCOPY NUMBER,
207 p_tan_number IN VARCHAR2,
208 p_organization_id IN NUMBER,
209 p_fin_year IN NUMBER,
210 p_period IN VARCHAR2 ,
211 p_collection_code IN VARCHAR2,
212 p_tax_authority_id IN NUMBER,
213 p_tax_authority_site_id IN NUMBER,
214 p_seller_name IN VARCHAR2,
215 p_seller_state IN VARCHAR2,
216 p_addrChangedSinceLastRet IN VARCHAR2,
217 --p_collector_status IN VARCHAR2, /*Bug 8880543 - Commented for eTDS/eTCS FVU Changes*/
218 p_persRespForCollection IN VARCHAR2,
219 p_desgOfPersResponsible IN VARCHAR2,
220 --p_RespPersAddress IN VARCHAR2,
221 p_RespPers_flat_no IN VARCHAR2 , -- Bug 12597773
222 p_RespPers_prem_bldg IN VARCHAR2 , -- Bug 12597773
223 p_RespPers_rd_st_lane IN VARCHAR2 , -- Bug 12597773
224 p_RespPers_area_loc IN VARCHAR2 , -- Bug 12597773
225 p_RespPers_tn_cty_dt IN VARCHAR2 , -- Bug 12597773
226 p_RespPersState IN VARCHAR2 ,
227 p_RespPersPin IN VARCHAR2 ,
228 p_RespPersAddrChange IN VARCHAR2,
229 p_start_date IN VARCHAR2,
230 p_end_date IN VARCHAR2,
231 p_pro_rcpt_num_org_ret IN NUMBER,
232 p_file_path IN VARCHAR2,
233 p_filename IN VARCHAR2,
234 p_action IN VARCHAR2
235 ) ;
236 END jai_etcs_pkg;