[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select rowidtochar(ROWID)
into v_row_id
from AK_FLOW_REGION_RELATIONS
where FROM_REGION_CODE = 'ICX_PO_REQ_HEAD_SUM_R'
and FROM_REGION_APPL_ID = 178
and FROM_PAGE_CODE = 'ICX_REQS_HEADER'
and FROM_PAGE_APPL_ID = 178
and TO_PAGE_CODE = 'ICX_REQS_DETAILS'
and TO_PAGE_APPL_ID = 178
and FLOW_CODE = 'ICX_COPY_REQS'
and FLOW_APPLICATION_ID = 178;
SELECT query_set
INTO v_query_size
FROM icx_parameters;
select substr(l_message,12,512) into l_err_mesg from dual;
SELECT SYSDATE + increment
FROM SYS.DUAL;
SELECT requisition_line_id
FROM po_requisition_lines
WHERE requisition_header_id = reqheader
ORDER BY line_num;
SELECT distribution_id, charge_account_id
FROM icx_cart_line_distributions
WHERE cart_id = l_cart_id
AND cart_line_id = l_cart_line_id;
SELECT note_to_agent
INTO v_note_to_buyer
FROM po_requisition_lines
WHERE requisition_header_id = l_req_header_id
AND rownum = 1;
SELECT to_char(current_max_unique_identifier + 1)
INTO v_requisition_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 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,
deliver_to_location,
deliver_to_requestor,
org_id
)
SELECT
v_cart_id,
sysdate,
v_shopper_id,
sysdate,
v_shopper_id,
v_shopper_id,
v_int_contact_id,
v_need_by_date,
'EXPENSE',
v_destination_org_id,
v_deliver_to_loc_id,
note_to_authorizer,
v_note_to_buyer,
1,
v_requisition_num,
NULL,
NULL,
description,
attribute_category,
NULL,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
v_deliver_to_location,
v_int_contact_name,
v_org_id
FROM po_requisition_headers
WHERE requisition_header_id = l_req_header_id;
SELECT icx_cart_distributions_s.nextval
INTO v_distribution_id
FROM SYS.DUAL;
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_distribution_id,
v_shopper_id,
sysdate,
v_shopper_id,
sysdate,
v_shopper_id,
v_org_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,
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,
need_by_date,
custom_defaulted,
deliver_to_location,
org_id
) select
v_cart_line_id,
v_cart_line_number,
sysdate,
v_shopper_id,
sysdate,
v_shopper_id,
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,
v_destination_org_id,
v_deliver_to_loc_id,
rl.blanket_po_header_id,
rl.blanket_po_line_num,
-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,
v_need_by_date,
'N',
v_deliver_to_location,
v_org_id
FROM po_requisition_lines rl
WHERE rl.requisition_header_id = l_req_header_id
AND rl.requisition_line_id = prec.requisition_line_id;
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,
org_id,
allocation_type,
allocation_value,
allocation_quantity)
SELECT v_cart_line_id,
v_cart_id,
icx_cart_line_distributions_s.nextval,
v_shopper_id,
sysdate,
v_shopper_id,
sysdate,
v_shopper_id,
rd.code_combination_id,
rd.accrual_account_id,
rd.variance_account_id,
rd.budget_account_id,
v_org_id,
nvl(rd.allocation_type, 'PERCENT'),
nvl(rd.allocation_value, round(rd.req_line_quantity / rl.quantity * 100, 5)),
rd.req_line_quantity
FROM po_req_distributions rd,
po_requisition_lines rl
WHERE rd.requisition_line_id = prec.requisition_line_id
AND rl.requisition_header_id = l_req_header_id
AND rl.requisition_line_id = rd.requisition_line_id;
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);
UPDATE icx_shopping_cart_lines b
SET item_number = ( SELECT a.concatenated_segments
FROM mtl_system_items_kfv a
WHERE a.inventory_item_id = b.item_id
AND a.organization_id = b.destination_organization_id
AND b.cart_id = v_cart_id
AND b.item_id IS NOT NULL)
WHERE cart_id = v_cart_id;