ORACLE APPS TECHNICAL

Wednesday, 5 November 2014

India Localization Tax Calculation

Wednesday, 8 October 2014

Actual Cost Adjustments in OPM Costing - R12

Actual Cost Adjustments in OPM Costing - R12

Actual Cost Adjustment functionality gives the users an ability to change the cost of an item – Product or Raw materials.

Typically this functionality to :
• Adjust cost to include freight and other charges recorded on a separate freight/charge invoice
• To handle price difference in invoices that are received in a different period than the receipt
• To adjust cost to include vendor rebates

The adjustment types available are:
• Average Cost Adjustment
• Value Adjustment
• Unit Cost Adjustment

Average Cost Adjustment
This type of adjustment requires the user to enter an adjustment quantity and a cost. The effect of this adjustment is to simulate a transaction that has happened outside the scope of OPM actual costing engine.

For example, if a customer uses a 3rd party system which has transactions that need to be included in the cost calculations, the customer can replicate the event with Actual Cost Adjustments can be used in OPM Costing to either directly affect the cost of items or to simulate the effect of a transaction that happens outside the purview of the Actual Costing process.

This white paper talks about the effect of the various cost adjustments on the cost of the items and recommended scenarios for each of adjustment types.
Using Actual Cost Adjustments in OPM Costing this type of adjustment. The actual costing logic would consider these transactions similar to a purchase receipt.

Value Adjustment
This type of adjustment requires the user to enter a total value of the adjustment that needs to be passed to the entire quantity. The effect of this type of adjustment is to add a specific value to the inventory account that needs to be spread to the onhand quantity.

For example, if the effect of vendor rebates need to be applied to the entire onhand quantity, this type of adjustment can be used.

Unit Cost Adjustments
This adjustment type requires the user to enter a fixed cost that they would like to apply to the existing unit cost. The effect of this adjustment is to add a specific value to the unit cost of the item.

For example, if the user wants to see the effect of changing the unit cost of the item by a fixed number for simulation purposes, this adjustment can be used.

Example
Let us take the following example to describe the types of adjustments and its effect on the cost of the item and Inventory account balances.
For an item FG100 under warehouse PR1, the following are the balances and
transactions that have taken place.

Quantity Cost
Opening Balance 100 LB $7.00
Receipts 100 LB $9.00

Actual Cost = (Prior Qty * Prior Cost) + Σ (Receipt Qty * PO Price)
__________________________________________
(Prior qty + Σ Receipt Qty)

((100 * 7.00) + (100 * 9.00))
= __________________________
(100 + 100)

= $ 8.00

Let’s now create the following adjustments.

Adjustment Type Quantity Cost / Value
Value Adjustment - $300.00
Average Adjustment 100.00 LB $11.00
Unit Cost Adjustment - $2.00

Let us look at the effect of each of these adjustments on Actual cost and Sub- Ledger entries:

Assumption: Purchase Order Receipts is booked at the PO price

Value Adjustments

Actual Costing Logic

PMAC cost =

(Prior Qty * Prior Cost) + Σ (Receipt Qty * PO Price) + Value Adjustments
_______________________________________________________________
(Prior qty + Σ Receipt Qty)

= ((100 * 7.00) + (100 * 9.00)) + 300.00
_________________________________
(100 + 100)

= $ 9.50

Inventory Valuations Comparison

Inventory valuations and the A/C balances in the INV account in GL can be compared to verify the effect of the Sub-Ledger entries.

Inventory Valuation = Actual cost * On Hand Quantity
= 9.50 * 200
Inventory Valuation = $ 1900.00

INV Account balances

Balances from prior period = $ 700.00 (100 * 7.00)
Receipt = $ 900.00 (100 * 9.00)
Value Adjustment = $ 300.00
Total = $ 1900.00

Average Cost Adjustments

Lets look at the effect of adding an Average Cost Adjustment of 100 LB @ $11.00 to the above scenario

Actual Costing Logic

PMAC cost =

(Prior Qty * Prior Cost) + Σ (Receipt Qty * PO Price) + Value Adjustments + Average Cost Adjustments ___________________________________________________________________________________
(Prior qty + Σ Receipt Qty + Σ Average Cost Adjustment Qty)

= ((100 * 7.00) + (100 * 9.00)) + 300.00 + (100 * 11.00)
_______________________________________________
(100 + 100 + 100)
= $ 10.00

Sub-Ledger Entries

