Query to find Details Of OM and Install Base

SELECT okh.contract_number         "Contract Number",cl.ATTRIBUTE6,
       okh.scs_code                "Code",
       ooh.order_number            "Order Number",
       TO_NUMBER(sl.line_number)   "SL Line",
       sl.lse_id                   "SL LSE ID",
       TO_NUMBER(cl.line_number)   "CL Line",
       cl.lse_id                   "CL LSE ID",
       TO_CHAR(cl.id)              "CL ID",
       msi.inventory_item_id,
       msi.segment1                "Item Number",
       cii.serial_number           "Serial Number",
       okh.start_date              "Contract Start Date",
       sl.start_date               "Line Start Date",
       cl.start_date               "Subline Start Date",
       cii.*
  FROM apps.okc_k_headers_all_b   okh,
       apps.okc_k_lines_b         sl,
       apps.okc_k_lines_b         cl,
       apps.okc_k_items           oki,
       apps.okc_k_rel_objs        rel,
       apps.csi_item_instances    cii,
       apps.mtl_system_items_b    msi,
       apps.oe_order_headers_all  ooh
 WHERE 1=1
   AND okh.id                   =  sl.chr_id
   AND cl.cle_id                =  sl.id
   AND sl.cle_id IS NULL
   AND cl.id                    =  oki.cle_id(+)
   AND oki.object1_id1          =  cii.instance_id
   AND cii.inventory_item_id    =  msi.inventory_item_id
   AND okh.inv_organization_id  =  msi.organization_id
   AND rel.chr_id               =  okh.id
   AND rel.jtot_object1_code    =  'OKX_ORDERHEAD'
   AND rel.object1_id1          =  ooh.header_id
   AND oki.object1_id2          =  '#'
   AND okh.contract_number = 'US000016348'
  -- AND cii.serial_number = 'FM1407CB00D'
 -- AND TO_CHAR(cl.id) = '97803583007615608399812980983947089161'
   -- AND okh.cust_po_number       =  ooh.cust_po_number
   -- AND okh.ship_to_site_use_id  =  ooh.ship_to_org_id
   /* if subline start date is earlier than line start date */
   -- AND sl.start_date > cl.start_date
   -- AND sl.line_number      = 1
 ORDER BY okh.contract_number,
       TO_NUMBER(sl.line_number),
       TO_NUMBER(cl.line_number);

3133: Activity instance 'FULFILL_LINE' is not a notified activity Error in OM / Progress Order lines STUCK in Fulfillment Set

If some of order lines in a fulfillment set are closed/cancelled (because of data corruption) while other lines of same set are still open then this is true case of data corruption .In such case open lines will got stuck in AWAITING FULFILLMENT.( Reason - Purpose of the Fulfillmet set is to fulfill all the lines in the set together, but if any of line is closed/cancelled then rest all lines will stuck at workflow activity FULFILL -NOTIFED or ERROR).

Here is issue description:

Order having 2 Line - Line1 and Line2 and Both lines are assigned to Fullfillment Ser "1".
But some how Line1 got closed, due this Even though we comepleted all the shipping process on Line, Still line2 is in Shipped Status and workflow erroring out with below error.


3133: Activity instance 'FULFILL_LINE' is not a notified activity

Solution - Remove the Fulfillment Set from Line2 and Retry the workflow activity.

FYI:

SELECT * FROM    APPS.oe_line_sets ls, APPS.oe_sets s WHERE  1=1
 --ls.line_id    = 402039
AND     ls.set_id     = s.set_id
AND s.set_id = 1636905;

***************************************************************
For Online And Classroom Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

How to Add Change Reason Codes in Oracle Order Management

1. Go To

Order Management Resp > Setup -> Quick Codes->Order Management

Search with Type as > CANCEL_CODE

Test Reason_1
Test Reason_2

2. Go To Sales Order Line Level and now we can see the above two newly added Reasons. 

Journal Approval Process in R12

Prerequisite SETUPS:
===================

1. Enable journal approval in the primary  ledger:

