Debugging an Internal Requisition which not turned to an Internal Sales Order

 

Once I have encountered a situation wherein an Internal Sales Order is not created for an Internal Requisition. Below are the steps I have followed the to resolve this problem

Firstly execute the following query to make sure whether an Internal SO is created for Internal Requisition or not.

--To check whether a SO is created for an Internal Requisition SELECT   oeh.order_number, oeh.header_id, oel.line_id, oel.line_number FROM oe_order_lines_all oel, oe_order_headers_all oeh, po_requisition_headers_all porh, po_requisition_lines_all porl WHERE oeh.header_id = oel.header_id AND oel.source_document_id = porh.requisition_header_id AND oel.source_document_line_id = porl.requisition_line_id AND porh.requisition_header_id = porl.requisition_header_id AND oel.order_source_id = 10             --order_source_id for 'Internal' AND oel.orig_sys_document_ref = '&Your_Int_Req_num' AND oel.org_id = porh.org_id ORDER BY oeh.header_id, oel.line_id;

If you find that a SO is not created then follow the below steps.

Order Interface Tables

If  SO is not created, then it must have got strucked in Interface tables

OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL

-- To check whether Requisition Lines are in Interface table or not SELECT COUNT (*) FROM oe_lines_iface_all WHERE orig_sys_line_ref IN ( SELECT requisition_line_id FROM po_requisition_lines_all WHERE requisition_header_id IN ( SELECT requisition_header_id FROM po_requisition_headers_all WHERE segment1 = '&Requisition_Number'));

When a Requisition data is inserted into Order Interface tables then transferred_to_oe_flag of both PO_REQUISITION_HEADERS_ALL and PO_REQUISITION_LINES_ALL with be set to ‘Y‘

If  SO is strucked in Interface tables then there must be some error/problem with Internal Requisition which we can find out in the application.

This is possible using the Order Management Responsibility – and navigating to Orders –> Import Orders –> Corrections

o    Choose an order type of Internal

o    In Order Reference use the List of Values to locate the Requisition Number

o    If the Requisition is not located – it has either been created into a Sales Order or not inserted into the tables by the Create Internal Orders program

If the Requisition is not located – it has either been created into a Sales Order or not inserted into the tables by the Create Internal Orders program. To resolve this kind of problem read this article.

If Requisition is found, check out for errors as why they got strucked.

Below are the possibles reasons for getting a Requisition strucked.

1) Item Pre-Requisites

o    Firstly make sure that your Internal Requisition is Approved

o    Items involved in Requisition are assigned to source and destination organizations or not? and also make sure that the items are assigned to Master Org.

Usually Master Org is the Item Validation Org, which you can find with the help of a profile option ‘OM: Item Validation Org‘.

o    Items should have enough onhand quantity in source organization

2) Item Attributes

Make sure the following item attributes are set for both Source and Destination Organizations, Item validation organization too.

o    In Purchasing Tab – add a price if the item is to be used in iProcurement.

Uncheck the purchasing checkboxes, if the item is to ONLY be ordered from an internal source.

o    In Order Management tab of Item Master the following attributes should be enabled.

1.    Internal Ordered

2.    Internal Orders Enabled

3.    OE Transactable

3) Shipping Network

Ensure you have shipping network established between Source and Destination Organizations.

Navigation: Inventory Responsibility –>  Setup –> Organizations –> Shipping Networks

4) Destination Organization as an Internal Customer

Destination Inventory Organization must be created as a Customer in the Operating Unit of the Source Inventory Organization that is used on the Internal Requisition.

You can check the Operating Unit for the Source Inventory Organization by using the following sql:

SELECT    'The Inventory Organization ' || oo.organization_code || 'has the inventory organization ID of ' || oo.organization_id || ' and is under the Operating Unit' || hro.NAME || ' which has the Operating Unit ID of ' || oo.operating_unit FROM org_organization_definitions oo, hr_all_organization_units hro WHERE UPPER (oo.organization_code) LIKE UPPER ('%&INV_ORG_CODE%') AND hro.organization_id = oo.operating_unit;

5) Item Price

Price must be defined for the Items involved in the Internal Requisition.

 

---

Once the error is corrected, In the Order Corrections form the ERROR flag is unchecked and the REQUEST_ID field cleared, the order can be resubmitted for Import with ‘Order Import’ Concurrent Program.

PLEASE NOTE -If you want to delete records from Interface then it is necessary to Delete the Lines first and then the header.

