[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_condition_group_result(
p_det_factor_templ_code IN ZX_DET_FACTOR_TEMPL_B.DET_FACTOR_TEMPL_CODE%TYPE,
p_condition_group_code IN ZX_CONDITION_GROUPS_B.CONDITION_GROUP_CODE%TYPE,
p_result IN BOOLEAN,
p_trx_line_index IN BINARY_INTEGER,
p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE);
SELECT TAX_RULE_ID,
TAX,
TAX_REGIME_CODE,
DET_FACTOR_TEMPL_CODE,
DETERMINING_FACTOR_CQ_CODE,
GEOGRAPHY_TYPE,
GEOGRAPHY_ID
FROM ZX_SCO_RULES_B_V r
WHERE SERVICE_TYPE_CODE = c_service_type_code
AND TAX = c_tax -- In phase 1, tax and regime should not be NULL
AND TAX_REGIME_CODE = c_tax_regime_code
AND c_tax_determine_date >= EFFECTIVE_FROM
AND (c_tax_determine_date <= EFFECTIVE_TO OR
EFFECTIVE_TO IS NULL)
AND System_Default_Flag <> 'Y'
AND (APPLICATION_ID = c_reference_application_id OR
APPLICATION_ID IS NULL)
AND Enabled_Flag = 'Y'
AND EXISTS (SELECT result_id
FROM ZX_PROCESS_RESULTS pr
WHERE pr.TAX_RULE_ID = r.TAX_RULE_ID
AND TAX_STATUS_CODE = c_tax_status_code
AND pr.Enabled_Flag = 'Y')
ORDER BY TAX, PRIORITY;
SELECT TAX_RULE_ID,
TAX,
TAX_REGIME_CODE,
DET_FACTOR_TEMPL_CODE ,
DETERMINING_FACTOR_CQ_CODE,
GEOGRAPHY_TYPE,
GEOGRAPHY_ID
FROM ZX_SCO_RULES_B_V r
WHERE SERVICE_TYPE_CODE = c_service_type_code
AND TAX = c_tax -- In phase 1, tax and regime should not be NULL
AND TAX_REGIME_CODE = c_tax_regime_code
AND c_tax_determine_date >= EFFECTIVE_FROM
AND (c_tax_determine_date <= EFFECTIVE_TO OR
EFFECTIVE_TO IS NULL)
AND System_Default_Flag <> 'Y'
AND RECOVERY_TYPE_CODE = c_recovery_type_code
AND (APPLICATION_ID = c_reference_application_id OR
APPLICATION_ID IS NULL)
AND EXISTS (SELECT result_id
FROM ZX_PROCESS_RESULTS pr
WHERE pr.TAX_RULE_ID = r.TAX_RULE_ID
AND pr.Enabled_Flag = 'Y')
AND Enabled_Flag = 'Y'
ORDER BY TAX, PRIORITY;
SELECT TAX_RULE_ID,
TAX,
TAX_REGIME_CODE,
DET_FACTOR_TEMPL_CODE,
DETERMINING_FACTOR_CQ_CODE,
GEOGRAPHY_TYPE,
GEOGRAPHY_ID
FROM ZX_SCO_RULES_B_V r
WHERE SERVICE_TYPE_CODE = c_service_type_code
AND TAX = c_tax -- In phase 1, tax and regime should not be NULL
AND TAX_REGIME_CODE = c_tax_regime_code
AND c_tax_determine_date >= EFFECTIVE_FROM
AND (c_tax_determine_date <= EFFECTIVE_TO OR
EFFECTIVE_TO IS NULL)
AND System_Default_Flag <> 'Y'
AND (APPLICATION_ID = c_reference_application_id OR
APPLICATION_ID IS NULL)
AND Enabled_Flag = 'Y'
AND EXISTS (SELECT result_id
FROM ZX_PROCESS_RESULTS pr
WHERE pr.TAX_RULE_ID = r.TAX_RULE_ID
AND pr.Enabled_Flag = 'Y')
ORDER BY TAX, PRIORITY;
SELECT PARENT_REGIME_CODE
FROM ZX_REGIME_RELATIONS
WHERE REGIME_CODE = c_tax_regime_code
-- AND c_tax_determine_date >= EFFECTIVE_FROM -- This effective period
-- AND (c_tax_determine_date <= EFFECTIVE_TO OR -- should be the same as
-- EFFECTIVE_TO IS NULL) -- the one in zx_regimes_b
ORDER BY PARENT_REG_LEVEL;
SELECT TAX_RULE_ID,
DET_FACTOR_TEMPL_CODE
FROM ZX_SCO_RULES_B_V r
WHERE SERVICE_TYPE_CODE = c_service_type_code
AND TAX = c_tax
AND TAX_REGIME_CODE = c_tax_regime_code
AND c_tax_determine_date >= EFFECTIVE_FROM
AND (c_tax_determine_date <= EFFECTIVE_TO OR
EFFECTIVE_TO IS NULL)
AND TAX_RULE_CODE = c_tax_rule_code
AND RECOVERY_TYPE_CODE = c_recovery_type_code
AND NVL(System_Default_Flag, 'N') = 'Y'
AND (APPLICATION_ID = c_reference_application_id
OR APPLICATION_ID IS NULL)
AND Enabled_Flag = 'Y'
ORDER BY TAX, PRIORITY;
SELECT TAX_RULE_ID,
det_factor_templ_code
FROM ZX_SCO_RULES_B_V
WHERE SERVICE_TYPE_CODE = c_service_type_code
AND TAX = c_tax
AND TAX_REGIME_CODE = c_tax_regime_code
AND c_tax_determine_date >= EFFECTIVE_FROM
AND (c_tax_determine_date <= EFFECTIVE_TO OR
EFFECTIVE_TO IS NULL)
AND TAX_RULE_CODE = c_tax_rule_code
AND RECOVERY_TYPE_CODE IS NULL
AND System_Default_Flag = 'Y'
AND APPLICATION_ID = c_reference_application_id
AND Enabled_Flag = 'Y'
ORDER BY TAX, PRIORITY;
SELECT TAX_RULE_ID,
TAX,
TAX_REGIME_CODE,
DET_FACTOR_TEMPL_CODE,
DETERMINING_FACTOR_CQ_CODE,
GEOGRAPHY_TYPE,
GEOGRAPHY_ID
FROM ZX_SCO_RULES_B_V r
WHERE SERVICE_TYPE_CODE = c_service_type_code
AND TAX = c_tax -- In phase 1, tax and regime should not be NULL
AND TAX_REGIME_CODE = c_tax_regime_code
AND c_tax_determine_date >= EFFECTIVE_FROM
AND (c_tax_determine_date <= EFFECTIVE_TO OR
EFFECTIVE_TO IS NULL)
AND System_Default_Flag <> 'Y'
AND EVENT_CLASS_MAPPING_ID = c_event_class_mapping_id
AND Enabled_Flag = 'Y'
AND EXISTS (SELECT result_id
FROM ZX_PROCESS_RESULTS pr
WHERE pr.TAX_RULE_ID = r.TAX_RULE_ID
AND pr.enabled_flag = 'Y')
ORDER BY PRIORITY;
SELECT TAX_RULE_ID,
TAX,
TAX_REGIME_CODE,
DET_FACTOR_TEMPL_CODE,
DETERMINING_FACTOR_CQ_CODE,
GEOGRAPHY_TYPE,
GEOGRAPHY_ID
FROM ZX_SCO_RULES_B_V r
WHERE SERVICE_TYPE_CODE = c_service_type_code
AND TAX = c_tax -- In phase 1, tax and regime should not be NULL
AND TAX_REGIME_CODE = c_tax_regime_code
AND c_tax_determine_date >= EFFECTIVE_FROM
AND (c_tax_determine_date <= EFFECTIVE_TO OR
EFFECTIVE_TO IS NULL)
AND System_Default_Flag <> 'Y'
AND TAX_EVENT_CLASS_CODE = c_tax_event_class_code
AND Enabled_Flag = 'Y'
AND EXISTS (SELECT result_id
FROM ZX_PROCESS_RESULTS pr
WHERE pr.TAX_RULE_ID = r.TAX_RULE_ID
AND pr.Enabled_Flag = 'Y')
ORDER BY PRIORITY;
SELECT TAX_RULE_ID,
TAX,
TAX_REGIME_CODE,
DET_FACTOR_TEMPL_CODE,
DETERMINING_FACTOR_CQ_CODE,
GEOGRAPHY_TYPE,
GEOGRAPHY_ID
FROM ZX_SCO_RULES_B_V r
WHERE SERVICE_TYPE_CODE = c_service_type_code
AND TAX = c_tax
AND TAX_REGIME_CODE = c_tax_regime_code
AND c_tax_determine_date >= EFFECTIVE_FROM
AND (c_tax_determine_date <= EFFECTIVE_TO OR
EFFECTIVE_TO IS NULL)
AND System_Default_Flag <> 'Y'
AND (TAX_EVENT_CLASS_CODE IS NULL OR
TAX_EVENT_CLASS_CODE = c_tax_event_class_code)
AND (EVENT_CLASS_MAPPING_ID IS NULL OR
EVENT_CLASS_MAPPING_ID = c_event_class_mapping_id)
AND Enabled_Flag = 'Y'
AND EXISTS (SELECT result_id
FROM ZX_PROCESS_RESULTS pr
WHERE pr.TAX_RULE_ID = r.TAX_RULE_ID
AND pr.Enabled_Flag = 'Y')
ORDER BY EVENT_CLASS_MAPPING_ID NULLS LAST,
TAX_EVENT_CLASS_CODE NULLS LAST,
PRIORITY;
SELECT TAX_RULE_ID, TAX, TAX_REGIME_CODE, DET_FACTOR_TEMPL_CODE,
DETERMINING_FACTOR_CQ_CODE,
GEOGRAPHY_TYPE,
GEOGRAPHY_ID
FROM ZX_SCO_RULES_B_V r
WHERE SERVICE_TYPE_CODE = c_service_type_code
AND TAX IS NULL
AND TAX_REGIME_CODE = c_tax_regime_code
AND c_tax_determine_date >= EFFECTIVE_FROM
AND (c_tax_determine_date <= EFFECTIVE_TO OR
EFFECTIVE_TO IS NULL)
AND (RECOVERY_TYPE_CODE = c_recovery_type_code OR
RECOVERY_TYPE_CODE IS NULL)
AND NVL(System_Default_Flag, 'N') <> 'Y'
AND (APPLICATION_ID = c_reference_application_id OR
APPLICATION_ID IS NULL)
AND Enabled_Flag = 'Y'
AND EXISTS (SELECT result_id
FROM ZX_PROCESS_RESULTS pr
WHERE pr.TAX_RULE_ID = r.TAX_RULE_ID)
ORDER BY PRIORITY;
SELECT s.CONDITION_GROUP_ID, s.CONDITION_GROUP_CODE, More_Than_Max_Cond_Flag,
Determining_Factor_Class1_Code, Determining_Factor_Cq1_Code,
Data_Type1_Code, DETERMINING_FACTOR_CODE1,
Operator1_Code, NUMERIC_VALUE1, DATE_VALUE1, ALPHANUMERIC_VALUE1,
VALUE_LOW1, VALUE_HIGH1, TAX_PARAMETER_CODE1,
Determining_Factor_Class2_Code, Determining_Factor_Cq2_Code,
Data_Type2_Code, DETERMINING_FACTOR_CODE2,
Operator2_Code, NUMERIC_VALUE2, DATE_VALUE2, ALPHANUMERIC_VALUE2,
VALUE_LOW2, VALUE_HIGH2, TAX_PARAMETER_CODE2,
Determining_Factor_Class3_Code, Determining_Factor_Cq3_Code,
Data_Type3_Code, DETERMINING_FACTOR_CODE3,
Operator3_Code, NUMERIC_VALUE3, DATE_VALUE3, ALPHANUMERIC_VALUE3,
VALUE_LOW3, VALUE_HIGH3, TAX_PARAMETER_CODE3,
Determining_Factor_Class4_Code, Determining_Factor_Cq4_Code,
Data_Type4_Code, DETERMINING_FACTOR_CODE4,
Operator4_Code, NUMERIC_VALUE4, DATE_VALUE4, ALPHANUMERIC_VALUE4,
VALUE_LOW4, VALUE_HIGH4, TAX_PARAMETER_CODE4,
Determining_Factor_Class5_Code, Determining_Factor_Cq5_Code,
Data_Type5_Code, DETERMINING_FACTOR_CODE5,
Operator5_Code, NUMERIC_VALUE5, DATE_VALUE5, ALPHANUMERIC_VALUE5,
VALUE_LOW5, VALUE_HIGH5, TAX_PARAMETER_CODE5,
Determining_Factor_Class6_Code, Determining_Factor_Cq6_Code,
Data_Type6_Code, DETERMINING_FACTOR_CODE6,
Operator6_Code, NUMERIC_VALUE6, DATE_VALUE6, ALPHANUMERIC_VALUE6,
VALUE_LOW6, VALUE_HIGH6, TAX_PARAMETER_CODE6,
Determining_Factor_Class7_Code, Determining_Factor_Cq7_Code,
Data_Type7_Code, DETERMINING_FACTOR_CODE7,
Operator7_Code, NUMERIC_VALUE7, DATE_VALUE7, ALPHANUMERIC_VALUE7,
VALUE_LOW7, VALUE_HIGH7, TAX_PARAMETER_CODE7,
Determining_Factor_Class8_Code, Determining_Factor_Cq8_Code,
Data_Type8_Code, DETERMINING_FACTOR_CODE8,
Operator8_Code, NUMERIC_VALUE8, DATE_VALUE8, ALPHANUMERIC_VALUE8,
VALUE_LOW8, VALUE_HIGH8, TAX_PARAMETER_CODE8,
Determining_Factor_Class9_Code, Determining_Factor_Cq9_Code,
Data_Type9_Code, DETERMINING_FACTOR_CODE9,
Operator9_Code, NUMERIC_VALUE9, DATE_VALUE9, ALPHANUMERIC_VALUE9,
VALUE_LOW9, VALUE_HIGH9, TAX_PARAMETER_CODE9,
Determining_Factor_Clas10_Code, Determining_Factor_Cq10_Code, Data_Type10_Code,
DETERMINING_FACTOR_CODE10, Operator10_Code, NUMERIC_VALUE10, DATE_VALUE10,
ALPHANUMERIC_VALUE10, VALUE_LOW10, VALUE_HIGH10,
TAX_PARAMETER_CODE10, CHART_OF_ACCOUNTS_ID, LEDGER_ID,
p.RESULT_ID,
s.constraint_id
FROM ZX_CONDITION_GROUPS_B s,
ZX_PROCESS_RESULTS p
WHERE s.DET_FACTOR_TEMPL_CODE = c_det_factor_templ_code AND
s.Enabled_Flag = 'Y' AND
s.CONDITION_GROUP_CODE = p.condition_group_CODE AND
p.TAX_RULE_ID = c_tax_rule_id AND
p.Enabled_Flag = 'Y' AND
(p.TAX_STATUS_CODE = c_tax_status_code OR
p.TAX_STATUS_CODE IS NULL )
ORDER BY p.PRIORITY;
insert_condition_group_result(p_det_factor_templ_code,
l_condition_group_code_tbl(j),
p_result,
p_structure_index,
p_event_class_rec );
SELECT Determining_Factor_Class_Code,
Determining_Factor_Cq_Code,
Data_Type_Code,
determining_factor_code,
TAX_PARAMETER_CODE,
Operator_Code,
NUMERIC_VALUE,
DATE_VALUE,
ALPHANUMERIC_VALUE,
VALUE_LOW,
VALUE_HIGH
FROM ZX_CONDITIONS
WHERE CONDITION_GROUP_CODE = c_condition_group_code
AND (Ignore_Flag <> 'Y' OR IGNORE_FLAG IS NULL);
g_determining_factor_class_tbl.DELETE;
g_determining_factor_cq_tbl.DELETE;
g_tax_parameter_code_tbl.DELETE;
g_data_type_tbl.DELETE;
g_determining_factor_code_tbl.DELETE;
g_operator_tbl.DELETE;
g_numeric_value_tbl.DELETE;
g_date_value_tbl.DELETE;
g_alphanum_value_tbl.DELETE;
g_value_low_tbl.DELETE;
g_value_high_tbl.DELETE;
SELECT item_type
INTO l_trx_alphanumeric_value
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.PRODUCT_ID(p_structure_index)
AND organization_id = nvl(ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.PRODUCT_ORG_ID(p_structure_index),
p_event_class_rec.internal_organization_id);
SELECT ptp.party_tax_profile_id
FROM zx_party_tax_profile ptp,
xle_entity_profiles le
WHERE ptp.party_type_code = 'FIRST_PARTY'
AND ptp.party_id = le.party_id
AND le.legal_entity_id = c_le_id;
SELECT owner_id_num,
classification_type_level_code,
start_position,
num_characters,
delimiter
FROM zx_fc_types_b
WHERE classification_type_code = c_classification_type_code;
SELECT primary_inventory_category_set
FROM zx_fc_country_defaults
WHERE country_code = c_country_code;
g_segment_array.DELETE;
SELECT TAX_CODE
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = c_product_id AND
ORGANIZATION_ID = c_item_org_id;
SELECT Tax_Classification_Code
FROM ZX_PARTY_TAX_PROFILE
WHERE PARTY_TAX_PROFILE_ID = c_party_tax_profile_id;
SELECT RESULT_ID,
CONDITION_GROUP_CODE,
TAX_STATUS_CODE,
LEGAL_MESSAGE_CODE,
MIN_TAX_AMT,
MAX_TAX_AMT,
MIN_TAXABLE_BASIS,
MAX_TAXABLE_BASIS,
MIN_TAX_RATE,
MAX_TAX_RATE,
Allow_Exemptions_Flag,
Allow_Exceptions_Flag,
Result_Type_Code,
NUMERIC_RESULT,
ALPHANUMERIC_RESULT,
STATUS_RESULT,
RATE_RESULT,
RESULT_API,
CONDITION_GROUP_ID,
CONDITION_SET_ID,
EXCEPTION_SET_ID,
TAX_RULE_ID
FROM ZX_PROCESS_RESULTS
WHERE RESULT_ID = p_result_id;
CURSOR select_tax_status_rate_code
(c_tax_regime_code VARCHAR2,
c_tax VARCHAR2,
c_tax_Rate_Code VARCHAR2,
c_tax_date VARCHAR2)
is
SELECT TAX_STATUS_CODE,
TAX_RATE_CODE
FROM ZX_SCO_RATES_B_V
WHERE TAX_REGIME_CODE = c_tax_Regime_Code
AND TAX = c_tax
AND TAX_RATE_CODE = c_tax_rate_code
AND (TAX_CLASS = 'OUTPUT' OR TAX_CLASS IS NULL)
--AND default_flg_effective_from <= c_tax_date
AND effective_from <= c_tax_date
AND (default_flg_effective_to >= c_tax_date
OR default_flg_effective_to IS NULL)
--AND NVL(Default_Rate_Flag, 'N') = 'Y'
AND NVL(Active_Flag, 'N') = 'Y'
ORDER BY tax_class NULLS LAST, subscription_level_code; -- Bug#5395227
select service_type_code into l_service_type_code
FROM zx_rules_b
where tax_rule_id = p_zx_result_rec.tax_rule_id;
Open select_tax_status_rate_code (p_tax_regime_code, p_tax, l_override_tax_rate_code,
p_tax_determine_date);
fetch select_tax_status_rate_code into l_tax_status_code, l_tax_rate_code;
If select_tax_status_rate_code%NOTFOUND then
--A record does not exist with that tax rate code for the given tax.
--Raise error;
IF select_tax_status_rate_code%isopen then
Close select_tax_status_rate_code;
Close select_tax_status_rate_code;
l_action_rec_tbl.delete;
Open select_tax_status_rate_code (p_tax_regime_code, p_tax, l_override_tax_rate_code,
p_tax_determine_date);
fetch select_tax_status_rate_code into l_tax_status_code, l_tax_rate_code;
If select_tax_status_rate_code%NOTFOUND then
--A record does not exist with that tax rate code for the given tax.
--Raise error;
IF select_tax_status_rate_code%isopen then
Close select_tax_status_rate_code;
Close select_tax_status_rate_code;
PROCEDURE insert_condition_group_result(
p_det_factor_templ_code IN ZX_DET_FACTOR_TEMPL_B.det_factor_templ_code%TYPE,
p_condition_group_code IN ZX_CONDITION_GROUPS_B.condition_group_CODE%TYPE,
p_result IN BOOLEAN,
p_trx_line_index IN BINARY_INTEGER,
p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE
)
IS
i BINARY_INTEGER;
'ZX.PLSQL.ZX_TDS_RULE_BASE_DETM_PVT.insert_condition_group_result.BEGIN',
'ZX_TDS_RULE_BASE_DETM_PVT: insert_condition_group_result(+)');
'ZX.PLSQL.ZX_TDS_RULE_BASE_DETM_PVT.insert_condition_group_result.END',
'ZX_TDS_RULE_BASE_DETM_PVT: insert_condition_group_result(-)');
'ZX.PLSQL.ZX_TDS_RULE_BASE_DETM_PVT.insert_condition_group_result',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
END insert_condition_group_result;
SELECT param.tax_parameter_code
FROM zx_det_factor_templ_dtl dtl,
zx_det_factor_templ_b templ,
zx_event_class_params param
WHERE templ.det_factor_templ_code = c_det_factor_templ_cd
AND dtl.det_factor_templ_id = templ.det_factor_templ_id
AND param.event_class_mapping_id = c_event_class_mapping_id
AND param.tax_parameter_code = dtl.tax_parameter_code
AND NOT EXISTS
(SELECT 1
FROM zx_condition_groups_b zcg,
zx_conditions zc
WHERE zcg.det_factor_templ_code = c_det_factor_templ_cd
AND zcg.enabled_flag = 'Y'
AND zc.condition_group_code = zcg.condition_group_code
AND zc.determining_factor_code = dtl.determining_factor_code
AND zc.determining_factor_class_code = dtl.determining_factor_class_code
AND NVL(zc.determining_factor_cq_code, 'x') = NVL(dtl.determining_factor_cq_code, 'x')
AND zc.ignore_flag = 'Y'
);