Thursday, 17 October 2013

P2P Mass Addition into Asset


1-      Expense item , Expense account as CIP or Asset clearing account
2-      Asset category in item level
3-      Define asset category account and book details in asset
4-      Create PO ,
5-      Approve and receive
6-      Create invoice in AP, Check track asset flag, and check line level distribution account as CIP of Asset clearing account .
7-      Validate invoice
8-      Post invoice to GL
9-      Run Mass Asset create program from payables
10-   Review asset detail in Prepare mass addition window
11-   Change the Queue status to Post

12-   Run Mass Asset post 

Charge Account Defaulting in PO

Charge account defaulting rules depend on 
a) Type of Item 
b) Destination Type
c) Destination Sub-inventory.

There are 3 kinds of Items used in Purchasing.

Expense Items - Referred to as Item A
Inventory Expense Items - Referred to as Item B
Inventory Asset Items - Referred to Item C


1) Expense Items - Referred to as Item A.
These items normally have the following attributes...

INVENTORY_ASSET_FLAG = N
PURCHASING_ITEM_FLAG = Y
INVENTORY_ITEM_FLAG = N

2) Inventory Expense Items - Referred to as Item B
These items normally have the following attributes...

INVENTORY_ASSET_FLAG = N
PURCHASING_ITEM_FLAG = Y
INVENTORY_ITEM_FLAG = Y


c) Inventory Asset Items - Referred to Item C
These items have the following attributes...

INVENTORY_ASSET_FLAG = Y
PURCHASING_ITEM_FLAG = Y
INVENTORY_ITEM_FLAG = Y
COSTING_ENABLED_FLAG = Y


2. DESTINATION TYPE :
The Destination type determines the final destination of the
items.
This can be of two types 
a) EXPENSE
b) INVENTORY - 



3. DESTINATION Subinventory:
When an Item is being received into Inventory , a sub-inventory 
needs to be specified. A subinventory can be 
a) ASSET subinventory
b) EXPENSE sub-inventory


The sql below will help in identifying what type of a 
subinventory , the item is being delivered INTO.


select 
substr(EXPENSE_ACCOUNT,1,6) Exp_act , 
substr(ENCUMBRANCE_ACCOUNT,1,7) Enc_act ,
ASSET_INVENTORY,
substr(SECONDARY_INVENTORY_NAME,1,15) S_name,
substr(DESCRIPTION,1,15) descrip,
substr(SOURCE_ORGANIZATION_ID,1,8) s_o_id
from 
MTL_SECONDARY_INVENTORIES
where 
SECONDARY_INVENTORY_NAME = '&destination_subinventory';

pass the value of DESTINATION_SUBINVENTORY 
from PO_DISTRIBUTIONS_ALL.DESTINATION_SUBINVENTORY

if the value of ASSET_INVENTORY = 1 then this is an ASSET subinventory.
if the value of ASSET_INVENTORY = 2 then this is NOT an ASSET subinventory.




3.Keeping in view of the types of Items and the destination mentioned above
These are the destination types that can be possible against each type of item

Item A can have ONLY one destination- EXPENSE

Item B can have the destination of 
a) EXPENSE
b) INVENTORY -- ASSET subinventory
c) INVENTORY -- EXPENSE subinventory

Item C can have the destination of 
a) EXPENSE
b) INVENTORY -- ASSET subinventory
c) INVENTORY -- EXPENSE subinventory



Defaulting Rules for 
1) Item A --- Defaults from the Expense a/c defined against the ITEM in the Receiving Organization.( Organization Items) 

=======================================================================================
2) Item B--- EXPENSE -Defaults from the Expense a/c defined against the ITEM in the
in the Receiving Organization.( Organization Items) 

2) Item B ---INVENTORY--- ASSET Subinventory .... charge account comes from 
a) EXPENSE a/c of the ASSET SUBINVENTORY in the RECEIVING Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information)

b) If the PO distribution DOES NOT have a SUBINVENTORY information
Expense a/c defined in the Organizational Parameters of the Receiving Organization.

3) Item B ---INVENTORY--- EXPENSE subinventory -- comes from 
a) EXPENSE a/c of the EXPENSE SUBINVENTORY in the RECEIVING Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information)

b) If the PO distribution DOES NOT have a SUBINVENTORY information
Expense a/c defined in the Organizational Parameters of the Receiving Organization.

