[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE PrintSelectedShipment(p_result_index NUMBER,
p_current_row NUMBER);
PROCEDURE UpdateResultSet(p_where_clause IN VARCHAR2 DEFAULT NULL,
p_session_id IN NUMBER);
delete pos_asn_search_result where session_id = l_session_id;
select decode(count(1), 0, 'Y', 'N')
into l_empty_cart
from POS_ASN_SHOP_CART_DETAILS
where session_id = l_session_id;
select ct.vendor_site_id,
vs.vendor_site_code,
ct.ship_to_location_id,
hrl.location_code
into l_supplier_site_id,
l_supplier_site,
l_ship_to_loc_id,
l_ship_to_location
from POS_ASN_SHOP_CART_HEADERS ct,
PO_VENDOR_SITES vs,
HR_LOCATIONS hrl
where ct.session_id = l_session_id
and ct.vendor_site_id = vs.vendor_site_id
and ct.SHIP_TO_LOCATION_ID = hrl.LOCATION_ID;
select ak.NUMBER_VALUE,
vs.VENDOR_SITE_CODE
into l_supplier_site_id,
l_supplier_site
from AK_WEB_USER_SEC_ATTR_VALUES ak,
PO_VENDOR_SITES vs
where ATTRIBUTE_CODE = 'ICX_SUPPLIER_SITE_ID'
and ak.NUMBER_VALUE = vs.VENDOR_SITE_ID
and WEB_USER_ID = l_user_id
and exists (select 1
from ak_resp_security_attributes
where attribute_code = 'ICX_SUPPLIER_SITE_ID'
and responsibility_id = l_responsibility_id);
fnd_message.get_string('ICX','ICX_POS_ASN_SELECT_RESULT') || '');
select date_format_mask
into l_format_mask
from icx_sessions
where session_id = l_session_id;
UpdateResultSet(l_where_clause, l_session_id);
delete pos_asn_search_result where session_id = l_session_id;
select count(1)
into l_num_shipments
from pos_asn_shop_cart_details
where session_id = l_session_id;
pos_select IN t_text_table DEFAULT g_dummy,
pos_start_row IN VARCHAR2 DEFAULT '1',
pos_submit IN VARCHAR2 DEFAULT 'STAY') IS
l_language VARCHAR2(5);
select ship_to_organization_id,
ship_to_location_id
into l_first_org_id,
l_first_loc_id
from pos_asn_shop_cart_headers
where session_id = l_session_id;
FOR l_counter IN 1..pos_select.count LOOP
l_po_shipment_id := to_number(pos_po_shipment_id(to_number(pos_select(l_counter))));
select SHIP_TO_ORGANIZATION_ID,
ship_to_location_id
into l_ship_to_org_id,
l_ship_to_loc_id
from po_line_locations
where line_location_id = l_po_shipment_id;
select count(1)
into l_header_count
from pos_asn_shop_cart_headers
where session_id = l_session_id;
if l_header_count = 0 and pos_select.count > 0 then
l_po_shipment_id := to_number(pos_po_shipment_id(to_number(pos_select(1))));
select poll.ship_to_organization_id,
poll.ship_to_location_id,
poh.vendor_id,
poh.vendor_site_id
into l_ship_to_org_id,
l_ship_to_loc_id,
l_vendor_id,
l_vendor_site_id
from po_line_locations poll,
po_headers poh
where poh.po_header_id = poll.po_header_id
and poll.line_location_id = l_po_shipment_id;
insert into pos_asn_shop_cart_headers
(
SESSION_ID,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
VENDOR_ID,
VENDOR_SITE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY
)
values
(
l_session_id,
l_ship_to_org_id,
l_ship_to_loc_id,
l_vendor_id,
l_vendor_site_id,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id
);
FOR l_counter IN 1..pos_select.count LOOP
select nvl(max(asn_line_id), 0) + 1
into l_asn_line_id
from pos_asn_shop_cart_details
where session_id = l_session_id;
l_po_shipment_id := to_number(pos_po_shipment_id(to_number(pos_select(l_counter))));
select poll.po_header_id,
poll.po_line_id,
poll.ship_to_organization_id,
pol.unit_meas_lookup_code
into l_po_header_id,
l_po_line_id,
l_ship_to_org_id,
l_unit_meas_lookup_code
from po_line_locations poll,
po_lines pol
where poll.line_location_id = l_po_shipment_id
and poll.po_line_id = pol.po_line_id;
insert into pos_asn_shop_cart_details
(
SESSION_ID,
ASN_LINE_ID,
PO_LINE_LOCATION_ID,
PO_HEADER_ID,
PO_LINE_ID,
SHIP_TO_ORGANIZATION_ID,
UNIT_OF_MEASURE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY
)
values
(
l_session_id,
l_asn_line_id,
l_po_shipment_id,
l_po_header_id,
l_po_line_id,
l_ship_to_org_id,
l_unit_meas_lookup_code,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id
);
PROCEDURE UpdateResultSet(p_where_clause IN VARCHAR2 DEFAULT NULL,
p_session_id IN NUMBER) IS
v_stmt VARCHAR2(2000);
delete pos_asn_search_result where session_id = p_session_id;
'insert into pos_asn_search_result select ' || to_char(p_session_id) || ',
PO_HEADER_ID,
PO_NUMBER,
PO_RELEASE_ID,
PO_LINE_ID,
LINE_NUMBER,
PO_SHIPMENT_ID,
SHIPMENT_NUMBER,
SHIP_TO_LOCATION_ID,
SHIP_TO_LOCATION_CODE,
SUPPLIER_ITEM_NUMBER,
ITEM_DESCRIPTION,
QUANTITY_ORDERED,
UNIT_OF_MEASURE_CODE,
DUE_DATE,
SUPPLIER_ID,
SUPPLIER_NAME,
SUPPLIER_SITE_ID,
SUPPLIER_SITE_CODE,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_ORGANIZATION_CODE,
SHIP_TO_ORGANIZATION_NAME,
ITEM_ID,
ITEM_NUMBER,
ITEM_REVISION,
CATEGORY_ID,
CATEGORY
from POS_ASN_PO_SHIPMENTS_V
where ' || p_where_clause;
END UpdateResultSet;
select NUMBER_VALUE
into l_supplier_id
from AK_WEB_USER_SEC_ATTR_VALUES
where ATTRIBUTE_CODE = 'ICX_SUPPLIER_ORG_ID'
and WEB_USER_ID = p_user_id;
select vs.vendor_id
into l_supplier_id
from po_vendor_sites vs,
po_vendor_contacts vc,
fnd_user fu
where fu.user_id = p_user_id
and fu.supplier_id = vc.vendor_contact_id
and vc.vendor_site_id = vs.vendor_site_id;
select NUMBER_VALUE
into l_supplier_site_id
from AK_WEB_USER_SEC_ATTR_VALUES
where ATTRIBUTE_CODE = 'ICX_SUPPLIER_SITE_ID'
and WEB_USER_ID = p_user_id;
select nvl(query_set, 25)
into l_pagesize
from icx_parameters;
htp.p('');
select decode(count(1), 0, 'Y', 'N')
into l_empty_cart
from POS_ASN_SHOP_CART_DETAILS
where session_id = l_session_id;
select count(1)
into l_in_cart
from pos_asn_shop_cart_details
where session_id = l_session_id
and po_line_location_id = l_shipment_id;
PrintSelectedShipment(l_result_index, l_current_row - p_start_row + 1);
htp.p('');
PROCEDURE PrintSelectedShipment(p_result_index NUMBER,
p_current_row NUMBER) IS
l_attribute_index NUMBER := ak_query_pkg.g_items_table.FIRST;
END PrintSelectedShipment;
g_attribute_table.DELETE;
select REQUIRED_FLAG
into l_required_flag
from ak_region_items
where REGION_CODE = 'POS_ASN_SEARCH_R'
and ATTRIBUTE_CODE = p_attribute_code;