Since the adjustment quantity does not affect the physical Inventory balance, the accounting entry that this adjustment creates uses only the difference between the cost specified in the adjustment and the calculated PMAC cost.

Inventory Valuations Comparison

Inventory ValuationU = Actual cost * On Hand Quantity
= 10.00 * 200
Inventory Valuation = $ 2000.00

INV Account balances

Balances from prior period = $ 700.00 (100 * 7.00)
Receipt = $ 900.00 (100 * 9.00)
Value Adjustment = $ 300.00
Average Cost Adjustment = $ 100.00 (100 * (11.00 – 10.00))

Total = $ 2000.00

Unit Cost Adjustments
Let us look at the effect of applying a Unit cost Adjustment of $2.00 to this scenario.

Actual Costing Logic
The Unit Cost Adjustments are applied after the actual cost calculations are completed as before. Hence, it’s a two-stage calculation of the actual unit cost of the item.

PMAC Cost =
(Prior Qty * Prior Cost) + Σ (Receipt Qty * PO Price) + Value Adjustments + Average Cost Adjustments __________________________________________________________________________________
(Prior qty + Σ Receipt Qty + Σ Average Cost Adjustment Qty)

= ((100 * 7.00) + (100 * 9.00)) + 300.00 + (100 * 11.00)
__________________________________________
(100 + 100 + 100)
= $ 10.00 (Without Unit Cost Adjustment)

Unit Cost Adjustment is considered only after the calculation of the actual cost in the Actual costing logic. So applying a Unit Cost Adjustment of $ 2.00, the new PMAC Cost will be as follows:

= $ 10.00 + $ 2.00 (Unit Cost Adjustment)
= $ 12.00

Sub-Ledger Entries
Since the Unit Cost Adjustment is essentially an addition to the Item Cost, the sub ledger considers entire quantity considered by the Actual Cost calculation and not just the on-hand quantity for the accounting postings.

Inventory Valuations Comparison

Inventory Valuation = Actual cost * On Hand Quantity
= 12.00 * 200
Inventory Valuation = $ 2400.00

INV Account balances

Balances from prior period = $ 700.00 (100 * 7.00)
Receipt = $ 900.00 (100 * 9.00)
Value Adjustment = $ 300.00
Average Cost Adjustment = - $ 100.00 (100 * (11.00 – 12.00))
Unit Cost Adjustment = $ 600.00 (300 * 2.00)
Total = $ 2400.00

CONCLUSION
The Actual Cost Adjustments is a very versatile functionality available in OPM Costing and can be used to affect the cost of items for a variety of reasons. The white paper shows the various uses and the effect of such adjustments to the cost of the item and Inventory account balances.

Monday, 29 September 2014

BOM API : Bom_Bo_Pub.process_bom

BOM API : Bom_Bo_Pub.process_bom

Declare

-- API input variables
l_bom_header_rec Bom_Bo_Pub.bom_head_rec_type := Bom_Bo_Pub.g_miss_bom_header_rec;
l_bom_revision_tbl Bom_Bo_Pub.bom_revision_tbl_type := Bom_Bo_Pub.g_miss_bom_revision_tbl;
l_bom_component_tbl Bom_Bo_Pub.bom_comps_tbl_type := Bom_Bo_Pub.g_miss_bom_component_tbl;
l_bom_ref_designator_tbl Bom_Bo_Pub.bom_ref_designator_tbl_type := Bom_Bo_Pub.g_miss_bom_ref_designator_tbl;
l_bom_sub_component_tbl Bom_Bo_Pub.bom_sub_component_tbl_type := Bom_Bo_Pub.g_miss_bom_sub_component_tbl;

-- API output variables
x_bom_header_rec Bom_Bo_Pub.bom_head_rec_type := Bom_Bo_Pub.g_miss_bom_header_rec;
x_bom_revision_tbl Bom_Bo_Pub.bom_revision_tbl_type := Bom_Bo_Pub.g_miss_bom_revision_tbl;
x_bom_component_tbl Bom_Bo_Pub.bom_comps_tbl_type := Bom_Bo_Pub.g_miss_bom_component_tbl;
x_bom_ref_designator_tbl Bom_Bo_Pub.bom_ref_designator_tbl_type := Bom_Bo_Pub.g_miss_bom_ref_designator_tbl;
x_bom_sub_component_tbl Bom_Bo_Pub.bom_sub_component_tbl_type := Bom_Bo_Pub.g_miss_bom_sub_component_tbl;
x_message_list Error_Handler.Error_Tbl_Type;

