[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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) into v_no_of_children
from icx_related_templates_val_v
where express_name = v_node_id
and RELATIONSHIP_TYPE <> 'TOP';
select count(-1) into v_no_of_children
from icx_related_templates_val_v
where express_name = v_default_template
and RELATIONSHIP_TYPE <> 'TOP';
select express_name,relationship_type
from icx_related_templates_val_v
where related_express_name = childnodeId
and (reserve_po_number = 'YES'
or reserve_po_number = 'OPTIONAL');
select express_name,relationship_type
from icx_related_templates_val_v
where related_express_name = childnodeId
and (reserve_po_number = 'NO'
or reserve_po_number = 'OPTIONAL'
or reserve_po_number is NULL);
select express_name,relationship_type
from icx_related_templates_val_v
where relationship_type = 'TOP'
order by express_name;
select count(-1) into v_no_of_children
from icx_related_templates_val_v
where express_name = v_node_id
and RELATIONSHIP_TYPE <> 'TOP';
SELECT QUERY_SET INTO c_query_size FROM ICX_PARAMETERS;
SELECT QUERY_SET INTO c_query_size FROM ICX_PARAMETERS;
SELECT cart_line_id
FROM icx_shopping_cart_lines
WHERE cart_id = v_cart_id
AND line_id = v_sequence_num
AND express_name = v_express_name
AND NVL(org_id, -9999) = NVL(v_org_id,-9999);
SELECT need_by_date,
deliver_to_requestor_id,
deliver_to_location_id,
destination_organization_id,
deliver_to_location,
created_by,
org_id
FROM icx_shopping_carts
WHERE cart_id = v_cart_id
FOR UPDATE;
l_qty_updated NUMBER := 0;
l_rows_updated NUMBER := 0;
l_rows_updated := 0;
/* Select the max of the cart_line_number for ordering */
SELECT max(cart_line_number) + 1 into v_cart_line_number
FROM icx_shopping_cart_lines
WHERE cart_id = l_cart_id;
select PO_REQUISITION_LINES_S.nextval into l_cart_line_id
from dual;
INSERT INTO icx_shopping_cart_lines
(cart_line_id,
cart_id,
creation_date,
created_by,
quantity,
line_id,
item_id,
item_revision,
unit_of_measure,
unit_price,
category_id,
line_type_id,
item_description,
destination_organization_id,
deliver_to_location_id,
suggested_buyer_id,
suggested_vendor_name,
suggested_vendor_site,
need_by_date,
suggested_vendor_contact,
suggested_vendor_phone,
suggested_vendor_item_num,
-- supplier_item_num, Obselate?
last_update_date,
last_updated_by,
org_id,
express_name,
item_number,
deliver_to_location,
custom_defaulted,
cart_line_number,
autosource_doc_header_id,
autosource_doc_line_num
-- ,deliver_to_requestor,
-- deliver_to_requestor_id
)
SELECT /* into icx_shopping_cart_lines */
l_cart_line_id,
l_cart_id,
sysdate,
l_shopper_id,
l_qty,
l_line_id,
prl.item_id,
prl.item_revision,
prl.unit_meas_lookup_code,
DECODE(ROUND(NVL(pl.unit_price, 0)*NVL(ph.rate,1),5),0,
NVL(prl.unit_price, 0), ROUND(NVL(pl.unit_price,0)*
NVL(ph.rate,1), 5)),
prl.category_id,
prl.line_type_id,
prl.item_description,
l_dest_org_id,
l_deliver_to_location_id,
prl.suggested_buyer_id,
pv.vendor_name,
pvs.vendor_site_code,
l_need_by_date,
DECODE(prl.suggested_vendor_contact_id, NULL, NULL,
pvc.last_name ||',' ||pvc.first_name),
pvc.phone,
prl.suggested_vendor_product_code,
-- supplier item num ?
sysdate,
l_shopper_id,
l_org_id,
v_express_name,
msi.concatenated_segments,
l_deliver_to_location,
'N',
v_cart_line_number,
prl.po_header_id,
pl.line_num
-- ,v_requestor_name
-- ,v_requestor_id
FROM po_reqexpress_headers prh,
po_reqexpress_lines prl,
mtl_system_items_kfv msi,
po_vendor_contacts pvc,
po_vendor_sites pvs,
po_vendors pv,
po_headers ph,
po_lines pl
WHERE prh.express_name = prl.express_name
AND prl.suggested_vendor_id = pv.vendor_id(+)
AND prl.suggested_vendor_site_id = pvs.vendor_site_id(+)
AND prl.suggested_vendor_contact_id = pvc.vendor_contact_id(+)
AND prl.po_header_id = ph.po_header_id(+)
AND nvl(ph.po_header_id, -1) = nvl(pl.po_header_id, -1)
AND prl.po_line_id = pl.po_line_id(+)
AND prl.source_type_code = 'VENDOR'
AND prl.item_id is not null
AND prl.item_id = msi.inventory_item_id
AND msi.purchasing_enabled_flag = 'Y'
AND prl.express_name = v_express_name
AND prl.sequence_num = to_number(l_line_id)
AND msi.organization_id = v_org
UNION
SELECT
l_cart_line_id,
l_cart_id,
sysdate,
l_shopper_id,
l_qty,
l_line_id,
prl.item_id,
prl.item_revision,
prl.unit_meas_lookup_code,
DECODE(ROUND(NVL(pl.unit_price, 0)*NVL(ph.rate,1),5),0,
NVL(prl.unit_price, 0), ROUND(NVL(pl.unit_price,0)*
NVL(ph.rate,1), 5)),
prl.category_id,
prl.line_type_id,
prl.item_description,
l_dest_org_id,
l_deliver_to_location_id,
prl.suggested_buyer_id,
pv.vendor_name,
pvs.vendor_site_code,
l_need_by_date,
DECODE(prl.suggested_vendor_contact_id, NULL, NULL,
pvc.last_name ||',' ||pvc.first_name),
pvc.phone,
prl.suggested_vendor_product_code,
-- supplier item num ?
sysdate,
l_shopper_id,
l_org_id,
v_express_name,
NULL,
l_deliver_to_location,
'N',
v_cart_line_number,
prl.po_header_id,
pl.line_num
-- ,v_requestor_name
-- ,v_requestor_id
FROM po_reqexpress_headers prh,
po_reqexpress_lines prl,
po_vendor_contacts pvc,
po_vendor_sites pvs,
po_vendors pv,
po_headers ph,
po_lines pl
WHERE prh.express_name = prl.express_name
AND prl.suggested_vendor_id = pv.vendor_id(+)
AND prl.suggested_vendor_site_id = pvs.vendor_site_id(+)
AND prl.suggested_vendor_contact_id = pvc.vendor_contact_id(+)
AND prl.po_header_id = ph.po_header_id(+)
AND nvl(ph.po_header_id, -1) = nvl(pl.po_header_id, -1)
AND prl.po_line_id = pl.po_line_id(+)
AND prl.source_type_code = 'VENDOR'
AND prl.item_id is null
AND prl.express_name = v_express_name
AND prl.sequence_num = to_number(l_line_id);
/* end of insert into icx_shopping_cart_lines */
-- Get the default accounts and update distributions
icx_req_acct2.get_default_account(l_cart_id,l_cart_line_id,
l_emp_id,l_org_id,l_account_id,l_account_num);
UPDATE icx_shopping_cart_lines
SET quantity = quantity + l_qty,
last_update_date = sysdate,
last_updated_by = l_shopper_id
WHERE cart_id = l_cart_id
AND cart_line_id = l_cart_line_id;
l_rows_updated := l_rows_updated + 1;
l_qty_updated := l_qty_updated + l_qty;
SELECT SUM(quantity * unit_price) INTO l_order_total
FROM icx_shopping_cart_lines
WHERE cart_id = l_cart_id;
total_page(l_rows_added,l_rows_updated, l_qty_added, l_qty_updated,
l_order_total, l_dest_org_id, v_express_name,
p_start_row, p_end_row, p_where,
end_row, p_query_set, p_row_count);
l_rows_updated number default 0,
l_qty_added number default 0,
l_qty_updated number default 0,
l_order_total number default 0,
l_dest_org_id number,
v_express_name VARCHAR2 default null,
p_start_row NUMBER DEFAULT 1,
p_end_row NUMBER DEFAULT NULL,
p_where VARCHAR2,
end_row NUMBER DEFAULT NULL,
p_query_set NUMBER DEFAULT NULL,
p_row_count NUMBER DEFAULT NULL) IS
l_add_message varchar2(500) := '';
l_update_message varchar2(500) := '';
l_template_selected_message varchar2(200) := '';
IF l_rows_updated > 0 THEN
FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_UPDATE');
FND_MESSAGE.SET_TOKEN('ITEM_QUANTITY', l_rows_updated);
l_update_message := FND_MESSAGE.GET;
l_print_message := l_add_message || '
' || l_update_message;
l_print_message := l_update_message;
l_template_selected_message := FND_MESSAGE.GET;
htp.br; -- add line between update and total message
htp.p(l_template_selected_message);
htp.p('' || l_template_selected_message);