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;

SQL Query to find Status of Inventory Accounting Periods

 

SELECT ood.organization_id "Organization ID" , ood.organization_code "Organization Code" , ood.organization_name "Organization Name" , oap.period_name "Period Name" , oap.period_start_date "Start Date" , oap.period_close_date "Closed Date" , oap.schedule_close_date "Scheduled Close" , DECODE(oap.open_flag, 'P','P - Period Close is processing' , 'N','N - Period Close process is completed' , 'Y','Y - Period is open if Closed Date is NULL' ,'Unknown') "Period Status" FROM org_acct_periods oap , org_organization_definitions ood WHERE oap.organization_id = ood.organization_id 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(oap.period_start_date) AND TRUNC (oap.schedule_close_date)) ORDER BY ood.organization_id, oap.period_start_date;