Customize Taxes and Charges in Landed Cost Management An Oracle White Paper Mar 2010
Table of Contents Customize Taxes and Charges in Landed Cost Management.................................................1 Table of Contents.....................................................................................................................2 Executive Overview.................................................................................................................3 Introduction ..............................................................................................................................3 Methods in Oracle Landed Cost Management........................................................................3 Pre-Receiving Flow..............................................................................................................3 Landed Cost as a Service Flow............................................................................................4 Prerequisite to use custom taxes and charges in LCM............................................................4 Releases and Patches ...........................................................................................................4 Implement custom tax in estimated landed cost calculation...................................................4 Technical Overview ............................................................................................................4 Process Flow........................................................................................................................5 Define Tax Code..................................................................................................................7 Sample Code: Implement Customized Tax........................................................................7 Implement custom charges in estimated landed cost calculation............................................9 Technical Overview ............................................................................................................9 Process Flow......................................................................................................................10 Define Charges/Cost Factors.............................................................................................11 Sample Code: Implement Customized Charges...............................................................12 References..............................................................................................................................14
2
Executive Overview The Oracle Landed Cost Management Calculation process provides the ability to calculate the estimated landed cost using charges manually assigned or automatically allocated based on the configuration in advance pricing. Landed Cost Management also uses taxes associated to the purchase order that was originally calculated by EB-Tax while calculating estimated landed cost. This white paper focuses on the LCM features that enable you to use customized charges and taxes to calculate estimated landed cost.
Introduction Oracle Landed Cost Management manages estimated and actual landed cost for an item purchased from a supplier. Oracle Landed Cost Management determines the "real" costs associated with acquiring items including non-recoverable taxes and charges. Changes that are mainly considered in estimated landed cost calculation includes insurance, transportation, handling, storage costs, container fees, and import or export charges. Oracle Landed Cost Management integrates with Purchasing, advance pricing and other modules. Landed Cost Management extracts the tax lines associated to the purchase order that was originally calculated by EB-Tax while calculating estimated landed cost of shipment. Similarly charges are extracted automatically from advance pricing while calculating estimated landed cost of shipment. Oracle Applications R12.1.1 onward allows custom logic to use taxes and charges during estimated landed cost calculation in LCM. The setups for both tax and charge customizations are discussed in this paper.
Methods in Oracle Landed Cost Management Pre-Receiving Flow Oracle Landed Cost Management pre-receiving functionality is used to estimate landed cost before the receipt of the item by the Receiving application. After the estimated landed cost is finalized in Landed Cost Management that information is passed to Receiving during the receipt of the item based on the Landed Cost Management shipment. The receipt is performed using the known estimated landed cost. Using Landed Cost Management the estimated landed cost is calculated for a shipment from the following: Price and taxes for the item on the associated purchase order. • Charges generated through the integration with Oracle Advanced Pricing or • manually entered in Landed Cost Management.
3
Landed Cost as a Service Flow Oracle Landed Cost Management can be used as a service. In this functionality, after the receipt of items into inventory the Landed Cost Management shipment is automatically created. The estimation of the item landed cost is also automatically calculated based on the purchase documents price, tax and the charge amounts.
Prerequisite to use custom taxes and charges in LCM Releases and Patches LCM functionality to use customized tax and charges in estimated landed cost calculation is available in Oracle Applications Release 12.1.2. Patch 9094631:R12.INL.B should be applied on top of release 12.1.1 to enable custom taxes and charges functionality.
Implement custom tax in estimated landed cost calculation Oracle Landed Cost Management has integration with Oracle Purchasing for generating the taxes used in estimated landed costs calculation for both flows: “Pre-Receiving in LCM” and “LCM as a service”. Landed Cost Management extracts the tax lines associated to the purchase order that was originally calculated by EB-Tax while calculating estimated landed cost for shipment, tax is associated with to its shipment Lines.
Technical Overview Taxes calculation takes place in LCM depending on the following flows: Pre-Receiving flow: User creates the LCM Shipment through the LCM Shipments Workbench UI. When the user chooses a PO Schedule as the source for creating an LCM Shipment Line, the api INL_TAX_PVT.Generate_Taxes is executed. LCM as a Service flow: User submits the Shipments Interface Import concurrent program, to import the Receipts done in Receiving into LCM Shipments. This program also provides the landed cost calculation for those LCM Shipments, but before that, it also calls the api INL_TAX_PVT.Generate_Taxes. Procedure INL_CHARGE_PVT.Generate_Taxes internally calls api INL_CUSTOM_PUB.Get_Taxes which need to be modified to implement custom tax logic.
4
Process Flow Following diagram shows the process flow of API INL_CHARGE_PVT.Generate_Taxes to calculate tax in estimated landed cost.
1. Api INL_CHARGE_PVT.Generate_Taxes calls INL_CUSTOM_PUB.Get_Taxes that returns the parameter Override Default Processing as FALSE when no tax customization is implemented otherwise, returns TRUE. 2. When custom logic is implemented to get taxes in INL_CUSTOM_PUB.Get_Taxes procedure, the Override Default Processing parameter should return TRUE. Custom tax details are populated and passed as output parameter tax_in_rec from INL_CUSTOM_PUB.Get_Taxes.
5
Tax Lines PL/SQL table to be populated: -- Record to keep Tax Line info. TYPE tax_ln_rec IS RECORD( tax_code VARCHAR2(30), ship_header_id NUMBER, source_parent_table_name VARCHAR2(30), source_parent_table_id NUMBER, tax_amt NUMBER, nrec_tax_amt NUMBER, currency_code VARCHAR2(15), currency_conversion_type VARCHAR2(30), currency_conversion_date DATE, currency_conversion_rate NUMBER, tax_amt_included_flag VARCHAR2(1), -- Association attributes to_parent_table_name VARCHAR2(30), to_parent_table_id NUMBER );
3. Then process flow checks the value of the parameter Override Default Processing •
•
FALSE : Api INL_TAX_PVT.Get_TaxesFromPO is called (for transaction source = ‘PO’), for obtaining the taxes from Oracle Purchasing. API returns Tax Lines PL/SQL table as output parameter. TRUE : Api INL_TAX_PVT.Get_TaxesFromPO is not called.
4. Tax information associated with the shipment is inserted into database tables INL_TAX_LINES and INL_ASSOCIATIONS from output parameter Tax Lines PL/SQL table tax_ln_rec .
6
Define Tax Code Tax Manager -> Tax Configuration -> Taxes
Sample Code: Implement Customized Tax Package INL_CUSTOM_PUB create or replace PACKAGE BODY INL_CUSTOM_PUB AS --------------
Utility name Type : Function : Pre-reqs : Parameters : IN :
OUT
: Get_Taxes Public None p_ship_header_rec p_ship_ln_groups_tbl p_ship_lines_tbl p_charge_lines_tbl
: x_tax_ln_tbl x_override_default_processing x_return_status
IN IN IN IN
INL_TAX_PVT.Shipment_Header%ROWTYPE, INL_TAX_PVT.sh_group_ln_tbl_tp, INL_TAX_PVT.ship_ln_tbl_tp, inl_tax_pvt.charge_ln_tbl_tp,
OUT inl_tax_pvt.tax_ln_tbl OUT BOOLEAN OUT NOCOPY VARCHAR2
7
--- Version : Current version 1.0 --- Notes : PROCEDURE Get_Taxes( p_ship_header_rec IN INL_TAX_PVT.Shipment_Header%ROWTYPE, p_ship_ln_groups_tbl IN INL_TAX_PVT.sh_group_ln_tbl_tp, p_ship_lines_tbl IN INL_TAX_PVT.ship_ln_tbl_tp, p_charge_lines_tbl IN inl_tax_pvt.charge_ln_tbl_tp, x_tax_ln_tbl OUT NOCOPY inl_tax_pvt.tax_ln_tbl, x_override_default_processing OUT NOCOPY BOOLEAN, x_return_status OUT NOCOPY VARCHAR2 ) IS BEGIN /* Code change begin to implement tax customization */
--
x_override_default_processing := FALSE;
/* Custom procedure INL_CUSTOM_SAMPLE called to get Tax details */ INL_CUSTOM_SAMPLE.Get_Taxes( p_ship_header_rec => p_ship_header_rec, p_ship_ln_groups_tbl => p_ship_ln_groups_tbl, p_ship_lines_tbl => p_ship_lines_tbl, p_charge_lines_tbl => p_charge_lines_tbl, x_tax_ln_tbl => x_tax_ln_tbl, x_return_status => x_return_status); x_override_default_processing := TRUE; RETURN; /* Code change ends to implement tax customization */ END Get_Taxes; END INL_CUSTOM_PUB;
Package INL_CUSTOM_SAMPLE create or replace PACKAGE BODY INL_CUSTOM_SAMPLE AS PROCEDURE Get_Taxes( p_ship_header_rec IN INL_TAX_PVT.Shipment_Header%ROWTYPE, p_ship_ln_groups_tbl IN INL_TAX_PVT.sh_group_ln_tbl_tp, p_ship_lines_tbl IN INL_TAX_PVT.ship_ln_tbl_tp, p_charge_lines_tbl IN inl_tax_pvt.charge_ln_tbl_tp, x_tax_ln_tbl OUT NOCOPY inl_tax_pvt.tax_ln_tbl, x_return_status OUT NOCOPY VARCHAR2 ) IS l_debug_info varchar2(3000); BEGIN x_tax_ln_tbl(1).tax_code := 'STATE'; x_tax_ln_tbl(1).ship_header_id := p_ship_header_rec.ship_header_id; x_tax_ln_tbl(1).source_parent_table_name := 'Open Interface'; x_tax_ln_tbl(1).source_parent_table_id := 1; x_tax_ln_tbl(1).tax_amt := 10; x_tax_ln_tbl(1).nrec_tax_amt := 10; x_tax_ln_tbl(1).currency_code := 'USD'; x_tax_ln_tbl(1).tax_amt_included_flag := 'N'; -- Association attributes x_tax_ln_tbl(1).to_parent_table_name := 'INL_SHIP_LINES'; x_tax_ln_tbl(1).to_parent_table_id := p_ship_lines_tbl(1).ship_line_id; END Get_Taxes; END INL_CUSTOM_SAMPLE;
8
Implement custom charges in estimated landed cost calculation Oracle Landed Cost Management has integration with Advance Pricing for generating the charges used in estimated landed costs calculation for both flows: “Pre-Receiving in LCM” and “LCM as a service”. Landed Cost Management extracts charges based on the modifiers setup of type “Freight and Special charge List” defined in Advanced Pricing and associated to either its Line Groups or its Shipment Lines during estimated landed cost calculation.
Technical Overview Charge generation takes place in LCM depending on the following flows: Pre-Receiving flow: User creates the LCM Shipment through the LCM Shipments Workbench UI. Api INL_CHARGE_PVT.Generate_Charges is executed when user selects ‘Generate Charge” option from action list while calculating estimated landed cost. LCM as a Service flow: User submits the Shipments Interface Import concurrent program, to import the Receipts done in Receiving into LCM Shipments. This program also provides the landed cost calculation for those LCM Shipments, but before that, it also calls the api INL_CHARGE_PVT.Generate_Charges. Procedure INL_CHARGE_PVT.Generate_Charges internally calls api INL_CUSTOM_PUB.Get_Charges which need to be modified to implement custom charge logic.
9
Process Flow Following diagram shows the process flow of API INL_CHARGE_PVT.Generate_Charges to extract charges in estimated landed cost calculation.
1. Api INL_CHARGE_PVT.Generate_Charges calls INL_CUSTOM_PUB.Get_Charges that returns the parameter Override Default Processing as FALSE when no charge customization is implemented otherwise, returns TRUE. 2. When custom logic is implemented to get charges in INL_CUSTOM_PUB.Get_Charges procedure, the Override Default Processing
10
parameter should return TRUE. Custom charges are populated and passed as output parameter charge_in_rec from INL_CUSTOM_PUB.Get_Charges. Tax Lines PL/SQL table to be populated: -- Record to keep Charge Line info. TYPE charge_ln_rec IS RECORD( charge_line_type_id NUMBER, landed_cost_flag VARCHAR2(1), update_allowed VARCHAR2(1), source_code VARCHAR2(25), charge_amt NUMBER, currency_code VARCHAR2(15), currency_conversion_type VARCHAR2(30), currency_conversion_date DATE, currency_conversion_rate NUMBER, party_id NUMBER, party_site_id NUMBER, trx_business_category VARCHAR2(240), intended_use VARCHAR2(30), product_fiscal_class VARCHAR2(240), product_category VARCHAR2(240), product_type VARCHAR2(240), user_def_fiscal_class VARCHAR2(240), tax_classification_code VARCHAR2(30), assessable_value NUMBER, ship_from_party_id NUMBER, ship_from_party_site_id NUMBER, ship_to_organization_id NUMBER, ship_to_location_id NUMBER, bill_from_party_id NUMBER, bill_from_party_site_id NUMBER, bill_to_organization_id NUMBER, bill_to_location_id NUMBER, poa_party_id NUMBER, poa_party_site_id NUMBER, poo_organization_id NUMBER, poo_location_id NUMBER, -- Association attributes to_parent_table_name VARCHAR2(30), to_parent_table_id NUMBER );
3. Then process flow checks the value of the parameter Override Default Processing •
•
FALSE : Api INL_CHARGE_PVT.Get_ChargesFromQP is called for obtaining the charge from Advance Pricing for line group, shipment header and shipment lines. API returns Charge Lines PL/SQL table as output parameter. TRUE : Api INL_CHARGE_PVT.Get_ChargesFromQP is not called.
4. Charges associated with the line group, shipment header and shipment are inserted into database tables INL_CHARGE_LINES and INL_ASSOCIATIONS from output parameter Charge Lines PL/SQL table charge_ln_rec .
Define Charges/Cost Factors Purchasing -> Setup -> Purchasing -> Cost Factor 11
Use following query to identify the value of the parameter charge_line_type_id of the charge type setup in above screen. select price_element_type_id charge_line_type_id, price_element_code charge_code from PON_PRICE_ELEMENT_TYPES where price_element_code ='FREIGHT'
Sample Code: Implement Customized Charges Package INL_CUSTOM_PUB create or replace
12
PACKAGE BODY INL_CUSTOM_PUB AS -- Utility name -- Type : -- Function : -- Pre-reqs : -- Parameters : -- IN : ----- OUT -hook execution -processing from ----- Version : --- Notes :
: Get_Charges Public None p_ship_header_rec p_ship_ln_group_rec p_ship_ln_tbl
IN inl_charge_pvt.ship_header_rec_tp, IN inl_charge_pvt.ship_ln_group_tbl_tp, IN inl_charge_pvt.ship_ln_tbl_tp,
x_charge_ln_tbl x_override_default_processing
OUT NOCOPY inl_charge_pvt.charge_ln_tbl OUT BOOLEAN (If TRUE, it enables the to override the default the caller routine) OUT NOCOPY VARCHAR2
x_return_status Current version 1.0
PROCEDURE Get_Charges( p_ship_header_rec p_ship_ln_group_tbl p_ship_ln_tbl_tp x_charge_ln_tbl x_override_default_processing x_return_status BEGIN
IN inl_ship_headers%ROWTYPE, IN inl_charge_pvt.ship_ln_group_tbl_tp, IN inl_charge_pvt.ship_ln_tbl_tp, OUT NOCOPY inl_charge_pvt.charge_ln_tbl, OUT NOCOPY BOOLEAN, OUT NOCOPY VARCHAR2 IS
/* Code change begin to implement charge customization */
--
x_override_default_processing := FALSE; INL_CUSTOM_SAMPLE.Get_Charges( p_ship_header_rec => p_ship_header_rec, p_ship_ln_group_rec => p_ship_ln_group_rec, p_ship_ln_tbl => p_ship_ln_tbl, x_charge_ln_tbl => x_charge_ln_tbl, x_return_status => x_return_status); x_override_default_processing := TRUE; RETURN;
/* Code change ends to implement tax customization */ END Get_Charges; END INL_CUSTOM_PUB;
Package INL_CUSTOM_SAMPLE create or replace PACKAGE BODY INL_CUSTOM_SAMPLE AS -- This custom code is for creating a $100 charge to be prorated into all Shipment Lines -- of the Line Group passed as the input parameter.
13
PROCEDURE Get_Charges( p_ship_header_rec p_ship_ln_group_rec p_ship_ln_tbl x_charge_ln_tbl x_return_status IS i number; l_debug_info varchar2(3000); charge_ln inl_charge_pvt.charge_ln_rec; BEGIN x_charge_ln_tbl(1).charge_line_type_id x_charge_ln_tbl(1).landed_cost_flag x_charge_ln_tbl(1).update_allowed x_charge_ln_tbl(1).source_code x_charge_ln_tbl(1).charge_amt x_charge_ln_tbl(1).currency_code -- Association attributes x_charge_ln_tbl(1).to_parent_table_name x_charge_ln_tbl(1).to_parent_table_id p_ship_ln_group_rec.ship_line_group_id; END Get_Charges;
IN inl_ship_headers%ROWTYPE, IN inl_charge_pvt.ship_ln_group_rec, IN inl_charge_pvt.ship_ln_tbl, OUT NOCOPY inl_charge_pvt.charge_ln_tbl, OUT NOCOPY VARCHAR2)
:= := := := := :=
62; 'Y'; 'N'; 'OI'; 100; 'USD';
:= 'INL_SHIP_LINE_GROUPS'; :=
END INL_CUSTOM_SAMPLE
References 1. Oracle Landed Cost Management Process Guide Release 12.1(Part No. E1429902)
14