=========================================================================================
4) Item C --- EXPENSE -Defaults from the Expense a/c defined against the ITEM in the
in the Receiving Organization.( Organization Items) 

5) Item C ---INVENTORY---ASSEST Subinventory .... charge account comes from 

a) Material a/c defined against the ASSET SUBINVENTORY /of the RECEIVING Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information)

b) Material a/c defined in the Organizational Parameters of the Receiving Organization.

6) Item C ---INVENTORY --EXPENSE Subinventory -- comes from 
a) Expense a/c defined against the EXPENSE Subinventory in Receiving Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information)

b) If the PO distirbution DOES NOT have a SUBINVENTORY information
Material a/c defined in the Organizational Parameters of the Receiving Organization.

Average Cost update through API

You need to insert into the open interface tables MTL_TRANSACTIONS_INTERFACE and MTL_TXN_COST_DET_INTERFACE and then process the transactions.

The following is a sample script which will insert an average cost update transaction in MTL_TRANSACTIONS_INTERFACE and MTL_TXN_COST_DET_INTERFACE. Once the transaction processes to completion, a transaction will be inserted into MTL_MATERIAL_TRANSACTIONS (transaction_type_id = 80) and the corresponding distribution will be inserted into MTL_TRANSACTION_ACCOUNTS.

Please note this is a sample script. Before running, valid values should inserted into the script based on your specific environment or the transaction will fail.


INSERT
INTO mtl_transactions_interface
(
source_code ,
source_line_id ,
source_header_id ,
process_flag ,
transaction_mode ,
creation_date ,
last_update_date ,
created_by ,
last_updated_by ,
inventory_item_id ,
organization_id ,
transaction_date ,
transaction_quantity ,
transaction_uom ,
transaction_type_id ,
transaction_interface_id ,
material_overhead_account ,
material_account ,
resource_account ,
overhead_account ,
outside_processing_account,
cost_group_id
)
SELECT 'AvgCostUpdate' ,
1 ,
1 ,
1 ,
3 ,
SYSDATE ,
SYSDATE ,
1010026 ,
1010026 ,
1137465 ,
606 ,
sysdate ,
0 ,
'Ea' ,
80 ,
mtl_material_transactions_s.nextval ,
17347 ,
17347 ,
17347 ,
17347 ,
17347 ,
1327
FROM dual;

INSERT
INTO mtl_txn_cost_det_interface
(
cost_element_id ,
level_Type ,
Organization_id ,
new_average_cost ,
transaction_interface_id,
last_update_date ,
creation_date ,
last_updated_by ,
created_by
)
VALUES
(
1 ,
1 ,
606 ,
20 ,
16032545,
sysdate ,
sysdate ,
1010026 ,
1010026
);

COMMIT; 


After the transactions are inserted into the tables, please query and submit these transactions from:

Inventory -> Transactions -> Transaction Open Interface -> submit by selecting Tools -> Resubmit All.

Note: Ensure that you have the transaction managers (Inventory -> Setup -> Interface Managers - Material Transaction Manager) up and running.

ASL Sourcing to Requisitions Setup and Usage Guide


Purpose:
This document steps through the required setups that are necessary in order to use Sourcing functionality that is provided by Oracle Purchasing.  This functionality will default the Source Document, Supplier, and Supplier Site onto a requisition when either a category (commodity) or item is entered onto a requisition.
Prerequisites:
    1) Define items.
    2) Create suppliers and supplier sites in the Suppliers window.
 
 