l_error_table Error_Handler.Error_Tbl_Type;
l_output_dir VARCHAR2(500) := '/usr/tmp/test';
l_debug_filename VARCHAR2(60) := 'test_debug.dbg';

l_return_status VARCHAR2(1) := NULL;
l_msg_count NUMBER := 0;
l_cnt NUMBER := 1;

-- WHO columns
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_row_cnt NUMBER := 1;
l_user_name VARCHAR2(30) := 'MFG';
l_resp_name VARCHAR2(30) := 'MFG_AND_DIST_SUPER_USER_APS';

BEGIN

-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;

-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
INTO l_application_id, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;

-- intiialize applications information
FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id); -- Mfg / Mfg & Dist Mgr / INV
dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );

-- initialize BOM header
l_bom_header_rec.assembly_item_name := 'RS_BOM_ASSY1';
l_bom_header_rec.organization_code := 'M1';
l_bom_header_rec.assembly_type := 1;
l_bom_header_rec.transaction_type := 'CREATE';
l_bom_header_rec.return_status := NULL;

l_cnt := 1;
-- initialize BOM components
-- component 1
l_bom_component_tbl (l_cnt).organization_code := 'M1';
l_bom_component_tbl (l_cnt).assembly_item_name := 'RS_BOM_ASSY1';
l_bom_component_tbl (l_cnt).start_effective_date := sysdate; -- to_date('16-JUL-2010 19:30:39','DD-MON-YY HH24:MI:SS'); -- should match timestamp for UPDATE
l_bom_component_tbl (l_cnt).component_item_name := 'RS_BOM_C1';
l_bom_component_tbl (l_cnt).alternate_bom_code := NULL;
l_bom_component_tbl (l_cnt).supply_subinventory := 'RIP';
l_bom_component_tbl (l_cnt).location_name := NULL; -- '6.6.6..'; -- provide concatenated segments for locator
l_bom_component_tbl (l_cnt).comments := 'Created from BOM API';
l_bom_component_tbl (l_cnt).item_sequence_number := '10';
l_bom_component_tbl (l_cnt).operation_sequence_number := '10';
l_bom_component_tbl (l_cnt).transaction_type := 'CREATE';
l_bom_component_tbl (l_cnt).quantity_per_assembly := 2;
l_bom_component_tbl (l_cnt).return_status := NULL;

-- component 2
l_cnt := l_cnt + 1;
l_bom_component_tbl (l_cnt).organization_code := 'M1';
l_bom_component_tbl (l_cnt).assembly_item_name := 'RS_BOM_ASSY1';
l_bom_component_tbl (l_cnt).start_effective_date := SYSDATE;
l_bom_component_tbl (l_cnt).component_item_name := 'RS_BOM_C2';
l_bom_component_tbl (l_cnt).alternate_bom_code := NULL;
l_bom_component_tbl (l_cnt).supply_subinventory := 'RIP';
l_bom_component_tbl (l_cnt).location_name := NULL;
l_bom_component_tbl (l_cnt).comments := 'Created from BOM API';
l_bom_component_tbl (l_cnt).item_sequence_number := '20';
l_bom_component_tbl (l_cnt).operation_sequence_number := '10';
l_bom_component_tbl (l_cnt).transaction_type := 'CREATE';
l_bom_component_tbl (l_cnt).quantity_per_assembly := 5;
l_bom_component_tbl (l_cnt).return_status := NULL;

-- initialize error stack for logging errors
Error_Handler.initialize;

-- call API to create / update bill
DBMS_OUTPUT.PUT_LINE('=======================================================');
DBMS_OUTPUT.PUT_LINE('Calling Bom_Bo_Pub.process_bom API');

Bom_Bo_Pub.process_bom (p_bo_identifier => 'BOM',
p_api_version_number => 1.0,
p_init_msg_list => TRUE,
p_bom_header_rec => l_bom_header_rec,
p_bom_revision_tbl => l_bom_revision_tbl,
p_bom_component_tbl => l_bom_component_tbl,
p_bom_ref_designator_tbl => l_bom_ref_designator_tbl,
p_bom_sub_component_tbl => l_bom_sub_component_tbl,
x_bom_header_rec => x_bom_header_rec,
x_bom_revision_tbl => x_bom_revision_tbl,
x_bom_component_tbl => x_bom_component_tbl,
x_bom_ref_designator_tbl => x_bom_ref_designator_tbl,
x_bom_sub_component_tbl => x_bom_sub_component_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count ,
p_debug => 'Y',
p_output_dir => l_output_dir,
p_debug_filename => l_debug_filename
);

