前提条件:Oracle EBS 企业税改方案(一)-业务需求整理及基础设置篇
@H_502_4@Oracle EBS系统版本:11.5.10
针对本次企业税改，OM & AR相对比较简单，结合公司实际业务，通常涉及的内容如下
一、Items : INV->Items->Invoicing 分别修改各个库存组织税码如下，若公司的产品单一并且只有一种税率，则建议设置为空，参考：Oracle EBS 企业税改方案(一)-业务需求整理及基础设置篇。
DECLARE
l_item_rec inv_item_grp.item_rec_type;
x_item_rec inv_item_grp.item_rec_type;
x_error_tbl inv_item_grp.error_tbl_type;
x_return_status VARCHAR2(1);
l_user_id NUMBER := 0; --User ID,Sysadmin here
--l_cur_mfg_org_id NUMBER := 122; --Current Inv Organization
--找到所有成品编物料
Cursor Cr Is
Select MSI.SEGMENT1,MSI.DESCRIPTION,MSI.ITEM_TYPE,MSI.CREATION_DATE,MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_STATUS_CODE,MSI.INVENTORY_ITEM_ID
From MTL_SYSTEM_ITEMS_B MSI
Where MSI.ITEM_TYPE = 'FG'
And MSI.SEGMENT1='181810109652'
And MSI.INVENTORY_ITEM_STATUS_CODE='Active'
Order By MSI.SEGMENT1;
l_RespId Number := 50237;
l_RespApplId Number := 401;
--税码
l_TaxName Varchar2(25) := 'VAT16';
BEGIN
--初始化
fnd_global.apps_initialize(user_id => l_user_id,resp_id => l_RespId,resp_appl_id => l_RespApplId);
--批量修改税率
For Rs In Cr Loop
--Item Number
l_item_rec.item_number := Rs.SEGMENT1;
--税码
l_item_rec.TAX_CODE := l_TaxName;
--Master Inv Organization first,then Current Inv Organization
l_item_rec.organization_id := Rs.ORGANIZATION_ID;
--API
inv_item_grp.update_item(p_commit => fnd_api.g_false,p_item_rec => l_item_rec,x_item_rec => x_item_rec,x_return_status => x_return_status,x_error_tbl => x_error_tbl);
--Result
IF x_return_status <> fnd_api.g_ret_sts_success THEN
ROLLBACK;
FOR i IN 1 .. x_error_tbl.COUNT LOOP
dbms_output.put_line('Transaction ID:' || x_error_tbl(i).transaction_id);
dbms_output.put_line('Unique ID:' || x_error_tbl(i).unique_id);
dbms_output.put_line('Message Name:' || x_error_tbl(i).message_name);
dbms_output.put_line('Message Text:' || x_error_tbl(i).message_text);
dbms_output.put_line('Table Name:' || x_error_tbl(i).table_name);
dbms_output.put_line('Column Name:' || x_error_tbl(i).column_name);
dbms_output.put_line('Organization ID:' || x_error_tbl(i).organization_id);
END LOOP;
IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
RAISE fnd_api.g_exc_unexpected_error;
ELSIF (x_return_status = fnd_api.g_ret_sts_error) THEN
--RAISE fnd_api.g_exc_error;
Null;
END IF;
ELSE
COMMIT;
dbms_output.put_line(x_item_rec.item_number);
END IF;
End Loop;
END;
二、Pricing : OM->Pricing->Price Lists->Adjust Price List : 由于税率的变化，导致销售单价也有变化低，大部分客户可能要调价，新销售价=原销售价/1.17*1.16。可以按照百分比调价如：Adjsut By Percent = -1*(1-1/1.17*1.16)/100 =-0.85
三、OM->Orders,Returns->Orders : 处理未结销售订单，如下情况
修改销售订单行的税码：
在做该项目之前，客户经常要求我们调价，或者由于原材料上涨幅度比较大，我们也需求给客户调价。因此，特别开发一个请求“GB销售单价更改”，其思路根据客户的价目表自动更新未挑库或未发运确认销售订单行，主要代码如下，可以供大家参考一下。
CREATE OR REPLACE Package Body CUX_OEPRICE_HIS_PKG Is
--===============================================================
-- Log
--===============================================================
Procedure Log(p_Msg In Varchar2) Is
Begin
Fnd_File.Put_Line(Fnd_File.Log,p_Msg);
End Log;
--===============================================================
-- Output
--===============================================================
Procedure Output(p_Msg In Varchar2) Is
Begin
Fnd_File.Put_Line(Fnd_File.Output,p_Msg);
End Output;
/*
P_PRICE_LIST_ID 客户价目表
P_USER_ID 用户ID
P_PICKED_FLAG(Y/N)
Y表示更新未挑库的销售订单行
N表示更新所有未发运确认的销售订单行（包括挑库和分部挑库)
*/
procedure ChangePrice(X_ERROR_CODE Out Nocopy Varchar2,X_ERROR_MSG Out Nocopy Varchar2,P_PRICE_LIST_ID Number,P_USER_ID Number,P_PICKED_FLAG Varchar2) Is
Cursor CrHdr Is
Select Distinct OEH.HEADER_ID
FROM QP_LIST_LINES_V QLL,OE_ORDER_LINES_ALL OEL,OE_ORDER_HEADERS_ALL OEH,MTL_SYSTEM_ITEMS_B MSI
Where QLL.LIST_HEADER_ID = OEL.PRICE_LIST_ID
And QLL.PRODUCT_ID = OEL.INVENTORY_ITEM_ID
And OEL.HEADER_ID = OEH.HEADER_ID
And OEL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
And OEL.SHIP_FROM_ORG_ID = MSI.ORGANIZATION_ID
And QLL.LIST_HEADER_ID = P_PRICE_LIST_ID
And OEL.FLOW_STATUS_CODE In
('AWAITING_SHIPPING','AWAITING_RETURN','ENTERED')
--And OEL.UNIT_SELLING_PRICE > 0
And OEL.UNIT_SELLING_PRICE <> QLL.OPERAND;
Begin
CUX_GOBAOPUBLIC_PKG.HtmlStart('GB销售单价更改');
Output('<table class="context" borderColor="#669933" width="100%" border=1>');
Output('<tr bgcolor="#FFE66F"><td colspan="9">提交日期:' || To_Char(Sysdate,'YYYY-MM-DD HH24:MI:SS') ||'</td></tr>');
For RsHdr In CrHdr Loop
CPHeadHtml;
--包括挑库和分部挑库
If(Nvl(P_PICKED_FLAG,'N')='N')Then
ChangePriceOrder01(RsHdr.Header_Id,P_PRICE_LIST_ID,P_USER_ID);
Else
ChangePriceOrder(RsHdr.Header_Id,P_USER_ID);
End If;
End Loop;
Output('</table></body></html>');
End ChangePrice;
--更新未挑库的销售订单行
procedure ChangePriceOrder(P_HEADER Number,P_USER_ID Number) Is
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_line_tb2 oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.request_tbl_type;
l_return_status varchar2(1000);
l_msg_count number;
l_msg_data varchar2(1000);
--v_line_number varchar2(1000);
l_index varchar2(1000);
x_header_val_rec oe_order_pub.header_val_rec_type;
x_header_adj_tbl oe_order_pub.header_adj_tbl_type;
x_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;
x_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
x_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;
x_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;
x_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
x_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type;
x_line_val_tbl oe_order_pub.line_val_tbl_type;
x_line_adj_tbl oe_order_pub.line_adj_tbl_type;
x_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type;
x_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
x_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;
x_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;
x_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
x_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type;
x_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;
x_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type;
i Number:= 1;
--需求要修改单价的销销售订单
Cursor CrLine(P_HDR_ID Number) Is
Select OEH.ORDER_NUMBER,OEH.ORG_ID,MSI.SEGMENT1 ITEM_NUMBER,MSI.DESCRIPTION ITEM_DESC,OEL.LINE_NUMBER || '.' || OEL.SHIPMENT_NUMBER LINE_NUM,OEL.HEADER_ID,OEL.LINE_ID,OEL.INVENTORY_ITEM_ID,OEL.UNIT_SELLING_PRICE,OEL.ORDERED_QUANTITY,QLL.OPERAND,OEL.TAX_CODE
FROM QP_LIST_LINES_V QLL,MTL_SYSTEM_ITEMS_B MSI
Where QLL.LIST_HEADER_ID = OEL.PRICE_LIST_ID
And QLL.PRODUCT_ID = OEL.INVENTORY_ITEM_ID
And OEL.HEADER_ID = OEH.HEADER_ID
And OEL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
And OEL.SHIP_FROM_ORG_ID = MSI.ORGANIZATION_ID
And QLL.LIST_HEADER_ID = P_PRICE_LIST_ID
And OEH.HEADER_ID = P_HDR_ID
And OEL.FLOW_STATUS_CODE In
('AWAITING_SHIPPING','ENTERED')
--And OEL.UNIT_SELLING_PRICE > 0
And OEL.UNIT_SELLING_PRICE <> QLL.OPERAND
And QLL.operand >0
Order By OEH.ORDER_NUMBER,OEL.LINE_NUMBER || '.' || OEL.SHIPMENT_NUMBER;
Begin
fnd_global.apps_initialize(P_USER_ID,50247,660);
oe_msg_pub.initialize;
oe_debug_pub.initialize;
oe_debug_pub.debug_on;
oe_debug_pub.setdebuglevel(5);
mo_global.set_policy_context('S',114);
l_header_rec := oe_order_pub.g_miss_header_rec;
l_header_rec.header_id := P_HEADER;
l_header_rec.operation := oe_globals.g_opr_update;
For Rs In CrLine(P_HEADER) Loop
l_line_tbl(i) := oe_order_pub.g_miss_line_rec;
l_line_tbl(i).line_id := Rs.Line_Id;
l_line_tbl(i).operation := oe_globals.g_opr_update;
l_line_tbl(i).UNIT_LIST_PRICE := Rs.Operand;
l_line_tbl(i).UNIT_SELLING_PRICE := Rs.Operand;
--If(Rs.Tax_Code Is Null)Then
l_line_tbl(i).TAX_CODE := 'VAT16';
--End If;
--打印显示要修改记录
Output('<tr>
<td align="center">'|| i ||'</td>
<td>'|| Rs.Order_Number||'</td>
<td>'|| Rs.Line_Num||'</td>
<td>'|| Rs.Item_Number||'</td>
<td>'|| Rs.Item_Desc||'</td>
<td align="right">'|| Rs.Ordered_Quantity||'</td>
<td align="right">'|| TO_CHAR(NVL(Rs.Unit_Selling_Price,0),'999,999,999.99')||'</td>
<td align="right">'|| TO_CHAR(NVL(Rs.Operand,999.99')||'</td>
<td align="right">'|| TO_CHAR(Rs.Ordered_Quantity*(NVL(Rs.Operand,0)-NVL(Rs.Unit_Selling_Price,0)),999.99')||'</td>
</tr>');
--插入销售订单单价修改历史记录
Insert Into CUX.CUX_OE_PRICE_HISTORY
(HEADER_ID,LINE_ID,ORG_ID,INVENTORY_ITEM_ID,UNIT_SELLING_PRICE,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
Values
(Rs.Header_Id,Rs.Line_Id,Rs.Org_Id,Rs.Inventory_Item_Id,Rs.Unit_Selling_Price,sysdate,P_USER_ID,SYSDATE,0);
i := i + 1;
End Loop;
--ChangePriceRow(l_header_rec,l_line_tbl);
If(i>1)Then
oe_order_pub.Process_Line(l_line_tbl,l_line_tb2,l_return_status,l_msg_count,l_msg_data);
dbms_output.put_line('l_index:' || l_index || 'l_msg_data1:' ||
l_msg_data);
If(l_msg_data Is Not Null)Then
Output('<tr><td colspan="9">操作提示:'||l_msg_data||'</td></tr>');
End If;
if l_msg_count > 0 then
for l_index in 1 .. l_msg_count loop
l_msg_data := oe_msg_pub.get(p_msg_index => l_index,p_encoded => 'F');
end loop;
end if;
dbms_output.put_line('l_index:' || l_index || 'l_msg_data:' ||
l_msg_data);
if l_return_status = fnd_api.g_ret_sts_success then
Output('<tr><td colspan="9">操作提示:售价修改成功</td></tr>');
commit;
else
Output('<tr><td colspan="9">操作提示:售价修改失败。说明:'|| l_msg_data ||'</td></tr>');
rollback;
end if;
End If;
Exception
When Others Then
Output('<tr><td colspan="9">操作提示:'|| sqlerrm ||'</td></tr>');
rollback;
End ChangePriceOrder;
--更新所有未发运确认的销售订单行（包括挑库和分部挑库)
procedure ChangePriceOrder01(P_HEADER Number,'ENTERED')
And Not Exists
(Select 'x'
From WSH_DELIVERY_DETAILS WDS
Where WDS.SOURCE_HEADER_ID = OEH.HEADER_ID
And WDS.SOURCE_LINE_ID = OEL.LINE_ID
And WDS.RELEASED_STATUS In ('Y','S')
And WDS.SUBINVENTORY Is Not Null Having
Sum(WDS.REQUESTED_QUANTITY) <= OEL.ORDERED_QUANTITY)
--And OEL.UNIT_SELLING_PRICE > 0
And OEL.UNIT_SELLING_PRICE <> QLL.OPERAND
And QLL.operand >0
Order By OEH.ORDER_NUMBER,p_encoded => 'F');
end loop;
end if;
dbms_output.put_line('l_index:' || l_index || 'l_msg_data:' ||
l_msg_data);
if l_return_status = fnd_api.g_ret_sts_success then
Output('<tr><td colspan="9">操作提示:售价修改成功</td></tr>');
commit;
else
Output('<tr><td colspan="9">操作提示:售价修改失败。说明:'|| l_msg_data ||'</td></tr>');
rollback;
end if;
End If;
Exception
When Others Then
Output('<tr><td colspan="9">操作提示:'|| sqlerrm ||'</td></tr>');
rollback;
End ChangePriceOrder01;
--报表题头
procedure CPHeadHtml Is
Begin
--Header
Output('<tr bgcolor="#FFE66F">
<td align="center">序号</td>
<td align="center">销售订单</td>
<td align="center">行号</td>
<td align="center">成品编号</td>
<td align="center">成品名称</td>
<td align="center">订单数量</td>
<td align="center">更改之前售价</td>
<td align="center">更改之后售价</td>
<td align="center">金额差异</td>
</tr>');
End CPHeadHtml;
End CUX_OEPRICE_HIS_PKG;
主要调用API包：oe_order_pub.Process_Line，其参数如下
四、AR接口：AR->Control->AutoInvoice->Interface Lines
也可以直接更新接口表税码如下:
Update RA_INTERFACE_LINES_ALL Set TAX_CODE = 'VAT16' Where ORG_ID = 114