Section 1:  Defining the Supplier and Item/Commodity Combination
To define the supplier and item/commodity combination please follow the steps below:
1. Navigate to the Approved Supplier List window (Navigation: Supply Base > Approved Supplier List).
2. In the Organizations window that appears, choose the ship-to organization for which to define Approved Supplier List (ASL) entries.
3. Choose one of the following options to which to assign a supplier:
1)  Item - Assigns a supplier to a particular item.
2) Commodity - Assigns a supplier to a group of items belonging to a category (or commodity).
4. Select an Item or Commodity.
5. Choose a Business Type from the following:
1) Direct (Supplier):  Choose this if the Company sells their products directly.  If Direct is selected, choose the supplier Name and optionally, the Site.
Attention:  The supplier Name and Site, if specified, must match the sourcing rule Supplier and Site to default the supplier information or  source document information successfully.  See Section 2, Defining Sourcing Rules.
2) Manufacturer:  Company manufactures and sells through distributors.  If Manufacturer is chosen, choose the Manufacturer Name.
3) Distributor: Company sells products made by manufacturers
Attention:  If Distributor is selected, choose the distributor Name and optionally, the Site. A associate the Distributor with a Manufacturer, meaning define a Manufacturer in the ASL before defining its Distributor.
6.  Choose the supplier's approval Status.  Use one of the default Statuses provided or a custom defined ASL Supplier Status in the Approved Supplier List Statuses window.  See Note < > for more details on creating custom ASL Supplier Statuses.  If a supplier is debarred for a specific commodity, the supplier is prevented from supplying all items within that commodity.  However, if a supplier for a commodity is approved, the item-specific status for the supplier still takes precedence.
7.  Optionally choose the supplier item number:  For Suppliers and Distributors, this supplier item number defaults to the purchase order and requisition lines, and is used to validate the source documents.
8. Optionally choose a Review By date.  Use this date to determine when a proactive business review will be performed for the supplier.
9. Choose the Record Details tabbed region.
10. Choose one of the following in Global:
1) Yes - This ASL entry is valid for all inventory organizations in this operating unit.
2) No - This ASL entry is local, meaning that it is valid only for the organization Chosen in the Organizations window when first opening the Approved Supplier List window.
Note:  If two ASL entries for the same item or commodity. meaning one Global entry that applies to all organizations and one local entry that applies only to the local inventory organization, the local entry takes precedence.
11. Save the form.
12. Optionally specify additional information, such as Supplier Scheduling, source documents, and capacity details for the ASL entry.   For more information see Section 3,  Defining the Supplier/Item Attributes.
13.  To disable an ASL entry that is no longer to be used please do the following:
1) In the Approved Supplier List window, identify the line (ASL entry) to be disabled.
2) In the Key Attributes tabbed region, select the Disabled check box for the line.
The item-supplier combination in this ASL entry is inactive for new documents that are created.  Note that disabling an ASL entry is not the same as debarring a supplier. Debarring a supplier prevents sourcing to that supplier for that item or for all items in that commodity. Disabling an ASL entry disables just that line. If a separate ASL entry for the supplier is enabled Oracle will still source to that supplier.  If a local ASL entry is disabled, Purchasing uses the Global entry instead, if there is one.
To re-enable a supplier and item/commodity combination deselect the Disabled check box.  The item-supplier combination in this ASL entry is now active for new documents which are created.
 
 
Section 2:  Defining the Supplier/Item Attributes
Use the Supplier-Item Attributes window to specify additional information for the Approved Supplier List entry, including source document, Supplier Scheduling, and planning constraint information.
To define the supplier and commodity/item attributes:
1. Navigate to the Supplier-Item Attributes window by choosing the Attributes button in the Approved Supplier List window.
Additional Information: The Create Local button creates a copy of an existing global Approved Supplier List entry and makes it local to the organization selected in the Organizations Window before the ASL form was opened.  The Create Local button is not available if already creating a local entry or if a local entry for the item (in this or the Approved Supplier List window) already exists.
2. Choose the Purchasing UOM.
3. Choose the Release generation Method from the following options:
1) Automatic Release/Review:   Automatically generate releases, but require a separate step for approval.
2) Automatic Release:  Automatically generate approved releases.  This choice is not available if Encumbrance is turned on.
3) Release Using AutoCreate: use the AutoCreate window to create releases.
4. Enter a Price Update Tolerance only if importing price/sales catalog information through the Purchasing Documents Open Interface.
The Price Update Tolerance specifies the maximum percentage increase allowed to a price for this item/supplier combination when the supplier sends updated price/sales catalog information through the Purchasing Documents Open Interface. This field affects only those documents blanket purchase agreements and catalog quotations imported through the Purchasing Documents Open Interface. See: Setting a Price Tolerance in a Price/Sales Catalog Update.
5. Optionally choose the Country of Origin.
The Country of Origin is the country in which an item is manufactured. Choose a Country of Origin if a supplier Site is specified in the Approved Supplier List window.
The Country of Origin is defaulted onto purchase orders for this item/supplier combination. However, the Country of Origin can be changed on the purchase order or later on the receipt.
6. Choose one of the following attribute groupings from the tabbed region:
1) Source Documents: Associate specific quotations or blanket purchase agreements with the supplier/item combination.
Note:     If selecting Source Documents, setting the profile option PO: Automatic Document Sourcing to Yes enables Purchasing to default the most recent source documents automatically if preferred that rather than specifying source documents here. Please understand that even with this profile set to No the ranking of the source documents (by sequence number as described below) and will still enable the correct Source Document listed to default on the Requisition Line(s).
1. Enter a unique Seq (Sequence) Number.
The Sequence Number is used internally. If you enter more than one source document, Oracle Purchasing will use the document with the lowest sequence number.
2. Select a Document Type from the following:
             --Blanket
             --Quotation
