客户化程序完成标准成本成批更新
You have to populate CST_ITEM_CST_DTLS_INTERFACE,CST_RESOURCE_COSTS_INTERFACE,
CST_RES_OVERHEADS_INTERFACE and CST_DEPT_OVERHEADS_INTERFACE accordingly.
The concurrent request can be launched by navigating to cost->cost mass
edits->Import Cost Information.
The parameters for the Cost Import concurrent program and their description is
as follows :
Import Cost Option : A LOV is provided from which the user can select one of
the import options which may be either to import Only item costs , Only
resource costs , Only overhead rates or all the cost information .
Option Table from which data is processed
---------- --------------------------------------------
Only item cost cst_item_cst_dtls_interface
Only resource costs cst_resource_costs_interface
Only overhead rates cst_res_overheads_interface ,
cst_dept_overheads_interface
All Cost Information From all the four interface tables
Mode to run this request : A LOV is provided with possible two
values , 'Insert new cost' or 'Remove and replace cost'.
The 'Insert new cost' mode , is useful if you are
importing large # of items and are not sure if that Item/Organization/Cost
Type combination already exists in the production tables, if it does then the
row in the interface table would be flaged as errored and not imported.This
would prevent any accidental overwrite of already existing data.
With 'Remove and replace cost' mode all the
previous cost information for this item, cost_type and organization combination
will be deleted from the production tables and the new information will
overwrite (replace) the already existing one.
Group Id Option : A LOV is provided from which the user can either
select 'ALL' or 'Specific Group Id' . If the user wishes to submit multiple
Cost Import process requests he can do so by submitting one request per group
id. For doing so the data in the interface tables should be stamped with
distinct group id value by using the NEXTVAL from the sequence generator
CST_LISTS_S .The use of this sequence geneartor is a MUST for generating
multiple groups or may lead to data corruption as these interface tables are
used by other processes too.
If the user selects "ALL" from the list then a group ID
generated by a sequence will replace the group ID in the interface tables (if
any) and all the unprocessed rows from the four interface table (viz.
cst_item_cst_dtls_interface , cst_resource_costs_interface ,
cst_res_overheads_interface , cst_dept_overheads_interface ) will be processed
in one run.
Cost type to import to : The user is provided with a LOV from which he
needs to select the cost type in which he wishes to import the cost
information. Even if the user has populated a cost type or cost type ID in the
interface tables, it would be overwritten with the one that is selected here.
The cost types that the user can pick from is restricted to the multi-org,
updateable cost types.
Delete succesfull rows : This parameter decides whether the
successfully processed rows should be deleted from the interface tables at the
end of the run. If the user selects 'Yes' then all the successful rows be
deleted, basically rows that do not have their error flag set to "E".
Importing directly into Frozen/Average cost type (i.e non updateable cost types)
and merging of new cost with existing costs is not supported at this time and
would still have to be processed by cost update routines. Also when importing
the costs from the interface table ,material overhead defaults (if any)
specified for an Organization/Category would not be respected.
Minimum columns in each table that the user needs to provide
1. CST_ITEM_CST_DTLS_INTERFACE
The columns that the user has to provide are
a. Inventory_item_id
b. organization_ID or organization_code.
c. resource_ID or resource_code for cost elements other than 1(material).
If we are importing cost into material cost element and default material
subelement has been specified on 'Define Organization parameters' form
then that would be respected unless the user overides it with a value
in this column.
d. usage_rate_or_amount
e. cost_element_ID or cost_element
f. Process Flag (must be set to 1)
We default values for based_on_rollup, shrinkage_rate, inventory_asset_flag,
lot_size from the row from CST_ITEM_COSTS for this item and the default cost
type(default cost type of the cost type specified to import the costs into). If
there is no such row already defined in CST_ITEM_COSTS, then, the values for
these 4 columns also need to be specified and they have to be the same for all
rows of this item, cost type and organization combination.
CST_RESOURCE_COSTS_INTERFACE
a. Resource_ID or resource_code
b. organization_ID or organization_code
c. resource_rate
d. Process_flag (must be set to 1)
CST_RES_OVERHEADS_INTERFACE
a. Resource_ID or resource_code
b. Overhead_ID or overhead
c. Organization_ID or organization_code
d. Process_flag(must be set to 1)
CST_DEPT_OVERHEADS_INTERFACE
a. Department_ID or department
b. Overhead_ID or overhead
c. Organization_ID
d. Rate_or_amount
e. Process_flag(must be set to 1)
The other columns will be defaulted.
This is available for only 11.5.9 and above customers.
Customers on 11.5.8 can apply Patch 2193391 to get this functionality.
Please refer to costing Manuals for further details.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
In case you have any questions please log a tar with costing group.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Sample cost update statement is below
INSERT INTO CST_ITEM_CST_DTLS_INTERFACE
(INVENTORY_ITEM_ID,ORGANIZATION_ID,COST_TYPE,RESOURCE_CODE,USAGE_RATE_OR_AMOUNT,
COST_ELEMENT_ID,PROCESS_FLAG)
VALUES
(9935,207,'Pending','Material',17,1,1);
commit;
Using this I update the pending cost for the item from $11 to $17.
Now once the pending cost is updated you will have to use 'Update Standard Cost'
CST_RES_OVERHEADS_INTERFACE and CST_DEPT_OVERHEADS_INTERFACE accordingly.
The concurrent request can be launched by navigating to cost->cost mass
edits->Import Cost Information.
The parameters for the Cost Import concurrent program and their description is
as follows :
Import Cost Option : A LOV is provided from which the user can select one of
the import options which may be either to import Only item costs , Only
resource costs , Only overhead rates or all the cost information .
Option Table from which data is processed
---------- --------------------------------------------
Only item cost cst_item_cst_dtls_interface
Only resource costs cst_resource_costs_interface
Only overhead rates cst_res_overheads_interface ,
cst_dept_overheads_interface
All Cost Information From all the four interface tables
Mode to run this request : A LOV is provided with possible two
values , 'Insert new cost' or 'Remove and replace cost'.
The 'Insert new cost' mode , is useful if you are
importing large # of items and are not sure if that Item/Organization/Cost
Type combination already exists in the production tables, if it does then the
row in the interface table would be flaged as errored and not imported.This
would prevent any accidental overwrite of already existing data.
With 'Remove and replace cost' mode all the
previous cost information for this item, cost_type and organization combination
will be deleted from the production tables and the new information will
overwrite (replace) the already existing one.
Group Id Option : A LOV is provided from which the user can either
select 'ALL' or 'Specific Group Id' . If the user wishes to submit multiple
Cost Import process requests he can do so by submitting one request per group
id. For doing so the data in the interface tables should be stamped with
distinct group id value by using the NEXTVAL from the sequence generator
CST_LISTS_S .The use of this sequence geneartor is a MUST for generating
multiple groups or may lead to data corruption as these interface tables are
used by other processes too.
If the user selects "ALL" from the list then a group ID
generated by a sequence will replace the group ID in the interface tables (if
any) and all the unprocessed rows from the four interface table (viz.
cst_item_cst_dtls_interface , cst_resource_costs_interface ,
cst_res_overheads_interface , cst_dept_overheads_interface ) will be processed
in one run.
Cost type to import to : The user is provided with a LOV from which he
needs to select the cost type in which he wishes to import the cost
information. Even if the user has populated a cost type or cost type ID in the
interface tables, it would be overwritten with the one that is selected here.
The cost types that the user can pick from is restricted to the multi-org,
updateable cost types.
Delete succesfull rows : This parameter decides whether the
successfully processed rows should be deleted from the interface tables at the
end of the run. If the user selects 'Yes' then all the successful rows be
deleted, basically rows that do not have their error flag set to "E".
Importing directly into Frozen/Average cost type (i.e non updateable cost types)
and merging of new cost with existing costs is not supported at this time and
would still have to be processed by cost update routines. Also when importing
the costs from the interface table ,material overhead defaults (if any)
specified for an Organization/Category would not be respected.
Minimum columns in each table that the user needs to provide
1. CST_ITEM_CST_DTLS_INTERFACE
The columns that the user has to provide are
a. Inventory_item_id
b. organization_ID or organization_code.
c. resource_ID or resource_code for cost elements other than 1(material).
If we are importing cost into material cost element and default material
subelement has been specified on 'Define Organization parameters' form
then that would be respected unless the user overides it with a value
in this column.
d. usage_rate_or_amount
e. cost_element_ID or cost_element
f. Process Flag (must be set to 1)
We default values for based_on_rollup, shrinkage_rate, inventory_asset_flag,
lot_size from the row from CST_ITEM_COSTS for this item and the default cost
type(default cost type of the cost type specified to import the costs into). If
there is no such row already defined in CST_ITEM_COSTS, then, the values for
these 4 columns also need to be specified and they have to be the same for all
rows of this item, cost type and organization combination.
CST_RESOURCE_COSTS_INTERFACE
a. Resource_ID or resource_code
b. organization_ID or organization_code
c. resource_rate
d. Process_flag (must be set to 1)
CST_RES_OVERHEADS_INTERFACE
a. Resource_ID or resource_code
b. Overhead_ID or overhead
c. Organization_ID or organization_code
d. Process_flag(must be set to 1)
CST_DEPT_OVERHEADS_INTERFACE
a. Department_ID or department
b. Overhead_ID or overhead
c. Organization_ID
d. Rate_or_amount
e. Process_flag(must be set to 1)
The other columns will be defaulted.
This is available for only 11.5.9 and above customers.
Customers on 11.5.8 can apply Patch 2193391 to get this functionality.
Please refer to costing Manuals for further details.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
In case you have any questions please log a tar with costing group.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Sample cost update statement is below
INSERT INTO CST_ITEM_CST_DTLS_INTERFACE
(INVENTORY_ITEM_ID,ORGANIZATION_ID,COST_TYPE,RESOURCE_CODE,USAGE_RATE_OR_AMOUNT,
COST_ELEMENT_ID,PROCESS_FLAG)
VALUES
(9935,207,'Pending','Material',17,1,1);
commit;
Using this I update the pending cost for the item from $11 to $17.
Now once the pending cost is updated you will have to use 'Update Standard Cost'
Concurrent program to update Frozen cost for the item from the pending cost.
------Form-------------
declare
access_id varchar2(100);
l_server_url varchar2(200);
l_parameters varchar2(200);
l_gfm_id number;
button_choice number;
v_request_id number;
p_mfg_org_id number;
begin
p_mfg_org_id := fnd_profile.value('MFG_ORGANIZATION_ID');
access_id := fnd_gfm.authorize(null);
fnd_profile.get('APPS_WEB_AGENT', l_server_url);
l_parameters :='access_id='||access_id||' l_server_url='||l_server_url;
fnd_function.execute(FUNCTION_NAME=>'FND_FNDFLUPL',
OPEN_FLAG => 'Y',
SESSION_FLAG => 'Y',
OTHER_PARAMS =>l_parameters);
FND_MESSAGE.set_name('FND','ATCHMT-FILE-UPLOAD-COMPLETE');
button_choice := FND_MESSAGE.question(
button1=> 'YES',
button2=> null,
button3=> 'NO',
default_btn => 1,
cancel_btn =>3,
icon=> 'question');
if ( button_choice = 1 ) then
l_gfm_id := fnd_gfm.get_file_id(access_id);
if l_gfm_id is null then
fnd_message.set_string('Please upload the cost file.');
fnd_message.show;
raise form_trigger_failure;
end if;
v_request_id:=FND_REQUEST.SUBMIT_REQUEST (
'MODULE_NAME',
'REQUEST_NAME', -- 此处调用包体的 read_blob
'',
to_char(sysdate,'DD-MON-YY HH24:MI:SS'),
false,
l_gfm_id,
p_mfg_org_id,
chr(0),
'','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','');
IF v_request_id = 0 THEN
FND_MESSAGE.RETRIEVE;
FND_MESSAGE.ERROR;
ELSE COMMIT;
END IF;
end if;
end;
----------------------
create or replace package body UPLOAD_STD_COST_PKG is
function split_last_pos(v_string in string, v_char in string) return number is
v_Result number := 0;
v_pos number;
v_start_pos number := 1;
begin
if instr(v_string, v_char) = 0 then
v_Result := 0;
else
while instr(v_string, v_char, 1, v_start_pos) > 0 loop
v_pos := instr(v_string, v_char, 1, v_start_pos);
v_start_pos := v_start_pos + 1;
end loop;
end if;
v_Result := v_pos;
return(v_Result);
end split_last_pos;
function check_number(p_data in varchar2)
return number is
i number;
j number;
s varchar2(3);
v_dot number:=0;
begin
select lengthb(p_data)
into j
from dual;
for i in 1..j loop
begin
select substr(p_data,i,1) into s from dual;
if s='.' then
v_dot:=v_dot+1;
if v_dot>1 then
return(0);
end if;
elsif s not in ('0','1','2','3','4','5','6','7','8','9') then
return(0);
end if;
exception when others then
return(0);
end;
end loop;
return(p_data);
exception when others then
return(0);
end;
procedure upload_std_cost(p_data varchar2, p_errbuf out varchar2) is
l_data_len number;
l_len number;
v_item_id number;
v_item_number varchar2(30);
v_item_cost varchar2(60);
v_cost number;
i number:=0;
j number:=0;
k number:=0;
begin
l_data_len := length(p_data);
--分列数据
select INSTR(p_data,',',1,1)
into i
from dual;
if (j<>0) then
fnd_file.put_line(fnd_file.log,'Data:' ||p_data||',Too many commas.');
end if;
if (i<=1) then
fnd_file.put_line(fnd_file.log,'Data:' ||p_data||',Data error.');
else
begin
select substr(p_data,1,i-1),substr(p_data,i+1)
into v_item_number,v_item_cost
from dual;
-- check if the item is effective
begin
select inventory_item_id
into v_item_id
from mtl_system_items_b
where organization_id = p_mfg_org_id
and segment1= v_item_number;
-- check if the cost is correct
--最多只能有一个".",而且必须全是数字
v_cost := check_number(v_item_cost);
if v_cost = 0 then
fnd_file.put_line(fnd_file.log,'Data:' ||p_data||',The cost is incorrect.');
else
insert into cst_item_cst_dtls_interface
(inventory_item_id,
organization_id,
cost_type,
resource_code,
USAGE_RATE_OR_AMOUNT,
COST_ELEMENT_ID,
PROCESS_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
values
(v_item_id,
p_mfg_org_id,
'Pending',
'MAT',
v_cost,
1,
1,
SYSDATE,P_USER_ID,SYSDATE,P_USER_ID);
end if;
exception when others then
fnd_file.put_line(fnd_file.log,'Data:' ||p_data||',The item is incorrect.');
end;
end;
end if;
p_errbuf := 'S';
exception
when others then
p_errbuf := 'E';
end;
procedure read_line(p_hex varchar2,
p_last_pos out number,
p_errinfo out varchar2) is
i_pos_s number := 1; ----开始标记
i_step number := 2; ----每次读取HEX字符串的个数
i_len number;
i_dec number;
v_string varchar2(5000) := '';
v_tmp varchar2(1000) := '';
v_errinfo varchar2(100);
v_len number;
begin
i_len := length(p_hex);
p_last_pos := split_last_pos(p_hex, '0D0A');
if i_len > (p_last_pos + 3) then
v_len := p_last_pos + 3;
else
v_len := i_len;
end if;
dbms_output.put_line('v_len=' || v_len);
while i_pos_s <= v_len loop
v_tmp := substr(p_hex, i_pos_s, i_step);
i_pos_s := i_pos_s + i_step;
---如果其ASCII值大于128，则说明是双字节字符，需要再取两位HEX值
if to_number(v_tmp, 'XXXX') > 128 then
v_tmp := v_tmp || substr(p_hex, i_pos_s, i_step);
i_pos_s := i_pos_s + i_step;
end if;
----hex_to_dec转换
i_dec := to_number(v_tmp, 'XXXX');
if (i_dec in (10,13)) then
----10换行 or 13回车符
if v_string is not null then
upload_std_cost(v_string, v_errinfo);
dbms_output.put_line('v_string=' || v_string);
dbms_output.put_line('v_errinfo=' || v_errinfo);
if nvl(v_errinfo, 'S') <> 'S' then
p_errinfo := v_errinfo;
exit;
end if;
end if;
v_string := '';
else
----正常数据
v_string := v_string || chr(i_dec);
end if;
end loop;
end;
procedure read_blob(errbuf out varchar2,
errcode out varchar2,
p_file_id in number,
p_mfg_organization_id in number) is
lobloc blob;
l_blob_len number;
l_buffer RAW(32767); -- 缓存的最大字符长度
l_amount number := 32767;
l_offset number := 1;
l_last_pos number;
v_errinfo varchar2(100);
begin
p_mfg_org_id := p_mfg_organization_id;
p_user_id := fnd_profile.VALUE('USER_ID');
delete from cst_item_cst_dtls_interface
where organization_id= p_mfg_org_id
and group_id is not null;
select file_data into lobloc from fnd_lobs where file_id = p_file_id;
l_file_id := p_file_id;
l_blob_len := DBMS_LOB.GETLENGTH(lobloc);
WHILE l_offset < l_blob_len LOOP
dbms_lob.read(lobloc, l_amount, l_offset, l_buffer);
read_line(l_buffer, l_last_pos, v_errinfo);
if nvl(v_errinfo, 'S') <> 'S' then
errbuf := v_errinfo;
rollback;
exit;
end if;
-- l_offset := l_offset + l_last_pos + 3;
l_offset := l_offset + l_last_pos;
fnd_file.put_line(fnd_file.log,'offset:' ||l_offset);
END LOOP;
DELETE FROM FND_LOBS WHERE FILE_ID = p_file_id;
commit;
end;
end UPLOAD_STD_COST_PKG;
