[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.
120.18 23-Feb-2009 Suresh Bug 9381398:
Modified the get_amt_tot function logic. Earlier the invoice amount
logic was based on xla distribution links. But due to some limitation
while accessing the amounts information for upgrade transactions
changed the logic to fetch the amounts from xla ae lines.
Refer bug 9386590 for more details on upgrade transanctions issue
with xla distribution links.
120.11.12010000.11 13-Sep-2011 sakekuma Bug 12779390: Modified the get_amt_tot function logic to calculate invoice amount.Added
and xe.EVENT_TYPE_CODE <> 'PREPAYMENT APPLIED' so that prepayment applied events are
not picked up while calculation invoice amount.
120.11.12010000.12 21-Mar-2012 Mallika Kandula Bug 13853951 : Modified get_amt_tot function to exclude 'PREPAYMENT UNAPPLIED' events
*********************************************************************************** */
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
AND trunc(sysdate) BETWEEN trunc(nvl(xetbp.main_effective_from, sysdate)) AND trunc(nvl(xetbp.main_effective_to, sysdate));
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 (xal.accounted_cr, 0), pn_precision))
- SUM (ROUND (NVL (xal.accounted_dr, 0), pn_precision))
into l_amt
FROM xla_transaction_entities xte,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal
WHERE xte.entity_id = xe.entity_id
AND xe.application_id = 200
AND xah.event_id = xe.event_id
AND xah.application_id = 200
AND xte.application_id = 200
AND xah.ledger_id = pn_ledger_id
AND NVL (xe.budgetary_control_flag, 'N') = 'N'
AND xe.event_status_code = 'P'
and xe.process_status_code = 'P'
and xe.EVENT_TYPE_CODE not in ('PREPAYMENT APPLIED','PREPAYMENT UNAPPLIED') --bug 12779390
AND xal.ae_header_id = xah.ae_header_id
AND xal.accounting_class_code = 'LIABILITY'
AND xte.entity_code = 'AP_INVOICES'
AND NVL (xte.source_id_int_1, -99) = pn_invoice_id;