A requisition with a quotation as a source document becomes a standard purchase order. A requisition with a blanket purchase agreement as a source document becomes a release. Standard purchase orders get source document information only from quotations.
If creating a Blanket as a source document for Oracle Supplier Scheduling, make sure the Supply Agreement option is selected for the blanket purchase agreement in the Terms and Conditions window.  Supplier Scheduling can communicate releases against a blanket purchase agreement only when this option is selected.
3. Choose a Document Number.
If Purchasing does not let allow a particular document number to be entered, it may be because the document is frozen, canceled, or not approved, or the agreement line is canceled. A quotation must be active.
4. Choose a Line Number.   The Status and Effective Dates for the document, if any, are displayed.
5. Save the form.
2) Supplier Scheduling: Associate Supplier Scheduling information with the supplier/item combination.  This option is available when an item and a supplier site is specified.
1. Optionally check Enable Planning Schedules and/or Enable Shipping Schedules if building Planning and/or Shipping Schedules.
If Enable Planning Schedules and/or Enable Shipping Schedules is checked, an assigned Scheduler may be added.
2. Check Enable AutoSchedule if to automatically build the schedule.
3. If Enable Planning Schedules and Enable AutoSchedule is checked, the following must be chosen:
              --Plan Bucket Pattern
              --Plan Schedule Type
4. If Enable Shipping Schedules and Enable AutoSchedule is checked, the following must be chosen:
              --Ship Bucket Pattern
              --Ship Schedule Type
5. If Enable Planning Schedules and Enable Authorizations is checked, it is an option to choose up to four Resource Authorizations with their associated timefences.
6.  Save the form.
3) Capacity Constraints: Specify capacity constraints for the supplier/item combination. This option is available when an item and a supplier site is specified.
The information entered here is used by Supply Chain Planning for planned orders.
1. In the Processing Lead Time field, enter the number of lead days it takes to receive this item after ordering it.
2. Choose a Delivery Calendar pattern to define the dates upon which the supplier delivers to this organization.
        This calendar is independent of the workday calendar.
3. Choose Order Modifier options to indicate ordering constraints, if any:
1) Minimum Order quantity: The minimum quantity that can be ordered.
2) Fixed Lot Multiple:  The incremental quantity that can be ordered on top of the Minimum Order quantity. For example, if the Minimum Order quantity is 100 and the Fixed Lot Multiple is 10, at least 100 must be ordered, and if the ordered amount is more than that, the amount ordered must be 110, 120, and so on.
4.  Create one or more entries in the Capacity area to indicate what the capacity is during a certain period or periods:
1) From Date: The date from which the capacity constraints take effect.
2) To Date: The date (optional) until which the capacity constraints take effect.
3) Capacity per Day: Quantity of units per day. This field is required if a From Date is entered.
5. Create one or more entries in the Tolerance Fences area to indicate how the capacity fluctuates depending on how many days in advance the orders will be made:
For example, if 12 Days in Advance is entered and a Tolerance % of 2, the amount ordered can exceed the supplier's capacity by 2 percent if ordered 12 days in advance.  Tolerance % must be specified if specifying Days in Advance.
4) Inventory: The information that you enter here enables use of the Vendor Managed Inventory (VMI) and Consigned Inventory functionality within Purchasing. You can choose this option if you specified an item and a supplier site.
1. For Vender Managed Inventory check VMI Enabled and:
1) Enter the UOM that the following minimum and maximum quantities represent.
2) Enter the Minimum quantity level for this item.
3) Enter the Maximum quantity level for this item.
2. If Supply Chain Exchange (SCE) is enabled, check the Automatic Allowed box to enable the replenishment method approval selection below.
1) Select the replenishment Approval method of Automatic, Supplier and Buyer, or Buyer. This field is only enabled if SCE is enabled and VMI Automatic Allowed is checked.
3.) For consigned inventory check Consigned from Supplier and:
1) Enter the number of days allowed before billing in Billing Cycle (Days). The system will display the Last Billing Date.

 
Section 3:  Defining Sourcing Rules
Sourcing can be defined that specify how to replenish items in an organization, such as purchased items in plants. Sourcing rules can also specify how to replenish all organizations, as when the entire enterprise gets a subassembly from a particular organization.
If there is a conflict between a sourcing rule and a bill of distribution, the sourcing rule takes precedence. For instance, if assigning a bill of distribution to AUS that tells it to source a part from NYC, AUS can still define a sourcing rule to source the part from SAC.  In this case, the local sourcing rule overrides the bill of distribution
To define a sourcing rule:
1. Navigate to the Sourcing Rule window.
2. Enter a unique sourcing rule name.
3. Indicate whether this sourcing rule is used for all organizations (global) or a single organization (local).
If the sourcing rule is local, enter an organization name; otherwise, the current organization will be the receiving organization.
4. Choose Copy From to copy the effectivity dates and shipping organization from another sourcing rule into this one.
5. Enter effectivity dates. A start date is mandatory, but entering an end date is optional.
6. For each range of effectivity dates, multiple shipping organizations may be included.  For each shipping organization that is included, select a sourcing type to specify whether the organization makes, buys, or internally transfers the item. It is also possible to copy a list of shipping organizations from an existing sourcing rule.
If a customer organization is listed as the receiving organization, then a supplier organization can not be selected as the shipping organization.
Note:   Suppliers and supplier sites are predefined in Oracle Payables.
7. Enter an allocation percentage for each shipping organization. Allocation percentage includes the number of planned orders issued to the part for the
      entire the planning horizon. The total allocation may not exceed 100.
