[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Selection process was run. If for LE(Legal Entity) then from
jgzzvattrxdetails else if LEDGER then from gl_ledgers for bug 7287545
120.13 09-Jan-2009 Varun Kejriwal Added a function get_amt_tot which takes invoice_id and ledger_id as parameters
and based on the type of the reporting entity ( LE/ Primary Ledger/ Secondary Ledger ),
it returns the appropriate invoice_amount.
*********************************************************************************** */
gv_debug_flag constant boolean := false;
SELECT DECODE(JZVRE.entity_level_code,'LEDGER',GL.currency_code,nvl(JZVTD.functional_currency_code, jzvtd.trx_currency_code))
,JZVTD.rep_context_entity_name
,JZVTD.rep_entity_id
,JZVTD.taxpayer_id
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
,jg_zz_vat_rep_entities JZVRE
,gl_ledgers GL
WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
AND JZVRS.vat_reporting_entity_id= pn_vat_rep_entity_id
AND (JZVRS.tax_calendar_period = pv_period_name
OR JZVRS.tax_calendar_year = pn_period_year)
AND JZVRE.vat_reporting_entity_id= JZVRS.vat_reporting_entity_id
and DECODE(JZVRE.entity_level_code,'LEDGER',JZVRE.ledger_id,1)=DECODE(entity_level_code,'LEDGER',GL.ledger_id,1)
AND rownum = 1;
SELECT cfgd.legal_entity_id
FROM jg_zz_vat_rep_entities cfg
,jg_zz_vat_rep_entities cfgd
WHERE cfg.vat_reporting_entity_id = pn_vat_rep_entity_id
AND (
( cfg.entity_type_code = 'ACCOUNTING'
and cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
)
or
( cfg.entity_type_code = 'LEGAL'
and cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
));
SELECT JZVRS.tax_registration_number
,min(JZVRS.period_start_date)
,max(JZVRS.period_end_date)
FROM jg_zz_vat_rep_status JZVRS
WHERE JZVRS.vat_reporting_entity_id= pn_vat_rep_entity_id
AND (JZVRS.tax_calendar_period = pv_period_name
OR JZVRS.tax_calendar_year = pn_period_year)
group by JZVRS.tax_registration_number;
SELECT XR.registered_name
,XR.registration_number
,FT.territory_short_name
,HL.address_line_1
,HL.address_line_2
,HL.address_line_3
,null address4
,HL.town_or_city
,HL.postal_code
--,HP.party_name This now retreived in the second cursor
,hp.party_id
-- Adding code for the GLOB006-ER
,HL.region_1
FROM xle_registrations XR
,xle_entity_profiles XEP
,hr_locations_all HL
,hz_parties HP
,fnd_territories_vl FT
WHERE XR.source_id = XEP.legal_entity_id
AND XR.source_table = 'XLE_ENTITY_PROFILES'
AND XEP.legal_entity_id = pn_legal_entity_id
AND XR.location_id = HL.location_id
AND HL.country = FT.TERRITORY_CODE
AND XEP.party_id = HP.party_id
and xr.identifying_flag = 'Y';
SELECT HCP.phone_number
FROM hz_contact_points HCP
WHERE HCP.owner_table_id = cp_party_id
AND HCP.owner_table_name = 'HZ_PARTIES'
AND HCP.primary_flag = 'Y'
AND HCP.contact_point_type= 'PHONE'
AND HCP.status = 'A';*/
SELECT per.party_name
,per.primary_phone_number
FROM HZ_PARTIES HP
,HZ_RELATIONSHIPS REL
,HZ_PARTIES PER
WHERE HP.PARTY_ID = cp_party_id
AND rel.object_id = HP.PARTY_ID
AND rel.subject_id = per.party_id
AND rel.relationship_code = 'CONTACT_OF'
AND rel.relationship_type = 'CONTACT'
AND rel.directional_flag = 'F'
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.subject_type = 'PERSON'
AND rel.object_table_name = 'HZ_PARTIES'
AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE);
select nvl(xler.registration_number,'') commercial_number
from XLE_REGISTRATIONS xler, XLE_JURISDICTIONS_B xlej, xle_entity_profiles xlee
where xlej.JURISDICTION_ID= xler.JURISDICTION_ID
and xlej.LEGISLATIVE_CAT_CODE = 'COMMERCIAL_LAW'
AND xler.source_id = xlee.LEGAL_ENTITY_ID
AND xler.source_TABLE = 'XLE_ENTITY_PROFILES'
and xlee.legal_entity_id = pn_legal_entity_id;
SELECT zptp.REP_REGISTRATION_NUMBER
INTO x_vat_reg_num
FROM ZX_PARTY_TAX_PROFILE zptp
,XLE_ETB_PROFILES xetbp
WHERE zptp.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
AND xetbp.party_id=zptp.party_id
AND xetbp.MAIN_ESTABLISHMENT_FLAG = 'Y'
AND xetbp.LEGAL_ENTITY_ID = pn_legal_entity_id;
SELECT cfgd.TAX_REGISTRATION_NUMBER
INTO x_vat_reg_num
FROM jg_zz_vat_rep_entities cfg
,jg_zz_vat_rep_entities cfgd
WHERE cfg.vat_reporting_entity_id = p_vat_reporting_entity_id
AND ( ( cfg.entity_type_code = 'ACCOUNTING'
AND cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
)
OR
( cfg.entity_type_code = 'LEGAL'
AND cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
)
);
SELECT legal_rep_entity.TAX_CALENDAR_NAME,
legal_rep_entity.ENABLE_REGISTERS_FLAG,
legal_rep_entity.ENABLE_REPORT_SEQUENCE_FLAG,
legal_rep_entity.ENABLE_ALLOCATIONS_FLAG,
legal_rep_entity.ENABLE_ANNUAL_ALLOCATION_FLAG,
legal_rep_entity.THRESHOLD_AMOUNT,
actg_rep_entity.ENTITY_IDENTIFIER
FROM JG_ZZ_VAT_REP_ENTITIES actg_rep_entity,
JG_ZZ_VAT_REP_ENTITIES legal_rep_entity
WHERE actg_rep_entity.vat_reporting_entity_id = p_vat_rep_entity_id
AND nvl(actg_rep_entity.mapping_vat_rep_entity_id,
actg_rep_entity.vat_reporting_entity_id)
= legal_rep_entity.vat_reporting_entity_id;
SELECT HL.country
INTO l_country_code
FROM xle_registrations XR
,xle_entity_profiles XEP
,hr_locations_all HL
WHERE XR.source_id = XEP.legal_entity_id
AND XR.source_table = 'XLE_ENTITY_PROFILES'
AND XEP.legal_entity_id = p_legal_entity_id
AND XR.location_id = HL.location_id
AND xr.identifying_flag = 'Y';
select nvl(base_amount,invoice_amount) into l_amt
from ap_invoices_all
where invoice_id = pn_invoice_id;
SELECT
sum(round(nvl(xdl.unrounded_accounted_cr,0),pn_precision)) -
sum(round(nvl(xdl.unrounded_accounted_dr,0),pn_precision))
into l_amt
from ap_invoice_distributions_all aid
,xla_ae_headers xah
,xla_ae_lines xal
,xla_distribution_links xdl
where aid.invoice_id = pn_invoice_id--13245
and xah.ledger_id = pn_ledger_id--2050
and aid.posted_flag ='Y'
and aid.accounting_event_id = xah.event_id
and xah.ae_header_id = xal.ae_header_id
and xah.application_id =200
and xal.application_id =200
and xal.accounting_class_code = 'LIABILITY'
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and aid.accounting_event_id = xdl.event_id
and xdl.application_id =200
and xdl.source_distribution_id_num_1 = aid.invoice_distribution_id;