DBMS_OUTPUT.PUT_LINE('=======================================================');
DBMS_OUTPUT.PUT_LINE('Return Status: '||l_return_status);

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN

dbms_output.put_line('x_msg_count:' || l_msg_count);

Error_Handler.GET_MESSAGE_LIST(x_message_list => l_error_table);
DBMS_OUTPUT.PUT_LINE('Error Message Count :'||l_error_table.COUNT);
FOR i IN 1..l_error_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(to_char(i)||':'||l_error_table(i).entity_index||':'||l_error_table(i).table_name);
DBMS_OUTPUT.PUT_LINE(to_char(i)||':'||l_error_table(i).message_text);
END LOOP;

END IF;
DBMS_OUTPUT.PUT_LINE('=======================================================');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception Occured :');
DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('=======================================================');
RAISE;
END ;

Wednesday, 24 September 2014

Oracle Apps BOM Tables and Queries

BOM_DEPARTMENTS Departments
BOM_DEPARTMENT_CLASSES Department classes
BOM_DEPARTMENT_RESOURCES Resources associated with departments
BOM_OPERATIONAL_ROUTINGS Routings
BOM_OPERATION_NETWORKS Routing operation networks
BOM_OPERATION_RESOURCES Resources on operations
BOM_OPERATION_SEQUENCES Routing operations
BOM_OPERATION_SKILLS
BOM_RESOURCES Resources, overheads, material cost codes, and material overheads
BOM_STANDARD_OPERATIONS Standard operations
BOM_ALTERNATE_DESIGNATORS Alternate designators
BOM_COMPONENTS_B Bill of material components
BOM_STRUCTURES_B Bills of material
BOM_STRUCTURE_TYPES_B Structure Type master table
BOM_BILL_OF_MATERIALS
BOM_PARAMETERS
BOM_DEPENDENT_DESC_ELEMENTS
BOM_REFERENCE_DESIGNATORS
BOM_SUBSTITUTE_COMPONENTS
FND_DOCUMENTS
FND_DOCUMENTS_TL
FND_DOCUMENTS_SHORT_TEXT
FND_ATTACHED_DOCUMENTS

Select * from MFG_LOOKUPS
WHERE
--LOOKUP_CODE = 'BOM_ITEM_TYPE'
--LOOKUP_CODE = 'WIP_SUPPLY_TYPE'
--LOOKUP_CODE = 'BOM_ASSEMBLY_TYPE'

-----------------------------------------------------------------
1) Query for BOM Details

SELECT d.inventory_item_id,
d.concatenated_segments "Assembly_item",
e.organization_code "Organization",
nvl(a.ALTERNATE_BOM_DESIGNATOR,'10') "ALTERNATIVE BOM",
b.ITEM_NUM "ITEM",
c.concatenated_segments "COMPONENT",
b.component_quantity "UNIT",
c.primary_uom_code "QUANTITY",
d.primary_uom_code "UNIT"
FROM bom_bill_of_materials a
,bom_inventory_components b
,mtl_system_items_kfv c
,mtl_system_items_kfv d
,org_organization_definitions e
WHERE 1=1
AND a.organization_id in (:P_organization_id)
AND (a.BILL_SEQUENCE_ID = b.BILL_SEQUENCE_ID
OR a.COMMON_BILL_SEQUENCE_ID = b.BILL_SEQUENCE_ID)
AND b.implementation_date IS NOT NULL
AND b.DISABLE_DATE IS NULL
AND a.organization_id = c.organization_id
AND b.COMPONENT_ITEM_ID = c.inventory_ITEM_ID
AND a.assembly_item_id = d.inventory_item_id
AND a.organization_id = d.organization_id
AND a.organization_id = e.organization_id
--AND c.organization_id = :P_organization_id
--AND a.ASSEMBLY_ITEM_ID = :P_assembly_item_id
-- AND ALTERNATE_BOM_DESIGNATOR IS NULL
ORDER BY e.organization_code,d.inventory_item_id,ALTERNATE_BOM_DESIGNATOR,b.ITEM_NUM;

----------------------------------------------------------------
2) Query for Resources

