[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from zx_fc_types_reg_assoc
where classification_type_code = p_fsc_rec.classification_type
and tax_regime_code = p_fsc_rec.tax_regime_code;
select unique 'Y'
from zx_fc_types_reg_assoc
where classification_type_code = p_fsc_rec.classification_type
and tax_regime_code IN (
select regime_code
from zx_regime_relations
connect by prior parent_regime_code = regime_code
start with regime_code = p_fsc_rec.tax_regime_code );
Select owner_id_num
from zx_fc_types_b
where classification_type_code = p_fsc_rec.classification_type;
select structure_id
from mtl_category_sets_b
where category_set_id = l_category_set_id;
select mtl.category_id
from mtl_categories_b_kfv mtl,
fnd_id_flex_structures flex
where mtl.structure_id = l_structure_id
and flex.ID_FLEX_NUM = mtl.STRUCTURE_ID
and flex.APPLICATION_ID = 401
and flex.ID_FLEX_CODE = 'MCAT'
and replace (mtl.concatenated_segments,flex.concatenated_segment_delimiter,'')= p_fsc_rec.condition_value;
select 'ALLOCATED'
from mtl_item_categories
where category_set_id = l_category_set_id
and category_id = l_category_id
and organization_id = p_fsc_rec.item_org_id
and inventory_item_id = p_fsc_rec.classified_entity_id;
select 'ALLOCATED'
from mtl_item_categories mit
where mit.category_set_id = l_category_set_id
and mit.organization_id = p_fsc_rec.item_org_id
and mit.inventory_item_id = p_fsc_rec.classified_entity_id
and exists (
select mtl.category_id
from mtl_categories_b_kfv mtl,
fnd_id_flex_structures flex,
( select start_position, num_characters
from zx_fc_types_b
where classification_type_code = p_fsc_rec.classification_type) fc
where mtl.structure_id = l_structure_id
and flex.ID_FLEX_NUM = mtl.STRUCTURE_ID
and flex.APPLICATION_ID = 401
and flex.ID_FLEX_CODE = 'MCAT'
and mtl.category_id = mit.category_id
and substr(replace (mtl.concatenated_segments,flex.concatenated_segment_delimiter,''),fc.start_position,fc.num_characters) = p_fsc_rec.condition_value);
Select owner_id_char
from zx_fc_types_b
where classification_type_code = p_fsc_rec.classification_type;
Select party_id ,Party_Type_Code
from zx_party_tax_profile
where party_tax_profile_id = p_fsc_rec.classified_entity_id;
select class_code
from hz_class_code_denorm
where class_category = l_tca_class_category_code
and concat_class_code = p_fsc_rec.condition_value;
select 'ALLOCATED',start_date_active,end_date_active
from hz_code_assignments
where class_category = l_tca_class_category_code
and class_code = l_class_code
and owner_table_name = l_table_owner
and owner_table_id = l_table_id
and p_fsc_rec.tax_determine_date between start_date_active and nvl(end_date_active,p_fsc_rec.tax_determine_date);
select 'ALLOCATED',start_date_active,end_date_active
from hz_code_assignments
where class_category = l_tca_class_category_code
and owner_table_name = l_table_owner
and owner_table_id = l_table_id
and p_fsc_rec.tax_determine_date between start_date_active and nvl(end_date_active,p_fsc_rec.tax_determine_date)
and class_code in (select class_code from hz_class_code_denorm
where class_category = l_tca_class_category_code
and SUBSTR(concat_class_code , 0, LENGTH(p_fsc_rec.condition_value)) = p_fsc_rec.condition_value
);
select 'EXISTS'
from hz_code_assignments
where class_category = l_tca_class_category_code
and owner_table_name = l_table_owner
and owner_table_id = l_table_id
and p_fsc_rec.tax_determine_date between start_date_active and nvl(end_date_active,p_fsc_rec.tax_determine_date)
and rownum = 1;
Select classification_type_code
from zx_fc_types_b
where classification_type_code = p_fsc_rec.classification_type;
select 'ALLOCATED',effective_from, effective_to
from zx_fc_codes_categ_assoc
where classification_type_code = NVL(FcType.OWNER_ID_CHAR,p_fsc_rec.classification_type)
and FcType
and Classification_Code_Concat = p_fsc_rec.condition_value
and trans_business_categ_type_code = 'TRX_BUSINESS_CATEGORY'
and Trans_Business_Categ_Concat = p_fsc_rec.event_class_code
and p_fsc_rec.tax_determine_date between effective_from and nvl(effective_to,p_fsc_rec.tax_determine_date);
select 'ALLOCATED',assoc.effective_from, assoc.effective_to
from zx_fc_codes_categ_assoc assoc,
zx_fc_types_b fctypes,
zx_fc_codes_denorm_b denorm
where assoc.classification_type_code = nvl(fctypes.owner_id_char,p_fsc_rec.classification_type)
and fctypes.classification_type_code = p_fsc_rec.classification_type
and assoc.Classification_Code_Concat = p_fsc_rec.condition_value
and assoc.trans_business_categ_type_code = 'TRX_BUSINESS_CATEGORY'
and denorm.CONCAT_CLASSIF_CODE = p_fsc_rec.condition_value
and denorm.CLASSIFICATION_CODE_LEVEL = fctypes.CLASSIFICATION_TYPE_LEVEL_CODE
and denorm.LANGUAGE = USERENV('LANG')
and assoc.Trans_Business_Categ_Concat = p_fsc_rec.event_class_code
and p_fsc_rec.tax_determine_date between assoc.effective_from and nvl(assoc.effective_to,p_fsc_rec.tax_determine_date);
select 'ALLOCATED',effective_from, effective_to
from zx_fc_codes_categ_assoc
where classification_type_code = p_fsc_rec.classification_type
and Classification_Code_Concat = p_fsc_rec.condition_value
and trans_business_categ_type_code = 'TRX_BUSINESS_CATEGORY'
and instr(p_fsc_rec.event_class_code,Trans_Business_Categ_Concat) <> 0
and p_fsc_rec.tax_determine_date between effective_from and nvl(effective_to,p_fsc_rec.tax_determine_date);
select 'ALLOCATED',assoc.effective_from, assoc.effective_to
from zx_fc_codes_categ_assoc assoc,
zx_fc_types_b fctypes,
zx_fc_codes_denorm_b denorm
where assoc.classification_type_code = nvl(fctypes.owner_id_char,p_fsc_rec.classification_type)
and fctypes.classification_type_code = p_fsc_rec.classification_type
and assoc.Classification_Code_Concat = p_fsc_rec.condition_value
and denorm.CONCAT_CLASSIF_CODE = p_fsc_rec.condition_value
and denorm.CLASSIFICATION_CODE_LEVEL = fctypes.CLASSIFICATION_TYPE_LEVEL_CODE
and denorm.LANGUAGE = USERENV('LANG')
and assoc.trans_business_categ_type_code = 'TRX_BUSINESS_CATEGORY'
and instr(p_fsc_rec.event_class_code,assoc.Trans_Business_Categ_Concat ) <> 0
and p_fsc_rec.tax_determine_date between assoc.effective_from and nvl(assoc.effective_to,p_fsc_rec.tax_determine_date);
SELECT 'ALLOCATED', le_effective_from, le_effective_to
FROM xle_firstparty_information_v
WHERE party_id = l_party_id
AND activity_category = l_tca_class_category_code
AND activity_code = l_class_code
-- AND p_fsc_rec.tax_determine_date between le_effective_from and nvl(le_effective_to,p_fsc_rec.tax_determine_date);
SELECT 'EXISTS'
FROM xle_firstparty_information_v
WHERE party_id = l_party_id
AND activity_category = l_tca_class_category_code
-- AND p_fsc_rec.tax_determine_date between le_effective_from and nvl(le_effective_to,p_fsc_rec.tax_determine_date)
AND p_fsc_rec.tax_determine_date between NVL(le_effective_from,p_fsc_rec.tax_determine_date) and nvl(le_effective_to,p_fsc_rec.tax_determine_date)
AND rownum = 1;
SELECT STATUS, DB_STATUS
INTO l_status, l_db_status
FROM fnd_product_installations
WHERE APPLICATION_ID = '401';
l_log_msg := 'E: EXC: OTHERS: select fnd_product_installations: '|| SQLCODE||': '||SQLERRM;
select party_id INTO l_party_id
from xle_entity_profiles
where legal_entity_id = p_fsc_rec.classified_entity_id;
SELECT party_tax_profile_id INTO l_ptp_id
FROM zx_party_tax_profile
where party_id = l_party_id
and party_type_code ='FIRST_PARTY';
select Classification_Code,
classification_code_level,
segment1,
segment2,
segment3,
segment4,
segment5,
effective_from,
effective_to
from Zx_Fc_Codes_Denorm_B
where Classification_Type_Categ_Code = p_fsc_cat_rec.classification_category
and classification_type_code = p_fsc_cat_rec.classification_type
and concat_classif_code = p_fsc_cat_rec.parameter_value
and p_fsc_cat_rec.tax_determine_date between effective_from and nvl(effective_to, p_fsc_cat_rec.tax_determine_date);
select delimiter
from zx_fc_types_b
where Classification_Type_Categ_Code = p_fsc_cat_rec.classification_category
and classification_type_code = p_fsc_cat_rec.classification_type;
SELECT 'N' dummy_flag,
reg.Registration_id,
reg.Registration_Type_Code,
reg.Registration_Number,
reg.Validation_Rule,
reg.Tax_Authority_id,
reg.Rep_Tax_Authority_id,
reg.Coll_Tax_Authority_id,
reg.Rounding_Rule_Code,
reg.Tax_Jurisdiction_Code,
reg.Self_Assess_Flag,
reg.Registration_Status_Code,
reg.Registration_Source_Code,
reg.Registration_Reason_Code,
reg.Party_Tax_Profile_id,
reg.Tax,
reg.Tax_Regime_Code,
reg.Inclusive_Tax_Flag,
-- reg.Has_Tax_Exemptions_Flag,
reg.Effective_From,
reg.Effective_To,
reg.Rep_Party_Tax_Name,
reg.Legal_Registration_id,
reg.Default_Registration_Flag,
reg.Bank_id,
reg.Bank_Branch_id,
reg.Bank_Account_Num,
reg.Legal_Location_id,
reg.Record_Type_Code,
reg.Request_id,
reg.Program_Application_id,
reg.Program_id,
reg.Program_Login_id,
reg.ACCOUNT_SITE_ID,
-- reg.Site_Use_id,
null, -- reg.Geo_Type_Classification_Code,
reg.ACCOUNT_ID,
reg.tax_classification_code,
reg.attribute1,
reg.attribute2,
reg.attribute3,
reg.attribute4,
reg.attribute5,
reg.attribute6,
reg.attribute7,
reg.attribute8,
reg.attribute9,
reg.attribute10,
reg.attribute11,
reg.attribute12,
reg.attribute13,
reg.attribute14,
reg.attribute15,
reg.attribute_category,
ptp.party_type_code,
ptp.supplier_flag,
ptp.customer_flag,
ptp.site_flag,
ptp.process_for_applicability_flag,
ptp.rounding_level_code,
ptp.withholding_start_date,
ptp.allow_awt_flag,
ptp.use_le_as_subscriber_flag,
ptp.legal_establishment_flag,
ptp.first_party_le_flag,
ptp.reporting_authority_flag,
ptp.collecting_authority_flag,
ptp.provider_type_code,
ptp.create_awt_dists_type_code,
ptp.create_awt_invoices_type_code,
ptp.allow_offset_tax_flag,
ptp.effective_from_use_le,
ptp.party_id,
ptp.rep_registration_number
FROM zx_registrations reg,
zx_party_tax_profile ptp
WHERE reg.party_tax_profile_id = c_party_tax_profile_id
AND reg.party_tax_profile_id = ptp.party_tax_profile_id
AND nvl(reg.tax_regime_code,1) = nvl(c_tax_regime_code,1)
AND nvl(reg.tax,nvl(c_tax,1)) = nvl(c_tax,1)
AND nvl(reg.tax_jurisdiction_code,nvl(c_jurisdiction_code,1)) = nvl(c_jurisdiction_code,1)
AND c_tax_determine_date >= reg.effective_from
AND (c_tax_determine_date <= reg.effective_to OR reg.effective_to IS NULL);
SELECT party_type_code
FROM zx_party_tax_profile
WHERE party_tax_profile_id = c_ptp_id;
SELECT
DECODE(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST',NULL) tax_rounding_rule
,DECODE(NVL(povs.Auto_Tax_Calc_Flag,'Y'),'N','N','Y') Auto_Tax_Calc_Flag
,povs.VAT_Code
,povs.VAT_Registration_Num
,DECODE(povs.Auto_Tax_Calc_Flag,
'L','LINE',
'H','HEADER',
'T','HEADER',
NULL) tax_rounding_level
,povs.amount_includes_tax_flag -- Added for Bug#12980272
FROM ap_supplier_sites_all povs
WHERE povs.vendor_id = p_account_id
AND povs.vendor_site_id = p_account_site_id;
SELECT
csu.Tax_Reference
,nvl(csu.Tax_Code,caa.tax_code) tax_code
,nvl(csu.Tax_Rounding_rule,caa.tax_rounding_rule) tax_rounding_rule
,nvl(csu.tax_header_level_flag, caa.tax_header_level_flag) tax_header_level_flag
,csu.Tax_Classification
FROM hz_cust_site_uses_all csu
,hz_cust_acct_sites cas
,hz_cust_accounts caa
WHERE csu.site_use_id = p_site_use_id
AND csu.cust_acct_site_id = p_account_site_id
AND csu.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = caa.cust_account_id
AND caa.cust_account_id = p_account_id;
SELECT
caa.tax_code tax_code
,caa.tax_rounding_rule tax_rounding_rule
,caa.tax_header_level_flag tax_header_level_flag
FROM
hz_cust_accounts caa
WHERE caa.cust_account_id = p_account_id;
-- Insert the FISCAL CLASSIFICATION CODE for LTE in level one
zx_fc_migrate_pkg.FIRST_LEVEL_FC_CODE_INSERT('PRODUCT_CATEGORY','FISCAL CLASSIFICATION CODE',
'Fiscal Classification Code',NULL,l_fc_id);
SELECT Category_set_ID
INTO l_category_set
FROM mtl_category_sets
WHERE Category_Set_Name = 'FISCAL_CLASSIFICATION';
zx_fc_migrate_pkg.FC_TYPE_INSERT('FISCAL_CLASSIFICATION','Fiscal Classification Code',l_category_set);
INSERT ALL INTO
ZX_FC_TYPES_REG_ASSOC
(Tax_regime_code,
classification_type_code,
effective_FROM,
effective_to,
record_type_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
classif_regime_id,
object_version_number)
VALUES
(TAX_REGIME_CODE,
'FISCAL_CLASSIFICATION',
SYSDATE,
NULL,
'MIGRATED',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
FND_GLOBAL.CONC_LOGIN_ID,
zx_fc_types_reg_assoc_s.nextval,
1)
SELECT unique tax_regime_code
FROM zx_rates_b
WHERE content_owner_id in
(SELECT unique org_id
FROM zx_product_options_all
WHERE application_id = 222
and tax_method_code='LTE');