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;

1404: Please first load this entity or check protect and custom level of MESSAGE 'POC_APPR/ACC_APPR_ACCEPT'.

1404: Please first load this entity or check protect and custom level of MESSAGE 'POC_APPR/ACC_APPR_ACCEPT'.


Solution;

From the menu in Workflow Builder, choose Help > About Oracle Workflow Builder 2.6.
Change the Access Level to 0 and select allow modifications of customized objects.

Hope it helps,

index Table Type in PLSQL

DECLARE

  TYPE TABLENAMETYPE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

  L_TABLENAMETYPE TABLENAMETYPE;

BEGIN

  --YOU CAN USE THIS L_TABLENAMETYPE TYPE HERE

  L_TABLENAMETYPE(1) := 'AB';

  L_TABLENAMETYPE(2) := 'ABC';

  L_TABLENAMETYPE(3) := 'ABD';

  L_TABLENAMETYPE(4) := 'ABE';

  FOR LDX IN 1 .. L_TABLENAMETYPE.COUNT

 

   LOOP

 

    DBMS_OUTPUT.PUT_LINE(L_TABLENAMETYPE(LDX));

 

  END LOOP;

  ---DELETE ALL VALUES

  L_TABLENAMETYPE.DELETE;

END;

--RESULT

AB

ABC

ABD

ABE

---USE TABLE TYPE WITH BULK COLLECT INTO

DECLARE

CURSOR C IS

  SELECT * FROM EMP;

TYPE ANY_TABLE_TYPE IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;

EMP_REC ANY_TABLE_TYPE;

BEGIN

  OPEN C;

  FETCH C BULK COLLECT

 

    INTO EMP_REC;

  CLOSE C;

  FORALL I IN EMP_REC.FIRST .. EMP_REC.LAST

 

    UPDATE EMP

   

       SET COMM = EMP_REC(I).SAL * 10 / 100

   

     WHERE EMPNO = EMP_REC(I).EMPNO;

  COMMIT;

END;

Perform Dynamic DLL in Oracle

EXECUTE IMMEDIATE statement can be used to execute dynamic SQL statements.

Syntax: EXECUTE IMMEDIATE 'User_Code';

--

-- Create/Drop Table using this PLSQL Script or Procedure

-- DLL Script

-- by mfaisal 02-Jan-2020

Declare

---Create

begin

  execute immediate 'CREATE TABLE X_DDL_ON_APPS (APPS_ID NUMBER)';

end;

Declare

--Drop with Oracle EBS Package

begin

  system.ad_apps_private.do_apps_ddl(schema_name => 'APPS',

                                     ddl_text => 'DROP TABLE X_DDL_ON_APPS ');

end;