SELECT c.concatenated_segments "Assemly_Item",
d.organization_code "Organization",
a.alternate_routing_designator "Alternate_routing",
b.operation_seq_num "OPERATION NUMBER",
e.department_code "WORK CENTER",
b.operation_description "DESCRIPTION(Operation)",
c.primary_uom_code "OPERATION UNIT",
G.uom "UNIT",
f.operation_code,
g.resource_code,
g.USAGE_RATE_OR_AMOUNT
h.DESCRIPTION
FROM bom_operational_routings a
,bom_operation_sequences b
,mtl_system_items_kfv c
,org_organization_definitions d
,bom_departments e
,bom_standard_operations f
,bom_operation_resources_v g
,bom_resources h
WHERE a.organization_id in (:P_organization_id)
AND a.organization_id = d.organization_id
AND a.organization_id = c.organization_id
AND a.assembly_item_id = c.inventory_item_id
AND a.routing_sequence_id = b.routing_sequence_id
AND b.department_id = e.department_id
AND b.disable_date is null
--AND a.assembly_item_id = :P_assembly_item_id
AND f.standard_operation_id(+) = b.standard_operation_id
AND g.operation_sequence_id = b.operation_sequence_id
AND h.resource_id = g.resource_id
order by d.organization_code,c.concatenated_segments,a.alternate_routing_designator, b.operation_seq_num

--------------------------------------------------------------------------------------------

3) Departments and Resources and Departments associated with Resources

select b.organization_code "Plant",
a.DEPARTMENT_CODE "Department",
a.DESCRIPTION "Department Description",
a.DEPARTMENT_CLASS_CODE "Department Class",
c.DESCRIPTION "Department Class Description",
d.location_code "Department_location"
from BOM_DEPARTMENTS a
,org_organization_definitions b
,BOM_DEPARTMENT_CLASSES c
,hr_locations d
where a.organization_id in (:P_organization_id)
AND a.organization_id = b.organization_id
AND a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE
AND a.organization_id = c.organization_id
AND a.location_id = d.location_id(+)

select b.organization_code "Plant",
a.RESOURCE_CODE "Resource Name",
a.DESCRIPTION "Resource Description",
a.UNIT_OF_MEASURE
from BOM_RESOURCES a
,org_organization_definitions b
--,mfg_lookups c
where a.organization_id in (:P_organization_id)
AND a.organization_id = b.organization_id
AND a.Disable_date is Null
--AND c.lookup_type = 'BOM_BASIS_TYPE'
--AND c.LOOKUP_CODE = a.DEFAULT_BASIS_TYPE

exec mo_global.set_policy_context('S',:P_org_id);

--BOM_RESOURCE_TYPE
--BOM_BASIS_TYPE

select * from mfg_lookups where lookup_type like 'BOM_BASIS_TYPE'

select * from BOM_DEPARTMENT_RESOURCES

select * from BOM_RESOURCE_SHIFTS

select b.organization_code "Plant",
a.DEPARTMENT_CODE "Department",
a.DESCRIPTION "Department Description",
a.DEPARTMENT_CLASS_CODE "Department Class",
c.DESCRIPTION "Department Class Description",
d.location_code "Department_location",
f.RESOURCE_CODE "Resource Name",
f.DESCRIPTION "Resource Description",
f.UNIT_OF_MEASURE
from BOM_DEPARTMENTS a
,org_organization_definitions b
,BOM_DEPARTMENT_CLASSES c
,hr_locations d
,BOM_DEPARTMENT_RESOURCES e
,BOM_RESOURCES f
-- ,BOM_RESOURCE_SHIFTS g
where a.organization_id in (:P_oranization_id)
AND a.organization_id = b.organization_id
AND a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE
AND a.organization_id = c.organization_id
AND a.location_id = d.location_id(+)
AND a.DEPARTMENT_ID = e.DEPARTMENT_ID
AND e.RESOURCE_ID = f.RESOURCE_ID
--AND a.DEPARTMENT_ID = g.DEPARTMENT_ID
--AND f.RESOURCE_ID = g.RESOURCE_ID

--------------------------------------------------------------------------------------------------------

Routing Open Interface

Concurrent: Bill and Routing Interface

-- Create Routing
insert into bom_op_routings_interface
(
assembly_item_id,
process_revision,
process_flag,
transaction_type,
routing_type,
organization_id
)
values
(
218967,
Null,
1, --1 (Pending), 3 (Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).
'CREATE',
1,----1) manufacturing, 2) engineering
204
);