If the allocation percentage for all the shipping organizations included within a range of effectivity dates equals 100, Planning Active is checked. If the sourcing rule is not planning active, the planning process will not use the rule to assign planned orders.
Note:  It does not allow setting the allocation percentage to less than or greater than 100 for sourcing rules that are already assigned in assignment sets.
8. Enter a numeric rank value to prioritize each sourcing type.
If two sources are listed with the same allocation percentage, planned orders are sourced from the highest rank first.
9. Select a shipping method, such as FEDEX, UPS, or rail.
10. Save the form.

Additional Information on Sourcing Rules:
1. Instructions for copy shipping organizations from an existing sourcing rule:
This feature allows the creation of long, previously defined lists of shipping organizations without manual entry.
1) Select a sourcing type to specify whether the item is made, bought, or internally transferred.
2) Choose Copy Shipping Orgs From.
3) In the Find window, select a sourcing rule that includes the shipping organizations to duplicate in this new sourcing rule.
4) Choose OK.
2.  To purge a sourcing rule:
1)  Select a sourcing rule name.
2). Choose Purge.

 
Section 4:  Assigning Sourcing Rules and Bills of Distribution
Once sourcing rules and/or bills of distribution have been created, these must be assigned  to particular items and/or organizations. These assignments are grouped together in assignment sets.  This is where various sourcing strategies define a particular supply chain network.
Each assignment set represents a selection of organizations and/or items to be planned. To influence the planning process, an assignment set must be included in these plan options.
In an assignment set assign  sourcing rules and bills of distribution at different levels as follows:
          1) An item across all organizations
          2) A single item in an inventory organization
          3) All items in an inventory organization
          4) Categories of items
          5) Categories of items in an inventory organization
          6) All organizations
These levels allow flexibility to assign a replenishment rule to as many or as few items as possible.  For example, a category of items could be defined as packaging material, and a sourcing rule that identifies the suppliers could be assigned.
To assign a sourcing rule or bill of distribution:
1.  Navigate to the Sourcing Rule/Bill of Distribution Assignments window.
2. Enter an assignment set name and description.
Note:  The assignment specified in profile option MRP: Default Sourcing Assignment Set is the only one used by Oracle Purchasing for its processing.
3. Select an Assigned To type  See: Assignments Hierarchy.
Note:  A sourcing rule or bill of distribution can be assigned to a category only if the the profile option, MRP:Sourcing Rule Category Set has been updated.
4. Enter an organization name, if the Assigned To type requires one.
Note:   Customers modeled as organizations to a global sourcing rule can not be assigned.
5. Enter the name of the customer to which to assign a sourcing rule or bill of distribution.
6. Enter the specific site to which to assign a sourcing rule or bill of distribution.
7. Enter an Item/Category if you selected Item or Item-Org as the Assign To type.
8. Enter the sourcing rule or bill of distribution as the Type.
9. Enter the name of the sourcing rule or bill of distribution.
10. Save the form.

