[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
When a new TDS year is defined, then this trigger gets fired and inserts data into JAI_AP_TDS_CERT_NUMS for
any organization (legal entity/operating unit) in which tan number is defined. If tan number is not defined for
any operating unit, then we cannot find a record in this table for the same OU, in this case while generating TDS
certificates the tan number defined at legal entity should be used where in organization_id of the record will be NULL
------------------------------------------------------------------------------------------------------------
Change History - FILENAME: ja_in_hr_org_insert_trg.sql
S.No Date Author AND Details
--------------------------------------------------------------------------------------------------------------
1 30/03/2002 RPK:BUG#2293270
Code modified to store the financial years/tan#/certificate ids of the opr units.
2 17/08/2002 Aparajita for bug # 2508085.
if the org tan number is maintained at legal entity level instead of operating unit level then
the earlier code was giving problem as the records are there in JAI_AP_TDS_ORG_TANS for the
legal_entity and not the operating unit.
revamped the code for this, old code is attached at the end.
3 20/02/2004 Vijay Shankar for Bug# 2762636, FileVersion: 618.1
- Added the code to insert data into JAI_AP_TDS_CERT_NUMS table for LE
4. 2/05/2005 rchandan for bug#4323338. Version 116.1
India Org Info DFF is eliminated as a part of JA migration. A table by name ja_in_ap_tds_org_tan is dropped
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
to refer to the new view instead of the dropped table.
5. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
DB Entity as required for CASE COMPLAINCE. Version 116.1
6. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
7 07/12/2005 Hjujjuru for the bug 4866533 File version 120.1
added the who columns in the insert to the table JAI_AP_TDS_CERT_NUMS
Dependencies Due to this bug:-
None
8. 12-Dec-2005 Hjujjuru for Bug 4873356 , File Version 120.3
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.
Modified the SQL%NOTFOUND to SQL%ROWCOUNT before the insert into the table JAI_AP_TDS_CERT_NUMS.
--------------------------------------------------------------------------------------------------------------*/
-- Start, Vijay Shankar for Bug# 2762636
BEGIN
pv_return_code := jai_constants.successful ;
SELECT org_tan_num
INTO v_leg_org_tan_num
FROM jai_ap_tds_org_tan_v --4323338
WHERE organization_id = pr_new.Legal_Entity_id ;
need to clarify with Vijay shankar whats the first update and insert below doing ?
*/
IF pr_new.tan_no IS NOT NULL THEN
UPDATE JAI_AP_TDS_CERT_NUMS
SET org_tan_num = pr_new.tan_no
WHERE legal_entity = pr_new.Legal_Entity_Id
AND (organization_id IS NULL OR organization_id = to_number(pr_new.Legal_Entity_Id) )
AND fin_yr = pr_new.Fin_Year
AND legal_entity = to_number(pr_new.Legal_Entity_Id);
INSERT INTO JAI_AP_TDS_CERT_NUMS (FIN_YR_CERT_ID,
organization_id, legal_entity, fin_yr,
CERTIFICATE_NUM, LINE_NUM, Org_tan_num,
-- added, Harshita for Bug 4866533
created_by, creation_date, last_updated_by, last_update_date
) VALUES ( JAI_AP_TDS_CERT_NUMS_S.nextval,
to_number(pr_new.Legal_Entity_Id), to_number(pr_new.Legal_Entity_Id), pr_new.Fin_Year,
NULL, NULL, pr_new.tan_no , --v_leg_org_tan_num, -- Harshita for Bug 4873356
-- added, Harshita for Bug 4866533
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
);
SELECT organization_id
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_code = 'TDS'
AND registration_type = 'OTHERS'
AND attribute_type_code = 'PRIMARY'
AND attribute_code = 'TAN NO'
AND attribute_Value = pr_new.tan_no
)
LOOP
v_org_tan_num := pr_new.tan_no;
SELECT org_tan_num
INTO v_org_tan_num
FROM jai_ap_tds_org_tan_v --- 4323338
WHERE organization_id = c_org_id.organization_id;
UPDATE JAI_AP_TDS_CERT_NUMS
SET org_tan_num = pr_new.tan_no --nvl(v_org_tan_num, v_leg_org_tan_num)
WHERE organization_id = c_org_id.organization_id
-- AND legal_entity = pr_new.Legal_Entity_Id
AND fin_yr = pr_new.Fin_Year;
INSERT INTO JAI_AP_TDS_CERT_NUMS (FIN_YR_CERT_ID,
organization_id, legal_entity, fin_yr,
CERTIFICATE_NUM, LINE_NUM, Org_tan_num,
-- added, Harshita for Bug 4866533
created_by, creation_date, last_updated_by, last_update_date
) VALUES ( JAI_AP_TDS_CERT_NUMS_S.nextval,
c_org_id.Organization_id,
/* Bug 5388544. Added by Lakshmi Gopalsami
* Checked the value of legal_entity_id, If it is 0, insert the
* same else insert the value of c_org_id.organization_id.
*/
decode(pr_new.Legal_Entity_Id, 0,0,c_org_id.organization_id),
pr_new.Fin_Year,
NULL, NULL, v_org_tan_num,
-- added, Harshita for Bug 4866533
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
);