GL Responsibility > Setups > Financials >  Accounting Setup Manager > Accounting Setups

Query with Ledger Name and click on "Update Accounting Options"
Click on "Update" of corresponding ledger and  then Click on "Ledger Options" and Enable Check Box "Enable Journal Approvals".

2. Enable Journal Approvals at Journal Source Level:

3. Setup Journal Approvals Limit for Employees.

Just for demo purpose i am using below limits,

Once Employee Stock, Ms. Pat enter journal, it will go approvals as per below limits.

4. Setup Profile Options:

Profile Name - Journals: Find Approver Method.

Go Up Management Chain: The journal batch moves up the approval hierarchy until it has  been  approved  by  an  approver  whose  authorization  limit  is  sufficient  to  allow approval. The journal batch must be approved by all intermediate approvers as well.

Go  Direct: The  journal  batch  is  sent  directly  to  the  first  approver  in  the  approval hierarchy  who  has  an  authorization  limit  high  enough  to  allow  approval.  The  preparer's direct manager receives a courtesy notice.

One Stop Then Go Direct: The journal batch is first sent to the preparer's manager for approval.  If  further  approvals  are  required, the  journal  batch  is  sent  directly  to  the  first approver  in  the  approval  hierarchy  who  has  an  authorization  limit  high  enough  to  allow approval.

Profile Option - Journals: Allow Preparer Approval  And Value - No

Process:

Now Create Journal and Approve Accordingly based on Approval Limit.

***************************************************************
For Online And Classroom Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

COGS and Deferred COGS In Oracle R12

The deferred COGS account is the new feature introduced in Release 12. The key fundamental behind the feature is that the COGS is now directly matched to the Revenue. In simple terms, this means, COGS for an order line will be recognized only if the revenue is recognized for that line making sure that the revenue and COGS are posted in the same month. Matching percentage is also taken care which ensures that revenue and cost are always in sync.

SETUP > Inventory Resp >  Setup > Organization > Parameters > Other Accounts

Here Enter Deferred COGS Account

STEP:
==========

1. Create Sales Order and do Pick Confirm
2. Complete Shipping Confirm Process and run interface trip stop Program.

 Below two Accounts Will hit.

Cr      > Inventory Valuation accoun
Dr      > Deferred COGS Accoun

Go To Inventory responsibility and Review the Accounts in Material Transactions form.

3. Run Workflow Background Process and AutoInvoice Master Program to Create Invoice in AR.

Now AR invoice Got Created, But Revenue not yet Recognized.

Go To AR Resp > Control > Accounting And Search with Sales Order Number.

Revenue Not Yet Recognized.

4. Recognize the revenue in AR.

NAV >  Accounts Receivables Resp > Control > Run "Revenue Recognition" Program.

After running the program, again check for Revenue.

5. Now we have to accept the Revenue.

Click on "Manage Revenue".

6. Run Below Set Of Concurrent Programs.

Record Order Management Transactions: records new sales order transaction activity such as shipments and RMA returns in Oracle Order Management.

Collect Revenue Recognition Information: determines the percentage of recognized or earned revenue related to invoiced sales order shipment lines in Oracle Receivables.

Generate COGS Recognition Events: creates and costs COGS recognition events for new sales order shipments/returns and changes in revenue recognition and credits for invoiced sales order shipment lines.

After Running ABove Programs, Verify "Material Transaction" Form. A non-physical transaction has been generated Transaction Type= COGS Recognition

Click on "Distributions" Button and verify Accounts.

Deffered COGS : Credit
COGS : Debit.

**********************************************************************************
For Online Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639
 

R12 Inventory Month End Period End Process

• Generally, We should open and close periods for each separate inventory organization independently. 

• Inventory and work in process transactions automatically create accounting entries. All accounting entries have transaction dates that belong in one accounting period. We can report and reconcile Our transaction activity to an accounting period and General Ledger. We can transfer summary or detail transactions to General Ledger. We can transfer these entries to General Ledger when We close the period or perform interim transfers.