To purge a sourcing rule or bill of distribution:
1. Select an assignment set name.
2. Choose Purge.

 
Section 5:  MRP Default Sourcing Assignment Set
A profile option must be set in order for Oracle Purchasing to recognize and find which group of Sourcing Rules to use.   This profile option is the MRP: Default Sourcing Assignment Set and must be set with the correct Assignment Set value to be used.
Note:  Oracle Purchasing will only recognize one Assignment Set from this profile option.   The hierarchy for how Oracle chooses which value of a profile is as follows.   First the system looks at the User value, if this is null it looks at the Responsibility (that the user is using) value.  If this is null it looks at the Application value.   And lastly, if all three of these are null Oracle will look at the value of the profile option at the Site level.
 
 
 
Section 6:  Troubleshooting
If encountering any problems with automatic sourcing after it is set up, the following solutions may help.
Problem #1:  Purchasing is not using the sourcing rules defined in the Sourcing Rule/Bill of Distribution window
Make sure that you have done both of the following:
1.  Assigned the sourcing rules to an assignment set in the Sourcing Rule/Bill of Distribution Assignments window.
2.  Make sure that the Assignment Set name in this window matches the assignment set name in the profile option MRP: Default Sourcing Assignment Set.
As stated above, Purchasing can use only one assignment set at a time.   If Purchasing is not using the sourcing rules that as expected, they may belong to another assignment set.  For the MRP: Default Sourcing Assignment Set profile option, choose the assignment set name to which the sourcing rules which are expected to be used are assigned in the Sourcing Rule/Bill of Distribution Assignments window.
Problem #2:  Source document information is not defaulting onto my requisition or purchase order
Assuming the sourcing rules have been defined and assigned correctly, the problem could be one of the following:
1. Make sure the instructions in the following two sections have been followed:
1) Defining the Supplier and Commodity/Item Combination and
2)  Defining the Supplier/Item Attributes. Make sure that, for the item, the supplier and site in the Approved Supplier List matches the supplier and site in the sourcing rule.
2.  The Approved Supplier List entry may be local.  In the Approved Supplier List window, choose the Record Details tabbed region and note the Global field.  If the Global field is set to No, then the sourcing information specified for the item is used only locally, by the current organization or the organization that originally created the Approved Supplier List entry, and other organizations will not receive the source document information.   Ensure that the correct organization is being used when entering in the requisition.   Likewise, if there are two Approved Supplier List entries for an item, and one is local and the other is global, the local entry takes precedence for your organization.3.  Sometimes only one Global entry is seen in the Approved Supplier List window, but local versions of that entry in the Supplier-Item Attributes window have been created.  Recall that a local entry takes precedence over a global one and is used only by the organization in which it was created.  In the Approved Supplier List window, choose the Attributes button.  If local entries exist, the Create Local button will be dimmed. 


Wednesday, 26 June 2013

Oracle note to Inventory-SLA-GL Reconcilation

Details
This white paper aims at providing troubleshooting guide for the Inventory period closing activities and Gl reconciliation issues which is faced after the Inventory period is closed.
Intention of the white paper is to provide the effective guidelines and some handful scripts which can be used in checking various aspects of period closing as well as Reconciliation between sub-ledger and General Ledger (GL).
Following Pre-checks are important before attempting to reconcile the value at Sub-ledger for a given account code combination with the value of that account code combination reflecting in GL.

The scripts mentioned are not applicable for Periodic Costing System (PAC).
This is applicable for R12 using Standard and/or Average Costing.
Suitable for Oracle seeded Account Derivation Rules (ADR).

NOTE : The script contained in the attachment section is being updated periodically. Please download it from Metalink every time you need to run it in order to have the latest version.
Summary
 1. All Material/WIP transactions should be costed.
This is the primary step in the process of Inventory Period close and ultimately in Reconciliation. If there are any errors in the respective tables or Cost worker errors those need to be resolved. To check for these errors, diagnostics scripts are available,
Please refer to the  Note 603657.1 -Error/Uncosted/Pending Material Transactions
2. Create Accounting-Cost Management request.
After checking all the transactions for the concerned Inventory period are costed, Create Accounting-Cost Management concurrent request should be run to transfer all the transactions to GL.
This concurrent request should get successfully completed.
For request parameters and the explanations for each of the parameters and the associated script :
Please refer to the Note 755943.1 -Transfer Inventory Transaction Into General Ledger through Subledger Accounting SLA Flow
 3. Inventory Period should be closed.