-- Create Routing Sequences
insert into bom_op_sequences_interface
(
assembly_item_id,
operation_seq_num,
department_id,
department_code,
process_flag,
transaction_type,
organization_id,
effectivity_date
)
values
(
218967,
10,
42877,
'PD1',
1,
'CREATE',
204,
sysdate
);

-- Create Operation Resources
insert into bom_op_resources_interface
(
resource_seq_num,
resource_id,
resource_code,
usage_rate_or_amount, --basis_type ,
assigned_units,--schedule_flag,autocharge_type,
assembly_item_id,
operation_seq_num, --operation_sequence_id,
process_flag,
transaction_type,
effectivity_date,
organization_id ,
schedule_flag,
schedule_seq_num
)
values
(
10,
90529,
'PR1',
1,
1,
218967,
10, --2346216 ,
1,
'CREATE',
sysdate,
204,
1,
10
);

select * from mtl_system_items_kfv where inventory_item_id = 133 and organization_id = 204

select * from bom_departments where department_id = 42877 and organization_id = 204

select * from BOM_DEPARTMENT_RESOURCES where department_id = 42877

select * from bom_resources where resource_id = 90529

select * from bom_op_resources_interface

BOM Routing Creation API

Creating Maintenance Routes with BOM_RTG_PUB API (You can use this API for discrete manufacturing)
Firstly I create a custom table then I insert the datas from excel sheet.

CREATE TABLE XXINN_MAINTENANCE_ROUTES(
ASSEMBLY_ITEM_NAME VARCHAR2(240 BYTE),
ORGANIZATION_CODE VARCHAR2(3 BYTE),
LONG_DESCRIPTION VARCHAR2(4000 BYTE),
OPERATION_DESCRIPTION VARCHAR2(400 BYTE),
OPERATION_SEQUENCE_NUMBER NUMBER,
DEPARTMENT_CODE VARCHAR2(10 BYTE),
RESOURCE_SEQUENCE_NUMBER NUMBER,
RESOURCE_CODE VARCHAR2(10 BYTE),
USAGE_RATE_OR_AMOUNT NUMBER,
ASSIGNED_UNITS NUMBER,
ERROR VARCHAR2(2000 BYTE)
)
Then I have written this procedure for creating the maintenance routes.

CREATE OR REPLACE PROCEDURE APPS.XXINN_CREATE_MAINTNCE_ROUTE(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER) IS
P_HEADER_REC Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
P_OPERATION_REC Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
P_RESOURCE_REC Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
p_RETURN_STATUS VARCHAR2(240);
p_MSG_COUNT NUMBER;
l_ret_text VARCHAR2(32000);
p_Message_List Error_Handler.Error_Tbl_Type;
i NUMBER;
j NUMBER;
x_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
x_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
x_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
x_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
x_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
x_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;

CURSOR C1_ENG IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code
FROM APPS.XXINN_MAINTENANCE_ROUTES t;

CURSOR C1_OPR(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.department_code,
LTRIM (RTRIM(T.LONG_DESCRIPTION)) LONG_DESCRIPTION,
translate (LTRIM(RTRIM( t.OPERATION_DESCRIPTION)),'ÇĞŞİÜÖ','CGSIUO') OPERATION_DESCRIPTION
FROM APPS.XXINN_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
ORDER BY t.operation_sequence_number;

CURSOR C1_RES(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2, p_seq_number NUMBER) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.department_code,
t.resource_sequence_number,
t.resource_code,
t.usage_rate_or_amount,
t.ASSIGNED_UNITS
FROM APPS.XXINN_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
AND t.operation_sequence_number = p_seq_number
ORDER BY t.resource_sequence_number;

BEGIN
FND_GLOBAL.APPS_INITIALIZE(1117, 23118, 426); --user_id, resp_id, resp_appl_id
FOR C_REC IN C1_ENG LOOP
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, C_REC.ASSEMBLY_ITEM_NAME);
DBMS_OUTPUT.PUT_LINE(C_REC.ASSEMBLY_ITEM_NAME);

P_HEADER_REC := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
P_HEADER_REC.ASSEMBLY_ITEM_NAME := C_REC.ASSEMBLY_ITEM_NAME;
P_HEADER_REC.ORGANIZATION_CODE := C_REC.ORGANIZATION_CODE;
P_HEADER_REC.Eng_Routing_Flag := 2; -- 1 for manufacturing 2- for Engineering
P_HEADER_REC.Transaction_Type := 'CREATE'; --
P_HEADER_REC.Return_Status := NULL; --
i := 0;
j := 0;
P_OPERATION_REC.DELETE;
P_RESOURCE_REC.DELETE;

