[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
1. Table insertion JAI_AP_ETDS_REQUESTS , Missing Data related to WHO Columns
2. cursor c_pan_number is changed to get pan no .
3. Cursor c_fin_year , Tan_number datatype changed from number to varchar2
4. Cursor c_check_dtls ,c_bank_branch_code is changed
Problem - Table used under this cursor has been obsuleted in R12
we should not use ap_check_all , ap_bank_branches etc.
5. In File jai_constants.pls - Two variable is used pan_no , accounting_information to Avoid Hard coding of these information.
4. 03/07/2007 sacsethi for bug 6157120 File version 120.6
R12RUP03-ST1:ETCS REPORT RUNS INTO ERROR
Problem - SH Cess FP missing
Code Fix -
1. SHE Cess also added in CESS amount .
Previous Formula -
cess_amount := tcs_cess_amount + sur_cess_amount
New Formula -
cess_amount := tcs_cess_amount + sur_cess_amount + tcs_sh_cess_amount
5. 22-SEP-2009 Bug 8880543
Added for eTDS/eTCS FVU changes.
6. 09-OCT-2009 Added by Bgowrava for Bug#9005248
Replace the literal 'PANNOTREQD' to 'PANNOTAVBL' according to the latest notification.
7. 27-Oct-2010 Bug 10238421
Description: eTCS changes for FVU 3.0
8. 18-Aug-2011 Bug:12597773 by amandali
Description:ETCS Quarterly reports not triggering Form 27B
Fix:Added parameters p_RespPers_flat_no,p_RespPers_prem_bldg,p_RespPers_rd_st_lane,p_RespPers_area_loc,
p_RespPers_tn_cty_dt and commented p_RespPersAddress in the procedures generate_etcs_returns, quarterly_returns,
create_quarterly_batch_header, validate_batch_header, create_quarterly_fh
Also, added a call to trigger JAINTCSB concurrent in generate_etcs_returns procedure.
Added validations to the parameters added in validate_batch_header
9. March 29, 2012 FVU 3.4 - Added Consolidated file hash in File Header
*****************************************************************************************************/
/*Bug 8880543 - Changes for eTDS/eTCS FVU Changes - Start*/
FUNCTION VALIDATE_ALPHA_NUMERIC(p_str VARCHAR2, p_length NUMBER) RETURN VARCHAR2 IS
lv_resp VARCHAR2(10);
(SELECT TRANSLATE(UPPER(substr(p_str, 1, 5)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','AAAAAAAAAAAAAAAAAAAAAAAAAA') src_str1,
TRANSLATE(substr(p_str, 6, 4),'0123456789','0000000000') src_str2,
TRANSLATE(UPPER(substr(p_str, 10, 1)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','AAAAAAAAAAAAAAAAAAAAAAAAAA') src_str3,
'AAAAA0000A' dest_str
FROM dual) LOOP
IF (i.src_str1 || i.src_str2 || i.src_str3) = i.dest_str or p_str = 'PANNOTAVBL' then
lv_resp := 'VALID';
SELECT jrr.ATTRIBUTE_VALUE
FROM JAI_RGM_DEFINITIONS jrd,
JAI_RGM_REGISTRATIONS jrr
WHERE jrd.REGIME_ID = jrr.REGIME_ID
AND jrr.ATTRIBUTE_CODE = cp_attr_code
AND jrd.REGIME_CODE = cp_rgm_code;
SELECT a.START_DATE
,a.END_DATE
FROM GL_PERIODS a
, gl_period_sets b
WHERE a.PERIOD_SET_NAME = b.period_set_name
AND b.period_set_id = cp_calendar_id
AND a.PERIOD_YEAR = cp_fin_year;
select '1'
from jai_rgm_definitions jrd,
jai_rgm_parties jrp
where jrd.regime_code = 'TCS'
and jrd.regime_id = jrp.regime_id
and jrp.organization_id = p_org_id;
select jrpr.attribute_value
from jai_rgm_parties jrp,
jai_rgm_definitions jrd,
jai_rgm_party_regns jrpr,
jai_rgm_registrations jrr
where jrd.regime_code = 'TCS'
and jrd.regime_id = jrp.regime_id
and jrp.regime_org_id = jrpr.regime_org_id
and jrr.attribute_code = p_attr_code
and jrp.organization_id = p_org_id
and jrr.registration_id = jrpr.registration_id;
select jrr.attribute_value
from jai_rgm_definitions jrd,
jai_rgm_registrations jrr
where jrd.regime_code = 'TCS'
and jrd.regime_id = jrr.regime_id
and jrr.attribute_code = p_attr_code;
SELECT * INTO v_req FROM JAI_AP_ETDS_REQUESTS WHERE batch_id = p_batch_id;
SELECT * INTO v_req FROM JAI_AP_ETDS_REQUESTS WHERE batch_id = p_batch_id;
select nvl(sum(tax_amt),0)
from jai_rgm_taxes
where tax_type = cp_tax_type
and trx_ref_id in
( select trx_ref_id
from jai_rgm_refs_all jra
where
source_ref_document_id = cp_source_doc_id and
jra.source_document_date between p_from_date and p_to_date
and settlement_id
IN ( select settlement_id
from jai_ap_rgm_payments
where check_id = cp_check_id
)
) ;
select pan_no
from JAI_CMN_CUS_ADDRESSES
where customer_id = cp_party_id
and confirm_pan = 'Y'
and pan_no is not null
and rownum = 1 ;
select
DECODE(jca.tcs_customer_type, 'COMPANIES', '01', 'OTHERS', '02') buyer_code
from JAI_CMN_CUS_ADDRESSES jca
where jca.customer_id = cp_party_id
and jca.tcs_customer_type is not null
and rownum = 1 ;
select *
from jai_ar_etcs_t for update ;
select nvl(sum(line_amt),0)
from jai_rgm_refs_all jra
where
source_ref_document_id = cp_source_document_ref_id and
jra.source_document_date between p_from_date and p_to_date
and settlement_id
IN ( select settlement_id
from jai_ap_rgm_payments
where check_id = cp_check_id
) ;
select receipt_date
from ar_cash_receipts
where cash_receipt_id = cp_receipt_id ;
select source_document_type
from jai_rgm_refs_all
where source_document_id = cp_source_ref_id
and source_document_type IN (jai_constants.ar_cash_tax_confirmed , jai_constants.trx_type_inv_comp)
and rownum = 1 ;
select trx_date
from ra_customer_trx_all
where customer_trx_id = cp_inv_id ;
SELECT INTERNAL_BANK_ACCOUNT_ID
FROM IBY_PAYMENTS_ALL
WHERE paper_document_number =cpn_check_id;*/
select a.branch_number
from ce_bank_branches_v a, ce_bank_accounts b
where a.branch_party_id = b.bank_branch_id
and b.bank_account_id = cp_bank_account_id ;*/
/*SELECT a.bank_num
FROM ap_bank_branches a, ap_bank_accounts_all b
WHERE a.bank_branch_id = b.bank_branch_id
AND b.bank_account_id = cp_bank_account_id;*/
insert into jai_ar_etcs_t
(
Batch_id ,
tcs_check_id ,
check_number ,
tcs_check_date ,
challan_no ,
challan_date ,
bank_branch_code ,
source_document_id ,
party_id ,
party_site_id ,
collection_flag ,
tcs_tax_rate ,
exempted_flag ,
certificate_issue_date ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
select
p_batch_id ,
jrp.check_id ,
jrp.check_number ,
jrp. check_deposit_date ,
jrp.challan_no ,
jrp.check_date ,
jrp.bsr_code ,
jra.source_ref_document_id ,
jra.party_id ,
jra.party_site_id ,
p_collection_code ,
jrt.tax_rate ,
jrt.exempted_flag ,
jrc.issue_date ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
from
jai_ap_rgm_payments jrp,
jai_rgm_refs_all jra,
jai_rgm_taxes jrt,
jai_rgm_certificates jrc
where
jrp.settlement_id = jra.settlement_id and
jrp.tax_authority_id = p_tax_authority_id and
jrp.tax_authority_site_id = nvl(p_tax_authority_site_id,jrp.tax_authority_site_id) and
jrp.org_tan_no = p_org_tan_num and
jrt.tax_type = 'TCS' and
jrt.trx_ref_id = jra.trx_ref_id and
jrc.certificate_id = jra.certificate_id and
jra.item_classification = p_collection_code and
jra.source_document_date between p_from_date and p_to_date and
jra.settlement_id is not null and
jra.certificate_id is not null
group by
p_batch_id ,
jrp.check_id ,
jrp.check_number ,
jrp.check_date ,
jrp.challan_no ,
jrp.check_deposit_date ,
jrp.bsr_code ,
jra.source_ref_document_id ,
jra.party_id ,
jra.party_site_id ,
jra.item_classification ,
jrt.tax_rate ,
jrt.exempted_flag ,
jrc.issue_date
having sum(jra.total_tax_amt) > 0;
update jai_ar_etcs_t
set
line_amt = ln_line_amt ,
tcs_amt = ln_tcs_amount ,
surcharge_amt = ln_surcharge_amount ,
cess_amt = ln_cess_amount ,
party_code = lv_customer_type ,
party_pan = lv_pan_no ,
source_document_date = lv_doc_date ,
source_document_type = lv_source_ref_type -- ,
--bank_branch_code = v_bank_branch_code
where current of c_get_recs;
select
description
from FND_FLEX_VALUES_VL a
where flex_value_set_id =
( select flex_value_set_id
from fnd_flex_value_sets
where flex_value_set_name ='JA_IN_INDIAN_STATES'
)
and flex_value = to_number(p_RespPersState) ;
Select ATTRIBUTE_VALUE
from jai_rgm_org_regns_v jrorv ,HR_ORGANIZATION_INFORMATION hrou
where jrorv.attribute_code = jai_constants.pan_no AND
hrou.org_information_context= jai_constants.accounting_information and
hrou.organization_id = p_organization_id and
jrorv.ORGANIZATION_ID = hrou.org_information3 AND
jrorv.REGIME_CODE = jai_constants.tds_regime ;
SELECT attribute2
FROM hr_all_organization_units
WHERE organization_id = p_organization_id;*/
SELECT start_date, end_date
FROM JAI_AP_TDS_YEARS
WHERE tan_no = p_tan_number --Date 26/05/2007 by sacsethi for bug 6153881
AND fin_year = p_fin_year;
SELECT location_id
FROM hr_all_organization_units
WHERE organization_id = p_organization_id;
SELECT location_code, address_line_1, address_line_2, address_line_3, null, null,
replace(postal_code, ' ') postal_code
FROM hr_locations_all
WHERE location_id = p_location_id;
Select Error_Message from jai_ap_etds_errors_t
where batch_id = cp_batch_id ;
select
substr(p_phone_no,0,(select instr(p_phone_no,'-')from dual)-1),
substr(p_phone_no,(select instr(p_phone_no,'-')from dual)+1)
from dual;
select count(1), nvl(sum(tcs_amt + surcharge_amt + cess_amt),0)
from
jai_ar_etcs_t
where
batch_id = ln_batch_id ;
select sum ( count( distinct tcs_tax_rate ) )
from jai_ar_etcs_t
WHERE batch_id = cp_batch_id
and check_number = cp_check_number
group by source_document_id, tcs_tax_rate, exempted_flag ;
select count(1)
from
( select 1
from
jai_ar_etcs_t
where
batch_id = ln_batch_id
group by
NVL(challan_no, 'No Challan Number'),
NVL(challan_date,lv_dummy_date),
NVL(bank_branch_code,'No Bank Branch'),
NVL(tcs_check_id , -1 )
) ;
select NVL(bank_branch_code,'No Bank Branch') bank_branch_code,
NVL(challan_no,'No Challan Number') challan_no,
NVL(challan_date,lv_dummy_date) challan_date,
check_number check_number,
tcs_check_id ,
sum(tcs_amt + surcharge_amt + cess_amt ) total_tcs_amount,
sum(tcs_amt) tcs_amt,
sum(surcharge_amt) surcharge_amt,
sum(cess_amt) cess_amt
from jai_ar_etcs_t a
where a.batch_id = p_batch_id
group by NVL(bank_branch_code,'No Bank Branch'),
NVL(challan_no,'No Challan Number'), NVL(challan_date,lv_dummy_date),
check_number, tcs_check_id;
select nvl(book_entry_deposited,'N')
from jai_ap_rgm_payments
where check_id = cp_check_id ;
select hp.party_name
from hz_parties hp, hz_cust_accounts hca
where hca.cust_account_id = cp_customer_id
and hca.party_id = hp.party_id;
select
address1,
address2,
address3,
address4,
city,
state,
postal_code
from
ar_addresses_v
where
address_id = cp_party_site_id ;
select meaning
from fnd_common_lookups
where lookup_type = 'IN_STATE'
and lookup_code = p_state_name;
select
party_id,challan_line_num, party_site_id,exempted_flag,
party_code, party_pan,source_document_id,
NVL(bank_branch_code,'No Bank Branch') bank_branch_code,
NVL(challan_no,'No Challan Number') challan_no,
NVL(challan_date,lv_dummy_date) challan_date,
check_number,
tcs_tax_rate,
sum(line_amt) line_amount,
max(certificate_issue_date) certificate_issue_date,
max(source_document_date) transaction_date ,
max(tcs_check_date) tcs_check_date,
sum(tcs_amt) tcs_amt,
sum(surcharge_amt) surcharge_amt,
sum(cess_amt) cess_amt,
sum(tcs_amt + surcharge_amt + cess_amt ) total_tcs_amount
from jai_ar_etcs_t a
where a.batch_id = p_batch_id and
challan_line_num = NVL(p_challan_line_num, challan_line_num)
group by
challan_line_num, party_id, party_site_id,exempted_flag, tcs_tax_rate,
check_number,party_code, party_pan,
NVL(bank_branch_code,'No Bank Branch'),
NVL(challan_no,'No Challan Number') ,
NVL(challan_date,lv_dummy_date) ,
source_document_id ;
INSERT INTO jai_ap_etds_errors_t
(batch_id, record_type, reference_id, error_message) VALUES
( ln_batch_id,'DD', v_line_number, p_return_message ) ;
UPDATE jai_ar_etcs_t
SET deductee_line_num = v_line_number
WHERE batch_id = ln_batch_id
and challan_line_num = dd.challan_line_num
and party_id = dd.party_id
and party_site_id = dd.party_site_id
and exempted_flag = dd.exempted_flag
and NVL(bank_branch_code,'No Bank Branch') = NVL(dd.bank_branch_code,'No Bank Branch')
and NVL(challan_no,'No Challan Number') = NVL(dd.challan_no,'No Challan Number')
and NVL(challan_date,lv_dummy_date) = NVL(dd.challan_date,lv_dummy_date)
and check_number = dd.check_number
and tcs_tax_rate = dd.tcs_tax_rate
and source_document_id = dd.source_document_id ;
SELECT JAI_AP_ETDS_T_S.nextval INTO ln_batch_id FROM DUAL;
INSERT INTO JAI_AP_ETDS_REQUESTS(
batch_id, request_id, legal_entity_id , org_tan_number, financial_year,
tax_authority_id, tax_authority_site_id, organization_id,
deductor_name, deductor_state, addr_changed_since_last_ret,
--deductor_status, /*Bug 8880543 - Commented Deductor Status for eTDS/eTCS FVU Changes*/
person_resp_for_deduction, designation_of_pers_resp, challan_start_date,
challan_end_date, file_path, filename ,
created_by ,creation_date , last_updated_by , last_update_date , last_update_login -- Date 28/06/2007 by sacsethi for bug 6157120
) VALUES (
ln_batch_id, v_conc_request_id, null , p_tan_number, p_fin_year,
p_tax_authority_id, p_tax_authority_site_id, p_organization_id,
p_seller_name, p_seller_state, p_addrChangedSinceLastRet,
--p_collector_status, /*Bug 8880543 - Commented Deductor Status for eTDS/eTCS FVU Changes*/
p_persRespForCollection, p_desgOfPersResponsible, p_Start_Date,
p_End_Date, p_file_path, p_filename ,
fnd_global.user_id , sysdate , fnd_global.user_id , sysdate ,fnd_global.login_id -- Date 28/06/2007 by sacsethi for bug 6157120
);
INSERT INTO jai_ap_etds_errors_t
(batch_id, record_type, error_message) values
( ln_batch_id, 'FH', p_return_message ) ;
select meaning into v_ministry_name_other
from ja_lookups lkup
where lkup.lookup_type = 'JAI_MIN_NAME_VALUES'
and lkup.lookup_code = '99';
insert into jai_ap_etds_errors_t(batch_id, record_type, error_message) values
( ln_batch_id, 'BH', p_return_message ) ;
insert into jai_ap_etds_errors_t
(batch_id, record_type, reference_id, error_message) values
( ln_batch_id, 'CD', v_line_number, p_return_message ) ;
UPDATE jai_ar_etcs_t
SET challan_line_num = v_line_number
WHERE batch_id = ln_batch_id
and nvl(challan_no,'No Challan Number') = nvl(cd.challan_no, 'No Challan Number')
and nvl(challan_date, lv_dummy_date) = nvl(cd.challan_date, lv_dummy_date )
and nvl(bank_branch_code, 'No Bank Branch') = nvl(cd.bank_branch_code, 'No Bank Branch')
and check_number = cd.check_number;