[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
added the who columns in the insert into tables JAI_AP_ETDS_REQUESTS and JAI_AP_ETDS_T.
Dependencies Due to this bug:-
None
11 23/12/2005 Hjujjuru for Bug 4889272, File Version 120.4
Removed the legal_entity_id parameter in the
procedures populate_details and generate_flat_file.
Removed the profile_org_id from the generate_flat_file procedure.
Modified the code to eliminate references to the
legal_entity_id and Operating Unit id.
Changed the position of the parameter p_organization_id in the
generate_flat_file procedure.
12 27/03/2006 Hjujjuru for Bug 5096787 , File Version 120.5
Spec changes have been made in this file as a part og Bug 5096787.
Now, the r12 Procedure/Function specs is in this file are in
sync with their corrsponding 11i counterparts
13 11/05/2007 sacsethi for bug 5647248 + 5658040
The following changes are done to make use of the same package for 26Q and 27Q
Modified the populate_details procedure:
1.added two parameters p_include_list,p_exclude_list in the call to populate_details,quarterly_returns
and generate_etds_returns.
2.Declared lv_list which is of varray type
3.added populate_include_exclude_list procedure in populate_details.
4.declared cursor c_prg_name which will fetch the concurrent program name in quarterly_returns procedure.
Based on this concurrent program name form_number is changed.
Report has been modified to generate Form 27A along with eTDS Quarterly Returns.
Added a call to the Concurrent JAINTDSA in the quarterly_returns procedure.
Added the update clause to modify the taxabale_amount of ja_in_ap_etds_temp
based on the taxable_amount of jai_ap_tds_thhold_trxs table.
In case of threshold transition and rollback transactions, the
taxable amount should be 0 for the differential invoice that has been created.
Hence, implemented this change.
14. 28-jun-2007 CSahoo for bug#6158875
modified the num of input parameters to yearly_returns procedure.
added the call to the function Fnd_date.canoncical_to_date.
15. 02-Jul-2007 CSahoo for bug#6158875, file version 120.10
modified the num of input parameters to querterly_returns procedure.
added the call to the function Fnd_date.canoncical_to_date.
16. 03/07/2007 CSAHOO FOR BUG#6158875, File Version 120.11
modified the following cursors c_tds_payment_check_id,c_base_payment_check_id,c_check_dtls.
17. 27/08/2007 CSahoo for bug#5975168, File Version 120.12
Added a check for status_lookup_code in cursor c_check_dtls
18. 27/08/2007 Csahoo for bug#6070014, File version 120.13
Changed the format mask from hardcoded value to
assigned value. This is done in procedure
create_quart_deductee_dtl
19. 13/02/2008 Lakshmi Gopalsami for bug# 6796765 File Version 120.5.12000000.4
1. Changed the function getSectionCode. Replaced the hardcoded value with p_string.
2. Changed in procedure create_challan_detail. Added upper for both p_challan_section and Sec.().
3. Changed in procedure create_deductee_detail. Added upper for both p_deductee_section and Sec.().
4. Changed in procedure create_quart_challan_dtl.
a) Added input parameter p_form_name to print the section code depending on the section.
b) Declared variables to select the section truncation depending on the form name.
c) Added logic to get the section code as 194C, 194D, 195 etc. from SEC. 194(C) and then check whether it can be converted to a
number. If so, the value will be printed in flat file else extract the value from 2nd character in flat file.
hardcoded the values of 194BB, 194EE, 194LA as per the details provided in NSDL as separate logic cannot be derived.
d) Changed the UTL_FILE.PUT_LINE parameter from getsectioncode to lv_output_string.
20. 15/02/2008 JMEENA for bug#6647362 File Version 120.5.12000000.4
Added 5000 UTL_FILE buffer size for bug#6647362
21. 20/02/2008 JMEENA for bug#4600778 File Version 120.5.12000000.5
Removed the cursor c_bank_branch_code and added column bsr_code in the cursor c_tds_payment_check_id and fetched the value in v_bank_branch_code.
22 08-Oct-2008 Bgowrava for Bug#6195027, File Version 120.5.12000000.5
Added sh_cess_rate in cursor c_tax_rates and defined related variables. Included ln_sh_cess_amt in ln_cess_amt.
23. 24-Oct-2008 Bgowrava for bug#7485031, File version 115.14.6017.12
Added code to use the include_flag and exclude_flag while determining the sections which need to be considered to be
inserted into the table JAI_AP_ETDS_T for the current execution. Also included code changes for bug 6281440
24. 06-Aug-2007 Forward Port Bug 6329774
Changed the challan date to base_invoice_date while printing deductee details.
25. 18-Oct-2008 Bgowrava for bug#6030953, File version 120.15.12010000.7, 120.21
Mandatory details to be printed in Form 27 A which is called from eTDS Quaterly. Following parameters are included in respective procedures
p_RespPers_flat_no
p_RespPers_prem_bldg
p_RespPers_rd_st_lane
p_RespPers_area_loc
p_RespPers_tn_cty_dt
p_RespPers_tel_no
p_RespPers_email
and parameter p_RespPersAddress is removed.
Affected procedures are :
create_quarterly_fh
validate_batch_header
quarterly_returns
generate_etds_returns
Also passed the parameters while submitting the request for Form 27A. Included p_deductor_status
---------------------------------------------------------------------------------------------------------*/
FUNCTION formatAmount( p_amount IN NUMBER) RETURN VARCHAR2 IS
BEGIN
-- return (replace(to_char(ROUND(nvl(p_amount,0), 2), '999999999999D99'),'.'));
SELECT attribute1
FROM hr_all_organization_units
WHERE organization_id = p_organization_id;
SELECT pay.check_id, apc.bank_account_id, apc.attribute3 challan_num, apc.attribute1 challan_date -- p
FROM ap_invoice_payments_all PAY, ap_checks_all APC
WHERE PAY.invoice_id = p_invoice_id
AND PAY.check_id = APC.check_id
AND APC.status_lookup_code NOT IN (lv_voided,lv_stop_init);--rchandan for bug#4428980
SELECT pay.check_id, apc.bank_account_id, check_date
FROM ap_invoice_payments_all PAY, ap_checks_all APC
WHERE PAY.invoice_id = p_invoice_id
AND PAY.check_id = APC.check_id
AND APC.status_lookup_code NOT IN (lv_voided, lv_stop_init);--rchandan for bug#4428980*/
SELECT apc.bank_account_id,
apc.attribute3 challan_num,
apc.attribute1 challan_date, */ -- pay.invoice_payment_id
/* Bug 4353842. Added by Lakshmi Gopalsami */
/* apc.check_number
FROM ap_checks_all APC
WHERE check_id = cpn_check_id;*/
SELECT apc.bank_account_id,
apc.attribute3 challan_num,
apc.attribute1 challan_date, -- pay.invoice_payment_id
apc.check_number
FROM ap_checks_all apc,
JAI_AP_TDS_INV_PAYMENTS jatp
WHERE
apc.check_id = jatp.check_id AND
jatp.check_id = cp_check_id;*/
/*Modified the cursor c_tds_payment_check_id by JMEENA for bug#4600778 to select the bsr_code from JAI_AP_TDS_PAYMENTS table */
CURSOR c_tds_payment_check_id(p_invoice_id IN NUMBER) IS
SELECT pay.check_id, apc.current_bank_account_name, JATP.Challan_no challan_num,
JATP.check_deposit_date challan_date, JATP.bsr_code branch_code
FROM ap_invoice_payments_all PAY, ap_checks_v APC, JAI_AP_TDS_PAYMENTS JATP
WHERE PAY.invoice_id = p_invoice_id
AND PAY.check_id = APC.check_id
AND APC.check_id = JATP.check_id
AND APC.status_lookup_code NOT IN (lv_voided,lv_stop_init);
SELECT pay.check_id, apc.current_bank_account_name, check_date
FROM ap_invoice_payments_all PAY, ap_checks_v APC
WHERE PAY.invoice_id = p_invoice_id
AND PAY.check_id = APC.check_id
AND APC.status_lookup_code NOT IN (lv_voided, lv_stop_init);
SELECT ACV.CURRENT_BANK_ACCOUNT_NAME ,
JATP.Challan_no challan_num,
JATP.check_deposit_date challan_date,
JATP.check_number
FROM AP_CHECKS_V ACV, JAI_AP_TDS_PAYMENTS JATP
WHERE ACV.check_id = JATP.check_id
AND jatp.check_id = cp_check_id
-- Bug 5975168. Added by csahoo
AND ACV.status_lookup_code NOT IN
('VOIDED', 'STOP INITIATED');
SELECT b.invoice_id prepay_invoice_id
FROM ap_invoice_distributions_all a, ap_invoice_distributions_all b
WHERE a.invoice_id = p_tds_invoice_id
AND a.prepay_distribution_id IS NOT NULL
AND (a.reversal_flag IS NULL OR a.reversal_flag = 'N') --rchandan for bug#4428980
AND b.invoice_distribution_id = a.prepay_distribution_id;
SELECT HZOP.BANK_OR_BRANCH_NUMBER
FROM HZ_ORGANIZATION_PROFILES HZOP
, CE_BANK_ACCOUNTS CEBA
WHERE HZOP.PARTY_ID = CEBA.BANK_BRANCH_ID
AND CEBA.BANK_ACCOUNT_ID = p_bank_account_id;*/
SELECT a.issue_date
FROM JAI_AP_TDS_F16_HDRS_ALL a, JAI_AP_TDS_INV_PAYMENTS b -- Bug#4517720 ja_in_ap_form16_dtl b
WHERE a.certificate_num = b.certificate_num
AND a.org_tan_num = b.org_tan_num
AND a.fin_yr = b.fin_year
AND b.invoice_id = p_tds_invoice_id;
SELECT nvl(jatp.check_date, jatp.check_date) check_date
/* decode (attribute1, NULL,check_date,to_date(attribute1)) */
FROM JAI_AP_TDS_INV_PAYMENTS jatp
WHERE
check_id = p_check_id;
SELECT
NVL(tax_rate,0),
(NVL(tax_rate,0) - (NVL(surcharge_rate,0) + NVL(cess_rate,0) + NVL(sh_cess_rate,0))) tds_rate, -- added NVL, Harshita for Bug 4639067 --Added sh_cess_rate by Bgowrava for bug #6195027
NVL(surcharge_rate,0) surcharge_rate,
NVL(cess_rate,0),
NVL(sh_cess_rate,0) --Added by Bgowrava for Bug#6195027
FROM
JAI_CMN_TAXES_ALL jtc
WHERE
tax_id = p_tax_id ;
INSERT INTO JAI_AP_ETDS_T (
batch_id,
-- line_number,
base_invoice_id,
base_taxabale_amount,
tds_invoice_id,
tds_invoice_num,
tds_invoice_date,
tds_section,
tds_tax_id,
tds_tax_rate,
tds_amount,
consider_for_challan,
consider_for_deductee,
tds_check_id, -- Vijay Shankar for Bug#4448293
base_invoice_date ,
-- added, Harshita for Bug 4866533
created_by,
creation_date,
last_updated_by,
last_update_date
) SELECT p_batch_id,
-- rownum + 2, -- 2 is added to all records to take care of file and batch header record line numbers
base_invoices.invoice_id, -- base_invoice_id
base_invoices.invoice_amount, -- base taxable amount
tds_invoices.invoice_id, -- tds_invoice_id
tds_invoices.invoice_num, -- tds_invoice_num
tds_invoices.invoice_date, -- tds_invoice_date
a.tds_section,
a.tds_tax_id,
a.tds_tax_rate,
a.tax_amount,
1,
1,
a.check_id,
base_invoices.invoice_date,
-- added, Harshita for Bug 4866533
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
from JAI_AP_TDS_INV_PAYMENTS a, ap_invoices_all base_invoices, ap_invoices_all tds_invoices
where a.parent_invoice_id = base_invoices.invoice_id
and a.invoice_id = tds_invoices.invoice_id
and a.invoice_date between p_tds_inv_from_date and p_tds_inv_to_date
and a.tax_authority_id = p_tds_vendor_id
and (p_tds_vendor_site_id is null or a.tax_authority_site_id = p_tds_vendor_site_id)
and a.payment_amount <> 0
and a.org_tan_num = p_org_tan_num
and
( lv_include_flag = 'N' or ( lv_include_flag = 'Y'
and upper(a.tds_section) in ( lv_include(1),lv_include(2),lv_include(3),lv_include(4),lv_include(5),lv_include(6),lv_include(7),lv_include(8),lv_include(9),lv_include(10) ) ))
and
( lv_exclude_flag = 'N' or ( lv_exclude_flag = 'Y'
and upper(a.tds_section) not in ( lv_exclude(1),lv_exclude(2),lv_exclude(3),lv_exclude(4),lv_exclude(5),lv_exclude(6),lv_exclude(7),lv_exclude(8),lv_exclude(9),lv_exclude(10)) ) )
; --Added above two conditions by Bgowrava for bug#7485031
FOR dtl IN (select a.rowid row_id, a.*,
b.vendor_id vendor_id, b.vendor_site_id vendor_site_id, b.invoice_type_lookup_code inv_type
from JAI_AP_ETDS_T a, ap_invoices_all b
where a.batch_id = p_batch_id and a.base_invoice_id = b.invoice_id)
LOOP
--added by csahoo for bug#5975168, start
ln_con_for_challan := 1;
GOTO update_now;
GOTO update_now;
<>
IF dtl.inv_type = 'PREPAYMENT' THEN
v_statement_id := '3g';
select sum(amount) - sum( nvl(prepay_amount_remaining, amount)) INTO v_prepayment_amount_applied
from ap_invoice_distributions_all
where invoice_id = dtl.base_invoice_id
and attribute1 = dtl.tds_tax_id;
/* following lines of UPDATE commented by Vijay Shankar for Bug# 3567864 and modified as below UPDATE st
UPDATE JAI_AP_ETDS_T
SET tds_amount = round(tds_amount - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2),
*/
-- added, Harshita for Bug 4525089
ln_surcharge_amt := round((dtl.tds_amount * ln_surcharge_rate /ln_tax_rate),2) ;
UPDATE JAI_AP_ETDS_T
SET tds_amount = round(tds_amount - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2),
base_taxabale_amount = base_taxabale_amount - v_prepayment_amount_applied,
base_vendor_id = dtl.vendor_id,
base_vendor_site_id = dtl.vendor_site_id,
base_invoice_type_lookup_code = dtl.inv_type,
tds_check_id = v_tds_check_id,
challan_num = v_challan_num,
challan_date = v_challan_date,
check_number = ln_check_number,
bank_branch_code = v_bank_branch_code,
base_invoice_check_id = v_base_invoice_check_id,
prepayment_amount_applied = v_prepayment_amount_applied,
challan_error = v_challan_err,
deductee_error = v_deductee_err,
amt_of_tds = ln_tds_amt,
amt_of_surcharge = ln_surcharge_amt,
amt_of_cess = ln_cess_amt,
certificate_issue_date = lv_cert_issue_date,
tds_check_date = lv_tds_check_date,
-- Bug 5975168. Added by csahoo
consider_for_challan = ln_con_for_challan,
consider_for_deductee = decode(ln_con_for_challan,0,0,ln_con_for_deductee)
WHERE rowid = dtl.row_id;
update jai_ap_etds_t a
set base_taxabale_amount =
( select nvl(taxable_amount,0)
from JAI_AP_TDS_THHOLD_TRXS b
where b.invoice_to_tds_authority_id = a.tds_invoice_id )
where a.batch_id = p_batch_id
and exists
( select 1
from JAI_AP_TDS_THHOLD_TRXS c
where c.invoice_to_tds_authority_id = a.tds_invoice_id
and c.tds_event like '%THRESHOLD%'
) ;
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;
* Declared variable to select the section truncation depending on
* the form name
*/
lv_sec_string VARCHAR2(50);
select concurrent_program_name
from fnd_concurrent_programs_vl
where concurrent_program_id= cp_conc_prg;
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 start_date, end_date
FROM JAI_AP_TDS_YEARS
-- added, Harshita for Bug 4889272
where TAN_NO = p_tan_number
AND fin_year = p_fin_year
and legal_entity_id = p_organization_id;
SELECT organization_id
FROM jai_ap_tds_org_tan_v --- JAI_AP_TDS_ORG_TANS is changed to view jai_ap_tds_org_tan_v 4323338
WHERE org_tan_num = p_tan_number;
/* SELECT jpr.attribute_value ORG_TAN_NUM
FROM jai_rgm_parties jrp,
JAI_RGM_REGISTRATIONS jrr,
jai_rgm_party_regns jpr
WHERE jrp.regime_org_id = jpr.regime_org_id
AND jpr.registration_id = jrr.registration_id
AND jrp.organization_id = p_organization_id
AND jrr.registration_type = lv_oth_reg_type
AND jrr.attribute_type_code = lv_prim_att_type
AND jrr.attribute_code = lv_pan_att_code;*/
SELECT NVL(jpr.attribute_value, jrr.attribute_value) ORG_TAN_NUM
FROM
jai_rgm_parties jrp,
JAI_RGM_REGISTRATIONS jrr,
jai_rgm_party_regns jpr
WHERE
jrp.regime_org_id = jpr.regime_org_id(+)
AND ( jpr.registration_id is null or jpr.registration_id = jrr.registration_id )
AND jrp.organization_id = p_organization_id
AND jrr.registration_type = lv_oth_reg_type
AND jrr.attribute_type_code = lv_prim_att_type
AND jrr.attribute_code = lv_pan_att_code;
SELECT organization_id, to_number(legal_entity_id)
FROM hr_operating_units
WHERE organization_id = p_organization_id;
SELECT 1
FROM hr_legal_entities
WHERE organization_id = p_organization_id;
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 vendor_name,
decode( UPPER(organization_type_lookup_code), 'COMPANY', '01', '02')
FROM po_vendors
WHERE vendor_id = p_vendor_id;
SELECT address_line1 addr1, address_line2 addr2, address_line3 addr3, address_line4 addr4, city,
UPPER(state) state, REPLACE(zip,' ') zip --Removed to_number for Bug 6281440
FROM po_vendor_sites_all
WHERE vendor_id = p_vendor_id
AND vendor_site_id = p_vendor_site_id;
SELECT meaning
FROM ja_lookups --fnd_common_lookups /* Ramananda for bug#4555466 */
WHERE lookup_type = p_state_type
AND lookup_code = p_state_name;
SELECT pan_no
FROM JAI_AP_TDS_VENDOR_HDRS
WHERE vendor_id = p_vendor_id
AND vendor_site_id = p_vendor_site_id;
SELECT check_date
FROM ap_checks_all
WHERE check_id = p_check_id;
SELECT a.issue_date
FROM JAI_AP_TDS_F16_HDRS_ALL a, JAI_AP_TDS_F16_DTLS_ALL b
WHERE a.CERTIFICATE_NUM = b.CERTIFICATE_NUM
AND a.org_tan_num = b.org_tan_num
AND a.fin_yr = b.fin_yr
AND b.tds_invoice_id = p_tds_invoice_id;
select *
from JAI_AP_ETDS_T a
where a.batch_id = p_batch_id
and a.consider_for_challan=1
FOR UPDATE OF challan_line_num;
SELECT tds_section, bank_branch_code, challan_num, challan_date, sum(tds_amount) tds_amount
FROM JAI_AP_ETDS_T a
WHERE a.batch_id = p_batch_id
AND a.consider_for_challan=1
GROUP BY tds_section, bank_branch_code, challan_num, challan_date;*/
select NVL(tds_section,'No Section') tds_section,
NVL(bank_branch_code,'No Bank Branch') bank_branch_code,
NVL(challan_num,'No Challan Number') challan_num,
NVL(challan_date,lv_dummy_date) challan_date,
check_number check_number,
sum(tds_amount) tds_amount,
sum(amt_of_tds) amt_of_tds,
sum(amt_of_surcharge) amt_of_surcharge,
sum(amt_of_cess) amt_of_cess
from JAI_AP_ETDS_T a
where a.batch_id = p_batch_id
and a.consider_for_challan=1
group by NVL(tds_section,'No Section'), NVL(bank_branch_code,'No Bank Branch'),
NVL(challan_num,'No Challan Number'), NVL(challan_date,lv_dummy_date),
check_number;
SELECT *
FROM JAI_AP_ETDS_T a
WHERE a.batch_id = p_batch_id
AND a.consider_for_challan=1
FOR UPDATE OF deductee_line_num;*/
select
base_vendor_id,challan_line_num, base_vendor_site_id,tds_tax_id,
NVL(tds_section,'No Section') tds_section,
NVL(bank_branch_code,'No Bank Branch') bank_branch_code,
NVL(challan_num,'No Challan Number') challan_num,
NVL(challan_date,lv_dummy_date) challan_date,
check_number,
tds_tax_rate,
max(certificate_issue_date) certificate_issue_date,
max( base_invoice_date) base_invoice_date ,
max(tds_check_date) tds_check_date,
max(tds_invoice_date) tds_invoice_date,
sum(amt_of_tds) amt_of_tds,
sum(amt_of_surcharge) amt_of_surcharge,
sum(amt_of_cess) amt_of_cess,
sum(base_taxabale_amount) base_taxabale_amount,
sum(tds_amount) tds_amount
from JAI_AP_ETDS_T a
where a.batch_id = p_batch_id
and a.consider_for_challan=1
and challan_line_num = NVL(p_challan_line_num, challan_line_num)
group by challan_line_num, base_vendor_id, base_vendor_site_id,tds_tax_id,
NVL(tds_section,'No Section'), NVL(bank_branch_code,'No Bank Branch'),
NVL(challan_num,'No Challan Number'), NVL(challan_date,lv_dummy_date),
check_number,tds_tax_rate;
SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = p_line_type
AND global_attribute1 = to_char(p_tax_id);--rchandan for bug#4333488
SELECT stform_type
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = p_tax_id;
select sum ( count( base_vendor_id ) ) -- distinct removed the distinct
from JAI_AP_ETDS_T
WHERE batch_id = p_batch_id
and nvl(tds_section, 'No Section') = nvl(cd.tds_section, 'No Section')
and nvl(challan_num,'No Challan Number') = nvl(cd.challan_num, '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 consider_for_challan=1
and check_number = p_check_number
group by base_vendor_id, base_vendor_site_id,tds_tax_id ;
select sum ( count( distinct base_vendor_id ) )
from JAI_AP_ETDS_T
WHERE batch_id = p_batch_id
and nvl(tds_section, 'No Section') = nvl(p_tds_section, 'No Section')
and nvl(challan_num,'No Challan Number') = nvl(p_challan_num, 'No Challan Number')
and nvl(challan_date, lv_dummy_date) = nvl(p_challan_date, lv_dummy_date )
and nvl(bank_branch_code, 'No Bank Branch') = nvl(p_bank_branch_code, 'No Bank Branch')
and consider_for_challan=1
and check_number = p_check_number
group by base_vendor_id, base_vendor_site_id,tds_tax_id ;
Select Error_Message from jai_ap_etds_errors_t
where batch_id = cp_batch_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_AP_ETDS_T
SET deductee_line_num = v_line_number
WHERE batch_id = v_batch_id
and consider_for_challan=1
and challan_line_num = dd.challan_line_num
and base_vendor_id = dd.base_vendor_id
and base_vendor_site_id = dd.base_vendor_site_id
and tds_tax_id = dd.tds_tax_id
and NVL(tds_section,'No Section') = NVL(dd.tds_section,'No Section')
and NVL(bank_branch_code,'No Bank Branch') = NVL(dd.bank_branch_code,'No Bank Branch')
and NVL(challan_num,'No Challan Number') = NVL(dd.challan_num,'No Challan Number')
and NVL(challan_date,lv_dummy_date) = NVL(dd.challan_date,lv_dummy_date)
and check_number = dd.check_number
and tds_tax_rate = dd.tds_tax_rate ;
replaced the AND with OR in the update statement.
Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )
----------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
jai_ap_rpt_apcr_pkg.compute_credit_balance.sql
----------------------------------------------------------------------------------------------------------------------------------------------------
115.1 3708878 IN60105D2+3603545 jai_ap_tds_etds_pkg.sql Apdas 25/06/2004
---------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------*/
DUMMY := null ;
SELECT JAI_AP_ETDS_T_S.nextval INTO v_batch_id FROM DUAL;
INSERT INTO JAI_AP_ETDS_REQUESTS(
batch_id, request_id,/* legal_entity_id, operating_unit_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,
person_resp_for_deduction, designation_of_pers_resp, challan_start_date,
challan_end_date, file_path, filename,
program_application_id, program_id, program_login_id,
-- added, Harshita for Bug 4866533
created_by, creation_date, last_updated_by, last_update_date
) VALUES (
v_batch_id, v_conc_request_id,/*p_legal_entity_id, p_profile_org_id,*/ p_tan_number, p_fin_year, -- Harshita for Bug 4889272
p_tax_authority_id, p_tax_authority_site_id, p_organization_id,
p_deductor_name, p_deductor_state, p_addrChangedSinceLastRet, p_deductor_status,
p_persRespForDeduction, p_desgOfPersResponsible, p_challan_Start_Date,
p_challan_End_Date, p_file_path, p_filename,
fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'),
-- added, Harshita for Bug 4866533
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
);
insert into jai_ap_etds_errors_t
(batch_id, record_type, error_message) values
( ln_batch_id, 'FH', p_return_message ) ;
v_form_number := 26; -- as per Vikrams update
/*SELECT count(1), sum(tds_amount)
FROM JAI_AP_ETDS_T
WHERE batch_id = v_batch_id AND consider_for_challan=1;*/
select count(1), sum(tds_amt)
INTO v_challan_cnt, v_totTaxDeductedAsPerChallan
from
(
select tds_section, bank_branch_code, challan_num, challan_date, sum(tds_amount) tds_amt
from JAI_AP_ETDS_T
WHERE batch_id = v_batch_id AND consider_for_challan=1
group by tds_section, bank_branch_code, challan_num, challan_date
);
SELECT count(1), sum(tds_amount) INTO v_deductee_cnt, v_totTaxDeductedAsPerDeductee
FROM JAI_AP_ETDS_T
WHERE batch_id = v_batch_id AND consider_for_deductee=1;
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_AP_ETDS_T
SET challan_line_num = v_line_number
WHERE batch_id = v_batch_id
and ( ( tds_section IS NULL AND cd.tds_section IS NULL ) OR ( tds_section = cd.tds_section ) )--nvl(tds_section, 'No Section') = nvl(cd.tds_section, 'No Section')
and ( (challan_num IS NULL AND cd.challan_num IS NULL ) OR ( challan_num = cd.challan_num ) ) --nvl(challan_num, 'No Challan Number') = nvl(cd.challan_num, 'No Challan Number')
and nvl(challan_date, trunc(sysdate) ) = nvl(cd.challan_date, trunc(sysdate) )
-- csahoo for bug 6158875, replaced the AND by OR below
and (( bank_branch_code IS NULL AND cd.bank_branch_code IS NULL ) OR ( bank_branch_code = cd.bank_branch_code )) --nvl(bank_branch_code, 'No Bank Branch') = nvl(cd.bank_branch_code, 'No Bank Branch')
and consider_for_challan=1;