Please do comment if you have any more issues with this kind of problem.

 

SQL Query and Information about Sales Order Line Number

SELECT TO_CHAR(l.line_number)

  || DECODE(l.shipment_number, NULL, NULL, '.'

  || TO_CHAR(l.shipment_number))

  || DECODE(l.option_number, NULL, NULL, '.'

  || TO_CHAR(l.option_number))

  || DECODE(l.component_number, NULL, NULL, DECODE(l.option_number, NULL, '.',NULL)

  || '.'

  ||TO_CHAR(l.component_number))

  || DECODE(l.service_number,NULL,NULL, DECODE(l.component_number, NULL, '.' , NULL)

  || DECODE(l.option_number, NULL, '.', NULL )

  || '.'

  || TO_CHAR(l.service_number)) "SO Line Num"

   FROM oe_order_headers_all h ,

  oe_order_lines_all l

  WHERE 1          = 1

AND h.order_number = '&Order_num'

AND h.header_id    = l.header_id;

SQL Query to find out the status of Interface Mangers

 SELECT x.process_type "Name",

  DECODE(

  (SELECT '1' FROM fnd_concurrent_requests cr,

    fnd_concurrent_programs_vl cp,

    fnd_application a WHERE cp.concurrent_program_id = cr.concurrent_program_id

  AND cp.concurrent_program_name                     = x.process_name

  AND cp.application_id                              = a.application_id

  AND a.application_short_name                       = x.process_app_short_name

  AND phase_code                                    != 'C'

  ),'1','Active','Inactive') "Status",

  x.worker_rows "Worker Rows",

  x.timeout_hours "Timeout Hours",

  x.timeout_minutes "Timeout Minutes",

  x.process_hours "Process Interval Hours",

  x.process_minutes "Process Interval Minutes",

  x.process_seconds "Process Interval Seconds"

FROM

  (SELECT mipc.process_code ,

    mipc.process_status ,

    mipc.process_interval ,

    mipc.manager_priority ,

    mipc.worker_priority ,

    mipc.worker_rows ,

    mipc.processing_timeout ,

    mipc.process_name ,

    mipc.process_app_short_name ,

    a.meaning process_type ,

    FLOOR(mipc.process_interval    /3600) process_hours ,

    FLOOR((mipc.process_interval   - (FLOOR(mipc.process_interval/3600) * 3600))/60) process_minutes ,

    (mipc.process_interval         - (FLOOR(mipc.process_interval/3600) * 3600) - (FLOOR((mipc.process_interval - (FLOOR(mipc.process_interval/3600) * 3600))/60) * 60)) process_seconds ,

    FLOOR(mipc.processing_timeout  /3600) timeout_hours ,

    FLOOR((mipc.processing_timeout - FLOOR(mipc.processing_timeout/3600) * 3600)/60) timeout_minutes

  FROM mtl_interface_proc_controls mipc,

    mfg_lookups a

  WHERE a.lookup_type = 'PROCESS_TYPE'

  AND a.lookup_code   = mipc.process_code

  ) x

  WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager

ORDER BY 1;

Oracle SQL Query to list Months and Years

 

/**************************************************************************

 *PURPOSE: To list out Months and Years                                   *

 *PARAMETERS: LEVEL count                                                 *

 *                                                                        *

 **************************************************************************/

 

 

SELECT a.months

  ||'/'

  ||b.years credit_card_expiration_date

FROM

  (SELECT TO_CHAR(add_months(TRUNC(SYSDATE, 'YYYY'), LEVEL - 1), 'MM') months

  FROM dual

    CONNECT BY LEVEL <= 12

  ) a,

  (SELECT SUBSTR(EXTRACT(YEAR FROM SYSDATE) + (LEVEL-1),3) years

  FROM dual

    CONNECT BY LEVEL <=10

  ) b

WHERE to_date(a.months

  ||'/'

  ||b.years,'mm/yy')>=SYSDATE

ORDER BY b.years,

  a.months;

SQL Query to findout information about a Concurrent Request

 

