[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM PO_LINES
WHERE po_header_id = x_po_header_id
ORDER BY line_num;
SELECT *
FROM PO_LINE_LOCATIONS
WHERE po_line_id = x_po_line_id
AND shipment_type not in ('BLANKET','SCHEDULED')
ORDER BY shipment_num;
SELECT *
FROM PO_DISTRIBUTIONS POD
WHERE POD.line_location_id = x_line_location_id
AND POD.distribution_type <> 'AGREEMENT' --
ORDER BY distribution_num;
SELECT *
FROM PO_DISTRIBUTIONS POD
WHERE POD.po_header_id = x_po_header_id
AND POD.distribution_type = 'AGREEMENT'
ORDER BY distribution_num;
SELECT fsp.ship_to_location_id,
fsp.bill_to_location_id,
fsp.ship_via_lookup_code,
fsp.fob_lookup_code,
fsp.freight_terms_lookup_code,
fsp.terms_id
INTO x_ship_to_location_id,
x_bill_to_location_id,
x_ship_via_lookup_code,
x_fob_lookup_code,
x_freight_terms_lu_code,
x_terms_id
FROM FINANCIALS_SYSTEM_PARAMETERS fsp;
SELECT null,
null,
null,
null,
null,
terms_id
INTO x_ship_to_location_id,
x_bill_to_location_id,
x_ship_via_lookup_code,
x_fob_lookup_code,
x_freight_terms_lu_code,
x_terms_id
FROM PO_VENDORS
WHERE vendor_id = x_vendor_id;
SELECT ship_to_location_id,
bill_to_location_id,
ship_via_lookup_code,
fob_lookup_code,
freight_terms_lookup_code,
terms_id
INTO x_ship_to_location_id,
x_bill_to_location_id,
x_ship_via_lookup_code,
x_fob_lookup_code,
x_freight_terms_lu_code,
x_terms_id
FROM PO_VENDOR_SITES
WHERE vendor_id = x_vendor_id
AND vendor_site_id = x_vendor_site_id;
SELECT distinct 'Y'
INTO x_valid_flag
FROM po_buyers_val_v
WHERE employee_id = (
SELECT agent_id FROM po_agents
WHERE sysdate between nvl(start_date_active, sysdate-1)
and nvl(end_date_active, sysdate+1)
AND agent_id = x_buyer_id);
SELECT distinct 'Y'
INTO x_valid_flag
FROM PO_VENDORS
WHERE vendor_id = x_vendor_id
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(start_date_active, SYSDATE-1)
AND nvl(end_date_active, SYSDATE+1);
SELECT distinct 'Y'
INTO x_valid_flag
FROM PO_VENDOR_SITES
WHERE vendor_site_id = x_vendor_site_id
AND vendor_id = x_vendor_id
AND nvl(rfq_only_site_flag,'N') <> 'Y'
AND purchasing_site_flag = 'Y'
AND SYSDATE < nvl(inactive_date, SYSDATE + 1);
SELECT distinct 'Y'
INTO x_valid_flag
FROM PO_VENDOR_CONTACTS
WHERE vendor_contact_id = x_vendor_contact_id
AND vendor_site_id = x_vendor_site_id
AND SYSDATE < nvl(inactive_date, SYSDATE+1);
SELECT distinct 'Y'
INTO x_valid_flag
FROM PO_SHIP_TO_LOC_ORG_V
WHERE location_id = x_ship_to_location_id
AND (set_of_books_id IS NULL OR set_of_books_id = x_sob_id);
SELECT distinct 'Y'
INTO x_valid_flag
FROM HR_BILLING_LOCATIONS_PO_V
WHERE location_id = x_bill_to_location_id;
SELECT distinct 'Y'
INTO x_valid_flag
FROM PO_LOOKUP_CODES
WHERE lookup_type = 'FOB'
AND SYSDATE < nvl(inactive_date, SYSDATE+1)
AND lookup_code = x_fob_lookup_code;
SELECT distinct 'Y'
INTO x_valid_flag
FROM PO_LOOKUP_CODES
WHERE lookup_type = 'FREIGHT TERMS'
AND SYSDATE < nvl(inactive_date, SYSDATE+1)
AND lookup_code = x_freight_terms_lu_code;
SELECT distinct 'Y'
INTO x_valid_flag
FROM AP_TERMS_VAL_V
WHERE term_id = x_terms_id;
SELECT distinct 'Y'
INTO x_valid_flag
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = x_item_id
AND purchasing_enabled_flag = 'Y'
AND organization_id = x_inv_org_id;
SELECT distinct 'Y'
INTO x_valid_flag
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.inventory_item_id = x_item_id
AND MSI.organization_id = x_inv_org_id
AND (MSI.outside_operation_flag <> 'Y'
OR (MSI.outside_operation_flag = 'Y'
AND EXISTS (SELECT 'op line type'
FROM PO_LINE_TYPES PLT
WHERE PLT.line_type_id = x_line_type_id
AND PLT.outside_operation_flag = 'Y')
)
);
SELECT distinct 'Y'
INTO x_valid_flag
FROM PO_LINE_TYPES
WHERE line_type_id = x_line_type_id
AND order_type_lookup_code <> 'AMOUNT';
SELECT distinct 'Y'
INTO x_valid_flag
FROM MTL_ITEM_REVISIONS_ORG_VAL_V
WHERE revision = x_item_revision
AND inventory_item_id = x_item_id;
SELECT MIC.category_id
INTO x_category_id
FROM MTL_ITEM_CATEGORIES MIC,
MTL_DEFAULT_SETS_VIEW MDSV
WHERE MIC.inventory_item_id = x_item_id
AND MIC.organization_id = x_inv_org_id
AND MIC.category_set_id = MDSV.category_set_id
AND MDSV.functional_area_id = 2;
SELECT distinct 'Y'
INTO x_valid_flag
FROM MTL_ITEM_CATEGORIES MIC,
MTL_DEFAULT_SETS_VIEW MDSV
WHERE MIC.category_id = x_category_id
AND MIC.inventory_item_id = x_item_id
AND MIC.organization_id = x_inv_org_id
AND MIC.category_set_id = MDSV.category_set_id
AND MDSV.functional_area_id = 2;
SELECT distinct 'Y'
INTO x_valid_flag
FROM MTL_CATEGORY_SET_VALID_CATS MCSVC,
MTL_DEFAULT_SETS_VIEW MDSV
WHERE MCSVC.category_id = x_category_id
AND MCSVC.category_set_id = MDSV.category_set_id
AND MDSV.functional_area_id = 2;
select validate_flag,
category_set_id
INTO l_validate_flag,
l_category_set_id
FROM MTL_DEFAULT_SETS_VIEW MDSV
where MDSV.functional_area_id = 2;
SELECT distinct 'Y'
INTO x_valid_flag
FROM MTL_CATEGORY_SET_VALID_CATS MCSVC,
MTL_CATEGORIES_VL MCV
WHERE MCSVC.category_id = x_category_id
AND MCSVC.category_set_id = l_category_set_id
AND MCV.category_id = MCSVC.category_id
AND sysdate < nvl(mcv.disable_date, sysdate+1)
AND mcv.enabled_flag = 'Y';
SELECT distinct 'Y'
INTO x_valid_flag
FROM MTL_CATEGORIES_VL MCV
WHERE MCV.category_id = x_category_id
AND sysdate < nvl(mcv.disable_date, sysdate+1)
AND mcv.enabled_flag = 'Y';
SELECT distinct 'Y'
INTO x_valid_flag
FROM gl_code_combinations gcc,
gl_sets_of_books sob
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.summary_flag = 'N'
AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
AND sob.set_of_books_id = x_sob_id;
SELECT distinct 'Y'
INTO x_valid_flag
FROM pa_projects_expend_v
WHERE project_id=x_project_id;
SELECT distinct 'Y'
INTO x_valid_flag
FROM mtl_project_v m,
pjm_project_parameters p
WHERE p.organization_id = x_ship_to_organization_id
AND m.project_id = p.project_id
AND m.project_id = x_project_id;
select distinct 'Y'
into x_valid_flag
from pa_tasks_expend_v
where project_id = x_project_id
and task_id = x_task_id;
select distinct 'Y'
into x_valid_flag
from pa_projects_all prj,
pa_tasks tsk
where
prj.project_id = x_project_id
and prj.project_id = tsk.project_id
and tsk.task_id = x_task_id
and x_exp_item_date between
nvl(prj.start_date,x_exp_item_date)
and nvl(prj.completion_date,x_exp_item_date)
and x_exp_item_date between
nvl(tsk.start_date,x_exp_item_date)
and nvl(tsk.completion_date,x_exp_item_date);
SELECT
'Y' into x_valid_flag
FROM
pa_expenditure_types_expend_v et
WHERE
system_linkage_function = 'VI' and
(et.project_id = x_project_id or et.project_id is null) and
trunc(sysdate) between nvl(et.expnd_typ_start_date_active, trunc(sysdate)) and
nvl(et.expnd_typ_end_date_Active, trunc(sysdate))
and et.expenditure_type = x_exp_type;
select 'Y'
into x_valid_flag
from pa_organizations_expend_v pao
where pao.active_flag = 'Y'
and pao.organization_id = x_org_id ;
/*SELECT expense_accrual_code
INTO x_expense_accrual_code
FROM po_system_parameters; Bug7351781 */
SELECT consigned_flag
INTO x_consigned_flag
FROM po_line_locations
WHERE line_location_id = x_line_location_id;
SELECT NVL(PLT.OUTSIDE_OPERATION_FLAG,'N')
INTO x_osp_line_flag
FROM PO_LINE_TYPES PLT, PO_LINES POL
WHERE PLT.LINE_TYPE_ID = POL.LINE_TYPE_ID
AND POL.PO_LINE_ID = x_po_line_id;
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 <> 'INVENTORY' )
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_consigned_flag,'N') = 'Y' and lookup_code = 'INVENTORY' )
OR( p_transaction_flow_header_id IS NOT NULL ) --< Bug 3546252 >
or( nvl(x_consigned_flag,'N') = 'N'
and ( (nvl(x_accrue_on_receipt_flag,'Y') = 'N'
and lookup_code ='EXPENSE')
or ( nvl(x_accrue_on_receipt_flag,'Y') = 'Y'
and lookup_code IN ('EXPENSE','INVENTORY','SHOP FLOOR') ) ) ) ) --bug7351781
and lookup_code= x_destination_type_code;
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 FLO-OR') )
and ( ( nvl( x_accrue_on_receipt_flag,'Y') = 'N'
and ( lookup_code = 'EXPENSE' or
x_consigned_flag = 'Y') )
OR (p_transaction_flow_header_id IS NOT NULL) --< Bug 3546252 >
OR (nvl(x_accrue_on_receipt_flag,'Y') = 'N'
and lookup_code ='EXPENSE')
OR ( nvl(x_accrue_on_receipt_flag,'Y') = 'Y'
and lookup_code IN ('EXPENSE','INVENTORY','SHOP FLOOR'))) --bug7351781
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 entity_type
into x_entity_type
from wip_entities
where wip_entity_id=x_wip_entity_id;
select count(*)
into x_count
from wip_entities we,
wip_discrete_jobs wdj
where we.wip_entity_id = wdj.wip_entity_id
and we.entity_type = 6
and wdj.status_type in (3,4,6);
select count(*) into x_count
from wip_osp_jobs_val_v job, pa_tasks task
where job.wip_entity_id = x_wip_entity_id
and job.organization_id = x_destination_organization_id
and task.task_id (+) = job.task_id;
select count(*)
into x_count
from wip_osp_lines_val_v
where line_id = x_wip_line_id
and organization_id = x_destination_organization_id;
select count(*)
INTO x_count
from WIP_OPERATIONS WO,
BOM_STANDARD_OPERATIONS BSO
WHERE WO.WIP_ENTITY_ID=x_wip_entity_id
AND operation_seq_num=x_wip_operation_seq_num
AND WO.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID (+) --Bug 3557910 Added Outer JOIN
AND nvl(BSO.OPERATION_TYPE,1) =1
AND BSO.line_id is null;
select count(*) into x_count
from wip_osp_operations_val_v
where operation_seq_num = x_wip_operation_seq_num
and organization_id = x_destination_organization_id
and ((wip_entity_id = x_wip_entity_id) and x_wip_line_id is null);
select count(*) into x_count
from wip_osp_operations_val_v
where operation_seq_num = x_wip_operation_seq_num
and organization_id = x_destination_organization_id
and repetitive_schedule_id = x_wip_repetitive_schedule_id;
select count(*) into x_count
from wip_osp_resources_val_v
where resource_seq_num = x_wip_resource_seq_num
and organization_id = x_destination_organization_id
and ((wip_entity_id = x_wip_entity_id and x_wip_line_id is null)
or repetitive_schedule_id = x_wip_repetitive_schedule_id)
and operation_seq_num = x_wip_operation_seq_num;
SELECT distinct 'Y'
INTO x_valid_flag
FROM HR_LOCATIONS_ALL
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 'Y'
INTO x_valid_flag
FROM po_headers_all POH
WHERE POH.po_header_id = p_contract_id
AND POH.type_lookup_code = 'CONTRACT'
AND POH.authorization_status = 'APPROVED'
AND NVL(POH.closed_code, 'OPEN') = 'OPEN'
AND NVL(POH.cancel_flag, 'N') <> 'Y'
AND NVL(POH.frozen_flag, 'N') <> 'Y';
SELECT 'Y'
INTO l_valid_flag
FROM po_headers_all
WHERE po_header_id = p_from_header_id
AND nvl(closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(cancel_flag,'N') <> 'Y';
SELECT pj.name,
pb.name
INTO l_job_name,
l_bg_name
FROM per_jobs_vl pj,
per_business_groups_perf pb
WHERE pj.business_group_id = pb.business_group_id
AND pj.job_id = p_job_id;
SELECT 'Y'
INTO l_valid_flag
FROM per_jobs
WHERE job_id = p_job_id
AND sysdate between date_from and nvl(date_to,sysdate+1);
SELECT mtl.concatenated_segments
INTO l_category
FROM po_job_associations pja,
mtl_categories_kfv mtl
WHERE pja.category_id = mtl.category_id
AND pja.job_id = p_job_id;
SELECT 'Y'
INTO l_valid_flag
FROM po_job_associations
WHERE job_id = p_job_id
AND ( inactive_date is null
OR trunc(sysdate) < trunc(inactive_date));
SELECT *
INTO x_po_header_record
FROM po_headers
WHERE po_header_id = x_po_header_id;
INSERT INTO po_online_report_text
(
online_report_id,
last_update_login,
last_updated_by,
last_update_date,
created_by,
creation_date,
sequence,
text_line,
message_type
)
--SQL What: Check if the requesting orgs for enabled org assignments are
-- still valid.
--SQL Why: Insert an error for each invalid requesting org
SELECT p_online_report_id,
l_login_id,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
x_sequence + ROWNUM,
SUBSTRB(l_err_prefix||psg.char1||l_text_line,1,2000),
l_message_type
FROM po_session_gt psg
WHERE psg.key = l_key
AND NOT EXISTS
(SELECT 'is active ou'
FROM hr_operating_units hou,
financials_system_params_all fspa,
po_system_parameters_all pspa
WHERE hou.organization_id = psg.num1
AND hou.organization_id = pspa.org_id
AND pspa.org_id = fspa.org_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(hou.date_from)
AND TRUNC(NVL(hou.date_to, SYSDATE+1))
);
INSERT INTO po_online_report_text
(
online_report_id,
last_update_login,
last_updated_by,
last_update_date,
created_by,
creation_date,
sequence,
text_line,
message_type
)
--SQL What: Check if the purchasing orgs for enabled org assignments are
-- still valid.
--SQL Why: Insert an error for each invalid purchasing org
SELECT p_online_report_id,
l_login_id,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
x_sequence + ROWNUM,
SUBSTRB(l_err_prefix||psg.char1||l_text_line,1,2000),
l_message_type
FROM po_session_gt psg
WHERE psg.key = l_key
AND (NOT EXISTS
(SELECT 'is active ou'
FROM hr_operating_units hou,
financials_system_params_all fspa,
po_system_parameters_all pspa
WHERE hou.organization_id = psg.num2
AND hou.organization_id = pspa.org_id
AND pspa.org_id = fspa.org_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(hou.date_from)
AND TRUNC(NVL(hou.date_to, SYSDATE+1))
)
OR
NOT EXISTS
(SELECT 'encumbrance check'
FROM financials_system_params_all fspa1,
financials_system_params_all fspa2
WHERE fspa1.org_id = psg.num1
AND fspa2.org_id = psg.num2
AND ( fspa1.org_id = fspa2.org_id
OR
( NVL(fspa1.purch_encumbrance_flag,'N') = 'N'
AND NVL(fspa1.req_encumbrance_flag,'N') = 'N'
AND NVL(fspa2.purch_encumbrance_flag,'N') = 'N'
AND NVL(fspa2.req_encumbrance_flag,'N') = 'N'
)
)
)
OR
NOT EXISTS
(SELECT 'Valid vendor site for POU'
FROM po_vendor_sites_all pvsa
WHERE pvsa.vendor_id = p_vendor_id
AND pvsa.org_id = psg.num2
AND pvsa.purchasing_site_flag = 'Y'
AND NVL(pvsa.rfq_only_site_flag, 'N') = 'N'
AND TRUNC(SYSDATE) <
TRUNC(NVL(pvsa.inactive_date, SYSDATE+1))
)
);
INSERT INTO po_online_report_text
(
online_report_id,
last_update_login,
last_updated_by,
last_update_date,
created_by,
creation_date,
sequence,
text_line,
message_type
)
--SQL What: Check if the purchasing sites for enabled org assignments are
-- still valid.
--SQL Why: Insert an error for each invalid purchasing site
SELECT p_online_report_id,
l_login_id,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
x_sequence + ROWNUM,
SUBSTRB(l_err_prefix||psg.char1||l_text_line,1,2000),
l_message_type
FROM po_session_gt psg
WHERE psg.key = l_key
AND NOT EXISTS
(SELECT 'Valid vendor site'
FROM po_vendor_sites_all pvsa
WHERE pvsa.vendor_site_id = psg.num3
AND pvsa.vendor_id = p_vendor_id
AND pvsa.org_id = psg.num2
AND pvsa.purchasing_site_flag = 'Y'
AND NVL(pvsa.rfq_only_site_flag, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(pvsa.inactive_date, SYSDATE+1))
);
SELECT po_session_gt_s.nextval
INTO x_key
FROM DUAL;
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3,
char1
)
--SQL What: Get info for all enabled org assignments of this GA
--SQL Why: Store info temporarily for org assignment submission checks
SELECT x_key,
pgoa.organization_id,
pgoa.purchasing_org_id,
pgoa.vendor_site_id,
hout.name
FROM po_ga_org_assignments pgoa,
hr_all_organization_units_tl hout
WHERE pgoa.po_header_id = p_po_header_id
AND pgoa.organization_id = hout.organization_id
AND hout.language = USERENV('LANG')
AND pgoa.enabled_flag = 'Y';