[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(purch_encumbrance_flag,'N')
into x_enc_flag
from financials_system_parameters ;
SELECT expense_accrual_code
INTO x_expense_accrual_code
FROM po_system_parameters;
select distinct 'Y' valid
into x_valid_flag
from po_lookup_codes
where lookup_type = 'DESTINATION TYPE'
and ( ( nvl( x_item_status,'D') = 'D'
and lookup_code = 'EXPENSE')
or ( nvl( x_item_status,'D') = 'E'
and lookup_code <> 'SHOP FLOOR')
or ( nvl( x_item_status,'D') = 'O'
and lookup_code = 'SHOP FLOOR') )
and ( ( nvl( x_accrue_on_receipt_flag,'Y') = 'N'
and lookup_code = 'EXPENSE')
OR p_transaction_flow_header_id is NOT NULL --
or (nvl( x_accrue_on_receipt_flag,'Y') = 'Y'
and (( x_expense_accrual_code = 'PERIOD END'
and lookup_code <> 'EXPENSE')
or x_expense_accrual_code <> 'PERIOD END')))
and lookup_code= x_destination_type_code;
SELECT distinct 'Y'
INTO x_valid_flag
FROM hr_employees_current_v
WHERE employee_id = x_deliver_to_person_id;
SELECT distinct 'Y'
INTO x_valid_flag
FROM HR_LOCATIONS
WHERE nvl(inventory_organization_id,x_ship_to_organization_id) = x_ship_to_organization_id
AND nvl(inactive_date, trunc(sysdate + 1)) > trunc(sysdate)
AND location_id = x_deliver_to_location_id;
SELECT distinct 'Y'
INTO x_valid_flag
FROM HZ_LOCATIONS
WHERE nvl(address_expiration_date, trunc(sysdate + 1)) > trunc(sysdate)
AND location_id = x_deliver_to_location_id;
select distinct 'Y'
into x_valid_flag
from mtl_secondary_inventories msub
where msub.organization_id = nvl(x_ship_to_organization_id, msub.organization_id)
and nvl(msub.disable_date, trunc(sysdate+1)) > trunc(sysdate)
and (x_item_id is null
or
(x_item_id is not null
and exists (select null
from mtl_system_items msi
where msi.organization_id = nvl(x_ship_to_organization_id, msi.organization_id)
and msi.inventory_item_id = x_item_id
and (msi.restrict_subinventories_code = 2
or (msi.restrict_subinventories_code = 1
and exists (select null
from mtl_item_sub_inventories mis
where mis.organization_id = nvl(x_ship_to_organization_id , mis.organization_id)
and mis.inventory_item_id = msi.inventory_item_id
and mis.secondary_inventory = msub.secondary_inventory_name))))))
and msub.secondary_inventory_name = x_destination_subinventory;
SELECT distinct 'Y' INTO x_valid
FROM org_organization_definitions ood
WHERE x_org_id = ood.organization_id
AND ood.set_of_books_id = x_sob_id;
SELECT distinct 'Y' into x_valid
FROM wip_repetitive_schedules wrs
WHERE wrs.organization_id=x_destination_organization_id
AND wrs.wip_entity_id = x_wip_entity_id
AND wrs.repetitive_schedule_id =
x_wip_repetitive_schedule_id
AND wrs.status_type IN (3,4,6)
AND x_wip_repetitive_schedule_id is not null;
SELECT distinct 'Y' into x_valid
FROM wip_discrete_jobs wdj
WHERE wdj.organization_id=x_destination_organization_id
AND wdj.wip_entity_id = x_wip_entity_id
AND wdj.status_type IN (3,4,6)
AND x_wip_repetitive_schedule_id is NULL;
SELECT distinct 'Y' into x_valid
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = x_account_id
AND gcc.enabled_flag = 'Y'
AND trunc(nvl(x_gl_date,SYSDATE)) BETWEEN
trunc(nvl(start_date_active,
nvl(x_gl_date,SYSDATE) ))
AND
trunc(nvl (end_date_active,
nvl(x_gl_date,SYSDATE) ))
AND gcc.detail_posting_allowed_flag = 'Y'
AND gcc.chart_of_accounts_id=
x_chart_of_accounts_id
AND gcc.summary_flag = 'N';
/* Bug: 1786105 For all the following three validation select, the table/views:
mtl_projects_v
pa_expenditure_types
per_organization_units
needs to be replaced with
pa_projects_expend_v
pa_expenditure_types_expend_v
pa_organizations_expend_v
or else the projects validation would fail.
*/
/* Bug 2892199 Added expenditure item date validation */
IF x_destination_type_code = 'EXPENSE' then
--bug 14662559: set 3 different message name for these 3 validation.
begin
SELECT 'Y' into x_valid1
FROM pa_projects_expend_v pap,
pa_tasks_expend_v pat
WHERE pap.project_id = x_project_id
AND pap.project_id = pat.project_id
AND pat.task_id = x_task_id
AND pat.chargeable_flag = 'Y'
AND x_expenditure_item_date BETWEEN
nvl(pap.start_date,x_expenditure_item_date)
AND nvl(pap.completion_date,x_expenditure_item_date)
AND x_expenditure_item_date BETWEEN
nvl(pat.start_date,x_expenditure_item_date)
AND nvl(pat.completion_date,x_expenditure_item_date) ;
SELECT 'Y' into x_valid2
FROM pa_expenditure_types_expend_v pet
WHERE pet.expenditure_type = x_expenditure_type
AND pet.system_linkage_function = 'VI';
SELECT 'Y' into x_valid3
FROM pa_organizations_expend_v pou
WHERE pou.organization_id=x_expenditure_organization_id;
SELECT 'Y' into x_valid2
FROM sys.dual
WHERE x_expenditure_type IS NULL
OR EXISTS( SELECT 'Valid Expenditure Type'
FROM pa_expenditure_types pet,
pa_expend_typ_sys_links sl -- Bug 9412338/9831707
WHERE pet.expenditure_type = x_expenditure_type
AND pet.expenditure_type = sl.expenditure_type --bug9412338/9831707
AND sl.system_linkage_function = 'VI' --bug9412338/9831707
);
SELECT 'Y' into x_valid3
FROM sys.dual
WHERE x_expenditure_organization_id IS NULL
OR EXISTS( SELECT 'Valid Expenditure Org'
FROM per_organization_units pou
WHERE pou.organization_id=
x_expenditure_organization_id
);