IB Error -- Stale Data

Issue -- While Transferring the Item Instance from one account to another account , we received below error.

Error: Stale Data

The requested page contains stale data. This error could have been caused through the use of the browser's navigation buttons (the browser Back button, for example).

Cause:
The view object OwnerPartyAM.LocationRN542_LocationRN_oracle_apps_csi_instance_location_server_LocationAM.LocationPartySiteVO contained no record. The displayed records may have been deleted, or the current record for the view object may not have been properly initialized.

Solution -- Please apply the patch -- 19821318:R12.CSI.B  and test the issue in any teset instance and then move to PROD.

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;

IB Issue - ORA-00001: unique constraint (OKS.OKS_K_ORDER_DETAILS_U2) violated in Package OKS_COD_PVT Procedure B_insert_row

Issue -- IB Not Created in oracle, record got failed with below error.


ORA-00001: unique constraint (OKS.OKS_K_ORDER_DETAILS_U2) violated in Package OKS_COD_PVT Procedure B_insert_row

Solution : Go To Oracle Installed Base Admin > Transaction Errors Processing --> Query with Sale Order / Item

Select Process Flag --> Tools --> Resubmit Interface Process   -- To Reprocess the record.

Responsibilities Assigned to User.

-- Query To find Responsibilities Assigned to User.

    SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",     
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM apps.fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('SLAKKIREDDY')  -- <change it>
   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
 ORDER BY frt.responsibility_name;

Not able to cancel internal requisition

Issue - I am unable to cancel the internal requisition even tough there is no ISO.

Reason - There is internal sales order record in Order Import interface.

As per functionality, system  will not allow m to Cancel an internal requisition line when there is pending sales order in order import interface.

Customer Query

-- Customer Contact Details :

 select account_number "Account Number"
     , obj.party_name "Customer Name"
     , sub.party_name "Contact Name"
     , hcp.contact_point_type || ': ' ||
       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address
                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number
                                    , 'WEB'  , hcp.url
                                    , 'Unknow contact Point Type ' || hcp.contact_point_type
             ) "How to Contact"
  from apps.hz_cust_accounts  hca
     , apps.hz_parties        obj
     , apps.hz_relationships  rel
     , apps.hz_contact_points hcp
     , apps.hz_parties        sub
 where hca.party_id           = rel.object_id
   and hca.party_id           = obj.party_id
   and rel.subject_id         = sub.party_id
   and rel.relationship_type  = 'CONTACT'
   and rel.directional_flag   = 'F'
   and rel.party_id           = hcp.owner_table_id
   and hcp.owner_table_name   = 'HZ_PARTIES'
   AND obj.party_name LIKE 'Oxford Unive%';

============================ Customer ==================================

SELECT    hl.orig_system_reference,hl.LAST_UPDATE_DATE
         ,hl.country
         ,hl.address1
         ,hl.address2
         ,hl.address3
         ,hl.address4
         ,hl.city
         ,hl.postal_code
         ,hl.state
         ,hl.province
         ,hl.county
         ,hl.content_source_type
         ,hl.actual_content_source actual_content_source
         ,hps.party_site_number
         ,hps.identifying_address_flag
         ,hps.status
         ,hps.party_site_name
         ,hps.created_by_module
         ,hps.actual_content_source site_actual_content_source
         ,hcasa.orig_system_reference site_orig_system_reference
         ,hcasa.status         acct_site_status
         ,hp.party_number
         ,hca.account_number
         ,hou.NAME operting_unit_name
         ,bill_to_flag
   FROM  apps.hz_locations        hl
        ,apps.hz_party_sites      hps
        ,apps.hz_cust_acct_sites_all  hcasa
        ,apps.hz_parties     hp
        ,apps.hz_cust_accounts   hca
        ,apps.hr_operating_units hou
   WHERE hcasa.party_site_id     = hps.party_site_id
   AND   hps.location_id         = hl.location_id
   AND   hp.party_id             = hca.party_id
   AND   hp.party_id             = hps.party_id
   AND   hca.cust_account_id     = hcasa.cust_account_id
   AND   hcasa.org_id            = hou.organization_id
   AND   hp.party_type           ='ORGANIZATION'
   AND   hps.status              = 'A'
   AND   hcasa.status            = 'A'
   AND   hp.status               = 'A'
   AND   hca.status              = 'A'
  -- AND hl.address1  like '8900%Uni%';
  -- AND   hp.party_number = '14613899';
  AND hp.party_name = 'Stanford University';
 

