1 PACKAGE BODY JAI_JAP_TY_TRIGGER_PKG AS
2 /* $Header: jai_jap_ty_t.plb 120.3 2006/07/14 09:03:46 lgopalsa noship $ */
3
4 /* REM +======================================================================+
5 REM NAME ARI_T1
6 REM
7 REM DESCRIPTION Called from trigger JAI_JAP_TY_ARIUD_T1
8 REM
9 REM NOTES Refers to old trigger JAI_JAP_TY_ARI_T1
10 REM
11 REM +======================================================================+
12 */
13
14
15 PROCEDURE ARI_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
16 v_org_tan_num jai_ap_tds_org_tan_v.org_tan_num%TYPE; --- 4323338
17 v_leg_org_tan_num jai_ap_tds_org_tan_v.org_tan_num%TYPE;--- 4323338
18 v_opt_unit_id NUMBER;
19
20 BEGIN
21 pv_return_code := jai_constants.successful ;
22 /*------------------------------------------------------------------------------------------------------------
23 Trigger Functionality:
24 When a new TDS year is defined, then this trigger gets fired and inserts data into JAI_AP_TDS_CERT_NUMS for
25 any organization (legal entity/operating unit) in which tan number is defined. If tan number is not defined for
26 any operating unit, then we cannot find a record in this table for the same OU, in this case while generating TDS
27 certificates the tan number defined at legal entity should be used where in organization_id of the record will be NULL
28 ------------------------------------------------------------------------------------------------------------
29 Change History - FILENAME: ja_in_hr_org_insert_trg.sql
30
31 S.No Date Author AND Details
32 --------------------------------------------------------------------------------------------------------------
33 1 30/03/2002 RPK:BUG#2293270
34 Code modified to store the financial years/tan#/certificate ids of the opr units.
35
36 2 17/08/2002 Aparajita for bug # 2508085.
37 if the org tan number is maintained at legal entity level instead of operating unit level then
38 the earlier code was giving problem as the records are there in JAI_AP_TDS_ORG_TANS for the
39 legal_entity and not the operating unit.
40
41 revamped the code for this, old code is attached at the end.
42
43 3 20/02/2004 Vijay Shankar for Bug# 2762636, FileVersion: 618.1
44 - Added the code to insert data into JAI_AP_TDS_CERT_NUMS table for LE
45
46 4. 2/05/2005 rchandan for bug#4323338. Version 116.1
47 India Org Info DFF is eliminated as a part of JA migration. A table by name ja_in_ap_tds_org_tan is dropped
48 and a view jai_ap_tds_org_tan_v is created to capture the PAN No,TAN NO and WARD NO. The code changes are done
49 to refer to the new view instead of the dropped table.
50
51 5. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
52 DB Entity as required for CASE COMPLAINCE. Version 116.1
53
54 6. 13-Jun-2005 File Version: 116.2
55 Ramananda for bug#4428980. Removal of SQL LITERALs is done
56
57 7 07/12/2005 Hjujjuru for the bug 4866533 File version 120.1
58 added the who columns in the insert to the table JAI_AP_TDS_CERT_NUMS
59 Dependencies Due to this bug:-
60 None
61
62 8. 12-Dec-2005 Hjujjuru for Bug 4873356 , File Version 120.3
63 Changed the value of TAN no from v_leg_org_tan_num to pr_new.tan_no in the insert to the table JAI_AP_TDS_CERT_NUMS.
64
65 Modified the SQL%NOTFOUND to SQL%ROWCOUNT before the insert into the table JAI_AP_TDS_CERT_NUMS.
66
67
68 --------------------------------------------------------------------------------------------------------------*/
69
70
71 -- Start, Vijay Shankar for Bug# 2762636
72 BEGIN
73 pv_return_code := jai_constants.successful ;
74 /*
75 SELECT org_tan_num
76 INTO v_leg_org_tan_num
77 FROM jai_ap_tds_org_tan_v --4323338
78 WHERE organization_id = pr_new.Legal_Entity_id ;
79
80 */
81 /*
82 need to clarify with Vijay shankar whats the first update and insert below doing ?
83 */
84
85
86
87 IF pr_new.tan_no IS NOT NULL THEN
88
89 UPDATE JAI_AP_TDS_CERT_NUMS
90 SET org_tan_num = pr_new.tan_no
91 WHERE legal_entity = pr_new.Legal_Entity_Id
92 AND (organization_id IS NULL OR organization_id = to_number(pr_new.Legal_Entity_Id) )
93 AND fin_yr = pr_new.Fin_Year
94 AND legal_entity = to_number(pr_new.Legal_Entity_Id);
95
96 IF SQL%ROWCOUNT = 0 THEN -- Harshita for Bug 4873356
97 INSERT INTO JAI_AP_TDS_CERT_NUMS (FIN_YR_CERT_ID,
98 organization_id, legal_entity, fin_yr,
99 CERTIFICATE_NUM, LINE_NUM, Org_tan_num,
100 -- added, Harshita for Bug 4866533
101 created_by, creation_date, last_updated_by, last_update_date
102 ) VALUES ( JAI_AP_TDS_CERT_NUMS_S.nextval,
103 to_number(pr_new.Legal_Entity_Id), to_number(pr_new.Legal_Entity_Id), pr_new.Fin_Year,
104 NULL, NULL, pr_new.tan_no , --v_leg_org_tan_num, -- Harshita for Bug 4873356
105 -- added, Harshita for Bug 4866533
106 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
107 );
108 END IF;
109 END IF;
110
111 EXCEPTION
112 -- there is no tan number defined at legal entity level and thats why execution comes here
113 WHEN NO_DATA_FOUND THEN
114 v_leg_org_tan_num := null;
115 END;
116 -- End, Vijay Shankar for Bug# 2762636
117
118 -- this loops through all the operating units under the legal entity. updates fin year cert info if a separate Tan number is
119 -- defined at the Operating Unit level
120 FOR c_org_id IN
121 (
122 SELECT organization_id
123 FROM JAI_RGM_ORG_REGNS_V
124 WHERE regime_code = 'TDS'
125 AND registration_type = 'OTHERS'
126 AND attribute_type_code = 'PRIMARY'
127 AND attribute_code = 'TAN NO'
128 AND attribute_Value = pr_new.tan_no
129 )
130 LOOP
131
132 v_org_tan_num := pr_new.tan_no;
133 v_opt_unit_id := c_org_id.organization_id;
134
135 -- check if tan number exists for the operating unit.
136 /* BEGIN
137
138 SELECT org_tan_num
139 INTO v_org_tan_num
140 FROM jai_ap_tds_org_tan_v --- 4323338
141 WHERE organization_id = c_org_id.organization_id;
142
143 EXCEPTION
144
145 WHEN NO_DATA_FOUND THEN
146 v_org_tan_num := null;
147 END;
148
149 IF v_leg_org_tan_num IS NULL AND v_org_tan_num IS NULL THEN
150 RAISE NO_DATA_FOUND;
151 END IF; */
152
153 UPDATE JAI_AP_TDS_CERT_NUMS
154 SET org_tan_num = pr_new.tan_no --nvl(v_org_tan_num, v_leg_org_tan_num)
155 WHERE organization_id = c_org_id.organization_id
156 -- AND legal_entity = pr_new.Legal_Entity_Id
157 AND fin_yr = pr_new.Fin_Year;
158
159
160 IF SQL%ROWCOUNT = 0 THEN -- Harshita for Bug 4873356
161 INSERT INTO JAI_AP_TDS_CERT_NUMS (FIN_YR_CERT_ID,
162 organization_id, legal_entity, fin_yr,
163 CERTIFICATE_NUM, LINE_NUM, Org_tan_num,
164 -- added, Harshita for Bug 4866533
165 created_by, creation_date, last_updated_by, last_update_date
166 ) VALUES ( JAI_AP_TDS_CERT_NUMS_S.nextval,
167 c_org_id.Organization_id,
168 /* Bug 5388544. Added by Lakshmi Gopalsami
169 * Checked the value of legal_entity_id, If it is 0, insert the
170 * same else insert the value of c_org_id.organization_id.
171 */
172 decode(pr_new.Legal_Entity_Id, 0,0,c_org_id.organization_id),
173 pr_new.Fin_Year,
174 NULL, NULL, v_org_tan_num,
175 -- added, Harshita for Bug 4866533
176 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
177
178 );
179 END IF;
180
181 END LOOP;
182
183 EXCEPTION
184 WHEN NO_DATA_FOUND THEN
185 --RAISE_APPLICATION_ERROR(-20009, 'Tan Number has not been defined for this legal entity / operating unit->'||v_opt_unit_id);
186 /* Added an exception block by Ramananda for bug#4570303 */
187 Pv_return_code := jai_constants.expected_error;
188 Pv_return_message := 'Tan Number has not been defined for this legal entity / operating unit->'||v_opt_unit_id ;
189
190 WHEN OTHERS THEN
191 --RAISE_APPLICATION_ERROR(-20008, 'Exception from ja_in_fin_year_cert_trg :' || LTRIM(RTRIM(SQLERRM)));
192 /* Added an exception block by Ramananda for bug#4570303 */
193 Pv_return_code := jai_constants.unexpected_error;
194 Pv_return_message := 'Encountered an error in JAI_JAP_TY_TRIGGER_PKG.ARI_T1 ' || substr(sqlerrm,1,1900);
195
196
197 END ARI_T1 ;
198
199 END JAI_JAP_TY_TRIGGER_PKG ;
[Home] [Help]