[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Solution - Insert Statement is changed jai_ap_tds_cert_nums , Column FIN_YR_CERT_ID
is added According to R12 standard.
4. 14-JUL-2009 vkaranam for bug#8679068 120.3.12000000.2
Issue:
TCS Certificates are generated without the TCS invoice is been paid and settled.
Fix:
TCS certificates will be generated only if the TCS invoice is been settled.
Changes:
Added jai_rgm_settlements.status='SETTLED' condition in the cursor
c_group_for_certificate.
5. 27-Oct-2010 Bug 10201267
Description: A single certificate is generated for Traansactions with
difeerent Item Classification.
Fix: Added Item Classification in group by clause of cursor c_group_for_certificate
so that a separate certificate is generated if Item Classification changes.
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
----------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------- */
---------------------------------------------------------------------------- */
/* ****************************** generate_tcs_certificates ***************************************** */
PROCEDURE generate_tcs_certificates
(
errbuf out nocopy varchar2,
retcode out nocopy varchar2,
pd_rgm_from_date in varchar2,
pd_rgm_to_date in varchar2,
pv_org_tan_num in varchar2,
pn_tax_authority_id in number ,
pn_tax_authority_site_id in number default null,
pn_customer_id in number default null,
pn_customer_site_id in number default null
)
is
/*Bug 10201267 - Separate Certificate to be generated if Item Classification Code is different
Added Item Classification to group by clause*/
cursor c_group_for_certificate
(
pd_rgm_from_date date,
pd_rgm_to_date date,
pv_org_tan_num varchar2,
pn_tax_authority_id number,
pn_tax_authority_site_id number,
pn_customer_id number,
pn_customer_site_id number
)
is
SELECT jrr.fin_year,
jrr.org_tan_no,
jrr.organization_id,
jrr.party_id,
jrr.party_site_id,
jrs.tax_authority_id,
jrs.tax_authority_site_id,
jrr.item_classification
FROM JAI_RGM_REFS_ALL jrr,
JAI_RGM_SETTLEMENTS jrs
WHERE jrr.settlement_id = jrs.settlement_id
AND jrr.source_document_date BETWEEN pd_rgm_from_date and pd_rgm_to_date
AND jrr.certificate_id IS NULL
AND jrs.status='SETTLED' --added for bug#8679068
AND jrs.tax_authority_id = pn_tax_authority_id
AND jrs.tax_authority_site_id = nvl(pn_tax_authority_site_id, tax_authority_site_id)
AND jrr.party_type = 'C'
AND jrr.party_id = nvl(pn_customer_id, party_id)
AND jrr.party_site_id = nvl(pn_customer_site_id, party_site_id)
AND jrr.org_tan_no = pv_org_tan_num
GROUP BY jrr.fin_year,
jrr.org_tan_no,
jrr.organization_id,
jrr.party_id,
jrr.party_site_id,
jrs.tax_authority_id,
jrs.tax_authority_site_id,
jrr.item_classification;
SELECT nvl(certificate_num, 0) + 1
FROM jai_ap_tds_cert_nums
WHERE org_tan_num = pv_org_tan_num
AND fin_yr = pn_fin_year
AND REGIME_CODE = pv_regime_code;
SELECT jai_rgm_certificates_s.nextval
FROM dual;
SELECT regime_id
FROM JAI_RGM_DEFINITIONS
WHERE regime_code = cp_regime_code;
ln_last_update_login number(15);
ln_last_update_login := fnd_global.login_id ;
UPDATE jai_rgm_refs_all
SET certificate_id = ln_certificate_id
, last_update_date = sysdate
, last_update_login = ln_last_update_login
where trx_ref_id in ( SELECT jrr.trx_ref_id
FROM jai_rgm_refs_all jrr,
jai_rgm_settlements jrs
WHERE jrr.settlement_id = jrs.settlement_id
AND jrr.source_document_date BETWEEN ld_from_date and ld_to_date
AND jrr.certificate_id IS NULL
AND jrs.tax_authority_id = cur_rec.tax_authority_id
AND jrs.tax_authority_site_id = cur_rec.tax_authority_site_id
AND jrr.party_type = jai_constants.party_type_customer
AND jrr.party_id = cur_rec.party_id
AND jrr.party_site_id = cur_rec.party_site_id
AND jrr.org_tan_no = cur_rec.org_tan_no
AND jrr.fin_year = cur_rec.fin_year
AND jrr.organization_id = cur_rec.organization_id
AND jrr.item_classification = cur_rec.item_classification);
insert into jai_ap_tds_cert_nums
(
FIN_YR_CERT_ID , -- Date 22/06/2007 by sacsethi for bug 6144923
regime_code ,
org_tan_num ,
fin_yr ,
certificate_num ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
values
(
JAI_AP_TDS_CERT_NUMS_S.NEXTVAL , -- Date 22/06/2007 by sacsethi for bug 6144923
jai_constants.tcs_regime,
pv_org_tan_num ,
cur_rec.fin_year ,
1 ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_last_update_login
);
Fnd_File.put_line(Fnd_File.LOG, 'Updated certificate number in jai_ap_tds_cert_nums');
update jai_ap_tds_cert_nums
set certificate_num = ln_certificate_num
where org_tan_num = pv_org_tan_num
and fin_yr = cur_rec.fin_year
and regime_code = jai_constants.tcs_regime;
/* insert into JAI_RGM_CERTIFICATES */
Fnd_File.put_line(Fnd_File.LOG, 'Inserting record in JAI_RGM_CERTIFICATES with certificate_id : ' || to_char(ln_certificate_id));
INSERT INTO
jai_rgm_certificates( CERTIFICATE_ID ,
CERTIFICATE_NUM ,
CERTIFICATE_DATE ,
PARTY_TYPE ,
PARTY_ID ,
PARTY_SITE_ID ,
REGIME_ID ,
TAX_AUTHORITY_ID ,
TAX_AUTHORITY_SITE_ID ,
FROM_DATE ,
TO_DATE ,
PRINT_FLAG ,
organization_id ,
ISSUE_DATE ,
FIN_YEAR ,
ORG_TAN_NO ,
PROGRAM_ID ,
PROGRAM_LOGIN_ID ,
PROGRAM_APPLICATION_ID,
REQUEST_ID ,
OBJECT_VERSION_NUMBER ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN )
VALUES( ln_certificate_id ,
ln_certificate_num ,
trunc(sysdate) ,
jai_constants.party_type_customer ,
cur_rec.party_id ,
cur_rec.party_site_id ,
ln_regime_id,
cur_rec.tax_authority_id ,
cur_rec.tax_authority_site_id,
ld_from_date ,
ld_to_date ,
NULL,
cur_rec.organization_id ,
NULL ,
cur_rec.fin_year ,
cur_rec.org_tan_no ,
ln_program_id ,
ln_program_login_id ,
ln_program_application_id ,
ln_request_id ,
NULL ,
SYSDATE ,
ln_user_id ,
SYSDATE ,
ln_user_id ,
ln_last_update_login );