FOR c_rec2 IN C1_OPR(C_REC.ASSEMBLY_ITEM_NAME, C_REC.ORGANIZATION_CODE) LOOP
i := i + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
' ' || C_REC2.OPERATION_SEQUENCE_NUMBER || '-' ||
C_REC2.DEPARTMENT_CODE);
DBMS_OUTPUT.PUT_LINE(' ' ||
C_REC2.OPERATION_SEQUENCE_NUMBER || '-' ||
C_REC2.DEPARTMENT_CODE||'-'||C_REC2.OPERATION_DESCRIPTION);
P_OPERATION_REC(i).ASSEMBLY_ITEM_NAME := C_REC2.ASSEMBLY_ITEM_NAME;
P_OPERATION_REC(i).ORGANIZATION_CODE := C_REC2.ORGANIZATION_CODE;
P_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER := C_REC2.OPERATION_SEQUENCE_NUMBER;
P_OPERATION_REC(i).DEPARTMENT_CODE := C_REC2.DEPARTMENT_CODE;
P_OPERATION_REC(i).LONG_DESCRIPTION := C_REC2.LONG_DESCRIPTION; --Ana tabloda long description diye bir alan aç
P_OPERATION_REC(i).OPERATION_DESCRIPTION := C_REC2.OPERATION_DESCRIPTION;
P_OPERATION_REC(i).Operation_Type := 1; --
P_OPERATION_REC(i).Start_Effective_Date := SYSDATE; --
P_OPERATION_REC(i).Transaction_Type := 'CREATE'; --

FOR c_rec3 IN C1_RES(C_REC.ASSEMBLY_ITEM_NAME,
C_REC.ORGANIZATION_CODE,
C_REC2.OPERATION_SEQUENCE_NUMBER) LOOP
j := j + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
' ' ||
C_REC3.RESOURCE_SEQUENCE_NUMBER || '-' ||
C_REC3.RESOURCE_CODE);
DBMS_OUTPUT.PUT_LINE(' ' ||
C_REC3.RESOURCE_SEQUENCE_NUMBER || '-' ||
C_REC3.RESOURCE_CODE);
P_RESOURCE_REC(j).ASSEMBLY_ITEM_NAME := C_REC3.ASSEMBLY_ITEM_NAME;
P_RESOURCE_REC(j).ORGANIZATION_CODE := C_REC3.ORGANIZATION_CODE;
P_RESOURCE_REC(j).OPERATION_SEQUENCE_NUMBER := C_REC3.OPERATION_SEQUENCE_NUMBER;
P_RESOURCE_REC(j).RESOURCE_SEQUENCE_NUMBER := C_REC3.RESOURCE_SEQUENCE_NUMBER;
P_RESOURCE_REC(j).RESOURCE_CODE := C_REC3.RESOURCE_CODE;
P_RESOURCE_REC(j).USAGE_RATE_OR_AMOUNT := C_REC3.USAGE_RATE_OR_AMOUNT;
P_RESOURCE_REC(j).Operation_Type := 1; --
P_RESOURCE_REC(j).Op_Start_Effective_Date := SYSDATE; --
P_RESOURCE_REC(j).Transaction_Type := 'CREATE'; --
P_RESOURCE_REC(j).Schedule_Flag := 1;
P_RESOURCE_REC(J).ASSIGNED_UNITS := C_REC3.ASSIGNED_UNITS;

END LOOP; --res

END LOOP; -- opr

x_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
x_rtg_revision_tbl.delete;
x_operation_tbl.delete;
x_op_resource_tbl.delete;
x_sub_resource_tbl.delete;
x_op_network_tbl.delete;

APPS.BOM_RTG_PUB.PROCESS_RTG(p_init_msg_list => TRUE,
p_rtg_header_rec => P_HEADER_REC,
p_operation_tbl => P_OPERATION_REC,
p_op_resource_tbl => P_RESOURCE_REC,
x_rtg_header_rec => x_rtg_header_rec,
x_rtg_revision_tbl => x_rtg_revision_tbl,
x_operation_tbl => x_operation_tbl,
x_op_resource_tbl => x_op_resource_tbl,
x_sub_resource_tbl => x_sub_resource_tbl,
x_op_network_tbl => x_op_network_tbl,
x_return_status => p_RETURN_STATUS,
X_MSG_COUNT => p_MSG_COUNT);

