create file with timestamp Using CMD

mkdir Backup_%Date:~-7,2%_%date:~4,2%_%date:~-4%_%time:~0,2%_%Time:~3,2%

Result: Backup_11_02_2020_11_08

Format date and time in a Windows batch script

@ECHO OFF
: Sets the proper date and time stamp with 24Hr Time for log file naming
: convention

SET HOUR=%time:~0,2%
SET dtStamp9=%date:~-4%%date:~4,2%%date:~7,2%_0%time:~1,1%%time:~3,2%%time:~6,2% 
SET dtStamp24=%date:~-4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%

if "%HOUR:~0,1%" == " " (SET dtStamp=%dtStamp9%) else (SET dtStamp=%dtStamp24%)

ECHO %dtStamp%

PAUSE

System Date & Time in Windos OS Command

echo %date%
Tue 02/11/2020

echo %time%
10:39:09.74

echo %date%  %time%
Tue 02/11/2020 10:39:09.74

Find Responsibility and Employee information of Form in Oracle EBS

SELECT distinct fue.user_id, fue.user_name, fue.employee_id ,paaf.assignment_number,  paaf.supervisor_id

  FROM FND_MENU_ENTRIES            FME,

       FND_RESPONSIBILITY_VL       fr,

       fnd_user_resp_groups_direct fd,

       fnd_user                    fue,

       per_all_assignments_f       paaf

 WHERE EXISTS (SELECT 'FUNCTION_ID'

          FROM FND_FORM FF, FND_FORM_FUNCTIONS F3

         WHERE FF.FORM_NAME = 'FORM_NAME'

           AND F3.FORM_ID = FF.FORM_ID

           AND F3.APPLICATION_ID = FF.APPLICATION_ID

           AND FME.FUNCTION_ID = F3.FUNCTION_ID)

   and fr.MENU_ID = fme.menu_id

   and fd.RESPONSIBILITY_ID = fr.RESPONSIBILITY_ID

   and fd.user_id = fue.user_id

   and sysdate between fue.start_date and nvl(fue.end_date, sysdate)

   and fue.employee_id is not null

   and sysdate between paaf.effective_start_date and nvl(paaf.effective_end_date, sysdate)

   and fue.employee_id = paaf.person_id

PR TO PO AND GRN REPORT QUERY Oracle EBS

CREATE OR REPLACE VIEW XX_RCV_TRANSACTIONS_V AS

/*

Created by m.Faisal

*/

SELECT PO_HEADER_ID ,PO_LINE_ID ,SHIPMENT_NUM,SHIPPED_DATE, SUM(RECEIVE)RECEIVE ,

SUM(DELIVER)DELIVER ,

SUM(RETURN_TO_VENDOR)RETURN_TO_VENDOR ,

SUM(ACCEPT)ACCEPT ,

SUM(REJECT)REJECT

FROM

(SELECT PO_HEADER_ID,

       PO_LINE_ID, rsh.SHIPMENT_NUM , rsh.SHIPPED_DATE,

       DECODE (transaction_type, 'RECEIVE', quantity, 0) RECEIVE,

       DECODE (transaction_type, 'DELIVER', quantity, 0) DELIVER,

       DECODE (transaction_type, 'RETURN TO VENDOR', quantity, 0)RETURN_TO_VENDOR,

       DECODE (transaction_type, 'ACCEPT', quantity, 0)ACCEPT,

       DECODE (transaction_type, 'REJECT', quantity, 0) REJECT

  FROM rcv_transactions rt , RCV_SHIPMENT_HEADERS_V rsh

  where rt.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID

  )

GROUP BY PO_HEADER_ID ,PO_LINE_ID ,SHIPMENT_NUM,SHIPPED_DATE;

