DOWNLOADING PAN DATA FROM COMMON BUSINESS NUMBER (CBN) SERVER – REG.
CBN Circular No. 1 [F. No. SW/3/3/96/01-DIT(S)/Vol IX]-2901-02, Dated :17-6-2010
Tel. No. : 011-23676480
Fax : 011-23676480
To,
1. Shri Hiten Mehta, Vice President,
M/s National Securities Depositories Ltd.,
Trade World, 4th Floor, ‘A’ Wing, Kamala Mills Compound,
Senapati Bapat Marg, Lower Parel,
Mumbai-400013.
2. Ms. Daksha Thakur, Asstt. Vice President,
M/s UTI Technology Services Ltd.,
Plot No. 3, Sector 11, CBD Belapur,
Navi Mumbai – 400614.
Sir/Madam,
A new facility is developed to download data through CBN module in text files to enable Service Providers to update their PAN database. The details are as follows:
1. Service Providers are made application users of CBN Application Software. Service Providers will access CBN database of Income Tax Department through Personal Computer (PC) installed in their premises and connected to Bharti network.
2. Service Providers will connect CBN Application as application users by running appropriate link in Internet Explorer. Details are given inAnnexure A. One user is created for each Service Provideri.e. UTSL0001 and NSDL0001 with default password. The password must be changed through Action→Change Password option immediately on first login.
3. (i) Data will be downloaded in the form of fixed width text files (no delimiter). Downloading of files is available through Query→Download Data. Service Provider will download data for following tables from CBN database one by one :
CSS_ALL_AREA_NCC CSS_ALL_CCIT_NCC
CSS_ALL_CIT_NCC CSS_ALL_RANGE_NCC
CSS_ALL_AO_NCC
CSS_NCBN_AIS
CSS_NCBNJPAN
CSS_ALL_EVENT
CSS_ALL_EVENT_LINK
CSS_ALL_PAN_DEL_LIST
CSS_DELETED_ASSESSEE_NCC
CSS IPAN_DELETED_PAN_RQST_NCC
CSS_GB_CODES
CSS_REJECT_REASON
CSS_ALLOWED_INITIALS
(ii) Destination file name will be shown in ‘Download File Name’ field. Naming convention of the files downloaded, File format and other details for updating PAN database at Service Provider site are enclosed herewith as Annexure-B.
(iii) Entire data in a table will be downloaded in the file where ‘Download duration’ is set as 9999 days. No entry is needed in ‘From Date’ and ‘To Date’ fields.
(iv) Entry in ‘From Date’ and ‘To Date’ field is needed in case of tables where ‘Download duration’ is set as 5 days. ‘From date’ can not be earlier than number of days mentioned in ‘Download duration’ field from the current date including the current date. The Date time format DDMMYYYY HH:MI:SS is in 24Hours time format ( i.e HH is in 24Hrs format). The range will be HH24 Hour of day (0-23), MI Minute (0-59) and SS Second (0-59).
(v) Sequence number of database column, Database column name, Database Column size in downloadable file is also shown on the screen. This is set by the CBN Administrator which can not be changed by the user.
(vi) Users should make sure that the required columns are ticked and their order is in conformity of the upload software of Service Provider before downloading the file.
(vii) On pressing ‘Download Data’ button file will be created in /dit/<SP>/cbn directory of Data Exchange Server installed in PDC and already in use for PAN process. Service Provider can download these files from Data Exchange Server as they are doing for other files of PAN process.
(vi) Hierarchy tables, CSS_ALL_AO_NCC, CSS_ALL_RANGE_NCC, CSS_ALL_CIT_NCC, CSS_ALL_CCIT_NCC and CSS_ALL_AREA_NCC will be downloaded together whenever any out of five tables is downloaded. Likewise, both the event related tables i.e. CSS_ALL_EVENT and CSS_ALL_EVENT_LINK will also be downloaded together. Therefore, only one table should be chosen for downloading from hierarchy and event tables to avoid generation of unnecessary files.
4. The export dump of required table will be provided by the Directorate for creating PAN database by the Service Provider. The files downloaded through CBN module will contain changed/incremental/full table data and will be used to update/append/replace data in tables of PAN database created by Service Provider.
5. The tables which have lesser data will be set ‘Download duration’ as 9999 and will be downloaded entirely. The Service Provider has to delete existing data in such tables from his database before uploading of data from the file downloaded from CBN thus creating the table entirely afresh. Tables which have large volume of data will be updated/appended from the incremental text files downloaded.
6. Service Provider should not download data from CBN more than once in a day. As per current schedule of refreshing of CBN from NCC, which is 1200 hrs., it is suggested that UTI will start downloading of data from CBN starting from 1400 hrs. and NSDL will start downloading of data after 1530 hrs. No download is permissible after 1900 hrs. in the evening. UTI will start downloading tables having large data first and NSDL will start downloading tables having small data first. However, if download is not completed due to any reason during above period Service Providers can download files starting 0930 hrs. in the next morning. The work of download in morning session should be managed so that it is finished before 1200 hrs. not causing any disruption in refreshing of CBN database from NCC.
7. Following directories have been created on Data Exchange Server which is already in use for PAN process and UTI and NSDL have access :
For UTI/dit/uti/cbn
For NSDL/dit/nsdl/cbn
Service Providers have been provided read only access on the files generated in above directories.
8. The existing facility for refreshing PAN database through leased line connection will not available after 30th June, 2010. Hence, from 1stJuly, 2010 onwards Service Providers will refresh their PAN data through above facility.
Yours faithfully,
(Neeraj Kumar)
Joint Director of Income-tax (Systems)
Annexure-A
Accessing Common Business Server(CBN) Application Software
Pre-requisite
1. To run CBN Application requires following software/batch files to install/run on client PC :
Copy ITD XP with all the contents in local PC from where CBN has to be accessed; and
For Windows PC(2000 and XP): Run following
(i) Run jinit l3122.exe fromJINITIATOR directory
(ii) Run ITDAPP.exe.
(iii) Copy orarrp.exe fromORARRP directory to C:Program files directory and Run orarrp.exe from their.
Above files will be provided through eMail.
2. Following DNS server addresses should be entered in ‘Internet Protocol (TCP/IP) Properties’ of Local Area Connection properties of the PC:
Preferred DNS server 10.152.39.11
Alternate DNS server 10.152.39.12
Accessing CBN Application Software
1. Start Internet Explorer software in PC having Windows environment (Windows 2000 or XP).
2. Run following link by typing in Internet Explorer’s Address tab: https://itdapp.incometax.net/forms/frmservlet?config=CBN
3. On clicking ‘Go’ button next to Address tab in Internet Explorer RSA Authentication screen will open. In RSA Authentication screen user has to give User Id provided with RSA tokens and password from RSA token screen.
4. On successful RSA authentication CBN Application User Logon screen will open. User has to enter CBN user i.e. UTSL0001 for UTITSL and NSDL0001 for NSDL and password to use file download screen through Query→Download Data. Default password is set as User id itself. Password must be changed immediately on first logon.
Annexure-B
Naming convention, Structure of Output files downloaded from CBN and updation of PAN database at Service Provider site
A. Naming convention for file downloaded from CBN will be as follows:
<USER><DATABASE TABLE><DDMMYYYYHHMMSS>.txt
On 1-7-2010 for user UTSL0001 and Database Table CSS_ALL_AREA_NCC, the file generated will be:
UTSL0001 CSS_ALL_AREA_NCC01072010112209.txt
Similarly, on 2-7-2010 for user NSDL0001 and Database Table CSS_NCBN_AIS, the file generated will be:
NSDLO001 CSS_NCBN_AIS02072010102219.txt
B. Structure of the Output file downloaded from CBN
Structure of the file for CSS_ALL_AREA_NCC
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| AREA_CD | CHAR | 3 | 1 | |
| AREA_NM | VARCHAR2 | 20 | 4 | |
| USERID | VARCHAR2 | 8 | 24 | |
| TIMESTAMP | DATE | 17 | DDMMYYYY HH:MI:SS | 32 |
Structure of the file for CSS_ALL_AO_NCC
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| AREA_CD | CHAR | 3 | 1 | |
| AO_TYPE | VARCHAR2 | 2 | 4 | |
| RANGE_CD | NUMBER | 3 | 6 | |
| AO__NO | NUMBER | 2 | 9 | |
| PPLR_NM | VARCHAR2 | 25 | 11 | |
| DESCR | VARCHAR2 | 40 | 36 | |
| BLDG_CD | VARCHAR2 | 5 | 76 | |
| CONNECT_STR | VARCHAR2 | 20 | 81 | |
| EMP_NO | NUMBER | 6 | 101 | |
| USERID | VARCHAR2 | 8 | 107 | |
| TIMESTAMP | DATE | 17 | DDMMYYYY HH:MI:SS | 115 |
| OLDJFLG | CHAR | 1 | 132 | |
| CITY_CD | NUMBER | 4 | 133 | |
| JURISDICTION | VARCHAR2 | 300 | 137 |
Structure of the file for CSS_ALL_RANGE_NCC
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| AREA_CD | CHAR | 3 | 1 | |
| RANGE_CD | NUMBER | 3 | 4 | |
| RANGE_TYPE | CHAR | 1 | 7 | |
| RANGENM | VARCHAR2 | 30 | 8 | |
| CIT_CD | NUMBER | 2 | 38 | |
| EMP_NO | NUMBER | 6 | 40 | |
| USERID | VARCHAR2 | 8 | 46 | |
| TIMESTAMP | DATE | 17 | DDMMYYYY HH:MI:SS | 54 |
| OLDJLG | CHAR | 1 | 71 | |
| CITY_CD | NUMBER | 4 | 72 |
Structure of the file for CSS_ALL_CIT_NCC
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| CD_AREA | CHAR | 3 | 1 | |
| CIT_CD | NUMBER | 2 | 4 | |
| CIT_NM | VARCHAR2 | 30 | 6 | |
| CCIT_CD | NUMBER | 2 | 36 | |
| CTU_CD | VARCHAR2 | 3 | 38 | |
| CONNECT_STR | VARCHAR2 | 20 | 41 | |
| EMP_NO | NUMBER | 6 | 61 | |
| USERID | VARCHAR2 | 8 | 67 | |
| TIMESTAMP | DATE | 17 | DDMMYYYY HH:MI:SS | 75 |
| OLDJFLG | CHAR | 1 | 92 | |
| CITY_CD | NUMBER | 4 | 93 |
Structure of the file for CSS_ALL_CCIT_NCC
| Field | Data type | Length | FormatA/alue(if Any) | Starting Position |
| AREA_CD | CHAR | 3 | 1 | |
| CCIT_CD | NUMBER | 2 | 4 | |
| CCIT_NM | VARCHAR2 | 30 | 6 | |
| EMP_NO | NUMBER | 6 | 36 | |
| USERID | VARCHAR2 | 8 | 42 | |
| TIMESTAMP | DATE | 17 | DDMMYYYY HH:MI:SS | 50 |
| OLD_FLG | CHAR | 1 | 67 | |
| CITY_CD | NUMBER | 4 | 68 |
Structure of the file for CSS_NCBN_AIS
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| PAN | CHAR | 10 | 1 | |
| USERID | VARCHAR2 | 8 | 11 | |
| TIMESTAMP | DATE | 17 | DDMMYYYYHH:MI:SS | 19 |
| STATUS | CHAR | 1 | 36 | |
| DOB | DATE | 8 | 37 | |
| AREA_CD_CD | CHARUMBER | 3 | 45 | |
| AO_TYPE | VARCHAR2 | 2 | 48 | |
| RANGE_CD | NUMBER | 3 | 50 | |
| AO_NO | NUMBER | 2 | 53 | |
| SRC_BROUGHT_REC | CHAR | 1 | 55 | |
| W_TAX | CHAR | 1 | 56 | |
| G_TAX | CHAR | 1 | 57 | |
| INTRST_TAX | CHAR | 1 | 58 | |
| E_TAX | CHAR | 1 | 59 | |
| LUX_TAX | CHAR | 1 | 60 | |
| IND_CTZN_FLG | CHAR | 1 | 61 | |
| DT_PAN_GEN | DATE | 8 | DDMMYYYY | 62 |
| HOT_CASE_FLG | CHAR | 1 | 70 | |
| TITLE | NUMBER | 1 | 71 | |
| SURNAME | VARCHAR2 | 75 | 72 | |
| FIRST_NM | VARCHAR2 | 25 | 147 | |
| MID_NM | VARCHAR2 | 25 | 172 | |
| COMMN_OFF_NM | VARCHAR2 | 64 | 197 | |
| COMMN_LINEl | VARCHAR2 | 25 | 261 | |
| COMMN_LINE2 | VARCHAR2 | 25 | 286 | |
| COMMN_LINE3 | VARCHAR2 | 25 | 311 | |
| COMMN_LINE4 | VARCHAR2 | 25 | 336 | |
| COMMN_LINE5 | VARCHAR2 | 25 | 361 | |
| COMMN_STATE | NUMBER | 2 | 386 | |
| COMMN PIN | NUMBER | 6 | 388 | |
| F_SURNAME | VARCHAR2 | 25 | 394 | |
| F_FIRST_NM | VARCHAR2 | 25 | 419 | |
| F_MID_NM | VARCHAR2 | 25 | 444 | |
| O_TITLE | NUMBER | 1 | 469 | |
| O_SURNAME | VARCHAR2 | 75 | 470 | |
| O_FIRST_NM | VARCHAR2 | 25 | 545 | |
| O_MID_NM | VARCHAR2 | 25 | 570 | |
| REGN_NO | VARCHAR2 | 30 | 595 | |
| GEN_MDL__FLG | VARCHAR2 | 1 | 625 | |
| DDUP_FLG | CHAR | 1 | 626 |
Structure of the file for CSS_ALL_PAN_DEL_LIST
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| PAN_TO_DEL | VARCHAR2 | 10 | 1 | |
| MATCHING_PAN | VARCHAR2 | 10 | 11 | |
| DATA_EXIST_FLG | CHAR | 1 | 21 | |
| DT_PAN_DELETED | DATE | 8 | 22 | |
| CONFIRM_FLG | CHAR | 1 | 30 | |
| DTLS_MATCH | CHAR | 1 | 31 | |
| AREA_CD | CHAR | 3 | 32 | |
| AO_TYPE | VARCHAR2 | 2 | 35 | |
| RANGE_CD | NUMBER | 3 | 37 | |
| AO_NO | NUMBER | 2 | 40 | |
| AO_DESCR | VARCHAR2 | 25 | 42 | |
| CONFIRMED_BY | VARCHAR2 | 8 | 67 | |
| REMARKS | VARCHAR2 | 70 | 75 | |
| USERID | VARCHAR2 | 8 | 145 | |
| TIMESAMP | DATE | 17 | DDMMYYYY HH:MI:SS | 153 |
| RQST_SRL_NO | NUMBER | 10 | 170 | |
| RESTOR_FLG | CHAR | 1 | 180 | |
| REGRP_ID | NUMBER | 3 | 181 | |
| DT_REGRP | DATE | 8 | 184 | |
| DT_RSTOR_RQST_EXEC | DATE | 8 | 192 |
Structure of the file for CSS_ALLOWED_INITIALS
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| NAME_INITIAL | VARCHAR2 | 2 | 1 | |
| USERID | VARCHAR2 | 8 | 3 | |
| TIMESTAMP | DATE | 17 | 11 |
Structure of the file for CSS_ALL_EVENT
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| SEQ_NO | NUMBER | 22 | 1 | |
| EVENT_CD | CHAR | 1 | 23 | |
| EVENT_DT | DATE | 8 | 24 | |
| PAN | CHAR | 10 | 32 | |
| REM | VARCHAR2 | 600 | 42 | |
| USERID | VARCHAR2 | 8 | 642 | |
| TIMESTAMP | DATE | 17 | DDMMYYYY HH:MI:SS | 650 |
Structure of the file for CSS_ALL_EVENT_LINK
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| SEQ_NO | NUMBER | 22 | 1 | |
| PAN | CHAR | 10 | 23 | |
| LINK_PAN | CHAR | 10 | 33 | |
| USERID | VARCHAR2 | 8 | 43 | |
| TIMESTAMP | DATE | 17 | DDMMYYYY HH:MI:SS | 51 |
Structure of the file for CSS_DELETED_ASSESSEE_NCC
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| PAN | CHAR | 10 | 1 | |
| USERID | VARCHAR2 | 8 | 11 | |
| TIMESTAMP | DATE | 17 | DDMMYYYY HH:MI:SS | 19 |
| STATUS | CHAR | 1 | 36 | |
| DOB | DATE | 8 | DDMMYYYY | 37 |
| AREA_CD | CHAR | 3 | 45 | |
| AO_TYPE | VARCHAR2 | 2 | 48 | |
| RANGE_CD | NUMBER | 3 | 50 | |
| AO_NO | NUMBER | 2 | 53 | |
| SRC_BROUGHT_REC | CHAR | 1 | 55 | |
| W_TAX | CHAR | 1 | 56 | |
| G_TAX | CHAR | 1 | 57 | |
| INTRST_TAX | CHAR | 1 | 58 | |
| E_TAX | CHAR | 1 | 59 | |
| LUX_TAX | CHAR | 1 | 60 | |
| IND_CTZN_FLG | CHAR | 1 | 61 | |
| DT_PAN_GEN | DATE | 8 | DDMMYYYY | 62 |
| HOT_CST_FLG | CHAR | 1 | 70 | |
| TITLE | NUMBER | 1 | 71 | |
| SURNAME | VARCHAR2 | 75 | 72 | |
| FIRST_NM | VARCHAR2 | 25 | 147 | |
| MID_NM | VARCHAR2 | 25 | 172 | |
| COMMN_OFF_NM | VARCHAR2 | 64 | 197 | |
| COMMN_LINE1 | VARCHAR2 | 25 | 261 | |
| COMMN_LINE2 | VARCHAR2 | 25 | 286 | |
| COMMN_LINE3 | VARCHAR2 | 25 | 311 | |
| COMMN_LINE4 | VARCHAR2 | 25 | 336 | |
| COMMN_LINE5 | VARCHAR2 | 25 | 361 | |
| COMMN_PIN | NUMBER | 2 | 386 | |
| COMMN_PIN | NUMBER | 6 | 388 | |
| F_SURNAME | VARCHAR2 | 25 | 394 | |
| F_FIRST_NM | VARCHAR2 | 25 | 419 | |
| F_MID-NM | VARCHAR2 | 25 | 444 | |
| O_TITLE | NUMBER | 1 | 469 | |
| O_SURNAME | VARCHAR2 | 75 | 470 | |
| O_FIRST_NM | VARCHAR2 | 25 | 545 | |
| O_MID_NM | VARCHAR2 | 25 | 570 | |
| REGN_NO | VARCHAR2 | 30 | 595 | |
| GEN_MDL_FLG | VARCHAR2 | 1 | 625 | |
| DDUP_FLG | CHAR | 1 | 626 |
Structure of the file for CSS_GB_CODES
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| CD_TYPE | NUMBER | 3 | 1 | |
| CD_VAL | VARCHAR2 | 20 | 4 | |
| CD_DESC | VARCHAR2 | 100 | 24 | |
| USERID | VARCHAR2 | 8 | 124 | |
| TIMESTAMP | DATE | 17 | DDMMYYYY HH:MI:SS | 132 |
Structure of the file for CSS_REJECT_REASON
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| REASON_CD | NUMBER | 3 | 1 | |
| REASON_DESCR | VARCHAR2 | 100 | 4 |
Structure of the file for CSS_NCBN_IPAN
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| PAN | VARCHAR2 | 10 | 1 | |
| AREA_CD | VARCHAR2 | 3 | 11 | |
| STATUS | CHAR | 1 | 14 | |
| DOB | DATE | 8 | 15 | |
| SEX | CHAR | 1 | 23 | |
| SURNAME | VARCHAR2 | 75 | 24 | |
| FIRST_NM | VARCHAR2 | 25 | 99 | |
| MID_MM | VARCHAR2 | 25 | 124 | |
| F_SURNAME | VARCHAR2 | 25 | 149 | |
| F_FIRST_NM | VARCHAR2 | 25 | 174 | |
| F_MID_NM | VARCHAR2 | 25 | 199 | |
| ALLQT_DT | DATE | 17 | DDMMYYYY HH:MI:SS | 224 |
Structure of the file for CSS IPAN_DELETED_PAN_RQST_NCC
| Field | Data type | Length | Format/Value(if Any) | Starting Position |
| AREA_CD | VARCHAR2 | 3 | 1 | |
| BATCH_NO | NUMBER | 6 | 4 | |
| BATCH_SNO | NUMBER | 2 | 10 | |
| STATUS | CHAR | 1 | 12 | |
| SURNAME | VARCHAR2 | 75 | 13 | |
| FIRST_NM | VARCHAR2 | 25 | 88 | |
| MID_NM | VARCHAR2 | 25 | 113 | |
| F_SURNAME | VARCHAR2 | 25 | 138 | |
| F_FIRST_NM | VARCHAR2 | 25 | 163 | |
| F_MID_NM | VARCHAR2 | 25 | 188 | |
| OTHER_NM | CHAR | 1 | 213 | |
| OTHER_SURNAME | VARCHAR2 | 75 | 214 | |
| OTHER_FIRST_NM | VARCHAR2 | 25 | 289 | |
| OTHER_MID_NM | VARCHAR2 | 25 | 314 | |
| SEX | CHAR | 1 | 339 | |
| DOB | DATE | 8 | 340 | |
| ALLOW DUP_PPAN | CHAR | 1 | 348 | |
| PAN | VARCHAR2 | 10 | 349 | |
| DUP_PPAN | CHAR | 1 | 359 | |
| ALLOT_DT | DATE | 17 | DDMMYYYY HH:MI:SS | 360 |
| XPORT_DT | DATE | 8 | 377 |
NOTE : The Date time format DDMMYYYY HH:MI:SS is in 24 Hours time format (HH is in 24Hrs format). The range will be HH24 Hour of day (0-23), MI Minute (0-59) and SS Second (0-59).
C. Updating tables in PAN database at Service Provider site
Where ‘Download duration’ is set to other than 9999, files downloaded will be generated on the basis of Timestamp. Where Timestamp is not available files will be generated on the basis of Date of Allotment of PAN i.e. ALLOT_DT or DT_PAN_GEN fields.
Service Provider will update their database on following basis:
| Table | Column on which basis data is to be updated |
| CSS_NCBN_AIS | PAN |
| CSS_ALL_PAN_DEL_LIST | PAN_TO_DEL and MATCHING_PANcombination i.e. both the field should be matched. |
| CSS_DELETED_ASSESSEE_NCC | New PANs entry will be available whenever a PAN is deleted and removed from the CSS_NCBN_AIS table. Such entry has to be removed from CSS_NCBN_AIS by the Service Provider from his database. Likewise, when a new PAN is available in CSSNCBNAIS and earlier it was available in CSS_DELETED_ASSESSEE_NCC it will be assumed as restored PAN and will be removed from CSS_ DELETED_ASSESSEE__NCC table by the Service Provider. |
| CSS_IPAN_DELETED_PAN_RQST_NCC | This table contains data of (i) PAN deleted after allotment through batch process i.e. IPAN and (ii) PAN transferred to AIS after allotment from IPAN. This table is required to be refreshed sparingly. Hence, no need to download data on a regular basis. If PANs are available in CSSJMCBN_AIS or CSS_DELETED_ASSESSEE_NCC tables presence in this table should be ignored. For other PANs it should be assumed that it is a deleted PAN which is deleted by the RCC and for restoration of this PAN or information on retained PAN concerned RCC should be approached. |
