[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT glsob.currency_code
,glsob.chart_of_accounts_id
FROM gl_sets_of_books glsob,
org_organization_definitions ood
WHERE glsob.set_of_books_id = ood.set_of_books_id
AND ood.organization_id = p_organization_id;
SELECT cic.item_cost
FROM cst_item_costs_for_gl_view cic,
mtl_parameters mp
WHERE cic.inventory_item_id = p_item_id
AND cic.organization_id = mp.cost_organization_id
AND cic.inventory_asset_flag = 1
AND mp.organization_id= p_source_organization_id;
SELECT cic.item_cost *
round(gl_currency_api.get_closest_rate_sql
(s_currency_code,
d_currency_code,
trunc(sysdate),
psp.DEFAULT_RATE_TYPE,
30),10)
FROM cst_item_costs_for_gl_view cic,
mtl_parameters mp,
po_system_parameters psp
WHERE cic.inventory_item_id = p_item_id
AND cic.organization_id = mp.cost_organization_id
AND cic.inventory_asset_flag = 1
AND mp.organization_id= p_source_organization_id;
SELECT msi.list_price_per_unit *
round(gl_currency_api.get_closest_rate_sql
(p_set_of_books_id,
glsob.currency_code,
trunc(sysdate),
psp.DEFAULT_RATE_TYPE,
30),10)
FROM mtl_system_items msi,
gl_sets_of_books glsob,
org_organization_definitions ood,
po_system_parameters psp
WHERE msi.inventory_item_id = p_item_id
AND ood.organization_id = p_source_organization_id
AND msi.organization_id = ood.organization_id
AND glsob.set_of_books_id = ood.set_of_books_id;
SELECT asset_inventory
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = NVL(p_destination_subinventory,'X')
AND organization_id = p_destination_organization_id;
SELECT inventory_asset_flag
FROM mtl_system_items
WHERE organization_id = p_destination_organization_id
AND inventory_item_id = p_item_id;
SELECT expense_account
INTO l_charge_account
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_destination_subinventory
AND organization_id = p_destination_organization_id;
SELECT expense_account
INTO l_charge_account
FROM mtl_system_items
WHERE organization_id = p_destination_organization_id
AND inventory_item_id = p_item_id;
SELECT expense_account
INTO l_charge_account
FROM mtl_parameters
WHERE organization_id = p_destination_organization_id;
SELECT material_account
INTO l_charge_account
FROM mtl_parameters
WHERE organization_id = p_destination_organization_id;
SELECT expense_account
INTO l_charge_account
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_destination_subinventory
AND organization_id = p_destination_organization_id;
SELECT expense_account
INTO l_charge_account
FROM mtl_parameters
WHERE organization_id = p_destination_organization_id;
SELECT material_account
INTO l_charge_account
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_destination_subinventory
AND organization_id = p_destination_organization_id;
SELECT material_account
INTO l_charge_account
FROM mtl_parameters
WHERE organization_id = p_destination_organization_id;
SELECT 'X'
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = p_ccid
AND gcc.enabled_flag = 'Y'
AND trunc(nvl(p_gl_date,SYSDATE))
BETWEEN trunc(nvl(start_date_active, nvl(p_gl_date,SYSDATE) ))
AND trunc(nvl (end_date_active, SYSDATE+1))
AND gcc.detail_posting_allowed_flag = 'Y'
AND gcc.chart_of_accounts_id= p_chart_of_accounts_id
AND gcc.summary_flag = 'N';
SELECT po_requisition_headers_s.nextval
FROM sys.dual;
SELECT po_requisition_lines_s.nextval
FROM sys.dual;
SELECT po_req_distributions_s.nextval
FROM sys.dual;
SELECT mp.ap_accrual_account,
mp.invoice_price_var_account
FROM mtl_parameters mp
WHERE mp.organization_id = p_destination_organization_id;
SELECT line_type_id
FROM PO_SYSTEM_PARAMETERS
WHERE org_id = p_org_id;
SELECT description
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_orgn_id;
SELECT mic.category_id
FROM mtl_item_categories mic,
mtl_default_sets_view mdsv
WHERE mic.inventory_item_id = p_item_id
AND mic.organization_id = p_destination_org_id
AND mic.category_set_id = mdsv.category_set_id
AND mdsv.functional_area_id = 2;
SELECT 'X'
FROM po_lookup_codes plc
WHERE plc.lookup_type = 'REQUISITION SOURCE TYPE'
AND plc.lookup_code = p_source_type_code;
SELECT 'X'
FROM po_lookup_codes plc
WHERE plc.lookup_type = 'DESTINATION TYPE'
AND plc.lookup_code = p_destination_type_code;
SELECT 'X'
FROM po_lookup_codes plc
WHERE plc.lookup_type = 'AUTHORIZATION STATUS'
AND plc.lookup_code = p_authorization_status;
SELECT set_of_books_id
FROM hr_operating_units
WHERE organization_id = p_organization_id;
SELECT nvl (msi.encumbrance_account,mp.encumbrance_account)
FROM mtl_system_items msi,
mtl_parameters mp
WHERE msi.inventory_item_id = p_item_id
AND msi.organization_id = p_destination_organization_id
AND mp.organization_id = msi.organization_id;
SELECT nvl (fsp.req_encumbrance_flag,'N')
FROM financials_system_parameters fsp
WHERE fsp.set_of_books_id = p_set_of_books_id;
SELECT NVL(expense_account,-1)
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_destination_organization_id;
SELECT mum.unit_of_measure
FROM mtl_units_of_measure mum
WHERE mum.uom_code = p_uom_code;
SELECT Sysdate INTO l_today FROM dual;
SELECT 'X' INTO l_dummy
FROM DUAL
WHERE NOT EXISTS
( SELECT 'X'
FROM po_requisition_headers
WHERE Segment1 = l_header_rec.segment1);
INSERT INTO po_requisition_headers(
org_id,
requisition_header_id,
preparer_id,
last_update_date,
last_updated_by,
segment1,
summary_flag,
enabled_flag,
segment2,
segment3,
segment4,
segment5,
start_date_active,
end_date_active,
last_update_login,
creation_date,
created_by,
description,
authorization_status,
note_to_authorizer,
type_lookup_code,
transferred_to_oe_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
government_context,
closed_code,
tax_attribute_update_code --
) VALUES (
l_header_rec.org_id,
l_header_rec.requisition_header_id,
l_header_rec.preparer_id,
l_today,
l_user_id,
l_header_rec.segment1,
l_header_rec.summary_flag,
l_header_rec.enabled_flag,
l_header_rec.segment2,
l_header_rec.segment3,
l_header_rec.segment4,
l_header_rec.segment5,
l_header_rec.start_date_active,
l_header_rec.end_date_active,
l_header_rec.last_update_login,
l_today,
l_user_id,
l_header_rec.description,
l_header_rec.authorization_status,
l_header_rec.note_to_authorizer,
l_header_rec.type_lookup_code,
l_header_rec.transferred_to_oe_flag,
l_header_rec.attribute_category,
l_header_rec.attribute1,
l_header_rec.attribute2,
l_header_rec.attribute3,
l_header_rec.attribute4,
l_header_rec.attribute5,
l_header_rec.attribute6,
l_header_rec.attribute7,
l_header_rec.attribute8,
l_header_rec.attribute9,
l_header_rec.attribute10,
l_header_rec.attribute11,
l_header_rec.attribute12,
l_header_rec.attribute13,
l_header_rec.attribute14,
l_header_rec.attribute15,
l_header_rec.government_context,
l_header_rec.closed_code ,
'CREATE' --
);
SELECT order_type_lookup_code,
purchase_basis,
matching_basis
INTO l_line_tbl(i).order_type_lookup_code,
l_line_tbl(i).purchase_basis,
l_line_tbl(i).matching_basis
FROM po_line_types
WHERE line_type_id = l_line_tbl(i).line_type_id;
INSERT INTO po_requisition_lines(
requisition_line_id,
requisition_header_id,
line_num,
line_type_id,
category_id,
item_description,
unit_meas_lookup_code,
unit_price,
quantity,
deliver_to_location_id,
to_person_id,
last_update_date,
last_updated_by,
source_type_code,
last_update_login,
creation_date,
created_by,
item_id,
item_revision,
encumbered_flag,
rfq_required_flag,
need_by_date,
source_organization_id,
source_subinventory,
destination_type_code,
destination_organization_id,
destination_subinventory,
line_location_id,
modified_by_agent_flag,
parent_req_line_id,
justification,
note_to_agent,
note_to_receiver,
purchasing_agent_id,
document_type_code,
blanket_po_header_id,
blanket_po_line_num,
currency_code,
rate_type,
rate_date,
rate,
currency_unit_price,
suggested_vendor_name,
suggested_vendor_location,
suggested_vendor_contact,
suggested_vendor_phone,
suggested_vendor_product_code,
un_number_id,
hazard_class_id,
must_use_sugg_vendor_flag,
reference_num,
on_rfq_flag,
urgent_flag,
cancel_flag,
quantity_cancelled,
cancel_date,
cancel_reason,
closed_code,
agent_return_note,
changed_after_research_flag,
vendor_id,
vendor_site_id,
vendor_contact_id,
research_agent_id,
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
attribute_category,
destination_context,
inventory_source_context,
vendor_source_context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
bom_resource_id,
government_context,
closed_reason,
closed_date,
transaction_reason_code,
quantity_received,
order_type_lookup_code,
purchase_basis,
matching_basis,
org_id, --
tax_attribute_update_code, --
MANUFACTURER_ID, --bug 7387487
MANUFACTURER_NAME,
MANUFACTURER_PART_NUMBER
) VALUES (
l_line_tbl(i).requisition_line_id,
l_line_tbl(i).requisition_header_id,
l_line_tbl(i).line_num,
l_line_tbl(i).line_type_id,
l_line_tbl(i).category_id,
l_line_tbl(i).item_description,
l_line_tbl(i).unit_meas_lookup_code,
l_line_tbl(i).unit_price,
l_line_tbl(i).quantity,
l_line_tbl(i).deliver_to_location_id,
l_line_tbl(i).to_person_id,
l_today, -- last_update_date
l_user_id, --last_updated_by
l_line_tbl(i).source_type_code,
l_login_id, --last_update_login
l_today, --creation_date
l_user_id, --created_by
l_line_tbl(i).item_id,
l_line_tbl(i).item_revision,
l_line_tbl(i).encumbered_flag,
l_line_tbl(i).rfq_required_flag,
l_line_tbl(i).need_by_date,
l_line_tbl(i).source_organization_id,
l_line_tbl(i).source_subinventory,
l_line_tbl(i).destination_type_code,
l_line_tbl(i).destination_organization_id,
l_line_tbl(i).destination_subinventory,
l_line_tbl(i).line_location_id,
l_line_tbl(i).modified_by_agent_flag,
l_line_tbl(i).parent_req_line_id,
l_line_tbl(i).justification,
l_line_tbl(i).note_to_agent,
l_line_tbl(i).note_to_receiver,
l_line_tbl(i).purchasing_agent_id,
l_line_tbl(i).document_type_code,
l_line_tbl(i).blanket_po_header_id,
l_line_tbl(i).blanket_po_line_num,
l_line_tbl(i).currency_code,
l_line_tbl(i).rate_type,
l_line_tbl(i).rate_date,
l_line_tbl(i).rate,
l_line_tbl(i).currency_unit_price,
l_line_tbl(i).suggested_vendor_name,
l_line_tbl(i).suggested_vendor_location,
l_line_tbl(i).suggested_vendor_contact,
l_line_tbl(i).suggested_vendor_phone,
l_line_tbl(i).suggested_vendor_product_code,
l_line_tbl(i).un_number_id,
l_line_tbl(i).hazard_class_id,
l_line_tbl(i).must_use_sugg_vendor_flag,
l_line_tbl(i).reference_num,
l_line_tbl(i).on_rfq_flag,
l_line_tbl(i).urgent_flag,
l_line_tbl(i).cancel_flag,
l_line_tbl(i).quantity_cancelled,
l_line_tbl(i).cancel_date,
l_line_tbl(i).cancel_reason,
l_line_tbl(i).closed_code,
l_line_tbl(i).agent_return_note,
l_line_tbl(i).changed_after_research_flag,
l_line_tbl(i).vendor_id,
l_line_tbl(i).vendor_site_id,
l_line_tbl(i).vendor_contact_id,
l_line_tbl(i).research_agent_id,
l_line_tbl(i).wip_entity_id,
l_line_tbl(i).wip_line_id,
l_line_tbl(i).wip_repetitive_schedule_id,
l_line_tbl(i).wip_operation_seq_num,
l_line_tbl(i).wip_resource_seq_num,
l_line_tbl(i).attribute_category,
l_line_tbl(i).destination_context,
l_line_tbl(i).inventory_source_context,
l_line_tbl(i).vendor_source_context,
l_line_tbl(i).attribute1,
l_line_tbl(i).attribute2,
l_line_tbl(i).attribute3,
l_line_tbl(i).attribute4,
l_line_tbl(i).attribute5,
l_line_tbl(i).attribute6,
l_line_tbl(i).attribute7,
l_line_tbl(i).attribute8,
l_line_tbl(i).attribute9,
l_line_tbl(i).attribute10,
l_line_tbl(i).attribute11,
l_line_tbl(i).attribute12,
l_line_tbl(i).attribute13,
l_line_tbl(i).attribute14,
l_line_tbl(i).attribute15,
l_line_tbl(i).bom_resource_id,
l_line_tbl(i).government_context,
l_line_tbl(i).closed_reason,
l_line_tbl(i).closed_date,
l_line_tbl(i).transaction_reason_code,
l_line_tbl(i).quantity_received,
l_line_tbl(i).order_type_lookup_code,
l_line_tbl(i).purchase_basis,
l_line_tbl(i).matching_basis,
l_line_tbl(i).org_id, --
'CREATE', --
l_manufacturer_id,
l_manufacturer_name,
l_manufacturer_pn
);
INSERT INTO po_req_distributions
(
distribution_id
,last_update_date
,last_updated_by
,requisition_line_id
,set_of_books_id
,code_combination_id
,req_line_quantity
,last_update_login
,creation_date
,created_by
,encumbered_flag
,gl_encumbered_date
,gl_encumbered_period_name
,gl_cancelled_date
,failed_funds_lookup_code
,encumbered_amount
,budget_account_id
,accrual_account_id
,variance_account_id
,prevent_encumbrance_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,government_context
,project_id
,task_id
,expenditure_type
,project_accounting_context
,expenditure_organization_id
,gl_closed_date
,source_req_distribution_id
,distribution_num
,project_related_flag
,expenditure_item_date
,org_id
,allocation_type
,allocation_value
,award_id
,end_item_unit_number
,recoverable_tax
,nonrecoverable_tax
,recovery_rate
,tax_recovery_override_flag
,oke_contract_line_id
,oke_contract_deliverable_id
)
VALUES
(
l_dist_rec.distribution_id
,l_today --last_update_date
,l_user_id --last_updated_by
,l_dist_rec.requisition_line_id
,l_dist_rec.set_of_books_id
,l_dist_rec.code_combination_id
,l_dist_rec.req_line_quantity
,l_login_id --last_update_login
,l_today --creation_date
,l_user_id --created_by
,l_dist_rec.encumbered_flag
,l_dist_rec.gl_encumbered_date
,l_dist_rec.gl_encumbered_period_name
,l_dist_rec.gl_cancelled_date
,l_dist_rec.failed_funds_lookup_code
,l_dist_rec.encumbered_amount
,l_dist_rec.budget_account_id
,l_dist_rec.accrual_account_id
,l_dist_rec.variance_account_id
,l_dist_rec.prevent_encumbrance_flag
,l_dist_rec.attribute_category
,l_dist_rec.attribute1
,l_dist_rec.attribute2
,l_dist_rec.attribute3
,l_dist_rec.attribute4
,l_dist_rec.attribute5
,l_dist_rec.attribute6
,l_dist_rec.attribute7
,l_dist_rec.attribute8
,l_dist_rec.attribute9
,l_dist_rec.attribute10
,l_dist_rec.attribute11
,l_dist_rec.attribute12
,l_dist_rec.attribute13
,l_dist_rec.attribute14
,l_dist_rec.attribute15
,l_dist_rec.government_context
,l_dist_rec.project_id
,l_dist_rec.task_id
,l_dist_rec.expenditure_type
,l_dist_rec.project_accounting_context
,l_dist_rec.expenditure_organization_id
,l_dist_rec.gl_closed_date
,l_dist_rec.source_req_distribution_id
,l_dist_rec.distribution_num
,l_dist_rec.project_related_flag
,l_dist_rec.expenditure_item_date
,l_dist_rec.org_id
,l_dist_rec.allocation_type
,l_dist_rec.allocation_value
,l_dist_rec.award_id
,l_dist_rec.end_item_unit_number
,l_dist_rec.recoverable_tax
,l_dist_rec.nonrecoverable_tax
,l_dist_rec.recovery_rate
,l_dist_rec.tax_recovery_override_flag
,l_dist_rec.oke_contract_line_id
,l_dist_rec.oke_contract_deliverable_id
);