Once Create Accounting-Cost Management concurrent request is completed successfully, Inventory period should be closed. While closing the Inventory Period, a report will get triggered called as "Period Close Reconciliation Report".
This concurrent program and report is used to create summarized transaction records. It displays the differences between accounted value and inventory in the Discrepancy column.
Following are key columns in Period Close Reconciliation Report:
Accounted Value: The valuation when calculated using previous adjusted summarization data + distribution information from MTA (Mtl_transaction_accounts).
On-hand Value: The valuation when calculated using current values from MOQ - quantities and costs from MMT. (Mtl_material_transactions).
Discrepancy: The difference between Account Value and On-hand Value.
Once the Period Close Reconciliation report completes normal, please check the report values and especially Discrepancy column. If there is a difference, this is the first indicator of mismatch in the Sub-ledger and Gl. In R12, you can run the Period Close reconciliation report for the closed period as well.
For more discussions on the Period Close Reconciliation Report,
Please refer to the  Note 295182.1 -Period Close Reconciliation Report (CSTRPCRE)
 4.BackdatedTransactions.
Timely closing of Inventory period will prevent the backdated transactions which will have its impact on Inventory valuation at the sub-ledger level as well as in the Gl. This is the primary factor which creates a difference in Sub-ledger and Gl.
Please refer to the Note 361597.1<strong-How Backdated Transactions Impact on Inventory Reports and GL Value
 5. Mismatch like MMT-MOQD and MMT-CQL.
There is mismatch in quantities in different sub-ledger tables like Mtl_material_transactions (MMT) and Mtl_on_hand_quantities (MOQ). This can also lead to sub-ledger level difference and discrepancy in Period close reconciliation report.
For MMT-MOQ mismatch and the resolution thereof
Please refer to the Note  279205.1 -Find Mismatch Between MTL_MATERIAL_TRANSACTIONS (MMT) and MTL_ONHAND_QUANTITIES_DETAIL
 In average costing Environment, there can be MMT-CQL mismatch,  
Please refer to the Note  378348.1 which provides txn.sql to identify MMT-CQL mismatch.
 6. No negative ledger Id's exists at the legal entity level.
This is the next level check in the SLA. If there are negative ledger id’s for the gl batches then these needs to be rectified and resolved. There is a diagnostics and Data fix which is available to resolve.
Please refer to the Note 883557.1 -How To Avoid and Fix Corruption in Data Transfer from SLA - Negative Ledger_ID, Not Reached GL, Duplicate in GL
 7. Sub-ledger Period Close Exception Report.
From Cost Management SLA Responsibility, Sub-ledger period close exception report states the entities/events which are errored while transferring to Gl. This report is very handy so to know the first level check whether there are any transactions not transferred to Gl.
8. From General Ledger(GL) responsibility, Account Analysis Sub-Ledger report 180 Characters report will be useful to get the data for the account code combination.
9. Recon_diag.sql
After completing the above pre-checks, If there is difference in the GL and Inventory, final step is to start the reconciliation process.
Recon_diag.sql is attatched.
In Recon_diag.sql there are 5 input values which need to be provided.
1)     Org code= The inventory organization for which reconciliation activity is going on.
2)      Reference account = the account code combination in question for which Gl and sub-ledger values are not matching.
3)      from date = The inventory period, or date in which value mismatch is there.
4)     to date= The inventory period, or date in which value mismatch is there.
5)     Ledger_id = Enter the Ledger_id of the default ledger.  This can be obtained from the gl_ledgers  table.

(For 3 & 4 , pl run the script on monthly basis. Ideally if you are reconciling the April-2012 period, then condition would be
from date=01-apr-2012 to date=30-apr-2012)

After running the Recon_diag.sql two text files will be created in users default locations.
File names will be SLA_GL_RECON.TXT and INV_SLA_RECON.TXT
 The output will gather data from the Sub-ledger tables like Mtl_transaction_accounts and Wip_transaction_accounts in INV_SLA_RECON.TXT.

Data from SLA and GL tables like Xla_transaction_entities, Xla_Ae_Headers and Xla_ae_lines   and Gl_je_lines, GL_Je_batches, Gl_import_references is gathered in SLA_GL_RECON.TXT.