[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 JAI_AP_ETDS_T
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
26. 28-May-2009 Bug 8505050 - Lines with amount as 0 for TDS is also displayed in the e-TDS flat file which causes issue during upload
Added the having clause to prevent entries in e-TDS file with 0 Tax amount
27. 15-Sep-2009 Changes for eTDS/eTCS File Validation Utility Changes - Bug 8880543
28. 09-OCT-2009 Added by Bgowrava for Bug#9005248
Replace the literal 'PANNOTREQD' to 'PANNOTAVBL' according to the latest notification.
29. 06-Jan-2010 Xiao Lv for Bug#7662155
Added New cursor c_get_tds_inv_det in the populate_details procedure to calculate the surcharge amount at threshold
transition when surcharge gets applicable.
30. 08-apr-2010 Bgowrava for Bug#9494515
Modified the query of the cursor c_pan_number and included the view jai_rgm_org_regns_v instead of the three table names.
This would fetch the correct PAN number for the organization.
31. 25-Oct-2010 Bug 10020606
Description: All TDS reports must be based on GL Date or date on which TCS Liability is booked
and not Invoice Date
Fix: Fetched data based on GL Date of Base Invoice
32. 23-Nov-2010 amandali for bug 10315928
Description:R12 : ETDS FVU CHANGES AS NOTIFIED ON 09-OCT-2010 WRT NEW VERSION OF VALIDATION
Fix:Added responsible person and deductor contact details in regime and displayed them in batch header.
Also made few changes for the transporters.
33. 05-Jan-2011 amandali for bug 10188600
Description:INDIA LOCALIZATION : TDS RETURN SHOWS DATE ON AMOUNT PAID/CREDITED INCORRECT
Fix:Amount paid/credited is showing the invoice date of the supplier.Changed it to the gl date.
Modified the payment date parameter to dd.tds_invoice_date from dd.base_invoice_date in a call to create_deductee_detail.
34. 17-Feb-2011 amandali for bug 11781966
Description: ETDS OUTPUT FAILS NSDL VALIDATION FOR UNMATCHED CHALLAN COUNT
Fix:Commented v_bh_unmatch_challan_cnt := 'NA' as no value needs to be passed for v_bh_unmatch_challan_count.
35. 17-Jun-2011 amandali for bug 12624907
Description:INDIA - PROGRAM TO GENERATE ETDS QUARTERLY CERTIFICATES 26Q ENDING WITH ERROR
Fix: The state attribute code contains the name of the state and not the state code.
Modified the datatype of v_state_name and also added the cursor c_get_state_code to print the state code in flat file.
36. 5-Aug-2011 amandali for bug 12691784
Description: ETDS Form 27A erroring out when 26Q run for multiple OUs
Fix: 1.Added a parameter p_deductor_addr_org_id in call to JAINTDSA
2.In Populate_details procedure, added a condition wrt organization id.
3. Challan details are populated irrespective of check number.Duplication of data exists. Added a check for check_number while updating records.
37. 5-Sep-2011 amandali for Bug: 12927114
Description:ETDS FLAT FILE VALIDATION ERRORS "Invalid Deductor / Collector Address2"
Fix: Concatenated the Deductor address2 and Address3 to 25 characters.
38. 07-Sep-2011 mmurtuza for bug 12943133
Description: ERROR "T-FV-3043 INVALID SECTION / COLLECTION CODE" WHILE VALIDATING FLAT FILE
Fix: Bypassed the truncation logic in create_quart_challan_dtl procedure if the section code is already as per flat file requirement
Modified getsectioncode function to accomodate sections with E and C
39. 14-Oct-2011 amandali for bug 13070779
Description:Payment amount in ETDS flat file is showing the wrong amount incase of prepayment application on validated standard invoice.
Fix:For RTN invoices, the base_taxabale_amount should be -ve but is updated to positive and hence the amount is summed up instead of subtraction.
Commented the multiplication of tds_amount/invoice_to_tds_authority_Amt in updation for base_taxabale_amount.
40. 29-NOV-11 mmurtuza for Bug 13439411
Description: WHILE VALIDATING E-TDS FILE FVU SOFTWARE IS GIVING ERROR
Fix: Added trim clause in getsectioncode function to remove the space
41. 08-Jan-2011 amandali for bug 13323753
Description:FVU validation errors.
Fixed:+Responsible address when given more than 25 characters, concurrent 26Q is errored out
+Same challan with different checks is shown in different challan records .It is now fixed to show in one challan record.
+Mismatch in the number of deductee records and the count of deductee records in challan record.
+Fixed error "T-FV-2097 Count of Challan/Transfer voucher Records in Batch Header not
matches with actual no. of Challan/Transfer Voucher No. "
42. 06-Mar-2012 amandali for bug 13702172
Description:FVU 3.3 uptake
Fix:Added parameter p_fh_consFileHash to the procedure create_quarterly_file_header and populating it with null in the flat file.
43. 05-Apr-2012 ER 13514846
Description: Invoices attracting Zero Rate TDS not displayed in eTDS Flat file
Fix: Modified populate_details procedure to fetch Invoices which have Zero Rate Tax ID attached
44. 24-SEP-2012 amandali for bug 14330534
Description: ETDS flat file erroring "T-FV-4012 Valid employee / party name must be provided"
Fix: Added validation to the length of supplier name in procedure validate_deductee_detail.
45. 10-Dec-2012 amandali for ER 15840480
Description:FVU3.6 Uptake
---------------------------------------------------------------------------------------------------------*/
G_PKG_NAME CONSTANT VARCHAR2(30) := 'JAI_AP_TDS_ETDS_PKG';
(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 in ('PANNOTAVBL', 'PANAPPLIED') then/*ETDS FVU3.1 Bug 11896260*/
lv_resp := 'VALID';
select '1'
from jai_rgm_definitions jrd,
jai_rgm_parties jrp
where jrd.regime_code = 'TDS'
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 = 'TDS'
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 = 'TDS'
and jrd.regime_id = jrr.regime_id
and jrr.attribute_code = p_attr_code;
SELECT SUM(application_amount)
FROM jai_ap_tds_prepayments jatp
WHERE invoice_id = p_invoice_id
AND (SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = jatp.invoice_distribution_id)
<=
(SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id IN (SELECT prepay_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = jatp.invoice_distribution_id_prepay
)
);
SELECT SUM(application_amount)
FROM jai_ap_tds_prepayments jatp
WHERE invoice_id IN (SELECT invoice_id
FROM ap_invoice_distributions_all
WHERE prepay_distribution_id IN (SELECT invoice_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
)
)
AND (SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = jatp.invoice_distribution_id)
>
(SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id IN (SELECT prepay_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = jatp.invoice_distribution_id_prepay
)
);
SELECT sum(prepay_amount_remaining)
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id;
SELECT tds_invoice_id, tds_check_id, tds_tax_id, tds_amount
FROM jai_ap_etds_t
WHERE tds_invoice_id = (SELECT MAX(invoice_to_tds_authority_id)
FROM jai_ap_tds_thhold_trxs
WHERE threshold_grp_id = p_threshold_grp_id
AND tds_event LIKE 'THRESHOLD TRANSITION%'
);
SELECT gl_date
FROM ap_invoices_all
WHERE invoice_id = p_tds_invoice_id;
SELECT invoice_to_tds_authority_id
FROM jai_ap_tds_thhold_trxs
WHERE tds_event LIKE 'THRESHOLD ROLLBACK%'
AND threshold_grp_id = p_threshold_grp_id
AND invoice_to_tds_authority_id > (SELECT MAX(invoice_to_tds_authority_id)
FROM jai_ap_tds_thhold_trxs
WHERE threshold_grp_id = p_threshold_grp_id
AND tds_event LIKE 'THRESHOLD TRANSITION%'
);
SELECT attribute1
FROM hr_all_organization_units
WHERE organization_id = p_organization_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.bank_account_name, JATP.Challan_no challan_num,
JATP.check_deposit_date challan_date, JATP.bsr_code branch_code, apc.check_number check_number \*Bug 7688789 - Added Check Number*\
FROM ap_invoice_payments_all PAY, ap_checks_all APC \* renamed the table from ap_checks_v to ap_checks_All for bug 12691784*\, 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.bank_account_name, JATP.Challan_no challan_num,
JATP.check_deposit_date challan_date, JATP.bsr_code branch_code, apc.check_number check_number \*Bug 7688789 - Added Check Number*\
FROM ap_invoice_payments_all PAY, ap_checks_all APC\*renamed the table from ap_checks_v to ap_checks_All for Bug 12691784*\, JAI_AP_TDS_PAYMENTS JATP
WHERE PAY.check_id = p_check_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 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 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
FROM JAI_AP_TDS_INV_PAYMENTS jatp
WHERE
check_id = p_check_id;
SELECT threshold_trx_id,
tax_amount,
taxable_amount,
tds_event
FROM jai_ap_tds_thhold_trxs jatt
WHERE jatt.invoice_to_tds_authority_id = cp_invoice_id
AND tds_event like 'SURCHARGE_CALCULATE';
SELECT
NVL(tax_rate,0) tax_rate,
(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) cess_rate,
NVL(sh_cess_rate,0) sh_cess_rate --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,
base_invoice_id,
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,
base_invoice_date ,
created_by,
creation_date,
last_updated_by,
last_update_date
) SELECT p_batch_id,
base_invoices.invoice_id, -- base_invoice_id
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,
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
\*Bug 10020606 - All TDS reports must be based on GL Date or date on which TCS Liability is booked*\
and base_invoices.gl_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 base_invoices.org_id = p_organization_id \* Added the condition for Bug 12691784 *\
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)) ) );
INSERT INTO JAI_AP_ETDS_T (
batch_id,
base_invoice_id,
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,
base_invoice_date,
base_vendor_id,
base_vendor_site_id,
base_invoice_type_lookup_code,
challan_num,
challan_date,
check_number,
bank_branch_code,
challan_error,
deductee_error,
amt_of_tds,
amt_of_surcharge,
amt_of_cess,
certificate_issue_date,
tds_check_date,
base_taxabale_amount,
tds_vendor_classification,
created_by,
creation_date,
last_updated_by,
last_update_date
)
select
p_batch_id,
jatit.invoice_id,
-9997,
NULL,
max(aia.invoice_date),
nvl(jatit.actual_section_code, jatit.default_section_code),
nvl(jatit.actual_tax_id, jatit.default_tax_id),
max(jitc.tax_rate),
sum(jatit.tax_amount),
1,
1,
-9999,
max(aia.invoice_date),
max(aia.vendor_id),
max(aia.vendor_site_id),
max(aia.invoice_type_lookup_code),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
0,
0,
NULL,
NULL,
max(aia.invoice_amount),
max(jte.tds_vendor_classification),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
FROM ap_invoices_all aia,
ap_invoice_distributions_all aida,
jai_ap_tds_inv_taxes jatit,
JAI_AP_TDS_VENDOR_HDRS jte,
JAI_CMN_TAXES_ALL jitc
WHERE aia.invoice_id = jatit.invoice_id
AND jatit.threshold_grp_id IS NOT NULL
AND aia.cancelled_date IS NULL
AND nvl(jatit.actual_tax_id, jatit.default_tax_id) = jitc.tax_id
AND aia.vendor_id = jte.vendor_id
AND aia.vendor_site_id = jte.vendor_site_id
AND aia.invoice_id = aida.invoice_id
and aida.accounting_Date between p_tds_inv_from_date and p_tds_inv_to_date
AND aida.match_status_flag in ('A','T')
AND aia.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
AND aia.org_id = p_organization_id
and not exists (select 1 from jai_ap_tds_thhold_trxs where invoice_id = jatit.invoice_id)
and jitc.tax_rate = 0
and jitc.vendor_id = p_tds_vendor_id
and (p_tds_vendor_site_id is null or jitc.vendor_site_id = p_tds_vendor_site_id)
and upper(jitc.section_code) in (select lookup_code
from fnd_lookup_values_vl
where lookup_type in ('JAI_TDS_SECTION_ZERO_RATE_197', 'JAI_TDS_SECTION_ZERO_RATE_197A'))
group by jatit.invoice_id, nvl(jatit.actual_section_code, jatit.default_section_code), nvl(jatit.actual_tax_id, jatit.default_tax_id);
INSERT INTO JAI_AP_ETDS_T (
batch_id,
base_invoice_id,
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,
base_invoice_date,
base_vendor_id,
base_vendor_site_id,
base_invoice_type_lookup_code,
challan_num,
challan_date,
check_number,
bank_branch_code,
challan_error,
deductee_error,
amt_of_tds,
amt_of_surcharge,
amt_of_cess,
certificate_issue_date,
tds_check_date,
base_taxabale_amount,
tds_vendor_classification,
created_by,
creation_date,
last_updated_by,
last_update_date
) select distinct
p_batch_id,
base_invoices.invoice_id,
-9999,
NULL,
base_invoices.invoice_date,
--'SEC. 194(C)',
decode(jte.tds_vendor_classification,'Transporter','SEC. 194(C)','Software', decode(povs.country, 'IN','SEC. 194(J)','SEC. 195')),
\*Added above decode by Avanija for FVU3.6*\
a.tax_id,
0,
0,
1,
1,
-9999,
base_invoices.invoice_date,
base_invoices.vendor_id,
base_invoices.vendor_site_id,
base_invoices.invoice_type_lookup_code,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
0,
0,
NULL,
NULL,
base_invoices.invoice_amount,
jte.tds_vendor_classification,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
from jai_cmn_taxes_all a, ap_invoices_all base_invoices, po_vendor_sites_all povs, \* Added by Avanija for FVU3.6 *\
ap_invoice_distributions_all aida, jai_ap_tds_vendor_hdrs jte
where jte.vendor_id = base_invoices.vendor_id
\* FVU3.6 -start *\
and povs.vendor_id = jte.vendor_id
and povs.vendor_site_id = jte.vendor_site_id
and base_invoices.cancelled_Date is null
and a.tax_rate = 0
\* FVU3.6 -end *\
and a.tax_id = aida.global_attribute1
and jte.vendor_site_id = base_invoices.vendor_site_id--Added by Zhhou for bug#15962641 20121205
--Changed by Zhiwei for Bug#15840480 FVU3.6 begin
-----------------------------------------------------
--and jte.tds_vendor_classification = 'Transporter'
and jte.tds_vendor_classification in ('Transporter','Software')
-----------------------------------------------------
--Changed by Zhiwei for Bug#15840480 FVU3.6 end
and aida.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS'
and aida.invoice_id = base_invoices.invoice_id
and aida.match_status_flag in ('A','T') \*10408793 - Unvalidated Invoices must not be picked*\
and base_invoices.org_id = p_organization_id
and base_invoices.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
and not exists (select '1' from jai_ap_tds_thhold_trxs jattt where jattt.invoice_id = base_invoices.invoice_id) \*Bug 10408793 - TDS should not be deducted*\
and not exists (select '1' from jai_ap_etds_t where base_invoice_id=base_invoices.invoice_id and batch_id=p_batch_id) \* Added by Avanija for FVU3.6 *\
--and base_invoices.gl_date between p_tds_inv_from_date and p_tds_inv_to_date
and aida.accounting_date between p_tds_inv_from_date and p_tds_inv_to_date \* Changed by Avanija for FVU3.6 *\
and
( lv_include_flag = 'N' or ( lv_include_flag = 'Y'
and upper(a.section_code) 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.section_code) 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)) ) )
union all
select distinct
p_batch_id,
base_invoices.invoice_id,
-9999,
NULL,
base_invoices.invoice_date,
--'SEC. 194(C)',
decode(jte.tds_vendor_classification,'Transporter','SEC. 194(C)','Software', decode(povs.country, 'IN','SEC. 194(J)','SEC. 195')),
\* Added above decode by Avanija for FVU3.6*\
-99,
0,
0,
1,
1,
-9999,
base_invoices.invoice_date,
base_invoices.vendor_id,
base_invoices.vendor_site_id,
base_invoices.invoice_type_lookup_code,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
0,
0,
NULL,
NULL,
base_invoices.invoice_amount,
jte.tds_vendor_classification,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
from ap_invoices_all base_invoices,
ap_invoice_distributions_all aida, jai_ap_tds_vendor_hdrs jte, po_vendor_sites_all povs
where jte.vendor_id = base_invoices.vendor_id
and jte.vendor_site_id = base_invoices.vendor_site_id--Added by Zhhou for bug#15962641 20121205
--Changed by Zhiwei for Bug#15840480 FVU3.6 begin
-----------------------------------------------------
--and jte.tds_vendor_classification = 'Transporter'
and jte.tds_vendor_classification in ('Transporter','Software')
-----------------------------------------------------
--Changed by Zhiwei for Bug#15840480 FVU3.6 end
and aida.global_attribute1 is NULL
\* FVU3.6 -start *\
and povs.vendor_id = jte.vendor_id
and povs.vendor_site_id = jte.vendor_site_id
and base_invoices.cancelled_Date is null
\* FVU3.6 -end *\
and (aida.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS' or aida.global_attribute_category is NULL)
and aida.match_status_flag in ('A','T') \*10408793 - Unvalidated Invoices must not be picked*\
and aida.invoice_id = base_invoices.invoice_id
and base_invoices.org_id = p_organization_id
and base_invoices.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
and not exists (select '1' from jai_ap_tds_thhold_trxs jattt where jattt.invoice_id = base_invoices.invoice_id) \*Bug 10408793 - TDS should not be deducted*\
and not exists (select '1' from jai_ap_etds_t where base_invoice_id=base_invoices.invoice_id and batch_id=p_batch_id) \* Added by Avanija for FVU3.6 *\
--and base_invoices.gl_date between p_tds_inv_from_date and p_tds_inv_to_date
and aida.accounting_date between p_tds_inv_from_date and p_tds_inv_to_date \* Changed by Avanija for FVU3.6 *\
and lv_exclude_flag = 'Y'
;
FOR c_get_threshold_grp IN (SELECT threshold_grp_id
FROM jai_ap_tds_inv_taxes
WHERE invoice_id IN (SELECT base_invoice_id
FROM jai_ap_etds_t
WHERE batch_id = p_batch_id
AND EXISTS (SELECT 1
FROM jai_ap_tds_thhold_trxs jatit_1
WHERE jatit_1.invoice_to_tds_authority_id = tds_invoice_id
AND jatit_1.tds_event LIKE 'THRESHOLD TRANSITION%'
)
)
)
LOOP
\*Check if Threshold Rollback has occured post Threshold Transition*\
OPEN c_chk_threshold_rollback(c_get_threshold_grp.threshold_grp_id);
INSERT INTO JAI_AP_ETDS_T (
batch_id,
base_invoice_id,
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,
base_invoice_date,
base_vendor_id,
base_vendor_site_id,
base_invoice_type_lookup_code,
challan_num,
challan_date,
check_number,
bank_branch_code,
challan_error,
deductee_error,
amt_of_tds,
amt_of_surcharge,
amt_of_cess,
certificate_issue_date,
tds_check_date,
base_taxabale_amount,
tds_vendor_classification,
created_by,
creation_date,
last_updated_by,
last_update_date
)
select
p_batch_id,
jatit.invoice_id,
-9999,
NULL,
aia.invoice_date,
nvl(jatit.actual_section_code, jatit.default_section_code),
nvl(jatit.actual_tax_id, jatit.default_tax_id),
jitc.tax_rate,
jatit.tax_amount,
1,
1,
-9999,
aia.invoice_date,
aia.vendor_id,
aia.vendor_site_id,
aia.invoice_type_lookup_code,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
0,
0,
NULL,
NULL,
aia.invoice_amount,
jte.tds_vendor_classification,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
FROM ap_invoices_all aia,
ap_invoice_distributions_all aida,
jai_ap_tds_inv_taxes jatit,
JAI_AP_TDS_VENDOR_HDRS jte,
JAI_CMN_TAXES_ALL jitc
WHERE aia.invoice_id = jatit.invoice_id
AND jatit.threshold_grp_id IS NOT NULL
AND jatit.threshold_grp_id = c_get_threshold_grp.threshold_grp_id
AND aia.cancelled_date IS NULL
AND NOT EXISTS (SELECT 1
FROM jai_ap_tds_prepayments
WHERE invoice_id = jatit.invoice_id)
AND nvl(jatit.actual_tax_id, jatit.default_tax_id) = jitc.tax_id
AND aia.vendor_id = jte.vendor_id
AND aia.vendor_site_id = jte.vendor_site_id
AND aia.invoice_id = aida.invoice_id
AND aida.match_status_flag in ('A','T')
AND aida.prepay_amount_remaining IS NULL
AND aia.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
AND aia.org_id = p_organization_id
AND NOT EXISTS (SELECT 1
FROM JAI_AP_ETDS_T
WHERE base_invoice_id = jatit.invoice_id
AND batch_id = p_batch_id
)
AND ( lv_include_flag = 'N' or ( lv_include_flag = 'Y' and upper(nvl(jatit.actual_section_code, jatit.default_section_code))
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(nvl(jatit.actual_section_code, jatit.default_section_code))
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), '94F')));
INSERT INTO JAI_AP_ETDS_T (
batch_id,
base_invoice_id,
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,
base_invoice_date,
base_vendor_id,
base_vendor_site_id,
base_invoice_type_lookup_code,
challan_num,
challan_date,
check_number,
bank_branch_code,
challan_error,
deductee_error,
amt_of_tds,
amt_of_surcharge,
amt_of_cess,
certificate_issue_date,
tds_check_date,
base_taxabale_amount,
tds_vendor_classification,
created_by,
creation_date,
last_updated_by,
last_update_date
)
select
p_batch_id,
jatit.invoice_id,
-9998,
NULL,
aia.invoice_date,
nvl(jatit.actual_section_code, jatit.default_section_code),
nvl(jatit.actual_tax_id, jatit.default_tax_id),
jitc.tax_rate,
jatit.tax_amount,
1,
1,
-9999,
aia.invoice_date,
aia.vendor_id,
aia.vendor_site_id,
aia.invoice_type_lookup_code,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
0,
0,
NULL,
NULL,
aia.invoice_amount,
jte.tds_vendor_classification,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
FROM ap_invoices_all aia,
ap_invoice_distributions_all aida,
jai_ap_tds_inv_taxes jatit,
JAI_AP_TDS_VENDOR_HDRS jte,
JAI_CMN_TAXES_ALL jitc
WHERE aia.invoice_id = jatit.invoice_id
AND jatit.threshold_grp_id IS NOT NULL
AND jatit.threshold_grp_id = c_get_threshold_grp.threshold_grp_id
AND aia.cancelled_date IS NULL
AND (EXISTS (SELECT 1
FROM jai_ap_tds_prepayments
WHERE invoice_id = jatit.invoice_id)
OR aida.prepay_amount_remaining IS NOT NULL)
AND nvl(jatit.actual_tax_id, jatit.default_tax_id) = jitc.tax_id
AND aia.vendor_id = jte.vendor_id
AND aia.vendor_site_id = jte.vendor_site_id
AND aia.invoice_id = aida.invoice_id
AND aida.match_status_flag in ('A','T')
AND aia.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
AND aia.org_id = p_organization_id
AND NOT EXISTS (SELECT 1
FROM JAI_AP_ETDS_T
WHERE base_invoice_id = jatit.invoice_id
AND batch_id = p_batch_id
)
AND ( lv_include_flag = 'N' or ( lv_include_flag = 'Y' and upper(nvl(jatit.actual_section_code, jatit.default_section_code))
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(nvl(jatit.actual_section_code, jatit.default_section_code))
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),'94F')));
UPDATE jai_ap_etds_t
SET tds_check_id = r_threshold_tran_inv.tds_check_id,
challan_num = r_tds_payment_check_id.challan_num,
challan_date = r_tds_payment_check_id.challan_date,
check_number = r_tds_payment_check_id.check_number,
bank_branch_code = r_tds_payment_check_id.branch_code,
amt_of_tds = ((tds_amount*ln_tds_amount)/r_threshold_tran_inv.tds_amount),
amt_of_surcharge = ((tds_amount*ln_surcharge_amount)/r_threshold_tran_inv.tds_amount),
amt_of_cess = ((tds_amount*ln_cess_amount)/r_threshold_tran_inv.tds_amount),
tds_invoice_date = ld_gl_date \*GL Date and Invoice Date are same for TDS Invoices*\
WHERE tds_check_id = -9999
AND tds_invoice_id IN (-9998, -9999)
AND (tds_vendor_classification IS NULL
--OR tds_vendor_classification <> 'Transporter'--Commented by Zhiwei for Bug#15840480 FVU3.6
--Added by Zhiwei for Bug#15840480 FVU3.6 begin
------------------------------------------------
--OR tds_vendor_classification <> 'Software'
OR tds_vendor_classification not in ('Transporter','Software')
------------------------------------------------
--Added by Zhiwei for Bug#15840480 FVU3.6 end
);
DELETE jai_ap_etds_t
WHERE batch_id = p_batch_id
AND EXISTS (SELECT 1
FROM jai_ap_tds_thhold_trxs
WHERE invoice_to_tds_authority_id = tds_invoice_id
AND tds_event LIKE 'THRESHOLD TRANSITION%'
AND threshold_grp_id = c_get_threshold_grp.threshold_grp_id
);
FOR c_update_amount IN (SELECT jaet.rowid row_id, jaet.*
FROM JAI_AP_ETDS_T jaet
WHERE tds_invoice_id = -9998
AND batch_id = p_batch_id)
LOOP
ln_calculated_inv_amt := 0;
SELECT invoice_amount - NVL(amount_paid, 0) INTO ln_calculated_inv_amt
FROM ap_invoices_all
WHERE invoice_id = c_update_amount.base_invoice_id;
fnd_file.put_line(FND_FILE.LOG, 'c_update_amount.base_invoice_id: ' || c_update_amount.base_invoice_id);
IF c_update_amount.base_invoice_type_lookup_code = 'STANDARD' THEN
OPEN c_get_pp_apply_amt(c_update_amount.base_invoice_id);
ELSIF c_update_amount.base_invoice_type_lookup_code = 'PREPAYMENT' THEN
OPEN c_get_applied_amt(c_update_amount.base_invoice_id);
OPEN c_get_remaining_amt(c_update_amount.base_invoice_id);
END IF; \*IF c_update_amount.base_invoice_type_lookup_code = 'STANDARD' THEN*\
UPDATE JAI_AP_ETDS_T
SET base_taxabale_amount = ln_calculated_inv_amt,
tds_invoice_id = -9999,
amt_of_tds = ((amt_of_tds*ln_calculated_inv_amt)/base_taxabale_amount),
amt_of_surcharge = ((amt_of_surcharge*ln_calculated_inv_amt)/base_taxabale_amount),
amt_of_cess = ((amt_of_cess*ln_calculated_inv_amt)/base_taxabale_amount)
WHERE base_invoice_id = c_update_amount.base_invoice_id
AND rowid = c_update_amount.row_id;
END LOOP; \*FOR c_update_amount*\
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
and a.tds_invoice_id not in ( -9999, -9997) ) \* Added Condition for Bug#10315928 *\ \* ER: 13514846 *\
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 by Xiao Lv for bug#7662155 on 06-Jan-2010, begin
OPEN c_get_tds_inv_det(dtl.tds_invoice_id);
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 a.tds_invoice_id <> -9999 --Bug 10315928
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%'
) ;
* Updates records which are available in
* jai_ap_tds_thhold_trxs *\
\*Bug 12415388 - Update Base Taxable amount to the portion of Check amount*\
UPDATE jai_ap_etds_t a
SET base_taxabale_amount =
( SELECT decode(b.tds_event,
'SURCHARGE_CALCULATE',
0,
\* nvl(taxable_amount,0) * sign(invoice_to_tds_authority_amt) * (tds_amount/abs(invoice_to_tds_authority_amt))) *\ --Added DECODE by Xiao Lv for Bug#7662155
nvl(taxable_amount,0) * sign(invoice_to_tds_authority_amt)) \*commented above and added nvl(taxable_amount,0) * sign(invoice_to_tds_authority_amt) for bug 13070779*\
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 a.tds_invoice_id not in ( -9999, -9997 ); --Bug 10315928 \* ER: 13514846 *\
\* This update is used to update invoices which
* are not available in jai_ap_tds_thhold_trxs
* but considered for calculating taxable_basis
* for threshold transition or rollback.
*\
UPDATE jai_ap_etds_t a
SET base_taxabale_amount = 0
WHERE a.batch_id = p_batch_id
AND base_taxabale_amount IS NULL;
SELECT SUM(application_amount)
FROM jai_ap_tds_prepayments jatp
WHERE invoice_id = p_invoice_id
AND (SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = jatp.invoice_distribution_id)
<=
(SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id IN (SELECT prepay_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = jatp.invoice_distribution_id_prepay
)
);
SELECT SUM(application_amount)
FROM jai_ap_tds_prepayments jatp
WHERE invoice_id IN (SELECT invoice_id
FROM ap_invoice_distributions_all
WHERE prepay_distribution_id IN (SELECT invoice_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
)
)
AND (SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = jatp.invoice_distribution_id)
>
(SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id IN (SELECT prepay_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = jatp.invoice_distribution_id_prepay
)
);
SELECT sum(prepay_amount_remaining)
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id;
SELECT tds_invoice_id, tds_check_id, tds_tax_id, tds_amount
FROM jai_ap_etds_t
WHERE tds_invoice_id = (SELECT MAX(invoice_to_tds_authority_id)
FROM jai_ap_tds_thhold_trxs
WHERE threshold_grp_id = p_threshold_grp_id
AND tds_event LIKE 'THRESHOLD TRANSITION%'
);
SELECT gl_date
FROM ap_invoices_all
WHERE invoice_id = p_tds_invoice_id;
SELECT invoice_to_tds_authority_id
FROM jai_ap_tds_thhold_trxs
WHERE tds_event LIKE 'THRESHOLD ROLLBACK%'
AND threshold_grp_id = p_threshold_grp_id
AND invoice_to_tds_authority_id > (SELECT MAX(invoice_to_tds_authority_id)
FROM jai_ap_tds_thhold_trxs
WHERE threshold_grp_id = p_threshold_grp_id
AND tds_event LIKE 'THRESHOLD TRANSITION%'
);
SELECT attribute1
FROM hr_all_organization_units
WHERE organization_id = p_organization_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.bank_account_name, JATP.Challan_no challan_num,
JATP.check_deposit_date challan_date, JATP.bsr_code branch_code, apc.check_number check_number /*Bug 7688789 - Added Check Number*/
FROM ap_invoice_payments_all PAY, ap_checks_all APC /* renamed the table from ap_checks_v to ap_checks_All for bug 12691784*/, 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.bank_account_name, JATP.Challan_no challan_num,
JATP.check_deposit_date challan_date, JATP.bsr_code branch_code, apc.check_number check_number /*Bug 7688789 - Added Check Number*/
FROM ap_invoice_payments_all PAY, ap_checks_all APC/*renamed the table from ap_checks_v to ap_checks_All for Bug 12691784*/, JAI_AP_TDS_PAYMENTS JATP
WHERE PAY.check_id = p_check_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 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 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
FROM JAI_AP_TDS_INV_PAYMENTS jatp
WHERE
check_id = p_check_id;
SELECT threshold_trx_id,
tax_amount,
taxable_amount,
tds_event
FROM jai_ap_tds_thhold_trxs jatt
WHERE jatt.invoice_to_tds_authority_id = cp_invoice_id
AND tds_event like 'SURCHARGE_CALCULATE';
SELECT jl.meaning MEANING
,jl.lookup_code LOOKUP_CODE
,jrd.regime_id REGIME_ID
FROM ja_lookups jl
,jai_rgm_definitions jrd
WHERE jl.lookup_code = jrd.regime_code
AND jl.lookup_type = G_JAI_INDIA_TAX_REGIMES
AND jl.lookup_code = G_TDS
;
INSERT INTO JAI_AP_ETDS_T (
batch_id,
base_invoice_id,
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,
base_invoice_date ,
-- Added for bug16889298 Start
tds_vendor_classification,
base_vendor_id,
base_vendor_site_id,
base_invoice_type_lookup_code,
amt_of_tds,
amt_of_cess,
amt_of_surcharge,
base_taxabale_amount,
-- Added for bug16889298 End
created_by,
creation_date,
last_updated_by,
last_update_date
)
SELECT p_batch_id,
base_invoices.invoice_id, -- base_invoice_id
NVL(a.invoice_id,-9999), -- -9999 for prior threshold case --tds_invoices.invoice_id, -- tds_invoice_id
tds_invoices.invoice_num, -- tds_invoice_num
NVL(tds_invoices.invoice_date, base_invoices.invoice_date), -- tds_invoice_date
a.section_code,
a.tax_category_id, --for new TDS, populate tax_category_id instead of tax_id
NVL(a.tax_rate,0), --effective tds rate
NVL(tds_invoices.invoice_amount,0),
1,
1,
aipa.check_id,
base_invoices.invoice_date,
-- Added for bug16889298 Start
jte.tds_vendor_classification,
base_invoices.vendor_id,
base_invoices.vendor_site_id,
base_invoices.invoice_type_lookup_code,
0,
0,
0,
base_invoices.invoice_amount,
-- Added for bug16889298 End
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
from jai_ap_tds_repository a
,ap_invoices_all base_invoices
,ap_invoices_all tds_invoices
,ap_invoice_payments_all aipa
,JAI_AP_TDS_VENDOR_HDRS jte -- Added for bug16889298
where a.source_invoice_id = base_invoices.invoice_id
and a.invoice_id = tds_invoices.invoice_id(+) -- Added outer join for bug16889298
and a.invoice_id = aipa.invoice_id(+) -- Added outer join for bug16889298
--Added for bug16889298 start
and base_invoices.vendor_id = jte.vendor_id
and base_invoices.vendor_site_id = jte.vendor_site_id
--Added for bug16889298 end
/*Bug 10020606 - All TDS reports must be based on GL Date or date on which TCS Liability is booked*/
and base_invoices.gl_date between p_tds_inv_from_date and p_tds_inv_to_date
and ((a.tds_authority_id = p_tds_vendor_id
and (p_tds_vendor_site_id is null or a.tds_authority_site_id = p_tds_vendor_site_id)
and a.challan_number is not null
) or (
a.invoice_id in(-9999, -9997) or a.invoice_id IS NULL
)
)
and NVL(a.Invoice_deleted,'N') = 'N'
and NVL(a.Source_invoice_deleted, 'N') = 'N'
-- and NVL(a.prior_threshold,'N') = 'N' --removed for bug16889298
and NVL(a.required_for_etds,'N') = 'Y'
-- and a.challan_number is not null --removed for bug16889298
and base_invoices.org_id = p_organization_id /* Added the condition for Bug 12691784 */
and a.org_tan_number = p_org_tan_num
and a.section_type = G_TDS_SECTION --Only show TDS setcion in eTDS file
and ( lv_include_flag = 'N' or ( lv_include_flag = 'Y'
and upper(a.section_code) 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.section_code) 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))))
;
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME || l_api_name, 'After insert into JAI_AP_ETDS_T batch_id:' || p_batch_id);
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
and a.tds_invoice_id not in ( -9999, -9997) )
LOOP
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME || l_api_name, 'in Loop for TDS invoice: ' || dtl.tds_invoice_id);
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 by Xiao Lv for bug#7662155 on 06-Jan-2010, begin
OPEN c_get_tds_inv_det(dtl.tds_invoice_id);
For rec_repository_lines in ( select jatrl.tax_id
,jatrl.tax_type
,jatrl.tax_amount
From jai_ap_tds_repo_lines jatrl
Where jatrl.invoice_id = dtl.tds_invoice_id
And jatrl.surcharge_rollback is null
)
LOOP
IF rec_repository_lines.tax_type = 'TDS' THEN
ln_tds_amt := rec_repository_lines.tax_amount;
UPDATE JAI_AP_ETDS_T
SET --tds_amount = round(tds_amount - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2), --Updated for bug16898321, Sum surcharge amount into tds_amount 20130602
tds_amount = round(ln_tds_amt + ln_cess_amt + ln_sh_cess_amt + ln_surcharge_amt - 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 + ln_sh_cess_amt, --Added sh_cess_amount by bug#16864998, sum cess and shcess together
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 a.tds_invoice_id <> -9999 --Bug 10315928
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%'
) ;
* Updates records which are available in
* jai_ap_tds_thhold_trxs */
/*Bug 12415388 - Update Base Taxable amount to the portion of Check amount*/
UPDATE jai_ap_etds_t a
SET base_taxabale_amount =
( SELECT decode(b.tds_event,
'SURCHARGE_CALCULATE',
0,
/* nvl(taxable_amount,0) * sign(invoice_to_tds_authority_amt) * (tds_amount/abs(invoice_to_tds_authority_amt))) */ --Added DECODE by Xiao Lv for Bug#7662155
nvl(taxable_amount,0) * sign(invoice_to_tds_authority_amt)) /*commented above and added nvl(taxable_amount,0) * sign(invoice_to_tds_authority_amt) for bug 13070779*/
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 a.tds_invoice_id not in ( -9999, -9997 ); --Bug 10315928 /* ER: 13514846 */
/* This update is used to update invoices which
* are not available in jai_ap_tds_thhold_trxs
* but considered for calculating taxable_basis
* for threshold transition or rollback.
*/
UPDATE jai_ap_etds_t a
SET base_taxabale_amount = 0
WHERE a.batch_id = p_batch_id
AND base_taxabale_amount IS NULL;
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 jatsm.section_code_revised INTO lv_output_string
FROM jai_ap_tds_sec_map jatsm
,ja_lookups jl
,jai_rgm_definitions jrd
WHERE jl.lookup_code = jrd.regime_code
AND jl.lookup_type = G_JAI_INDIA_TAX_REGIMES
AND jl.lookup_code = G_TDS
AND jatsm.section_code = p_tds_section
-- AND jatsm.form_type = 1
AND jatsm.form_type = decode(p_form_name,'27Q',1,2) --Updated by Chong for bug#16824926
AND jatsm.regime_id = jrd.regime_id
AND jatsm.from_date <= p_challan_date
AND (jatsm.to_date IS NULL
OR jatsm.to_date >= p_challan_date
)
;
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) ;
/*Update to get fin year info from regime definition for eTDS bug#16414088
-- to get financial and assessment years
CURSOR c_fin_year(p_tan_number \*p_legal_entity_id*\ IN VARCHAR2, p_fin_year IN NUMBER, p_organization_id IN NUMBER ) IS
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 p.start_date
,p.end_date
from gl_period_sets ps
,gl_periods p
,JAI_RGM_REGISTRATIONS rr
,JAI_RGM_DEFINITIONS gd
Where ps.period_set_id = to_number(rr.attribute_value)
and ps.period_set_name = p.period_set_name
and gd.regime_code = 'TDS'
and gd.regime_id = rr.regime_id
and rr.attribute_code = 'TDS_CALENDAR'
;
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
AND organization_id = nvl(p_organization_id, organization_id);
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 ORG_TAN_NUM
FROM jai_rgm_org_regns_v
WHERE
regime_code = 'TDS'
AND organization_id = p_organization_id
AND registration_type = lv_oth_reg_type
AND attribute_type_code = lv_prim_att_type
AND 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 = nvl(p_organization_id,p_deductor_addr_org_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,confirm_pan_flag /*eTDS FVU3.1 Bug 11896260*/
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
and a.tds_invoice_id <> -9999 -- Bug#10315928
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 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,/* Commented for Bug 13323753 */
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,
--Changed by Zhhou for bug#15962641 20121205 begin
------------------------------------------------------------
tds_vendor_classification /* Uncommented by Avanija for FVU3.6 */
--decode(tds_vendor_classification,NULL,NULL,'TRANS/SOFTW') tds_vendor_classification /* Avanija for FVU3.6 */
------------------------------------------------------------
--Changed by Zhhou for bug#15962641 20121205 end
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, -commented for Bug 13323753 */
--Changed by Zhhou for bug#15962641 20121205 begin
------------------------------------------------------------
tds_vendor_classification; /* Uncommented by Avanija for FVU3.6 */
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, /* Commented for Bug 13323753 */
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,
max(tds_invoice_id) tds_invoice_id,
max(base_invoice_id) base_invoice_id,
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 (a.tds_invoice_id <> -9999 /* Bug#10315928 */
OR (a.tds_invoice_id = -9999 and (a.tds_vendor_classification not in ( 'Transporter', 'Software') OR a.tds_vendor_classification IS NULL))) /*11896260*/
/* Added Software by Avanija for FVU3.6*/
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, -Commented for Bug 13323753 */tds_tax_rate, base_invoice_id
having sum(amt_of_tds) > 0
/* ER: 13514846 - Query to populate Zero Rate Records in Deductee*/
union
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,
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,
max(tds_invoice_id) tds_invoice_id,
max(base_invoice_id) base_invoice_id,
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 a.tds_invoice_id = -9997
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),
tds_tax_rate, base_invoice_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
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,
/* NVL(check_number, -9999) check_number,- Commented for Bug 13323753 */
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,
max(tds_invoice_id) tds_invoice_id, --Added by Bgowrava for bug#9346767
max(base_invoice_id) base_invoice_id, /* Added for Bug 10188600*/
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 a.tds_invoice_id = -9999
and challan_line_num = NVL(p_challan_line_num, challan_line_num)
--Changed by Zhiwei for Bug#15840480 FVU3.6 begin
---------------------------------------------------------
--and a.tds_vendor_classification = 'Transporter' /*Bug 11896260 - Distinguish between Transporters and Invoices before Threshold*/
and a.tds_vendor_classification = c_vendor_classification
---------------------------------------------------------
--Changed by Zhiwei for Bug#15840480 FVU3.6 end
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),
/*NVL(check_number, -9999), - Commented for Bug 13323753 */ tds_tax_rate, base_invoice_id; /* Added base_invoice_id for bug 15996337 */
SELECT gl_date
FROM ap_invoices_all
WHERE invoice_id = p_base_invoice_id;
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 'A'
from jai_cmn_taxes_all, fnd_lookup_values_vl
where tax_id = p_tax_id
and upper(section_code) = lookup_code
and (stform_type is not null or tax_rate = 0)
and lookup_type = 'JAI_TDS_SECTION_ZERO_RATE_197'
UNION
select 'B'
from jai_cmn_taxes_all, fnd_lookup_values_vl
where tax_id = p_tax_id
and tax_rate = 0
and upper(section_code) = lookup_code
and lookup_type = 'JAI_TDS_SECTION_ZERO_RATE_197A';
select 'A'
from fnd_lookup_values_vl
where upper(p_section_code) = lookup_code
and lookup_type = 'JAI_TDS_SECTION_ZERO_RATE_197'
UNION
select 'B'
from fnd_lookup_values_vl
where upper(p_section_code) = lookup_code
and lookup_type = 'JAI_TDS_SECTION_ZERO_RATE_197A';
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_invoice_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 tds_invoice_id not in ( -9999, -9997) /* Bug#10315928 */ /* ER 13514846 */
/* and check_number = p_check_number - Commented for Bug 13323753 */
group by base_vendor_id, base_vendor_site_id,tds_tax_id, base_invoice_id
having sum(amt_of_tds) >0 /* Added having clause for Bug 13323753 */
/* ER: 13514846 */
union
select sum ( count( distinct base_invoice_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 tds_invoice_id = -9997
group by base_vendor_id, base_vendor_site_id,tds_tax_id, base_invoice_id
;
select sum ( count( distinct base_invoice_id ) )
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 - Commented for Bug 13323753 */
and tds_invoice_id = -9999
and (tds_vendor_classification not in ('Transporter','Software') OR tds_vendor_classification IS NULL) /* Avanija for FVU3.6 */
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('SEC. 194(C)', 'No Section')
--and nvl(tds_section, 'No Section') =decode(pv_vendor_classification, 'Transporter', 'SEC. 194(C)','SEC. 194(J)') /* Avanija for FVU3.6 */ /* Commented for bug 15996337 */
and consider_for_challan=1
--Changed by Zhiwei for Bug#15840480 FVU3.6 begin
-----------------------------------------------------------------------------
--and tds_vendor_classification = 'Transporter' /*Bug 11896260*/
and tds_vendor_classification = pv_vendor_classification --Commented by Zhhou for bug#15962641 20121205 /*Uncommented by Avanija for FVU3.6*/
-----------------------------------------------------------------------------
--Changed by Zhiwei for Bug#15840480 FVU3.6 end
--Added by Zhhou for bug#15962641 20121205 begin
-----------------------------------------------------------------------------
--and tds_vendor_classification in ('Transporter','Software') /* Commented by Avanija for FVU3.6 */
-----------------------------------------------------------------------------
--Added by Zhhou for bug#15962641 20121205 end
and tds_invoice_id = -9999
group by base_vendor_id, base_vendor_site_id, tds_tax_id, base_invoice_id; /* Added base_invoice_id for bug 16002763 */
SELECT 'T'
FROM jai_ap_tds_vendor_hdrs
WHERE vendor_id = p_vendor_id;*/
SELECT gl_date
FROM ap_invoices_all
WHERE invoice_id = cp_invoice_id;
Select Error_Message from jai_ap_etds_errors_t
where batch_id = cp_batch_id ;
select flex_value
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 description= p_state_name;
SELECT to_char(start_date, 'YYYY') INTO ln_year
FROM jai_ap_tds_years
WHERE legal_entity_id = p_organization_id
AND fin_year = p_fin_year;
insert into jai_ap_etds_errors_t
(batch_id, record_type, reference_id, error_message) values
( v_batch_id,'DD', v_line_number, p_return_message ) ; /*Bug 8880543 - Modified ln_batch_id to v_batch_id*/
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 --commented for Bug 13323753 */
and tds_tax_rate = dd.tds_tax_rate ;
UPDATE jai_ap_etds_t
SET deductee_line_num = v_line_number
WHERE batch_id = v_batch_id
and consider_for_challan=1
and tds_invoice_id = -9999
and challan_line_num = dd_t.challan_line_num
and base_vendor_id = dd_t.base_vendor_id
and base_vendor_site_id = dd.base_vendor_site_id
and tds_tax_id = dd_t.tds_tax_id
and NVL(tds_section,'No Section') = NVL(dd_t.tds_section,'No Section')
and NVL(bank_branch_code,'No Bank Branch') = NVL(dd_t.bank_branch_code,'No Bank Branch')
and NVL(challan_num,'No Challan Number') = NVL(dd_t.challan_num,'No Challan Number')
and NVL(challan_date,lv_dummy_date) = NVL(dd_t.challan_date,lv_dummy_date)
/* and nvl(check_number,'No Check Number') = nvl(dd_t.check_number,'No Check Number') --commnented for Bug 13323753 */
and tds_tax_rate = dd_t.tds_tax_rate
--Changed by Zhiwei for Bug#15840480 FVU3.6 begin
---------------------------------------------------------------
--and tds_vendor_classification = 'Transporter';
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;
FND_FILE.put_line(FND_FILE.log, 'Deductor Address must be selected as the report is being submitted for TAN Number');
RAISE_APPLICATION_ERROR(-20018, 'Deductor Address must be selected as the report is being submitted for TAN Number', true);
select count(*) into v_regime_override
from jai_rgm_parties jrp,
jai_rgm_definitions jrd,
jai_rgm_party_regns jrpr,
jai_rgm_registrations jrr
where jrd.regime_code = 'TDS'
and jrd.regime_id = jrp.regime_id
and jrp.regime_org_id = jrpr.regime_org_id
and jrp.organization_id in (select distinct organization_id from jai_ap_tds_org_tan_v where org_tan_num = p_tan_number)
and jrr.registration_id = jrpr.registration_id;
RAISE_APPLICATION_ERROR(-20014, 'For the selected TAN number attributes must be same across the OUs', true);
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, /*Bug 8880543 - Commented for eTDS/eTCS FVU Changes*/
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, /*Bug 8880543 - Commented for eTDS/eTCS FVU Changes*/
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
( v_batch_id, 'FH', p_return_message ) ; /*Bug 8880543 - Modified ln_batch_id to v_batch_id*/
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
AND ((tds_invoice_id not in ( -9999, -9997)) /* Bug#10315928 */ /* ER :13514846 */
OR (tds_invoice_id = -9999 AND (tds_vendor_classification IS NULL
--Changed by Zhhou for bug#15962641 20121205 begin
---------------------------------------------------------------
OR tds_vendor_classification not in ('Transporter','Software')
--OR tds_vendor_classification <> 'Transporter'
---------------------------------------------------------------
--Changed by Zhhou for bug#15962641 20121205 end
))); /*Bug 11896260*/
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
( v_batch_id, 'BH', p_return_message ) ; /*Bug 8880543 - Modified ln_batch_id to v_batch_id*/
insert into jai_ap_etds_errors_t
(batch_id, record_type, reference_id, error_message) values
( v_batch_id, 'CD', v_line_number, p_return_message ) ; /*Bug 8880543 - Modified ln_batch_id to v_batch_id*/
UPDATE JAI_AP_ETDS_T
SET challan_line_num = v_line_number
WHERE batch_id = v_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, trunc(sysdate) ) = nvl(cd.challan_date, trunc(sysdate) )
and nvl(bank_branch_code, 'No Bank Branch') = nvl(cd.bank_branch_code, 'No Bank Branch')
and consider_for_challan=1
and tds_vendor_classification is null; /* Avanija for FVU3.6 */
UPDATE jai_ap_etds_t
SET challan_line_num = v_line_number
WHERE batch_id = v_batch_id
--and nvl(tds_section, 'No Section') = decode(cd.tds_vendor_classification,'Transporter','SEC. 194(C)', 'SEC. 194(J)') /*Avanija for FVU3.6 */
and nvl(tds_section, 'No Section') = cd.tds_section /*Commented above and added the AND for tds section for bug 15996337 */
and consider_for_challan=1
and tds_invoice_id = -9999
--Changed by Zhiwei for Bug#15840480 FVU3.6 begin
------------------------------------------------------------------
--and tds_vendor_classification = 'Transporter';/*ETDS FVU3.1 Bug 11896260*/