Generate COGS Recognition Events -- Error in CST_RevenueCogsMatch_PVT . Match_RevenueCOGS ( 50 )

Issue -- While Running "Generate COGS Recognition Events" , Program error outwith below error.

program_exception EXCEPTION IN Create_CogsRecognitionEvents
PROGRAM_EXCEPTION IN Match_RevenueCOGS at (50):
Failed to insert COGS Recognition Events into MMT due to a failed validation during bulk insertion.
Invalid item
Inventory item ID is invalid or does not have the flags enabled correctly to be processed by material processor
Error in CST_RevenueCogsMatch_PVT . Match_RevenueCOGS ( 50 )


Reason and solution.-- Few items are not defined as inventory items. ( In Our case Initially we defined item as Inventory item and created Sales Order and later user disabled the inventory flag. Due to this while running COGS Program we are getting below error ) . To find out the item details, Use below Query and enable  INVENTORY_ITEM_FLAG as 'Y" and then run "Generate COGS Recognition Events" Program.

SELECT organization_id,
MSI.SEGMENT1 ITEM_NUMBER,
MSI.DESCRIPTION,
MSI.INVENTORY_ITEM_FLAG
FROM apps.MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_FLAG = 'N'
AND MSI.INVENTORY_ITEM_ID IN (SELECT CRTT.INVENTORY_ITEM_ID FROM apps.CST_REVENUE_COGS_MATCH_LINES CRTT
WHERE CRTT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND CRTT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EXISTS (SELECT 1 FROM apps.cst_revenue_recognition_lines crrl
   WHERE crrl.revenue_om_line_id=crtt.revenue_om_line_id
    AND potentially_unmatched_flag IS NOT null)) ORDER BY MSI.SEGMENT1;

For more details, Please refer Metalink Note - 971355.1

Service Contract Not Imported From Sales Order

Issue -- Service contract Line is not created for one salesorder line.

Reason -- In My case user didnt populated Service Start date and End Dare @ Sales order line Level.

Solution -- Populate Servoice Start and End Dates and Reprocess the the record.

While searching for above issue,i came across below point from one of the blog.

When a Sales Order is created in Order Management which includes a Service line, when the order is booked and shipped a Service Contract is created (or amended depending on the order).

In some cases, the service contract is not created.  In this blog post I try to give some pointers to help you debug this integration point.  Note that in R12 there was a change in how this integration point works, here I refer only to R12 functionality.  If you are on an earlier EBS release please see Doc ID 463912.1 for troubleshooting steps.

Is your order interfaced?
Make sure that your order has been successfully shipped and the line has been interfaced.  Sometimes the order can be deferred until a future date.  Check the OM workflow to see if that's happened.  You'll see the deferred date in the OM Order Line (OEOL) 'Fulfill' node.  If it's not deferred to a future date but is not closed, then running the Workflow Background Process is required to progress the workflow.

Is the instance created in IB?
Make sure that the install base instance has been created.  Search in IB using the sales order number and make sure that the item with the linked service line is created as an instance.
Remember that the inventory item must be set up with 'Track in Installed Base' and 'Service Allowed' set to yes (ticked) in the item master.

What's the status of the interfaced record?
When the sales order is processed, a record is created in the OKS_REPROCESSING table.  Check the status of the this record in Service Contracts > Reprocess Order.
Query using the sales order number. This should return the sales order with a status of NEW (if the record is available for processing) or ERROR (if the record has been processed but failed to create the service contract).
If the status is NEW then all you have to do is select it for processing and then submit the Service Contracts Order Processing concurrent program.
If the status is ERROR then the error message displayed should give you an indication of why the contract creation has failed.

If all fails and you can't find the order in the Reprocess Order form, the query below will return order numbers with service lines which have not created a contract and are not in OKS_REPROCESSING table.  

    select ord.order_number "Order_Number",
    ord.header_id "Order_Id",
    line.line_number,
    instance_number,
    line.service_reference_type_code,
    line.service_reference_line_id "Reference ID",
    line_id "Service_Line_ID"
    from csi_item_instances ib,
    oe_order_lines_all line,
    oe_order_headers_all ord
    where not ib.last_oe_order_line_id is null
    and ib.last_oe_order_line_id = line.service_reference_line_id
    and ord.header_id = line.header_id
    and line.flow_status_code = 'CLOSED'
    and line.service_reference_type_code = 'ORDER'
    and line.line_id not in
    (select order_line_id from oks_reprocessing);

You can then use this information to implement a workaround such as creating the service contract manually OR you can log a service request with Oracle Support to get a datafix which will insert the missing order lines into OKS_REPROCESSING.

The following notes also have useful information about this integration point:
Integration Troubleshooting: Order Management to Service Contracts (Doc ID 1455545.1)
R12 How to Process Order Lines from Order Management to Create Service Contracts (Doc ID 785760.1)

How to Find Requisition is Catalog or Non-Catalog

By using table po_requisition_lines_all we can find out Whether Requisition is Catalog or Non-Catalog

Field CATALOG_TYPE in po_requisition_lines_all should display the value CATALOG or NONCATALOG, By Using this we can decide the Requisition type - Catalog / Noncatalog

Uncosted Material Transactions

How to resolve Month End Uncosted Materials Transactions.

1. Check whether the cost manager is active or Not , Go To: Inventory responsibility > Setup > Interface Managers, Cost Manager must be active. If not, launch Manager from Tools > Launch Manager.

2. Check whether there are records that are un-costed in error in Interface Tables.

SELECT COUNT(*) FROM apps.mtl_material_transactions where costed_flag = 'E';

3. Verify Doc ID 1069492.1 -- How to Resolve Period Close Pending Transaction R12

BOM Query

SELECT msi.segment1 Item,msi.organization_id, msi.description item_Desc, bom.alternate_bom_designator Alternate_BOM,
msil.segment1 Component_Item, msil.description Component_Item_Desc,
BIC.component_quantity
FROM apps.MTL_SYSTEM_ITEMS MSI ,
apps.BOM_BILL_OF_MATERIALS BOM ,
apps.BOM_INVENTORY_COMPONENTS BIC,
apps.MTL_SYSTEM_ITEMS MSIL
WHERE BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND bom.bill_sequence_id = bic.bill_sequence_id
AND nvl(bic.disable_date, sysdate) >= SYSDATE
AND BIC.component_ITEM_ID = MSIL.INVENTORY_ITEM_ID
AND Bom.ORGANIZATION_ID = MSIL.ORGANIZATION_ID
AND msi.segment1 = 'BOM_ITEM_TEST'
ORDER BY msi.segment1, bom.alternate_bom_designator, bic.item_num;