Wednesday, March 31, 2010
1. What are the important tables for sub-ledger accounting?
xla_transaction_entities
xla_events
xla_ae_headers
xla_ae_lines
xla_distribution_links
xla_control_balances
2. How the SLA events get generated?
Through the SLA API's in the subledgers' code
3. What are the steps to transfer the data to GL from the subledgers?
Submit Create Accounting / Transfer Journal Entries to GL.
1. Create Accounting
2. Tranfer to GL (includes Journal Import)
3. Post to GL
4. Which process push the data into which tables?
Create Accounting - Subledger tables and xla_events -> xla_ae_headers, xla_ae_lines and xla_distribution_links
Transfer to GL - xla_ae_headers and xla_ae_lines -> gl_je_headers and gl_je_lines
Post to GL - gl_je_headers and gl_je_lines -> gl_balances
Subledger Accounting Balances Update - xla_ae_headers and xla_ae_lines -> xla_control_balances
5. What data will be there in xla_control_balances?
Balances related to Third Party Control Accounts
Wednesday, March 31, 2010 by Team search · 0
It can be used to drilldown GL to AR (or) in the reverse way. Here let us try to collect the General Ledger information from the Account receivables information (Receipt Number). All the queries used below were tested in R12.1.1 instance.
Collecting the sub ledger information (Receipt Information)
Here we collect all the information about the Receipt number from sub-ledger (AR) tables. The most important information and used to link with GL tables is AR_DISTRIBUTIONS_ALL.line_id
by Team search · 0
API to populate the Descriptive element Value of an item ( inv_item_catalog_elem_pub.process_item_descr_elements)
DECLARE
v_inv_item_id NUMBER:= 0;
v_catalog_Group_id NUMBER:= 0;
v_organization_id NUMBER: = 0;
x_generated_descr VARCHAR2(240);
x_return_status VARCHAR2 (1);
x_errorcode NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (1000);
x_msg_index_out NUMBER: =0;
l_item_desc_elem_table INV_ITEM_CATALOG_ELEM_PUB.ITEM_DESC_ELEMENT_TABLE;
l_current_item_desc_elem INV_ITEM_CATALOG_ELEM_PUB.ITEM_DESC_ELEMENT;
l_error_message VARCHAR2 (1000);
j NUMBER := 0;
BEGIN
--Getting the item id for the existing item ABCTEST
BEGIN
SELECT inventory_item_id
INTO v_inv_item_id
FROM mtl_system_items_b msi,
mtl_parameters mp
WHERE msi.organization_id = mp.organization_id
AND msi.segment1 = 'ABCTEST'
AND mp.organization_code = 'V1';
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Error in getting the item id for Item ABCTEST and error is '||SUBSTR (SQLERRM, 1,200));
END;
-- Populating the pl/Sql table. In this for Element Name CatalogElement1, Element value ABCValue1 is Mapped.
l_current_item_desc_elem.DESCRIPTION_DEFAULT := 'Y';
l_current_item_desc_elem.ELEMENT_NAME := 'CatalogElement1';
l_current_item_desc_elem.ELEMENT_VALUE := 'ABCValue1';
l_item_desc_elem_table (j) := l_current_item_desc_elem;
BEGIN
FND_MSG_PUB.INITIALIZE;
--API to assign Element Value to the Item--
inv_item_catalog_elem_pub.process_item_descr_elements
( p_api_version => 1.0
, p_inventory_item_id => v_inv_item_id
, p_item_desc_element_table => l_item_desc_elem_table
, x_generated_descr => x_generated_descr
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
COMMIT;
IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
fnd_file.put_line (fnd_file.LOG,'Element value ABCValue1 is Successfully assigned to the element name CatalogElement1 for the Item ABCTEST ');
ELSE
--Getting the Error Reason if the element value is not assigned
-- If there are multiple errors
IF (FND_MSG_PUB.Count_Msg > 1) THEN
FOR k IN 1..FND_MSG_PUB.Count_Msg LOOP
FND_MSG_PUB.Get (p_msg_index => k,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => x_msg_index_out);
IF x_msg_data IS NOT NULL THEN
l_error_message:= l_error_message ||'-'||x_msg_data;
END IF;
END LOOP ;
ELSE
--Only one error
FND_MSG_PUB.Get (p_msg_index => 1,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => x_msg_index_out);
l_error_message:= x_msg_data;
END IF;
Fnd_file.put_line(fnd_file.LOG,'Error in assigning Element value ABCValue1 to the element name CatalogElement1 for the Item ABCTEST and reason is '||l_error_message);
END IF;
END;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Error '|| SUBSTR (SQLERRM, 1,200));
END;
by Team search · 0
DECLARE
V_item_id NUMBER: = 0;
V_catalog_Group_id NUMBER: = 0;
V_organization_id NUMBER: = 0;
V_request_id NUMBER: = 0;
BEGIN
--Getting the Organization id
BEGIN
SELECT Organization_id
INTO v_organization_id
FROM mtl_parameters mp
WHERE mp.organization_code = 'V1';
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Error in getting the Organization id for Organization code V1 and error is '||SUBSTR (SQLERRM, 1,200));
END;
--Getting the item id for the existing item ABCTEST
BEGIN
SELECT inventory_item_id
INTO v_item_id
FROM mtl_system_items_b msi
WHERE msi.segment1 = 'ABCTEST'
AND msi.organization_id = v_organization_id;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Error in getting the item id for Item ABCTEST and error is '||SUBSTR (SQLERRM, 1,200));
END;
--Getting the catalog group id for the existing Catalog Group Name 'NewCatalog'
BEGIN
SELECT item_catalog_group_id
INTO v_catalog_group_id
FROM mtl_item_catalog_groups
WHERE segment1='NewCatalog';
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Error in getting the catalog group id for catalog group Newcatalog and error is '||SUBSTR (SQLERRM, 1,200));
END;
--Inserting into Item interface table
BEGIN
INSERT INTO mtl_system_items_interface
(inventory_item_id,
organization_id,
process_flag,
set_process_id,
transaction_type,
item_catalog_group_id
)
VALUES
(v_item_id,
v_organization_id,
1,
1,
'UPDATE',
v_catalog_group_id
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Error in inserting record in interface table and error is '||SUBSTR (SQLERRM, 1,200));
END;
--Submit the item import program in Update Mode to update the item catalog group information
BEGIN
fnd_file.put_line (fnd_file.LOG,'--Submitting Item Import Program for Item--');
v_request_id:= Fnd_Request.submit_request (
application => 'INV',
Program => 'INCOIN',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => v_organization_id,
argument2 => 1,
argument3 => 1, --Group ID option (All)
argument4 => 1, -- Group ID Dummy
argument5 => 1, -- Delete processed Record
argument6 => 1, -- Set Process id
argument7 => 2 -- Update item
);
COMMIT;
IF (v_request_id = 0) THEN
fnd_file.put_line (fnd_file.LOG,'Item Import Program Not Submitted');
ELSE
fnd_file.put_line (fnd_file.LOG,'Item Import Program submitted');
END IF;
END;
END;
by Team search · 0
Subscribe to:
Posts (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.