[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_SELECT_ITEMS_TTL');
FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_SELECT_ITEMS_TXT');
ak_query_pkg.g_items_table(i).update_flag = 'Y' and
ak_query_pkg.g_items_table(i).secured_column <> 'T' and
ak_query_pkg.g_items_table(i).item_style <> 'HIDDEN' then
v_vendor_on_flag := 'Y';
select hrev.full_name,
hrl.location_id,
hrl.location_code,
ood.organization_id,
ood.organization_code
from hr_locations hrl,
hr_employees_current_v hrev,
org_organization_definitions ood,
financials_system_parameters fsp
where hrev.employee_id = v_shop_id
and hrev.location_id = hrl.location_id
and ood.organization_id = nvl(hrl.inventory_organization_id,
fsp.inventory_organization_id)
and sysdate < nvl(hrl.inactive_date, sysdate + 1);
SELECTED_ARRAY_ID = 0;
select count(-1) into v_test
from po_reqexpress_headers
where express_name = v_return_template
and (reserve_po_number = 'YES' OR reserve_po_number = 'OPTIONAL');
select count(-1) into v_test
from po_reqexpress_headers
where express_name = v_return_template
and (reserve_po_number = 'NO' OR reserve_po_number = 'OPTIONAL' OR reserve_po_number is null);
select count(-1)
from icx_shopping_carts_v
where cart_id = v_cart_id
and shopper_id = v_shopper_id;
select PO_REQUISITION_HEADERS_S.nextval from sys.dual;
SELECT sysdate+increment from sys.dual;
SELECT to_char(current_max_unique_identifier +1),
sysdate,
icx_cart_distributions_s.nextval
INTO v_req_num, v_sysdate, v_dist_id
FROM po_unique_identifier_control
WHERE table_name = 'PO_REQUISITION_HEADERS'
FOR UPDATE OF current_max_unique_identifier;
UPDATE po_unique_identifier_control
SET current_max_unique_identifier =
current_max_unique_identifier+1
WHERE table_name = 'PO_REQUISITION_HEADERS';
insert into icx_shopping_carts (
cart_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
shopper_id,
saved_flag,
approver_id,
approver_name,
deliver_to_requestor_id,
deliver_to_requestor,
need_by_date,
destination_type_code,
destination_organization_id,
deliver_to_location_id,
deliver_to_location,
req_number_segment1,
emergency_flag,
org_id
) values (
v_cart_id,
v_sysdate,
shopper_id,
v_sysdate,
shopper_id,
shopper_id,
1,
NULL,
NULL,
employee_id,
shopper_name,
v_need_by_date,
'EXPENSE',
v_org_id,
v_location_id,
v_location_code,
v_req_num,
l_emer,
v_oo_id);
insert into icx_cart_distributions (
cart_id,
DISTRIBUTION_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
ORG_ID) values (
v_cart_id,
v_dist_id,
shopper_id,
v_sysdate,
shopper_id,
v_sysdate,
shopper_id,
v_oo_id);
select attribute7
from po_requisition_headers
where requisition_header_id = reqheader;
select requisition_line_id
from po_requisition_lines
where requisition_header_id = reqheader;
select concatenated_segments
from mtl_system_items_kfv a,
icx_shopping_cart_lines b
where a.inventory_item_id = b.item_id
and a.organization_id = b.destination_organization_id
and b.cart_line_id = v_cart_line_id
and b.cart_id = v_cart_id;
SELECT distribution_id, charge_account_id
FROM icx_cart_line_distributions
WHERE cart_id = v_cart_id
AND cart_line_id = v_cart_line_id;
select pa.instance_label
from wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
where ias.item_type = 'POREQ'
and ias.item_key = req_header_id
and ias.process_activity = pa.instance_id
and pa.activity_name = ac.name
and pa.activity_item_type = ac.item_type
and pa.process_name = ap.name
and pa.process_item_type = ap.item_type
and pa.process_version = ap.version
and i.item_type = 'POREQ'
and i.item_key = ias.item_key
and i.begin_date between ac.begin_date and nvl(ac.end_date, i.begin_date)
and ias.activity_status = 'NOTIFIED'
order by ias.execution_time;
select preparer_id, authorization_status
into v_preparer_id, v_req_status
from po_requisition_headers
where requisition_header_id = v_req_header_id;
should add code to call ICX_PO_REQS_CANCEL_SV.update_web_reqs_status
directly instead of poreqwf.doCancel... will implement later..
-- cancel the current req
poreqwf.doCancel('POREQ', v_req_header_id);
select to_char(current_max_unique_identifier +1)
into v_req_num
from po_unique_identifier_control
WHERE table_name = 'PO_REQUISITION_HEADERS'
FOR UPDATE OF current_max_unique_identifier;
UPDATE po_unique_identifier_control
SET current_max_unique_identifier = current_max_unique_identifier+1
WHERE table_name = 'PO_REQUISITION_HEADERS';
select deliver_to_location_id, destination_type_code,
destination_organization_id, note_to_agent
into v_dlvr_loc_id, v_dest_code, v_dest_org_id, v_buyer_note
from po_requisition_lines
where requisition_header_id = v_req_header_id
and rownum = 1;
select PO_REQUISITION_HEADERS_S.nextval
into v_cart_id
from sys.dual;
insert into icx_shopping_carts (
cart_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
shopper_id,
deliver_to_requestor_id,
need_by_date,
destination_type_code,
destination_organization_id,
deliver_to_location_id,
note_to_approver,
note_to_buyer,
saved_flag,
req_number_segment1,
-- approver_id,
-- approver_name,
header_description,
header_attribute_category,
reserved_po_num,
header_attribute1,
header_attribute2,
header_attribute3,
header_attribute4,
header_attribute5,
header_attribute6,
header_attribute7,
header_attribute8,
header_attribute9,
header_attribute10,
header_attribute11,
header_attribute12,
header_attribute13,
header_attribute14,
header_attribute15,
emergency_flag,
deliver_to_location,
deliver_to_requestor,
org_id
) select
v_cart_id,
sysdate,
rh.last_updated_by,
sysdate,
rh.created_by,
v_web_user_id,
v_preparer_id,
sysdate,
v_dest_code,
v_dest_org_id,
v_dlvr_loc_id,
rh.note_to_authorizer,
v_buyer_note,
3,
v_req_num,
-- approver_id,
-- approver_name,
description,
attribute_category,
attribute7,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
l_emer,
l_location_code,
l_shopper_name,
v_org_id
from po_requisition_headers rh
where requisition_header_id = v_req_header_id;
insert into icx_cart_distributions
(cart_id,
distribution_id,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
created_by,
org_id)
select
v_cart_id,
icx_cart_distributions_s.nextval,
rh.last_updated_by,
sysdate,
rh.created_by,
sysdate,
rh.created_by,
v_org_id
from po_requisition_headers rh
where requisition_header_id = v_req_header_id;
select PO_REQUISITION_LINES_S.nextval into v_cart_line_id from dual;
insert into icx_shopping_cart_lines (
cart_line_id,
cart_line_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
cart_id,
item_id,
item_revision,
unit_of_measure,
quantity,
unit_price,
suggested_vendor_item_num,
category_id,
line_type_id,
item_description,
suggested_vendor_name,
suggested_vendor_site,
destination_organization_id,
deliver_to_location_id,
autosource_doc_header_id,
autosource_doc_line_num,
-- status_flag,
-- acct_id,
-- acct_num,
line_id,
line_attribute_category,
line_attribute1,
line_attribute2,
line_attribute3,
line_attribute4,
line_attribute5,
line_attribute6,
line_attribute7,
line_attribute8,
line_attribute9,
line_attribute10,
line_attribute11,
line_attribute12,
line_attribute13,
line_attribute14,
line_attribute15,
custom_defaulted,
deliver_to_location,
org_id
) select
v_cart_line_id,
l_cart_line_number,
sysdate,
rl.last_updated_by,
sysdate,
rl.created_by,
v_cart_id,
rl.item_id,
rl.item_revision,
rl.unit_meas_lookup_code,
rl.quantity,
rl.unit_price,
rl.suggested_vendor_product_code,
rl.category_id,
rl.line_type_id,
rl.item_description,
rl.suggested_vendor_name,
rl.suggested_vendor_location,
rl.destination_organization_id,
rl.deliver_to_location_id,
rl.blanket_po_header_id,
rl.blanket_po_line_num,
-- decode(pl.po_line_id, null, -999, pl.po_line_id),
-999,
rl.attribute_category,
rl.attribute1,
rl.attribute2,
rl.attribute3,
rl.attribute4,
rl.attribute5,
rl.attribute6,
rl.attribute7,
rl.attribute8,
rl.attribute9,
rl.attribute10,
rl.attribute11,
rl.attribute12,
rl.attribute13,
rl.attribute14,
rl.attribute15,
'N',
l_location_code,
v_org_id
from po_requisition_lines rl
where rl.requisition_header_id = v_req_header_id
and rl.requisition_line_id = prec.requisition_line_id;
update icx_shopping_cart_lines
set item_number = l_item_number
where cart_id = v_cart_id
and cart_line_id = v_cart_line_id;
select icx_cart_line_distributions_s.nextval into v_line_dist_id from dual;
insert into icx_cart_line_distributions
(cart_line_id,
cart_id,
distribution_id,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
created_by,
charge_account_id,
accrual_account_id,
variance_account_id,
budget_account_id,
distribution_num,
allocation_type,
allocation_value,
org_id)
select v_cart_line_id,
v_cart_id,
v_line_dist_id,
rd.last_updated_by,
sysdate,
rd.last_update_login,
sysdate,
rd.created_by,
rd.code_combination_id,
rd.accrual_account_id,
rd.variance_account_id,
rd.budget_account_id,
l_dist_num,
rd.allocation_type,
rd.allocation_value,
v_org_id
from po_req_distributions rd,
po_requisition_lines rl
-- po_lines pl
-- where rd.requisition_line_id = rl.requisition_line_id
where rd.requisition_line_id = prec.requisition_line_id
and rl.requisition_header_id = v_req_header_id
-- and rl.blanket_po_header_id = pl.po_header_id(+)
and rl.requisition_line_id = rd.requisition_line_id;
icx_req_acct2.update_account_by_id(v_cart_id,v_cart_line_id,v_org_id,v_line_dist_id,l_dist_num);
UPDATE icx_cart_line_distributions
SET distribution_num = v_dist_num
WHERE cart_id = v_cart_id
AND cart_line_id = v_cart_line_id
AND distribution_id = distribution.distribution_id;
icx_req_acct2.update_account_by_id( v_cart_id => v_cart_id,
v_cart_line_id => v_cart_line_id,
v_oo_id => v_org_id,
v_distribution_id => distribution.distribution_id,
v_line_number => v_dist_num);
select gsob.CURRENCY_CODE,
fc.PRECISION
from gl_sets_of_books gsob,
FND_CURRENCIES fc,
org_organization_definitions ood
where ood.ORGANIZATION_ID = v_org
and fc.CURRENCY_CODE = gsob.CURRENCY_CODE
and ood.SET_OF_BOOKS_ID = gsob.SET_OF_BOOKS_ID;