/**************************************************************************

 *PURPOSE: To find out information about a Concurrent Request             *

 **************************************************************************/

 

 SELECT  fcrs.request_id, fcrs.user_concurrent_program_name,

         fcrs.actual_start_date, fcrs.actual_completion_date,

         FLOOR(((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60)/3600)||':'||

         FLOOR((((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60) -

         FLOOR(((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60)/3600)*3600)/60)||':'||

         round((((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60) -

         FLOOR(((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60)/3600)*3600 -

         (FLOOR((((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60) -

         FLOOR(((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) "HOURS:MINUTES:SECONDS",

         fcrs.argument_text, fcrs.requestor,

         DECODE (fcrs.status_code,

                'A', 'Waiting',

                'B', 'Resuming',

                'C', 'Normal',

                'D', 'Cancelled',

                'E', 'Errored',

                'F', 'Scheduled',

                'G', 'Warning',

                'H', 'On Hold',

                'I', 'Normal',

                'M', 'No Manager',

                'Q', 'Standby',

                'R', 'Normal',

                'S', 'Suspended',

                'T', 'Terminating',

                'U', 'Disabled',

                'W', 'Paused',

                'X', 'Terminated',

                'Z', 'Waiting',

                 fcrs.status_code

                ) "Status",

         decode(fcrs.phase_code,

                'C','Completed',

                'I','Inactive',

                'R','Running',

                'A','Active',

                fcrs.phase_code) "Phase Code",  fcrs.completion_text,

         fcrs.responsibility_application_id, frt.responsibility_name,

         fcrs.save_output_flag, fcrs.request_date ,

         decode (fcrs.execution_method_code,

                 'B', 'Request Set Stage Function',

                 'Q', 'SQL*Plus',

                 'H', 'Host',

                 'L', 'SQL*Loader',

                 'A', 'Spawned',

                 'I', 'PL/SQL Stored Procedure',

                 'P', 'Oracle Reports',

                 'S', 'Immediate',

                 fcrs.execution_method_code

               ) execution_method , fcrs.concurrent_program_id, fcrs.program_short_name, fcrs.printer,

               fcrs.parent_request_id

    FROM fnd_conc_req_summary_v fcrs, 

         fnd_responsibility_tl frt

   WHERE 1 = 1

AND user_concurrent_program_name LIKE '%'

--and argument_text LIKE '%'

--and requestor not in ('SYSADMIN','INVADMIN')

--and request_id = 9686914

AND frt.language = 'US' AND fcrs.responsibility_id = frt.responsibility_id

and fcrs.actual_start_date < sysdate

--and fcrs.phase_code = 'R'

--and fcrs.status_code = 'X'

--and fcrs.status_code not in ('P','D','Q','C')

--and trunc(fcrs.actual_start_date) =trunc(sysdate)

--and trunc(fcrs.actual_completion_date) = trunc(sysdate)

ORDER BY fcrs.actual_start_date DESC;

Shipment Transaction Accounting Different against RCV Transaction Delivery

select mmt.SHIPMENT_NUMBER,to_char(mmt.TRANSACTION_DATE,'YYYY/MM/DD') TDATE, mmt.INVENTORY_ITEM_ID,msib.SEGMENT1 ITEM,msib.DESCRIPTION,

mmt.ORGANIZATION_ID SORGID, ood.ORGANIZATION_CODE SORGCODE,ood.ORGANIZATION_NAME SORGNAME,

mmt.TRANSACTION_QUANTITY,cc.CONCATENATED_SEGMENTS,

mmt.TRANSFER_ORGANIZATION_ID RORGID,ood1.ORGANIZATION_CODE RORGCODE,ood1.ORGANIZATION_NAME RORGNAME,

sum(nvl(xal.ACCOUNTED_DR,0)) ACCOUNTED_DR,sum(nvl(xal.ACCOUNTED_CR,0)) ACCOUNTED_CR

from gl_je_headers gjh, gl_je_lines gjl,gl_code_combinations_kfv CC,gl_import_references gir,xla_ae_lines xal,xla.xla_transaction_entities xte

,mtl_material_transactions mmt, mtl_system_items_b msib, org_organization_definitions ood, org_organization_definitions ood1

where gjh.STATUS = 'P' and gjh.PERIOD_NAME = :P_PERIOD_NAME -- 'Aug-21-22' 

and gjh.JE_SOURCE = 'Inventory' and gjh.JE_CATEGORY = 'INTE'

and gjh.JE_HEADER_ID = gjl.JE_HEADER_ID --and gjl.CODE_COMBINATION_ID = 605635 

and gjl.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID and cc.SEGMENT1 =:p_OU_CODE  ---'10211 '

and cc.segment6 = '12190411'-- in('10411','14711) and cc.segment6 in('12190611','12190711')

--and trunc(mmt.TRANSACTION_DATE) >= (select distinct trunc(START_DATE) from gl_period_statuses where PERIOD_NAME=:p_PERIOD_NAME)

---and trunc(mmt.TRANSACTION_DATE) <= (select distinct trunc(END_DATE) from gl_period_statuses where PERIOD_NAME=:p_PERIOD_NAME)

and gjl.JE_HEADER_ID = gir.JE_HEADER_ID and gjl.JE_LINE_NUM = gir.JE_LINE_NUM and gir.GL_SL_LINK_ID = xal.GL_SL_LINK_ID

and gir.REFERENCE_5 = xte.ENTITY_ID and xte.SOURCE_ID_INT_1 = mmt.TRANSACTION_ID and mmt.TRANSACTION_TYPE_ID = 62

and mmt.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID and mmt.ORGANIZATION_ID = msib.ORGANIZATION_ID and mmt.ORGANIZATION_ID = ood.ORGANIZATION_ID

and mmt.TRANSFER_ORGANIZATION_ID = ood1.ORGANIZATION_ID

---and mmt.SHIPMENT_NUMBER= '1442448211'  ---'1441524611'

and mmt.TRANSACTION_ID not in(

select x.TRANSFER_TRANSACTION_ID from mtl_material_transactions x where x.transaction_type_id = 61 ---and x.SHIPMENT_NUMBER='14424482'

and x.TRANSACTION_DATE  BETWEEN to_date('2021/09/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') AND to_date('2021/09/30 23:59:59', 'YYYY/MM/DD HH24:MI:SS')

)

group by mmt.SHIPMENT_NUMBER,to_char(mmt.TRANSACTION_DATE,'YYYY/MM/DD'),mmt.INVENTORY_ITEM_ID,msib.SEGMENT1,msib.DESCRIPTION,

mmt.ORGANIZATION_ID,ood.ORGANIZATION_CODE,ood.ORGANIZATION_NAME,

mmt.TRANSACTION_QUANTITY,cc.CONCATENATED_SEGMENTS,

mmt.TRANSFER_ORGANIZATION_ID,ood1.ORGANIZATION_CODE,ood1.ORGANIZATION_NAME

order by mmt.SHIPMENT_NUMBER

HOW TO RE-OPEN A CLOSED INVENTORY ACCOUNTING PERIOD

 SELECT acct_period_id period, open_flag, period_name name, 

      period_start_date, schedule_close_date, period_close_date 

      FROM  org_acct_periods 

      WHERE organization_id = &org_id 

      order by 1,2;

--UPDATE org_acct_periods

SET open_flag = ‘Y’,

period_close_date = NULL,

summarized_flag = ‘N’

WHERE organization_id = &&org_id

AND acct_period_id >= &&acct_period_id;

 

--DELETE mtl_period_summary

WHERE organization_id = &org_id

AND acct_period_id >= &acct_period_id;

---DELETE mtl_period_cg_summary

WHERE organization_id = &org_id

AND acct_period_id >= &acct_period_id;

--DELETE mtl_per_close_dtls

WHERE organization_id = &org_id

AND acct_period_id >= &acct_period_id;

---DELETE cst_period_close_summary

WHERE organization_id = &org_id

AND acct_period_id >= &acct_period_id; 

--commit

SQL Query to find Status of GL and PO Accounting Periods

SELECT sob.name "Set of Books" ,
  fnd.product_code "Product Code" ,
  ps.PERIOD_NAME "Period Name" ,
  ps.START_DATE "Period Start Date" ,
  ps.END_DATE "Period End Date" ,
  DECODE(ps.closing_status, 'O','O - Open' ,
                            'N','N - Never Opened' ,
                            'F','F - Future Enterable' ,
                            'C','C - Closed' ,'Unknown') "Period Status"
FROM gl_period_statuses ps ,
  GL_SETS_OF_BOOKS sob ,
  FND_APPLICATION_VL fnd
WHERE ps.application_id      IN (101,201) -- GL & PO
AND sob.SET_OF_BOOKS_ID       = ps.SET_OF_BOOKS_ID
AND fnd.application_id        = ps.application_id
AND ps.adjustment_period_flag = 'N'
AND (TRUNC(SYSDATE) -- Comment line if a a date other than SYSDATE is being tested.
  --AND ('01-DEC-2014' -- Uncomment line if a date other than SYSDATE is being tested.
  BETWEEN TRUNC(ps.start_date) AND TRUNC (ps.end_date))
ORDER BY ps.SET_OF_BOOKS_ID,
  fnd.product_code,
  ps.start_date;