[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p.output_tax_code,
p.retention_tax_code
-- FROM fnd_lookups l, pa_projects p bug#4574838
FROM zx_output_classifications_v l,
pa_projects p
WHERE p.project_id = c_project_id
AND l.lookup_code = decode(c_retention_flag,'TRUE',p.retention_tax_code,p.output_tax_code)
AND l.org_id IN (c_org_id, -99)
AND l.enabled_flag = 'Y'
AND (l.start_date_active <= c_trx_date OR
l.start_date_active is null)
AND (l.end_date_active >= c_trx_date OR
l.end_date_active is null)
AND rownum = 1
ORDER BY l.org_id desc;
SELECT l.lookup_code
FROM zx_output_classifications_v l,
-- fnd_lookups l, bug#4574838
pa_expenditure_type_ous_all type,
pa_expenditure_items_all item
WHERE item.expenditure_item_id = c_expenditure_item_id
AND item.expenditure_type = type.expenditure_type
AND item.org_id = c_org_id
AND item.org_id = type.org_id
AND l.lookup_code = type.output_tax_classification_code
AND l.org_id IN (c_org_id, -99)
AND l.enabled_flag = 'Y'
AND (l.start_date_active <= c_trx_date OR
l.start_date_active is null)
AND (l.end_date_active >= c_trx_date OR
l.end_date_active is null)
AND rownum = 1
ORDER BY l.org_id desc
;
SELECT ev.tax_code
FROM pa_event_output_tax ev
WHERE ev.event_id = c_event_id;
| GL (E.g. Trx line Insert)
| ACCT_DIST - Use Accounting Distribution lines to default tax
| code from GL. (E.g. Trx Line Update)
| GL_ACCT_FIXUP - If tax code should be enforced from Natural Account
| Ignore hierarchy and default tax code from GL only
| using Revenue Account distributions that do NOT
| allow override of tax code.
| (E.g. Tax code fixup on Transaction completion)
| GL_ACCT_FIRST - If tax code should be enforced from Natural Account
| FIRST default tax code from GL using Revenue
| Account distributions that do NOT allow override
| of tax code.
| If not found, default thru the hierarchy using
| Accounting distributions.
| (E.g. Autoinvoice and Recurring Invoice)
|
| RETURNS
| tax_code - if there is a valid active one
| vat_tax_id - Used by AR
| amount_includes_tax_flag - Used by AR Trx Workbench
| amount_includes_tax_override - Used by AR Trx Workbench
| exception NO_DATA_FOUND when no tax code found
|
| EXAMPLE PL/SQL BLOCK
| Calling get_default_tax_classification() the procedure will return tax
| classification code or an exception
|
| HISTORY
| 21-Jun-04 Sudhir Sekuri Created.
|
*----------------------------------------------------------------------------*/
--
-- OE/OSM/AR Tax code defaulting API
--
PROCEDURE get_default_tax_classification (
p_ship_to_site_use_id IN NUMBER,
p_bill_to_site_use_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
-- p_warehouse_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_trx_date IN DATE,
p_trx_type_id IN NUMBER,
p_tax_classification_code OUT NOCOPY VARCHAR2 ,
-- p_vat_tax_id OUT NOCOPY NUMBER,
-- p_amt_incl_tax_flag OUT NOCOPY VARCHAR2,
-- p_amt_incl_tax_override OUT NOCOPY VARCHAR2,
p_cust_trx_id IN NUMBER default null,
p_cust_trx_line_id IN NUMBER default null,
p_customer_id IN NUMBER default null,
p_memo_line_id IN NUMBER default null,
APPL_SHORT_NAME IN VARCHAR2 default null,
FUNC_SHORT_NAME IN VARCHAR2 default null,
p_party_flag IN VARCHAR2 default null,
p_party_location_id IN VARCHAR2 default null,
p_entity_code IN VARCHAR2,
p_event_class_code IN VARCHAR2,
p_application_id IN NUMBER,
p_internal_organization_id IN NUMBER,
p_ccid IN NUMBER default null
) IS
-- v_tax_classification_code VARCHAR2(50) := NULL;
SELECT t.tax_code,
t.vat_tax_id,
amount_includes_tax_flag,
amount_includes_tax_override
FROM AR_VAT_TAX T
WHERE t.tax_code = c_tax_code
AND t.set_of_books_id = c_set_of_books_id
AND c_trx_date between t.start_date and
nvl(t.end_date, c_trx_date)
AND nvl(t.enabled_flag, 'Y') = 'Y'
AND nvl(t.tax_class, 'O') = 'O';
select enforce_tax_from_acct_flag
from zx_evnt_cls_options
where application_id = c_application_id
and entity_code = c_entity_code
and event_class_code = c_event_class_code
and first_pty_org_id = (Select party_tax_profile_id
From zx_party_tax_profile
where party_id = c_org_id
and party_type_code = 'OU')
and c_trx_date >= effective_from
and c_trx_date <= nvl(effective_to,c_trx_date)
and enabled_flag = 'Y';
select lookup_code
from zx_output_classifications_v
where lookup_code = c_tax_code
AND org_id IN (c_org_id, -99)
AND enabled_flag = 'Y'
AND (start_date_active <= c_trx_date OR
start_date_active is null)
AND (end_date_active >= c_trx_date OR
end_date_active is null)
AND rownum = 1
ORDER BY org_id desc;
SELECT su.tax_code
FROM HZ_CUST_SITE_USES_ALL su, ZX_OUTPUT_CLASSIFICATIONS_V l
WHERE su.site_use_id = c_site_use_id
AND su.org_id = c_org_id
AND l.lookup_code = su.tax_code
AND l.org_id IN (c_org_id, -99)
AND l.enabled_flag = 'Y'
AND (l.start_date_active <= c_trx_date OR
l.start_date_active is null)
AND (l.end_date_active >= c_trx_date OR
l.end_date_active is null)
AND rownum = 1
ORDER BY l.org_id desc;
SELECT CUST_ACCT.cust_account_id
FROM HZ_CUST_ACCOUNTS CUST_ACCT,
HZ_CUST_ACCT_SITES CUST_ACCT_SITES,
HZ_CUST_SITE_USES CUST_SITE_USES
WHERE CUST_ACCT.cust_account_id = CUST_ACCT_SITES.cust_account_id
AND CUST_ACCT_SITES.cust_acct_site_id = CUST_SITE_USES.cust_acct_site_id
AND CUST_SITE_USES.site_use_id = c_site_use_id;
SELECT c.tax_code
FROM HZ_CUST_ACCOUNTS_ALL c, ZX_OUTPUT_CLASSIFICATIONS_V l
WHERE c.cust_account_id = c_customer_id
AND c.org_id = c_org_id
AND l.lookup_code = c.tax_code
AND l.org_id IN (c_org_id, -99)
AND l.enabled_flag = 'Y'
AND (l.start_date_active <= c_trx_date OR
l.start_date_active is null)
AND (l.end_date_active >= c_trx_date OR
l.end_date_active is null)
AND rownum = 1
ORDER BY l.org_id desc;
SELECT m.tax_code
-- FROM fnd_lookups l, AR_MEMO_LINES M bug#4574838
FROM zx_output_classifications_v l, AR_MEMO_LINES m
WHERE m.memo_line_id = c_memo_line_id
AND l.lookup_code = m.tax_code
AND l.org_id IN (c_org_id, -99)
AND l.enabled_flag = 'Y'
AND (l.start_date_active <= c_trx_date OR
l.start_date_active is null)
AND (l.end_date_active >= c_trx_date OR
l.end_date_active is null)
--AND l.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
AND rownum = 1
ORDER BY l.org_id desc;
SELECT i.tax_code
-- FROM fnd_lookups l, MTL_SYSTEM_ITEMS bug#4574838
FROM zx_output_classifications_v l, MTL_SYSTEM_ITEMS i
WHERE i.inventory_item_id = c_item_id
AND i.organization_id = c_organization_id
AND l.lookup_code = i.tax_code
AND l.org_id IN (c_org_id, -99)
AND l.enabled_flag = 'Y'
AND (l.start_date_active <= c_trx_date OR
l.start_date_active is null)
AND (l.end_date_active >= c_trx_date OR
l.end_date_active is null)
-- AND l.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
AND rownum = 1
ORDER BY l.org_id desc;
SELECT loc.country
FROM HZ_CUST_ACCT_SITES acct_site,
HZ_PARTY_SITES party_site,
HZ_LOCATIONS loc,
HZ_CUST_SITE_USES site_uses
WHERE site_uses.site_use_id = c_site_use_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id;
SELECT country
FROM HZ_LOCATIONS
WHERE location_id = c_party_location_id;
Select distinct tax_classification_code into :b_tax_code
from gl_code_combinations gcc,
zx_acct_tx_cls_defs_all gtoa
where code_combination_id = :l_ccid ' ||
' and gcc.'||tax_gbl_rec.natural_acct_column||
' = gtoa.account_segment_value
and gtoa.ledger_id = :l_set_of_books_id
and gtoa.org_id = :l_org_id
and gtoa.tax_class = ''O''
and nvl(gtoa.allow_tax_code_override_flag, ''Y'') = ''N'';
Select distinct tax_classification_code into :b_tax_code
from gl_code_combinations gcc,
zx_acct_tx_cls_defs_all gtoa
where code_combination_id = :l_ccid '||
'and gcc.'||tax_gbl_rec.natural_acct_column||
' = gtoa.account_segment_value
and gtoa.ledger_id = :l_set_of_books_id
and gtoa.org_id = :l_org_id
and gtoa.tax_class = ''O'';
SELECT org_id,
def_option_hier_1_code,
def_option_hier_2_code,
def_option_hier_3_code,
def_option_hier_4_code,
def_option_hier_5_code,
def_option_hier_6_code,
def_option_hier_7_code,
home_country_default_flag,
tax_classification_code,
tax_method_code,
inclusive_tax_used_flag,
tax_use_customer_exempt_flag,
tax_use_product_exempt_flag,
tax_use_loc_exc_rate_flag,
tax_allow_compound_flag,
tax_rounding_rule,
tax_precision,
tax_minimum_accountable_unit,
use_tax_classification_flag,
allow_tax_rounding_ovrd_flag
FROM zx_product_options_all
WHERE org_id = c_org_id
AND application_id = c_application_id
AND event_class_mapping_id IS NULL;
SELECT org_id,
def_option_hier_1_code,
def_option_hier_2_code,
def_option_hier_3_code,
def_option_hier_4_code,
def_option_hier_5_code,
def_option_hier_6_code,
def_option_hier_7_code,
home_country_default_flag,
tax_classification_code,
tax_method_code,
inclusive_tax_used_flag,
tax_use_customer_exempt_flag,
tax_use_product_exempt_flag,
tax_use_loc_exc_rate_flag,
tax_allow_compound_flag,
tax_rounding_rule,
tax_precision,
tax_minimum_accountable_unit,
use_tax_classification_flag,
allow_tax_rounding_ovrd_flag
FROM zx_product_options_all
WHERE org_id = c_org_id
AND application_id = c_application_id
AND event_class_mapping_id IS NULL;
SELECT default_country
FROM ar_system_parameters_all
WHERE org_id = c_org_id;