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;

Unprocessed Shipping Transactions query in Oracle Apps r12

SELECT   'Unprocessed Shipping Transactions' trntype, org.organization_id,

            org.organization_code, org.organization_name, COUNT (*) totaltrn,

            tt.transaction_date, tt.expected_arrival_date, error_explanation,

            ERROR_CODE, gp.period_code

       FROM wsh_delivery_details wdd,

            wsh_delivery_assignments wda,

            wsh_new_deliveries wnd,

            wsh_delivery_legs wdl,

            wsh_trip_stops wts,

            mtl_transactions_interface tt,

            org_organization_definitions org,

            gmf_period_statuses gp

      WHERE wdd.source_code = 'OE'

        AND wdd.released_status = 'C'

        AND wdd.inv_interfaced_flag IN ('N', 'P')

        AND wdd.organization_id = org.organization_id

        AND wda.delivery_detail_id = wdd.delivery_detail_id

      ---  AND wnd.delivery_id = wda.delivery_id

       --- AND wdd.organization_id = tt.organization_id(+)

        AND wnd.status_code IN ('CL', 'IT')

        AND wdl.delivery_id = wnd.delivery_id

        AND wts.pending_interface_flag IN ('Y', 'P')

        AND gp.period_code = gp.period_code

        AND TRUNC (wts.actual_departure_date) >= TRUNC (gp.start_date)

        AND TRUNC (wts.actual_departure_date) <= TRUNC (gp.end_date)

        --AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN :P_FROM_DATE and :P_TO_DATE

        ---AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN TO_DATE('01-MAY-2018 00:00:00','DD-MON-YYYY HH24:MI:SS' ) AND

        ---TO_DATE('31-MAY-2018 23:59:59','DD-MON-YYYY HH24:MI:SS')

        ----AND wdl.pick_up_stop_id = wts.stop_id

   GROUP BY org.organization_id,

            org.organization_code,

            org.organization_name,

            error_explanation,

            ERROR_CODE,

            tt.transaction_date,

            tt.expected_arrival_date,

            gp.period_code

   ORDER BY org.organization_name;

Blog Page disable copy paste Query

Blog Page disable copy paste Query

<!- start disable copy paste --><script src='demo-to-prevent-copy-paste-on-blogger_files/googleapis.js'></script><script type='text/javascript'> if(typeof document.onselectstart!="undefined" ) {document.onselectstart=new Function ("return false" ); } else{document.onmousedown=new Function ("return false" );document.onmouseup=new Function ("return false"); } </script>

<!-- End disable copy paste --></!->