• When We transfer to General Ledger, a general ledger (GL) batch ID and organization code are sent with the transferred entries. We can review and report the GL batch number in General Ledger and request Inventory and Work in Process reports by the same batch number. We can also view general ledger transfers in Inventory and drill down by GL batch ID into the inventory and WIP accounting distributions.

• The period close process permanently closes an open period. We can no longer charge transactions to a closed period. Once We close a period, it cannot be reopened. As a precaution, We can do a GL transfer without closing the period.

• If inventory organization’s parameter for Transfer to GL is None, accounting entries are not transferred to the General Ledger. 

 Oracle Inventory uses accounting periods to group material transactions and work in process transactions for accounting purposes. So normally these deatils are grouped for a month and the transaction details are posted to GL.

An automatic general ledger transfer is processed when We close an accounting period.

Pending transactions:

Before closing the inventory Period make sure all pending transactions are resolved. In pending transaction form we can see all pending transactions or while trying to close the period, automatically system will display all pending transaction information. In pending transactions we can find number of unprocessed material transactions, uncosted material transactions, and pending WIP costing transactions existing in this period. These must be resolved before the period is closed.

Resolution Recommended  on  pending receiving transactions,pending material transactions, and pending shop floor move transactions existing in this period.

Inventory Resp --> Transactions --> Pending Transactions 

Close a open/error status period :


Inventory Resp  --> Accounting Close Cycle --> Inventory Accounting Periods

***************************************************************
For Online And Classroom Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Inventory Issues -Pending Transaction Issues

Issues 1 - Transaction Struck as pending for a perticular Period ( NOV - 14 ) and because of this we are unable to close NOV-14 Period.

Reason 1 - These are few interorg transfer orders From M1 to M2 org and initially we missed out standard cost of item in price and corresponding transactions got failed, So now we added Items to the price list, but the destination org M2 is closed for Nov-14 Period, so they failed again.

Solution - Change the transaction date to Dec 1 to push the transactions.

Issue 2 -  We have few uncosted transactions with following error in material transaction form.

CST_INVALID_ACCT_ALIAS  - The Account Alias Is Not Defined For This Org

Reason - Few account alias issue transactions having transaction_source_id as NULL in mtl_material_transactions Tables. To find the transaction details which are having transaction_source_id as NULL please user below Query.

SELECT TRANSACTION_ID, TRANSACTION_SOURCE_ID
FROM   MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_SOURCE_TYPE_ID = 6  --  Source Type = Account alias
AND ((TRANSACTION_SOURCE_ID IS NULL) OR (TRANSACTION_SOURCE_ID is not null
AND NOT EXISTS
(SELECT 1 FROM MTL_GENERIC_DISPOSITIONS
WHERE DISPOSITION_ID = TRANSACTION_SOURCE_ID)));

NOTE - The table MTL_GENERIC_DISPOSITIONS holds the list of account aliases

Solution - First test the solution in TEST Instance

1st Run the below script to to take back up.

CREATE TABLE TESTTABLE123 AS ( SELECT * FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE COSTED_FLAG IN ('N','E')
AND TRANSACTION_SOURCE_TYPE_ID = 6 
AND ORGANIZATION_ID =  999  -- Your Inventory Org ID
AND TRANSACTION_SOURCE_ID IS NULL);

2. Run the below update script.

UPDATE mtl_material_transactions mmt
SET transaction_source_id = (SELECT DISPOSITION_ID FROM MTL_GENERIC_DISPOSITIONS
                             WHERE organization_id = mmt.organization_id
                             AND   description = '&&alias_desc'),
costed_flag = 'N',
error_code = NULL,
error_explanation = NULL,
transaction_group_id = NULL,
last_updated_by  = -6352950
WHERE costed_flag in ('N','E')
AND transaction_source_type_id = 6  
AND organization_id =  &org_id  --  -- Your Inventory Org ID
AND transaction_source_id IS NULL
AND exists (SELECT DISPOSITION_ID FROM MTL_GENERIC_DISPOSITIONS
             WHERE organization_id = mmt.organization_id
               AND description = '&alias_desc');

***************************************************************
For Online And Classroom Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639