============================ Customer ==================================
 
   SELECT
       ----------------------------------------------------------
       -- Party Information
       ----------------------------------------------------------
       hp.party_number                      "Registry ID",
       hp.party_name                        "Party Name",
       hp.party_type                        "Party Type",
       DECODE(hp.status,
              'A', 'Active',
              'I', 'Inactive',
              hp.status)                    "Party Status",
       ----------------------------------------------------------
       -- Account Information
       ----------------------------------------------------------
       hca.account_number                   "Account Number",
       DECODE(hca.status,
              'A', 'Active',
              'I', 'Inactive',
              hca.status)                   "Account Status",
       hca.account_name                     "Account Description",
       hca.customer_class_code              "Classification",
       DECODE(hca.customer_type,
              'R', 'External',
              'I', 'Internal',
              hca.customer_type)            "Account Type",
       ----------------------------------------------------------
       -- Site Information
       ----------------------------------------------------------
       hps.party_site_number                "Customer Site Number",
       DECODE(hcas.status,
              'A', 'Active',
              'Inactive')                   "Site Status",
       DECODE(hcas.bill_to_flag,
              'P', 'Primary',
              'Y', 'Yes',
              hcas.bill_to_flag)            "Bill To Flag",
       DECODE(hcas.ship_to_flag,
              'P', 'Primary',
              'Y', 'Yes',
              hcas.ship_to_flag)            "Ship To Flag",
       hcas.cust_acct_site_id               "Customer Acct Site ID",
       ----------------------------------------------------------
       -- Address Information
       ----------------------------------------------------------
       hl.address1                          "Address1",
       hl.address2                          "Address2",
       hl.address3                          "Address3",
       hl.address4                          "Address4",
       hl.city                              "City",
       hl.state                             "State",
       hl.postal_code                       "Zip Code",
       ter.name                             "Territory",

       ----------------------------------------------------------
       -- Collector Information
       ----------------------------------------------------------
       col.name                             "Collector Name",
       ----------------------------------------------------------
       -- Account Profile Information
       ----------------------------------------------------------
       hcp.credit_checking                  "Credit Check Flag",
       hcp.credit_hold                      "Credit Hold Flag",
       hcpa.auto_rec_min_receipt_amount     "Min Receipt Amount",
       hcpa.overall_credit_limit            "Credit Limit",
       hcpa.trx_credit_limit                "Order Credit Limit",
       ----------------------------------------------------------
       -- Attachment Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.fnd_documents_vl doc,
                   apps.fnd_lobs         blo,
                   apps.fnd_attached_documents att
             WHERE doc.media_id = blo.file_id
               AND doc.document_id = att.document_id
               AND att.entity_name = 'AR_CUSTOMERS'
               AND att.pk1_value   = hca.cust_account_id
               AND ROWNUM = 1), 'N'
       ) "Attachment Flag",
       ----------------------------------------------------------
       -- Party Relationship Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_cust_acct_relate_all hzcar
             WHERE hzcar.cust_account_id = hca.cust_account_id
               AND hzcar.relationship_type = 'ALL'
               AND ROWNUM = 1), 'N'
       ) "Party Relationship Flag",
       ----------------------------------------------------------
       -- Account Relationship Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_cust_acct_relate_all hzcar
             WHERE hzcar.cust_account_id = hca.cust_account_id
               AND ROWNUM = 1), 'N'
       ) "Account Relationship Flag",
       ----------------------------------------------------------
       -- Party Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_parties hp2
             WHERE 1=1
               AND hp2.party_id = hp.party_id
               AND (
                    hp2.url IS NOT NULL OR
                    -- LENGTH(TRIM(hp.email_address)) > 5
                    INSTR(hp2.email_address, '@') > 0 OR
                    hp2.primary_phone_purpose IS NOT NULL
                    )
           ), 'N'
       ) "Party Contact Flag",
       ----------------------------------------------------------
       -- Account Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_contact_points
             WHERE status = 'A'
               AND owner_table_id =
                   (SELECT hcar.party_id
                      FROM apps.hz_cust_account_roles   hcar,
                           apps.ar_contacts_v           acv
                     WHERE hcar.cust_account_id   = hca.cust_account_id
                       AND hcar.cust_account_role_id = acv.contact_id
                       AND hcar.cust_acct_site_id IS NULL  -- look for account level only
                       AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)
                    )
               AND ROWNUM = 1), 'N'
       ) "Account Contact Flag",
       ----------------------------------------------------------
       -- Site Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_contact_points
             WHERE status = 'A'
               AND owner_table_id =
                   (
                      SELECT hcar.party_id
                        FROM apps.hz_cust_account_roles   hcar,
                             apps.ar_contacts_v           acv
                       WHERE hcar.cust_acct_site_id     =  hcas.cust_acct_site_id
                         AND hcar.cust_account_role_id  =  acv.contact_id
                         AND ROWNUM = 1  -- add this row to show inactive sites (i.e. with no site id)
                   )
               AND ROWNUM = 1), 'N'      -- any contact (email, phone, fax) would suffice this condition
       ) "Site Contact Flag"
  FROM
       apps.hz_parties              hp,
       apps.hz_party_sites          hps,
       apps.hz_cust_accounts_all    hca,
       apps.hz_cust_acct_sites_all  hcas,
       apps.hz_customer_profiles    hcp,
       apps.hz_cust_profile_amts    hcpa,
       apps.hz_locations            hl,
       apps.ra_territories          ter,
       apps.ar_collectors           col
 WHERE
       1=1
   AND party_name like  'Florida Power%'
   AND hp.party_id            =  hca.party_id
   AND hca.cust_account_id    =  hcas.cust_account_id(+)
   AND hps.party_site_id(+)   =  hcas.party_site_id
   AND hp.party_id            =  hcp.party_id 
   AND hca.cust_account_id    =  hcp.cust_account_id
   AND hps.location_id        =  hl.location_id(+)
   AND col.collector_id       =  hcp.collector_id
   AND hcas.territory_id      =  ter.territory_id(+)
   AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id
 --  AND hp.party_type          = 'ORGANIZATION'    -- only ORGANIZATION Party types
  -- AND hp.status              = 'A'               -- only Active Parties/Customers
   AND hp.party_number        = 8899
 ORDER BY TO_NUMBER(hp.party_number), hp.party_name, hca.account_number;

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;

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.