IF p_RETURN_STATUS <> 'S' THEN
error_handler.Get_Message_List(p_message_list);
FOR i IN 1 .. p_message_list.COUNT LOOP

IF i = 1 THEN
l_ret_text := p_Message_List(i).message_text;

ELSE
l_ret_text := l_ret_text || chr(10) || p_Message_List(i).message_text;
END IF;

END LOOP;

-- ROLLBACK;

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' ERROR!!!!!!!' || chr(10) || 'Error:' ||p_Message_List(i).message_type||'-'||p_Message_List(i).message_name||
l_ret_text);

DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' ERROR!!!!!!!' || chr(10) || 'Error:' ||p_Message_List(i).message_type||'-'||p_Message_List(i).message_name||
l_ret_text);

UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR = l_ret_text
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code;

ELSE

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,

P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' the process is successful..');

DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' the process is successful..');

UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR = null
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code;

END IF;

END LOOP;

END

;
/

BOM Creation API

===============================================================
bom_bo_pub.process_bom
(p_bo_identifier => 'BOM',
-- This parameter is required. It is used by the API to compare the version number of
-- incoming calls to its current version number.
p_api_version_number => 1.0 ,
-- This parameter is set to TRUE, allows callers to request that the API do the
--initialization of message list on their behalf.
p_init_msg_list => TRUE,
-- This is a set of data structures that represent the incoming business objects. This is a
--record that holds the Bill of Materials header for the BOM
p_bom_header_rec => v_bom_header_rec,
-- All the p*_tbl parameters are data structure that represent incoming business objects
-- They are PL/SQL tables of records that hold for each of the other entities.
p_bom_revision_tbl => v_bom_revision_tbl , p_bom_component_tbl => v_bom_component_tbl, p_bom_ref_designator_tbl => v_bom_ref_designator_tbl, p_bom_sub_component_tbl => v_bom_sub_component_tbl,
-- All the x*_tbl parameters are data structure that represent outgoing business objects
-- They are PL/SQL tables of records that hold records for each of the other entities except
-- now they have all the changes that the import program made to it through all the
-- steps.
x_bom_header_rec => v_bom_hdr_rec , x_bom_revision_tbl => v_bom_rev_tbl,
x_bom_component_tbl => v_bom_comp_tbl,
x_bom_ref_designator_tbl => v_bom_ref_desig_tbl, x_bom_sub_component_tbl => v_bom_sub_comp_tbl,
-- This is a flag that indicates the state of the whole business object after the
-- import. 'S' - Success, 'E' - Error, 'F' - Fatal Error, 'U' - Unexpected Error ,
x_return_status => v_return_status ,
-- This holds the number of messages in the API message stack after
--the import.
x_msg_count => v_msg_count
p_debug => 'N',
p_output_dir => '',
p_debug_filename => '' );
=========================================================

Saturday, 12 July 2014

Use of Fnd_log.STRING in PL/Sql code

Fnd_Log.STRING(log_level => Fnd_Log.LEVEL_STATEMENT,MODULE =>xxt.apps.custompackage name
                  ,message => 'your message');

FND: Debug Log Level   Codes

   LEVEL_UNEXPECTED    : 6
   LEVEL_ERROR         : 5
   LEVEL_EXCEPTION     : 4
   LEVEL_EVENT         : 3
   LEVEL_PROCEDURE     : 2
   LEVEL_STATEMENT     : 1
--------------------------------------------

FND: Debug Log Enabled : Yes

FND: Debug Log Module   Set this to %

Step 2
Login to the application and reproduce the problem.

Step 3
SELECT *
FROM fnd_log_messages
WHERE user_id = <id>
ORDER BY log_sequence DESC

Why to set the profile option to statement level?
This profile option has following main levels.-
Error
Warning
Procedure
Statement

select * from fnd_log_messages where user_id = <id>and LOG_LEVEL =<log level>

To purge data in production:

You can run concurrent program “Purge Debug Log and System Alerts”.

I have written a pl/sql concurrent process to interface Purchase Orders from 3rd Party System. How will add debug messages?
    fnd_log.STRING(log_level => fnd_log.level_statement
                  ,module    => '<module >'
                  ,message   => '<message >');

Note:

fnd_log.string eventually calls procedure FND_LOG.STRING_UNCHECKED_INTERNAL2. This procedure uses pragma AUTONOMOUS_TRANSACTION with a commit.