/* Formatted on 1/29/2020 5:58:33 PM (QP5 v5.336) */

  SELECT PRHA.SEGMENT1                            REQUISTION_NO,

         POH.SEGMENT1                             PO_NO,

         MSI.CONCATENATED_SEGMENTS                ITEM_CODE,

         MSI.DESCRIPTION                          ITEM_DESCRIPTION,

         NVL (SUM (PLA.QUANTITY), 0)              PO_QUANTITY,

         rcv.SHIPMENT_NUM                         IGP_Number,

         rcv.SHIPPED_DATE                         IGP_Date,

         SUM (rcv.RECEIVE)                        IGP_Quantity,

         SUM (rcv.ACCEPT)                         Inspected_quantity,

         SUM (rcv.DELIVER)                        GRN_quantity,

         SUM (rcv.REJECT)                         B_Grade_quantity,

         SUM (rcv.RECEIVE) - SUM (rcv.ACCEPT)     Balance_to_Inspect,

         PLA.ATTRIBUTE_CATEGORY                   PO_TYPE,

         PLA.ATTRIBUTE1                           PO_ITEM_CATEGORY,

         PLA.ATTRIBUTE5                           PO_CATEGORY,

         PLA.ATTRIBUTE6                           PO_SUB_CATEGORY,

         POH.PO_HEADER_ID,

         PDA.PO_LINE_ID,

         MSI.INVENTORY_ITEM_ID

    FROM PO_HEADERS_ALL            POH,

         PO_LINES_ALL              PLA,

         PO_DISTRIBUTIONS_ALL      PDA,

         PO_REQ_DISTRIBUTIONS_ALL  PRDA,

         PO_REQUISITION_LINES_ALL  PRLA,

         PO_REQUISITION_HEADERS_ALL PRHA,

         APPS.MTL_SYSTEM_ITEMS_KFV MSI,

         xx_rcv_transactions_V    rcv

   WHERE     POH.PO_HEADER_ID = PDA.PO_HEADER_ID

         AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID

         AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID

         AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID

         AND PLA.PO_HEADER_ID = PDA.PO_HEADER_ID

         AND PLA.PO_HEADER_ID = POH.PO_HEADER_ID

         AND MSI.ORGANIZATION_ID = 87

         AND NVL (PLA.CANCEL_FLAG, 'N') <> 'Y'

         AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID

         AND rcv.PO_HEADER_ID = POH.PO_HEADER_ID

         AND rcv.PO_LINE_ID = PLA.PO_LINE_ID

        -- AND PRHA.SEGMENT1 = 124                        --- requisition number

GROUP BY PRHA.SEGMENT1,

         POH.SEGMENT1,

         MSI.CONCATENATED_SEGMENTS,

         MSI.DESCRIPTION,

         rcv.SHIPMENT_NUM,

         rcv.SHIPPED_DATE,

         PLA.ATTRIBUTE_CATEGORY                  ,

         PLA.ATTRIBUTE1                          ,

         PLA.ATTRIBUTE5                          ,

         PLA.ATTRIBUTE6                          ,

         POH.PO_HEADER_ID,

         PDA.PO_LINE_ID,

         MSI.INVENTORY_ITEM_ID

Find all responsibility of Form in Oracle EBS

--Responsibility on the base of Form

SELECT fr.*

  FROM FND_MENU_ENTRIES FME, FND_RESPONSIBILITY_VL fr

 WHERE     EXISTS

               (SELECT 'FUNCTION_ID'

                  FROM FND_FORM FF, FND_FORM_FUNCTIONS F3

                 WHERE     FF.FORM_NAME = 'FORM_NAME'

                       AND F3.FORM_ID = FF.FORM_ID

                       AND F3.APPLICATION_ID = FF.APPLICATION_ID

                       AND FME.FUNCTION_ID = F3.FUNCTION_ID)

       AND fr.MENU_ID = fme.menu_id

HOW TO DELETE A DFF CONTEXT

Sometime I’m quite annoyed by the typo mistake when creating a DFF context. The DFF segment screen doesn’t allow deletion of context. Fortunately, Oracle has internal API to do such thing. Following is a sample.
–*******************************************
–* Delete a descriptive flexfield
–*******************************************
SET ECHO OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_application_id                NUMBER := 0;
l_descriptive_flexfield_name    VARCHAR2(100) :=  ‘FND_COMMON_LOOKUPS’ ;
l_descriptive_flex_context_cod  VARCHAR2(100) :=  ‘XFND_CLWW_PURGE_FOLDER’;
BEGIN
–FND_DESCRIPTIVE_FLEXS_PKG –this package is for DFF
–FND_DESCR_FLEX_CONTEXTS_PKG –this package is for DFF Context
–FND_DESCR_FLEX_COL_USAGE_PKG –this package is for DFF Column useage
–When creating a new DFF Context, it will check the DFF Column usage if the context is already used.
–so when deleting a DFF Context, both the context and column usage should be deleted.
FOR c IN (SELECT application_column_name
FROM fnd_descr_flex_column_usages
WHERE application_id = l_application_id
AND descriptive_flexfield_name = l_descriptive_flexfield_name
AND descriptive_flex_context_code = l_descriptive_flex_context_cod)
LOOP

fnd_descr_flex_col_usage_pkg.delete_row(
x_application_id                => l_application_id
,x_descriptive_flexfield_name    => l_descriptive_flexfield_name
,x_descriptive_flex_context_cod  => l_descriptive_flex_context_cod
,x_application_column_name       => c.application_column_name
);
END LOOP;
fnd_descr_flex_contexts_pkg.delete_row(
x_application_id                => l_application_id,
,x_descriptive_flexfield_name    => l_descriptive_flexfield_name
,x_descriptive_flex_context_cod  => l_descriptive_flex_context_cod
);

–commit;
end;