[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select icx_req_cart_errors_s.nextval into l_error_id from dual;
insert into icx_req_cart_errors
(error_id,cart_id,distribution_num,cart_line_id,last_updated_by,last_update_date,last_update_login,creation_date,created_by,error_text)
values(l_error_id,v_cart_id,v_distribution_num,v_cart_line_id,null,sysdate,l_shopper_id,sysdate,l_shopper_id,v_message);
select substr(l_error_message,12,512) into l_err_mesg from dual;
select hrl.location_code
from hr_locations hrl,
org_organization_definitions ood,
financials_system_parameters fsp
where hrl.location_id = locid
and ood.organization_id = nvl(hrl.inventory_organization_id,
fsp.inventory_organization_id)
and sysdate < nvl(hrl.inactive_date,sysdate + 1);
select full_name
from HR_EMPLOYEES_CURRENT_V
where employee_id = v_employee_id;
select concatenated_segments
from mtl_system_items_kfv
where INVENTORY_ITEM_ID = id
and ORGANIZATION_ID = org;
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;
delete icx_req_cart_errors
where cart_id = v_cart_id;
select REQ_NUMBER_SEGMENT1
from icx_shopping_carts
where CART_ID = l_cart_id;
SELECT hrl.location_id,
nvl(hrl.inventory_organization_id,
fsp.inventory_organization_id) organization_id
from hr_locations hrl,
financials_system_parameters fsp
where sysdate < nvl(hrl.inactive_date, sysdate + 1)
and hrl.location_code = loc;
SELECT employee_id
FROM HR_EMPLOYEES_CURRENT_V
WHERE full_name = v_req_name
and organization_id = v_org_id;
SELECT organization_id
FROM HR_EMPLOYEES_CURRENT_V
where employee_id = v_preparer_id;
SELECT count(1)
FROM icx_shopping_cart_lines
WHERE cart_id = v_cart_id;
select hecv.default_code_combination_id employee_default_account_id,
msi.expense_account
from hr_employees_current_v hecv,
mtl_system_items msi,
icx_shopping_carts isc,
icx_shopping_cart_lines iscl
where msi.INVENTORY_ITEM_ID (+) = iscl.ITEM_ID
and nvl(msi.ORGANIZATION_ID,
nvl(isc.DESTINATION_ORGANIZATION_ID,
iscl.DESTINATION_ORGANIZATION_ID)) =
nvl(isc.DESTINATION_ORGANIZATION_ID,
iscl.DESTINATION_ORGANIZATION_ID)
and hecv.EMPLOYEE_ID = v_emp_id
and nvl(isc.org_id, -9999) = nvl(v_oo_id, -9999)
and nvl(iscl.org_id, -9999) = nvl(v_oo_id, -9999);
select CHART_OF_ACCOUNTS_ID
from gl_sets_of_books,
financials_system_parameters fsp
where gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;
select cart_line_number, DELIVER_TO_LOCATION
from icx_shopping_cart_lines
where DELIVER_TO_LOCATION_ID is null
and cart_id = v_cart_id
and nvl(org_id, -9999) = nvl(v_oo_id, -9999);
SELECT employee_id
FROM HR_EMPLOYEES_CURRENT_V
where full_name = approver;
select cart_line_id from icx_shopping_cart_lines
where (quantity is null
OR quantity = 0);
select saved_flag
from icx_shopping_Carts
where cart_id = l_cart_id
and shopper_id = l_shopper;
select need_by_date from icx_shopping_Carts
where cart_id = l_cart_id;
select cart_line_id from
icx_shopping_cart_lines
where cart_id = v_cart_id;
select reserved_po_num
from icx_shopping_carts
where cart_id = cartId
and shopper_id = shopperId;
CURSOR C3 IS SELECT to_char(current_max_unique_identifier + 1)
FROM po_unique_identifier_control
WHERE table_name = 'PO_HEADERS'
FOR UPDATE OF current_max_unique_identifier;
delete icx_req_cart_errors
where cart_id = v_cart_id;
UPDATE po_unique_identifier_control
SET current_max_unique_identifier =
current_max_unique_identifier + 1
WHERE CURRENT of C3;
update ICX_SHOPPING_CARTS
set APPROVER_ID = decode(icx_approver_id, FND_API.G_MISS_CHAR, APPROVER_ID,
icx_approver_id),
APPROVER_NAME = decode(icx_approver_name,
FND_API.G_MISS_CHAR, APPROVER_NAME,
icx_approver_name),
DELIVER_TO_LOCATION_ID =
decode(icx_deliver_to_location_id,
FND_API.G_MISS_CHAR, DELIVER_TO_LOCATION_ID,
icx_deliver_to_location_id),
DELIVER_TO_LOCATION = decode(icx_deliver_to_location,
FND_API.G_MISS_CHAR, DELIVER_TO_LOCATION,
icx_deliver_to_location),
DELIVER_TO_REQUESTOR_ID =
decode(icx_deliver_to_requestor_id,
FND_API.G_MISS_CHAR, DELIVER_TO_REQUESTOR_ID,
icx_deliver_to_requestor_id),
DELIVER_TO_REQUESTOR =
decode(icx_deliver_to_requestor,
FND_API.G_MISS_CHAR, DELIVER_TO_REQUESTOR,
icx_deliver_to_requestor),
DESTINATION_ORGANIZATION_ID =
decode(icx_dest_org_id, FND_API.G_MISS_CHAR, DESTINATION_ORGANIZATION_ID,
icx_dest_org_id),
NEED_BY_DATE = decode(icx_need_by_date, FND_API.G_MISS_CHAR, NEED_BY_DATE,
v_need_date),
NOTE_TO_APPROVER = decode(icx_note_to_approver,
FND_API.G_MISS_CHAR, NOTE_TO_APPROVER, icx_note_to_approver),
NOTE_TO_BUYER = decode(icx_note_to_buyer, FND_API.G_MISS_CHAR, NOTE_TO_BUYER,
icx_note_to_buyer),
HEADER_DESCRIPTION = decode(icx_header_description,
FND_API.G_MISS_CHAR, HEADER_DESCRIPTION,
icx_header_description),
HEADER_ATTRIBUTE1 = decode(icx_header_attribute1,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE1,
icx_header_attribute1),
HEADER_ATTRIBUTE2 = decode(icx_header_attribute2,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE2,
icx_header_attribute2),
HEADER_ATTRIBUTE3 = decode(icx_header_attribute3,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE3,
icx_header_attribute3),
HEADER_ATTRIBUTE4 = decode(icx_header_attribute4,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE4,
icx_header_attribute4),
HEADER_ATTRIBUTE5 = decode(icx_header_attribute5,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE5,
icx_header_attribute5),
HEADER_ATTRIBUTE6 = decode(icx_header_attribute6,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE6,
icx_header_attribute6),
HEADER_ATTRIBUTE7 = decode(icx_header_attribute7,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE7,
icx_header_attribute7),
HEADER_ATTRIBUTE8 = decode(icx_header_attribute8,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE8,
icx_header_attribute8),
HEADER_ATTRIBUTE9 = decode(icx_header_attribute9,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE9,
icx_header_attribute9),
HEADER_ATTRIBUTE10 = decode(icx_header_attribute10,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE10,
icx_header_attribute10),
HEADER_ATTRIBUTE11 = decode(icx_header_attribute11,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE11,
icx_header_attribute11),
HEADER_ATTRIBUTE12 = decode(icx_header_attribute12,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE12,
icx_header_attribute12),
HEADER_ATTRIBUTE13 = decode(icx_header_attribute13,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE13,
icx_header_attribute13),
HEADER_ATTRIBUTE14 = decode(icx_header_attribute14,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE14,
icx_header_attribute14),
HEADER_ATTRIBUTE15 = decode(icx_header_attribute15,
FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE15,
icx_header_attribute15),
RESERVED_PO_NUM = l_po_number,
LAST_UPDATE_DATE = sysdate
where CART_ID = v_cart_id
and SHOPPER_ID = v_shopper_id;
update icx_cart_distributions
SET LAST_UPDATE_DATE = sysdate,
CHARGE_ACCOUNT_SEGMENT1 = decode(icx_charge_acct_seg1,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT1,
icx_charge_acct_seg1),
CHARGE_ACCOUNT_SEGMENT2 = decode(icx_charge_acct_seg2,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT2,
icx_charge_acct_seg2),
CHARGE_ACCOUNT_SEGMENT3 = decode(icx_charge_acct_seg3,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT3,
icx_charge_acct_seg3),
CHARGE_ACCOUNT_SEGMENT4 = decode(icx_charge_acct_seg4,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT4,
icx_charge_acct_seg4),
CHARGE_ACCOUNT_SEGMENT5 = decode(icx_charge_acct_seg5,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT5,
icx_charge_acct_seg5),
CHARGE_ACCOUNT_SEGMENT6 = decode(icx_charge_acct_seg6,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT6,
icx_charge_acct_seg6),
CHARGE_ACCOUNT_SEGMENT7 = decode(icx_charge_acct_seg7,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT7,
icx_charge_acct_seg7),
CHARGE_ACCOUNT_SEGMENT8 = decode(icx_charge_acct_seg8,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT8,
icx_charge_acct_seg8),
CHARGE_ACCOUNT_SEGMENT9 = decode(icx_charge_acct_seg9,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT9,
icx_charge_acct_seg9),
CHARGE_ACCOUNT_SEGMENT10 = decode(icx_charge_acct_seg10,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT10,
icx_charge_acct_seg10),
CHARGE_ACCOUNT_SEGMENT11 = decode(icx_charge_acct_seg11,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT11,
icx_charge_acct_seg11),
CHARGE_ACCOUNT_SEGMENT12 = decode(icx_charge_acct_seg12,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT12,
icx_charge_acct_seg12),
CHARGE_ACCOUNT_SEGMENT13 = decode(icx_charge_acct_seg13,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT13,
icx_charge_acct_seg13),
CHARGE_ACCOUNT_SEGMENT14 = decode(icx_charge_acct_seg14,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT14,
icx_charge_acct_seg14),
CHARGE_ACCOUNT_SEGMENT15 = decode(icx_charge_acct_seg15,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT15,
icx_charge_acct_seg15),
CHARGE_ACCOUNT_SEGMENT16 = decode(icx_charge_acct_seg16,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT16,
icx_charge_acct_seg16),
CHARGE_ACCOUNT_SEGMENT17 = decode(icx_charge_acct_seg17,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT17,
icx_charge_acct_seg17),
CHARGE_ACCOUNT_SEGMENT18 = decode(icx_charge_acct_seg18,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT18,
icx_charge_acct_seg18),
CHARGE_ACCOUNT_SEGMENT19 = decode(icx_charge_acct_seg19,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT19,
icx_charge_acct_seg19),
CHARGE_ACCOUNT_SEGMENT20 = decode(icx_charge_acct_seg20,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT20,
icx_charge_acct_seg20),
CHARGE_ACCOUNT_SEGMENT21 = decode(icx_charge_acct_seg21,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT21,
icx_charge_acct_seg21),
CHARGE_ACCOUNT_SEGMENT22 = decode(icx_charge_acct_seg22,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT22,
icx_charge_acct_seg22),
CHARGE_ACCOUNT_SEGMENT23 = decode(icx_charge_acct_seg23,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT23,
icx_charge_acct_seg23),
CHARGE_ACCOUNT_SEGMENT24 = decode(icx_charge_acct_seg24,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT24,
icx_charge_acct_seg24),
CHARGE_ACCOUNT_SEGMENT25 = decode(icx_charge_acct_seg25,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT25,
icx_charge_acct_seg25),
CHARGE_ACCOUNT_SEGMENT26 = decode(icx_charge_acct_seg26,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT26,
icx_charge_acct_seg26),
CHARGE_ACCOUNT_SEGMENT27 = decode(icx_charge_acct_seg27,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT27,
icx_charge_acct_seg27),
CHARGE_ACCOUNT_SEGMENT28 = decode(icx_charge_acct_seg28,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT28,
icx_charge_acct_seg28),
CHARGE_ACCOUNT_SEGMENT29 = decode(icx_charge_acct_seg29,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT29,
icx_charge_acct_seg29),
CHARGE_ACCOUNT_SEGMENT30 = decode(icx_charge_acct_seg30,
FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT30,
icx_charge_acct_seg30)
where
cart_id = v_cart_id;
update icx_shopping_carts
set DELIVER_TO_LOCATION_ID = p_deliver_to_location_id,
DELIVER_TO_LOCATIOn = p_deliver_to_location,
DESTINATION_ORGANIZATION_ID = p_deliver_to_org_id
where CART_ID = v_cart_id
and SHOPPER_ID = v_shopper_id;
update icx_shopping_Carts
set DELIVER_TO_LOCATION_ID = null,
DESTINATION_ORGANIZATION_ID = d_org_id,
DELIVER_TO_LOCATION = null
where CART_ID = v_cart_id
and SHOPPER_ID = v_shopper_id;
update icx_shopping_carts
set DELIVER_TO_REQUESTOR_ID = requesterID,
DELIVER_TO_REQUESTOR = p_requester
where CART_ID = v_cart_id
and SHOPPER_ID = v_shopper_id;
update icx_shopping_Carts
set DELIVER_TO_REQUESTOR_ID = null,
DELIVER_TO_REQUESTOR = null
where CART_ID = v_cart_id
and SHOPPER_ID = v_shopper_id;
update icx_shopping_carts
set APPROVER_ID = requesterID,
APPROVER_NAME = p_requester
where cart_id = v_cart_id
and shopper_id = v_shopper_id;
update icx_shopping_carts
set APPROVER_ID = null,
APPROVER_NAME = null
where cart_id = v_cart_id
and shopper_id = v_shopper_id;
update icx_shopping_cart_lines
set LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = v_shopper_id,
CATEGORY_ID = decode(c_category_id, 0, CATEGORY_ID,
a_category_id(i)),
ITEM_REVISION = decode(c_item_revision, 0, ITEM_REVISION,
a_item_revision(i)),
NEED_BY_DATE = decode(c_line_need_date, 0, NEED_BY_DATE,
v_need_date),
ITEM_DESCRIPTION = decode(c_item_description,
0 , ITEM_DESCRIPTION,
a_item_description(i)),
EXPENDITURE_ITEM_DATE = decode(c_expend_item_date,
0, EXPENDITURE_ITEM_DATE, v_expend_date),
EXPENDITURE_ORGANIZATION_ID = decode(c_expend_org, 0,
EXPENDITURE_ORGANIZATION_ID, a_expend_org(i)),
QUANTITY = decode(c_qty_va, 0, QUANTITY, n_number),
SUGGESTED_BUYER_ID = decode(c_suggested_buyer_id,
0, SUGGESTED_BUYER_ID, a_suggested_buyer_id(i)),
PROJECT_ID = decode(c_project_id, 0, PROJECT_ID,
a_project_id(i)),
SUGGESTED_VENDOR_CONTACT = decode(c_suggested_vendor_contacta,
0, SUGGESTED_VENDOR_CONTACT,
a_suggested_vendor_contacta(i)),
SUGGESTED_VENDOR_ITEM_NUM = decode(c_suggested_vendor_item_numa,
0, SUGGESTED_VENDOR_ITEM_NUM,
a_suggested_vendor_item_numa(i)),
SUGGESTED_VENDOR_NAME = decode(c_suggested_vendor_namea,
0, SUGGESTED_VENDOR_NAME,
a_suggested_vendor_namea(i)),
SUGGESTED_VENDOR_PHONE = decode(c_suggested_vendor_phonea,
0, SUGGESTED_VENDOR_PHONE,
a_suggested_vendor_phonea(i)),
SUGGESTED_VENDOR_SITE = decode(c_suggested_vendor_sitea,
0, SUGGESTED_VENDOR_SITE,
a_suggested_vendor_sitea(i)),
TASK_ID = decode(c_task_id, 0, TASK_ID, a_task_id(i)),
UNIT_OF_MEASURE = decode(c_unit_of_measurement,
0, UNIT_OF_MEASURE,
a_unit_of_measurement(i)),
UNIT_PRICE = decode(c_unit_price, 0, UNIT_PRICE,
a_unit_price(i)),
DELIVER_TO_LOCATION_ID = decode(c_deliver_to_location_id_l,
0, DELIVER_TO_LOCATION_ID,
a_deliver_to_location_id_l(i)),
DESTINATION_ORGANIZATION_ID = decode(c_dest_org_id_l,
0, DESTINATION_ORGANIZATION_ID,
a_dest_org_id_l(i)),
DELIVER_TO_LOCATION = decode(c_deliver_to_location_l,
0, DELIVER_TO_LOCATION,
a_deliver_to_location_l(i)),
LINE_ATTRIBUTE1 = decode(c_line_attribute_1a,
0, LINE_ATTRIBUTE1,
a_line_attribute_1a(i)),
LINE_ATTRIBUTE2 = decode(c_line_attribute_2a,
0, LINE_ATTRIBUTE2,
a_line_attribute_2a(i)),
LINE_ATTRIBUTE3 = decode(c_line_attribute_3a,
0, LINE_ATTRIBUTE3,
a_line_attribute_3a(i)),
LINE_ATTRIBUTE4 = decode(c_line_attribute_4a,
0, LINE_ATTRIBUTE4,
a_line_attribute_4a(i)),
LINE_ATTRIBUTE5 = decode(c_line_attribute_5a,
0, LINE_ATTRIBUTE5,
a_line_attribute_5a(i)),
LINE_ATTRIBUTE6 = decode(c_line_attribute_6a,
0, LINE_ATTRIBUTE6,
a_line_attribute_6a(i)),
LINE_ATTRIBUTE7 = decode(c_line_attribute_7a,
0, LINE_ATTRIBUTE7,
a_line_attribute_7a(i)),
LINE_ATTRIBUTE8 = decode(c_line_attribute_8a,
0, LINE_ATTRIBUTE8,
a_line_attribute_8a(i)),
LINE_ATTRIBUTE9 = decode(c_line_attribute_9a,
0, LINE_ATTRIBUTE9,
a_line_attribute_9a(i)),
LINE_ATTRIBUTE10 = decode(c_line_attribute_10a,
0, LINE_ATTRIBUTE10,
a_line_attribute_10a(i)),
LINE_ATTRIBUTE11 = decode(c_line_attribute_11a,
0, LINE_ATTRIBUTE11,
a_line_attribute_11a(i)),
LINE_ATTRIBUTE12 = decode(c_line_attribute_12a,
0, LINE_ATTRIBUTE12,
a_line_attribute_12a(i)),
LINE_ATTRIBUTE13 = decode(c_line_attribute_13a,
0, LINE_ATTRIBUTE13,
a_line_attribute_13a(i)),
LINE_ATTRIBUTE14 = decode(c_line_attribute_14a,
0, LINE_ATTRIBUTE14,
a_line_attribute_14a(i)),
LINE_ATTRIBUTE15 = decode(c_line_attribute_15a,
0, LINE_ATTRIBUTE15,
a_line_attribute_15a(i))
--bug 690784 command out the following line and add two lines
-- where CART_LINE_ID = icx_cart_line_ida(i);
update icx_cart_line_distributions
set LAST_UPDATE_DATE = sysdate,
CHARGE_ACCOUNT_SEGMENT1 = decode(c_charge_acct_seg1a,
0, CHARGE_ACCOUNT_SEGMENT1,
a_charge_acct_seg1a(i)),
CHARGE_ACCOUNT_SEGMENT2 = decode(c_charge_acct_seg2a,
0, CHARGE_ACCOUNT_SEGMENT2,
a_charge_acct_seg2a(i)),
CHARGE_ACCOUNT_SEGMENT3 = decode(c_charge_acct_seg3a,
0, CHARGE_ACCOUNT_SEGMENT3,
a_charge_acct_seg3a(i)),
CHARGE_ACCOUNT_SEGMENT4 = decode(c_charge_acct_seg4a,
0, CHARGE_ACCOUNT_SEGMENT4,
a_charge_acct_seg4a(i)),
CHARGE_ACCOUNT_SEGMENT5 = decode(c_charge_acct_seg5a,
0, CHARGE_ACCOUNT_SEGMENT5,
a_charge_acct_seg5a(i)),
CHARGE_ACCOUNT_SEGMENT6 = decode(c_charge_acct_seg6a,
0, CHARGE_ACCOUNT_SEGMENT6,
a_charge_acct_seg6a(i)),
CHARGE_ACCOUNT_SEGMENT7 = decode(c_charge_acct_seg7a,
0, CHARGE_ACCOUNT_SEGMENT7,
a_charge_acct_seg7a(i)),
CHARGE_ACCOUNT_SEGMENT8 = decode(c_charge_acct_seg8a,
0, CHARGE_ACCOUNT_SEGMENT8,
a_charge_acct_seg8a(i)),
CHARGE_ACCOUNT_SEGMENT9 = decode(c_charge_acct_seg9a,
0, CHARGE_ACCOUNT_SEGMENT9,
a_charge_acct_seg9a(i)),
CHARGE_ACCOUNT_SEGMENT10 = decode(c_charge_acct_seg10a,
0, CHARGE_ACCOUNT_SEGMENT10,
a_charge_acct_seg10a(i)),
CHARGE_ACCOUNT_SEGMENT11 = decode(c_charge_acct_seg11a,
0, CHARGE_ACCOUNT_SEGMENT11,
a_charge_acct_seg11a(i)),
CHARGE_ACCOUNT_SEGMENT12 = decode(c_charge_acct_seg12a,
0, CHARGE_ACCOUNT_SEGMENT12,
a_charge_acct_seg12a(i)),
CHARGE_ACCOUNT_SEGMENT13 = decode(c_charge_acct_seg13a,
0, CHARGE_ACCOUNT_SEGMENT13,
a_charge_acct_seg13a(i)),
CHARGE_ACCOUNT_SEGMENT14 = decode(c_charge_acct_seg14a,
0, CHARGE_ACCOUNT_SEGMENT14,
a_charge_acct_seg14a(i)),
CHARGE_ACCOUNT_SEGMENT15 = decode(c_charge_acct_seg15a,
0, CHARGE_ACCOUNT_SEGMENT15,
a_charge_acct_seg15a(i)),
CHARGE_ACCOUNT_SEGMENT16 = decode(c_charge_acct_seg16a,
0, CHARGE_ACCOUNT_SEGMENT16,
a_charge_acct_seg16a(i)),
CHARGE_ACCOUNT_SEGMENT17 = decode(c_charge_acct_seg17a,
0, CHARGE_ACCOUNT_SEGMENT17,
a_charge_acct_seg17a(i)),
CHARGE_ACCOUNT_SEGMENT18 = decode(c_charge_acct_seg18a,
0, CHARGE_ACCOUNT_SEGMENT18,
a_charge_acct_seg18a(i)),
CHARGE_ACCOUNT_SEGMENT19 = decode(c_charge_acct_seg19a,
0, CHARGE_ACCOUNT_SEGMENT19,
a_charge_acct_seg19a(i)),
CHARGE_ACCOUNT_SEGMENT20 = decode(c_charge_acct_seg20a,
0, CHARGE_ACCOUNT_SEGMENT20,
a_charge_acct_seg20a(i)),
CHARGE_ACCOUNT_SEGMENT21 = decode(c_charge_acct_seg21a,
0, CHARGE_ACCOUNT_SEGMENT21,
a_charge_acct_seg21a(i)),
CHARGE_ACCOUNT_SEGMENT22 = decode(c_charge_acct_seg22a,
0, CHARGE_ACCOUNT_SEGMENT22,
a_charge_acct_seg22a(i)),
CHARGE_ACCOUNT_SEGMENT23 = decode(c_charge_acct_seg23a,
0, CHARGE_ACCOUNT_SEGMENT23,
a_charge_acct_seg23a(i)),
CHARGE_ACCOUNT_SEGMENT24 = decode(c_charge_acct_seg24a,
0, CHARGE_ACCOUNT_SEGMENT24,
a_charge_acct_seg24a(i)),
CHARGE_ACCOUNT_SEGMENT25 = decode(c_charge_acct_seg25a,
0, CHARGE_ACCOUNT_SEGMENT25,
a_charge_acct_seg25a(i)),
CHARGE_ACCOUNT_SEGMENT26 = decode(c_charge_acct_seg26a,
0, CHARGE_ACCOUNT_SEGMENT26,
a_charge_acct_seg26a(i)),
CHARGE_ACCOUNT_SEGMENT27 = decode(c_charge_acct_seg27a,
0, CHARGE_ACCOUNT_SEGMENT27,
a_charge_acct_seg27a(i)),
CHARGE_ACCOUNT_SEGMENT28 = decode(c_charge_acct_seg28a,
0, CHARGE_ACCOUNT_SEGMENT28,
a_charge_acct_seg28a(i)),
CHARGE_ACCOUNT_SEGMENT29 = decode(c_charge_acct_seg29a,
0, CHARGE_ACCOUNT_SEGMENT29,
a_charge_acct_seg29a(i)),
CHARGE_ACCOUNT_SEGMENT30 = decode(c_charge_acct_seg30a,
0, CHARGE_ACCOUNT_SEGMENT30,
a_charge_acct_seg30a(i))
where CART_LINE_ID = icx_cart_line_ida(i);
update icx_shopping_cart_lines
set (DELIVER_TO_LOCATION_ID, DESTINATION_ORGANIZATION_ID) =
(select hrl.location_id,
nvl(hrl.inventory_organization_id,
fsp.inventory_organization_id)
from hr_locations hrl,
financials_system_parameters fsp
where sysdate < nvl(hrl.inactive_date, sysdate + 1)
and hrl.location_code =
icx_shopping_cart_lines.DELIVER_TO_LOCATION)
where DELIVER_TO_LOCATION_ID is null
and CART_ID = v_cart_id;
update icx_shopping_cart_lines
set (DELIVER_TO_LOCATION_ID, DESTINATION_ORGANIZATION_ID,
DELIVER_TO_LOCATION) =
(select DELIVER_TO_LOCATION_ID,
DESTINATION_ORGANIZATION_ID,
DELIVER_TO_LOCATION
from icx_shopping_Carts
where cart_id = v_cart_id)
where cart_id = v_cart_id;
update icx_shopping_cart_lines
set (SUGGESTED_VENDOR_CONTACT, SUGGESTED_VENDOR_PHONE,
SUGGESTED_VENDOR_NAME, SUGGESTED_VENDOR_SITE) =
(select SUGGESTED_VENDOR_CONTACT, SUGGESTED_VENDOR_PHONE,
SUGGESTED_VENDOR_NAME, SUGGESTED_VENDOR_SITE
from icx_shopping_cart_lines
where cart_id = v_cart_id
and cart_line_number = (select min(cart_line_number)
from icx_shopping_cart_lines
where cart_id = v_cart_id
and suggested_vendor_name is not NULL))
where cart_id = v_cart_id
and SUGGESTED_VENDOR_NAME is null;
select count(*) into v_exist
from gl_sets_of_books gsb,
financials_system_parameters fsp,
gl_code_combinations gl
where gsb.SET_OF_BOOKS_ID = fsp.set_of_books_id
and gsb.CHART_OF_ACCOUNTS_ID = gl.CHART_OF_ACCOUNTS_ID
and gl.CODE_COMBINATION_ID = v_account_id;
update icx_cart_line_distributions
set CHARGE_ACCOUNT_ID = v_account_id,
ACCRUAL_ACCOUNT_ID = v_accrual_acct_id,
VARIANCE_ACCOUNT_ID = v_variance_acct_id,
BUDGET_ACCOUNT_ID = v_budget_acct_id
where CART_LINE_ID = icx_cart_line_ida(i);
delete icx_cart_line_distributions
where cart_line_id = prec.cart_line_id;
delete icx_shopping_cart_lines
where (quantity is null
or quantity = 0)
and cart_id = v_cart_id;
--that just updated valid locations
v_errored := FALSE;
update icx_shopping_cart_lines
set (DELIVER_TO_LOCATION_ID,
DESTINATION_ORGANIZATION_ID) =
(select null,
DESTINATION_ORGANIZATION_ID
from icx_shopping_carts
where cart_id = v_cart_id)
where cart_id = v_cart_id
and DELIVER_TO_LOCATION_ID is null;
SELECT count(distinct(SUGGESTED_VENDOR_NAME)) into supp_count
FROM icx_shopping_cart_lines
where cart_id = v_cart_id;
update icx_shopping_Carts
set saved_flag = 1
where cart_id = v_cart_id
and nvl(org_id,-9999) = nvl(n_org_id,-9999);
update icx_shopping_Carts
set saved_flag = 0
where cart_id = v_cart_id
and nvl(org_id, -9999) = nvl(n_org_id, -9999);
select substr(l_error_message,12,512) into l_err_mesg from dual;
update icx_shopping_carts
set saved_flag = '2'
where saved_flag = '0'
and cart_id = v_cart_id
and nvl(org_id, -9999) = nvl(n_org_id, -9999);
update icx_shopping_carts
set saved_flag = '4'
where cart_id = v_cart_id
and nvl(org_id, -9999) = nvl(n_org_id, -9999);
-- delete the cart
delete icx_shopping_carts
where cart_id = v_cart_id
and shopper_id = v_shopper_id;
delete icx_cart_line_distributions
where cart_id = v_cart_id;
delete icx_shopping_cart_lines
where cart_id = v_cart_id;
delete icx_cart_distributions
where cart_id = v_cart_id;
select substr(l_error_message,12,512) into l_err_mesg from dual;