[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.code_combination_id code_combination_id
from po_req_distributions a,
po_requisition_lines b,
po_requisition_headers c
where a.requisition_line_id = v_cart_line_id
and a.requisition_line_id = b.requisition_line_id
and b.requisition_header_id = v_cart_id
and b.requisition_header_id = c.requisition_header_id
and nvl(a.org_id,-9999) = nvl(v_oo_id,-9999)
order by distribution_id;
select count(*)
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 = acct_id;
select a.line_num
from po_requisition_lines a,
po_requisition_headers b
where a.requisition_line_id = cartline_id
and a.requisition_header_id = cartid
and a.requisition_header_id = b.requisition_header_id
and nvl(a.org_id,-9999) = nvl(oo_id,-9999);
select line_num
from po_requisition_lines
where requisition_line_id = cartline_id
and requisition_header_id = cartid
and nvl(org_id,-9999) = nvl(oo_id,-9999);
PROCEDURE insert_row(v_cart_line_id IN NUMBER,
v_oo_id IN NUMBER,
v_cart_id IN NUMBER,
v_account_id IN NUMBER default NULL,
v_n_segments IN NUMBER default NULL,
v_segments IN fnd_flex_ext.SegmentArray,
v_account_num IN VARCHAR2 default NULL,
v_allocation_type IN VARCHAR2 default NULL,
v_allocation_value IN NUMBER default NULL,
v_line_quantity IN NUMBER default NULL) is
v_col_name varchar2(100);
l_insert_sql varchar2(8000);
select max(a.distribution_num)
from po_req_distributions a,
po_requisition_lines b,
po_requisition_headers c
where a.requisition_line_id = v_cart_line_id
and b.requisition_line_id = a.requisition_line_id
and b.requisition_header_id = v_cart_id
and b.requisition_header_id = c.requisition_header_id
and nvl(a.org_id,-9999) = nvl(v_oo_id,-9999);
select a.line_num
from po_requisition_lines a,
po_requisition_headers b
where a.requisition_line_id = v_cart_line_id
and a.requisition_header_id = v_cart_id
and a.requisition_header_id = b.requisition_header_id
and nvl(a.org_id,-9999) = nvl(v_oo_id,-9999);
select hecv.default_code_combination_id employee_default_account_id,
hecv.organization_id employee_org_id,
hecv.business_group_id employee_bus_group_id,
fsp.org_id po_org_id,
isc.NEED_BY_DATE,
isc.DESTINATION_TYPE_CODE,
isc.DESTINATION_ORGANIZATION_ID,
isc.DELIVER_TO_LOCATION_ID,
fsp.set_of_books_id,
isc.ITEM_ID,
isc.ITEM_REVISION,
isc.item_description,
msi.expense_account,
isc.UNIT_MEAS_LOOKUP_CODE,
isc.QUANTITY,
isc.UNIT_PRICE,
isc.CATEGORY_ID,
isc.LINE_TYPE_ID,
isc.SUGGESTED_VENDOR_NAME,
isc.SUGGESTED_VENDOR_LOCATION
FROM financials_system_parameters fsp,
hr_employees_current_v hecv,
mtl_system_items msi,
po_requisition_lines isc
where isc.requisition_line_id = reqline_id
and msi.INVENTORY_ITEM_ID (+) = isc.ITEM_ID
and nvl(msi.ORGANIZATION_ID, isc.DESTINATION_ORGANIZATION_ID) = isc.DESTINATION_ORGANIZATION_ID
and hecv.EMPLOYEE_ID = emp_id
and nvl(isc.org_id, -9999) = nvl(v_oo_id, -9999);
select to_person_id
from po_requisition_lines a,
po_requisition_headers b
where a.requisition_line_id = line_id
and a.requisition_header_id = v_cart_id
and a.requisition_header_id = b.requisition_header_id
and nvl(a.org_id,-9999) = nvl(v_oo_id,-9999);
SELECT fsp.set_of_books_id
FROM financials_system_parameters fsp,
gl_sets_of_books gsob
WHERE gsob.set_of_books_id = fsp.set_of_books_id
AND nvl(fsp.org_id, -9999) = nvl(v_oo_id, -9999);
select po_req_distributions_s.nextval into v_distribution_id
from sys.dual;
INSERT INTO po_req_distributions (distribution_id,
last_update_date,
last_updated_by,
requisition_line_id,
set_of_books_id,
code_combination_id,
req_line_quantity,
last_update_login,
creation_date,
created_by,
distribution_num,
allocation_type,
allocation_value)
VALUES (v_distribution_id, -- bug 689962
sysdate,
l_shopper_id,
v_cart_line_id,
v_set_of_books_id,
v_account_id,
v_line_quantity,
l_shopper_id,
sysdate,
l_shopper_id,
l_dist_num,
v_allocation_type,
v_allocation_value);
UPDATE po_req_distributions
SET accrual_account_id=
(SELECT accrued_code_combination_id
FROM po_system_parameters),
budget_account_id = v_account_id,
variance_account_id = v_account_id
WHERE requisition_line_id = v_cart_line_id
-- bug 689962 only the current distribution needs to be update
AND distribution_id = v_distribution_id
-- end
AND nvl(org_id,-9999) = nvl(v_oo_id,-9999);
update po_req_distributions a
set a.ACCRUAL_ACCOUNT_ID = NVL(v_accrual_acct_id, a.accrual_account_id),
a.VARIANCE_ACCOUNT_ID = NVL(v_variance_acct_id, a.variance_account_id),
a.BUDGET_ACCOUNT_ID = NVL(v_budget_acct_id, a.budget_account_id)
where a.requisition_line_id = v_cart_line_id
-- bug 689962, only apply customization to current distribution
and a.distribution_id = v_distribution_id
-- end
and nvl(a.org_id,-9999) = nvl(v_oo_id,-9999);
select substr(l_error_message,12,512) into l_err_mesg from dual;
PROCEDURE update_row(v_cart_line_id IN NUMBER,
v_oo_id IN NUMBER,
v_cart_id IN NUMBER,
v_distribution_id IN NUMBER,
v_line_number IN NUMBER,
v_account_id IN NUMBER default NULL,
v_n_segments IN NUMBER default NULL,
v_segments IN fnd_flex_ext.SegmentArray,
v_account_num IN VARCHAR2 default NULL,
v_allocation_type IN VARCHAR2 default NULL,
v_allocation_value IN NUMBER default NULL,
v_line_quantity IN NUMBER default NULL) is
cursor get_ak_columns is
select ltrim(rtrim(d.COLUMN_NAME)) COL_NAME
from ak_region_items a,
ak_attributes b,
ak_regions c,
ak_object_attributes d
where a.NODE_DISPLAY_FLAG = 'Y'
and a.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
and a.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
and b.DATA_TYPE = 'VARCHAR2'
and c.REGION_APPLICATION_ID = 601
and a.REGION_CODE = c.REGION_CODE
and a.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
and c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
and a.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
and a.region_code = 'ICX_CART_LINE_DISTRIBUTIONS_R'
and d.COLUMN_NAME like 'CHARGE_ACCOUNT_SEGMENT%'
order by a.display_sequence;
l_insert_sql varchar2(8000);
select cart_line_number
from icx_shopping_cart_lines
where cart_id = v_cart_id
and cart_line_id = v_cart_line_id;
l_insert_sql := 'Update icx_cart_line_distributions set
last_updated_by = ' || l_shopper_id
|| ' ,last_update_login = ' || l_shopper_id
|| ' ,last_update_date = sysdate';
l_insert_sql := l_insert_sql || ', allocation_type = ''' || v_allocation_type || '''';
l_insert_sql := l_insert_sql || ', allocation_value = ' || v_allocation_value;
l_insert_sql := l_insert_sql || ', charge_account_id = ' || v_account_id;
l_insert_sql := l_insert_sql || ', charge_account_num = ''' || v_account_num || '''';
l_insert_sql := 'Update icx_cart_line_distributions
set last_updated_by = :last_updated_by,
last_update_login = :last_update_login ,
last_update_date = :last_update_date,
allocation_type = decode(:allocation_type, null, allocation_type, :allocation_type ),
allocation_value = decode( :allocation_value, null, allocation_value, :allocation_value ),
charge_account_id = decode( :charge_account_id , null, charge_account_id, :charge_account_id ),
charge_account_num = decode( :charge_account_num, null, charge_account_num, :charge_account_num )';
l_insert_sql := l_insert_sql || ',' || prec.COL_NAME || ' = ''' || ':a' || to_char(l) || '''';
l_insert_sql := l_insert_sql || ' where cart_id = ' || v_cart_id ||
' and cart_line_id = ' || v_cart_line_id || ' and distribution_id = ' || v_distribution_id;
l_insert_sql := l_insert_sql || ' where cart_id = :cart_id and cart_line_id = :cart_line_id and distribution_id = :distribution_id ';
dbms_sql.parse( v_cursor_id, l_insert_sql, DBMS_SQL.native);
dbms_sql.bind_variable(v_cursor_id, ':last_updated_by', l_shopper_id );
dbms_sql.bind_variable(v_cursor_id, ':last_update_date', sysdate );
dbms_sql.bind_variable(v_cursor_id, ':last_update_login', l_shopper_id );
dbms_sql.parse(l_call,l_insert_sql ,dbms_sql.native);
update icx_cart_line_distributions
set 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 = v_cart_line_id
and CART_ID = v_cart_id
and DISTRIBUTION_ID = v_distribution_id;
select substr(l_error_message,12,512) into l_err_mesg from dual;
/* Find Account Id based on concatenated segments passin and update the account
* distribution tables based on account id found.
* Pass in distribution id to update existing account, and insert a new row
* if distiribution id is not passed.*/
/* NOTE: this is used when no segments are turned on in AK for display or
update, so only update the charge account id and charge account num */
PROCEDURE update_account_num(v_cart_id IN NUMBER,
v_cart_line_id IN NUMBER,
v_oo_id IN NUMBER,
v_account_num IN VARCHAR2,
v_distribution_id IN NUMBER default NULL,
v_line_number IN NUMBER default NULL,
v_allocation_type IN VARCHAR2 default NULL,
v_allocation_value IN NUMBER default NULL,
v_validate_flag IN VARCHAR2 default 'Y',
v_line_quantity IN VARCHAR2 default NULL) is
v_error_message varchar(1000);
select max(a.distribution_num)
from po_req_distributions a,
po_requisition_lines b,
po_requisition_headers c
where a.requisition_line_id = cartline_id
and a.requisition_line_id = b.requisition_line_id
and b.requisition_header_id = v_cart_id
and b.requisition_header_id = c.requisition_header_id
and nvl(a.org_id, -9999) = nvl(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 a.line_num
from po_requisition_lines a,
po_requisition_headers b
where a.requisition_line_id = cartline_id
and a.requisition_header_id = v_cart_id
and a.requisition_header_id = b.requisition_header_id
and nvl(a.org_id, -9999) = nvl(oo_id,-9999);
any segment in the insert_row or update_row procedure */
v_n_segments := 0;
insert_row(v_cart_line_id,v_oo_id,v_cart_id, v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value, v_line_quantity);
update_row(v_cart_line_id,v_oo_id,v_cart_id,v_distribution_id,v_line_number,v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value);
/* Find Account Id based on table of segments passin and update the account
* distribution tables based on account id found
* Pass in distribution id to update existing account, and insert a new row
* if distiribution id is not passed.*/
PROCEDURE update_account(v_cart_id IN NUMBER,
v_cart_line_id IN NUMBER,
v_oo_id IN NUMBER,
v_segments IN fnd_flex_ext.SegmentArray,
v_distribution_id IN NUMBER default NULL,
v_line_number IN NUMBER default NULL,
v_allocation_type IN VARCHAR2 default NULL,
v_allocation_value IN NUMBER default NULL,
v_validate_flag IN VARCHAR2 default 'Y',
v_line_quantity IN VARCHAR2 default NULL) is
v_error_message varchar(1000);
select max(a.distribution_num)
from po_req_distributions a,
po_requisition_lines b,
po_requisition_headers c
where a.requisition_line_id = reqline_id
and a.requisition_line_id = b.requisition_line_id
and b.requisition_header_id = v_cart_id
and b.requisition_header_id = c.requisition_header_id
and nvl(a.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 a.line_num
from po_requisition_lines a,
po_requisition_headers b
where a.requisition_line_id = reqline_id
and a.requisition_header_id = v_cart_id
and a.requisition_header_id = b.requisition_header_id
and nvl(a.org_id, -9999) = nvl(v_oo_id,-9999);
insert_row(v_cart_line_id,v_oo_id,v_cart_id, v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value, to_number(v_line_quantity));
update_row(v_cart_line_id,v_oo_id,v_cart_id,v_distribution_id,v_line_number,v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value);
SELECT default_code_combination_id employee_default_account_id
from hr_employees_current_v
where employee_id = v_emp_id;
select msi.expense_account
from 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 iscl.cart_id = isc.cart_id
and iscl.cart_id = v_cart_id
and iscl.cart_line_id = v_cart_line_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
from icx_shopping_cart_lines
where cart_id = v_cart_id
and cart_line_id = v_cart_line_id
and nvl(org_id, -9999) = nvl(v_oo_id,-9999);
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;
insert_row(v_cart_line_id,v_oo_id,v_cart_id,v_account_id,v_n_segments,v_segments,v_account_num);
SELECT default_code_combination_id employee_default_account_id
from hr_employees_current_v
where employee_id = v_emp_id;
select msi.expense_account
from mtl_system_items msi,
po_requisition_headers poh,
po_requisition_lines pol
where msi.inventory_item_id(+) = pol.item_id
AND nvl(msi.ORGANIZATION_ID,
NVL(pol.DESTINATION_ORGANIZATION_ID, -999)) =
nvl(pol.DESTINATION_ORGANIZATION_ID, -999)
and pol.requisition_header_id = poh.requisition_header_id
and pol.requisition_header_id = v_cart_id
and pol.requisition_line_id = v_cart_line_id
and nvl(poh.org_id,-9999) = nvl(v_oo_id,-9999)
and nvl(pol.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 a.line_num
from po_requisition_lines a,
po_requisition_headers b
where a.requisition_header_id = v_cart_id
and a.requisition_header_id = b.requisition_header_id
and a.requisition_line_id = v_cart_line_id;
PROCEDURE update_account_by_id(v_cart_id IN NUMBER,
v_cart_line_id IN NUMBER,
v_oo_id IN NUMBER,
v_distribution_id IN NUMBER,
v_line_number IN NUMBER) is
v_segments fnd_flex_ext.SegmentArray;
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 charge_account_id
from icx_cart_line_distributions
where cart_id = cartid
and cart_line_id = cartline_id
and distribution_id = dist_id
and nvl(org_id,-9999) = nvl(oo_id,-9999);
select cart_line_number
from icx_shopping_cart_lines
where cart_id = cartid
and cart_line_id = cartline_id;
icx_req_acct2.update_row(v_cart_line_id,v_oo_id,v_cart_id,v_distribution_id,
v_line_number,v_account_id,v_n_segments,v_segments,
v_account_num);