Monday, 29 October 2012

How to Find the Node, Platform and IP address in Instance

 Oracle apps Node, Platform and IP address in Instance

Navigation: System Administrator -> Install -> Nodes


How to find all Form Personalization through script in Oracle APPS


SELECT FORM_ID, FORM_NAME, USER_FORM_NAME, DESCRIPTION
FROM FND_FORM_VL
WHERE FORM_NAME IN
                                     (SELECT FORM_NAME 
                                      FROM FND_FORM_CUSTOM_RULES 
                                      GROUP BY FORM_NAME)

How to Registration PL SQL Stored Procedure Report in Oracle apps

STEP 1: Create PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.XX_ITEM_REPORT (
   errbuf             OUT      VARCHAR2,
   retcode            OUT      NUMBER,
   P_ORGANIZATION_ID  IN       NUMBER)
   is
   Cursor item_cur
   is
   SELECT SEGMENT1,Description,Primary_UOM_Code,Inventory_item_id
   FROM MTL_SYSTEM_ITEMS_B MSI
   WHERE MSI.ORGANIZATION_ID= NVL(P_ORGANIZATION_ID,MSI.ORGANIZATION_ID)
   And INVENTORY_ITEM_STATUS_CODE='Active'
   Order by SEGMENT1;
 
   l_header   VARCHAR2 (9000) := ' ';
   l_record   VARCHAR2 (9000) := ' ';
   l_count    NUMBER          := 0;
 
   BEGIN
   errbuf := ' ';
   retcode := 0;

    DBMS_APPLICATION_INFO.set_client_info ('81'); --ORG_ID
    l_header :=  'ITEM CODE'||CHR(9)||'DESCRIPTION'||CHR(9)||CHR(9)||CHR(9)||'Primary UOM'||CHR(9)||'ITEM ID';
    fnd_file.put_line (fnd_file.output, l_header);
 
    FOR item IN item_cur
      LOOP
         l_record := item.segment1||CHR(9)||CHR(9)||
                     rpad(item.description,30)||CHR(9)||
                     item.primary_uom_code||CHR(9)||CHR(9)||
                     item.INVENTORY_ITEM_ID;
         fnd_file.put_line (fnd_file.output, l_record);

      END LOOP;
   
      EXCEPTION
        WHEN NO_DATA_FOUND  THEN
        fnd_file.put_line (fnd_file.LOG, SQLERRM);
        fnd_file.CLOSE;
        WHEN OTHERS  THEN
        retcode := 2; -- Consider logging the error and then re-raise
        fnd_file.put_line (fnd_file.LOG, SQLERRM);
        fnd_file.CLOSE;
        RAISE;
   
      END;
/
STEP 2:

STEP 3:


STEP 4:

STEP 5: