Friday, 30 November 2012

How to find Workflow User, Item Name, Function Message and Lookups



--Workflow Users/Roles
SELECT * FROM WF_USERS where name = 'XXUSER';
SELECT * FROM WF_ROLES where name = 'XXUSER';
SELECT * FROM WF_USER_ROLES where user_name = 'XXUSER';
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS where user_name = 'XXUSER';

--Workflow Item Name and Attributes
SELECT * FROM WF_ITEM_TYPES where name = 'OKCAUKAP'; --PO Approval (POAPPRV) ; PO Requisition Approval (REQAPPRV); 
--OM Order Header (OEOH); OM Order Line (OEOL)
SELECT * FROM WF_ITEM_TYPES_VL where name = 'OKCAUKAP';
SELECT * FROM WF_ITEM_TYPES_VL where display_name like '%Approval';
SELECT * FROM WF_ITEM_TYPES_VL where description like '%Order%';
SELECT * FROM WF_ITEM_ATTRIBUTES where item_type = 'OKCAUKAP';
SELECT * FROM WF_ITEM_ATTRIBUTES_TL;

--Workflow Functions
SELECT * FROM WF_ACTIVITIES where item_type = 'OKCAUKAP' and version = 1;
SELECT * FROM WF_ACTIVITIES_TL where item_type = 'OKCAUKAP';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES where activity_name = 'CREATE_SOA';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL;
SELECT * FROM WF_ACTIVITY_TRANSITIONS;

--Workflow Lookups
SELECT * FROM WF_LOOKUPS_TL where lookup_type = 'OKC_DB_RETRY';

--Workflow Messages
SELECT * FROM WF_MESSAGES where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGES_TL where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';


--Workflow Particular item run and values
SELECT * FROM WF_ITEMS where item_type = 'OKCAUKAP' order by begin_date desc;
--USER_KEY/ITEM_KEY => select * from okc_k_headers_b where contract_number = '3308531';
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES where item_type = 'OKCAUKAP' and item_key='20683312';
SELECT * FROM WF_PROCESS_ACTIVITIES where process_item_type = 'OKCAUKAP' and process_version = 1;

SELECT * FROM WF_NOTIFICATIONS WHERE MESSAGE_TYPE = 'OKCAUKAP' AND MESSAGE_NAME = 'APPROVE_CONTRACT';
SELECT * FROM WF_NOTIFICATION_ATTRIBUTES where notification_id = ;

SELECT * FROM WF_DEFERRED;

Monday, 19 November 2012

How To Find Descriptive Flexfield (DFF) Type

 DESCRIPTIVE FLEX FIELD


Example:
Application : Purchasing
Title : PO Headers
Context Field value : one or more type

SELECT  ATTRIBUTE_CATEGORY
FROM     PO_HEADERS_ALL
WHERE  SEGMENT1=1386

How to Find Document Entities


SELECT fde.document_entity_id, fde.data_object_code,
          fde.table_name, fde.application_id, fatl.application_name,
          tl.user_entity_name, tl.user_entity_prompt, fde.creation_date,
          fde.created_by, fde.last_update_date, fde.last_updated_by,
          fde.last_update_login, fde.pk1_column, fde.pk2_column,
          fde.pk3_column, fde.pk4_column, fde.pk5_column
     FROM fnd_application_tl fatl,
          fnd_document_entities_tl tl,
          fnd_document_entities fde
    WHERE fde.data_object_code = tl.data_object_code
      AND tl.LANGUAGE = USERENV ('LANG')
      AND fde.application_id = fatl.application_id
      AND fatl.LANGUAGE = USERENV ('LANG');

Tuesday, 6 November 2012

How to convert dob to age in oracle


Select
Trunc(months_between(sysdate,dob)/12) YEAR,
Trunc(Mod(months_between(sysdate,dob),12)) MONTH,
Trunc(sysdate - add_months(dob,Trunc(months_between(sysdate,dob)/12)*12
         +Trunc(mod(months_between(sysdate,dob),12)))) DAYS
From (Select to_date(:P_Date,'DD-MON-YYYY') dob from Dual);

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:


Saturday, 29 September 2012

How many responsibility assign in a User



SELECT  FU.USER_ID,FU.USER_NAME
        ,FAT.APPLICATION_NAME
        ,FA.APPLICATION_SHORT_NAME SHORT_NAME
        ,FRT.RESPONSIBILITY_NAME
        ,FRT.DESCRIPTION
        ,FR.RESPONSIBILITY_KEY
        ,TO_CHAR(FUR.CREATION_DATE,'DD-MON-YYYY HH:MI:SS AM') RESPONS_START_DATE
FROM    FND_RESPONSIBILITY FR
,       FND_RESPONSIBILITY_TL FRT
,       FND_APPLICATION     FA
,       FND_APPLICATION_TL  FAT
,       FND_USER_RESP_GROUPS_DIRECT FUR
,       FND_USER            FU
WHERE   FR.APPLICATION_ID   =   FRT.APPLICATION_ID
AND     FR.RESPONSIBILITY_ID=   FRT.RESPONSIBILITY_ID
AND     FA.APPLICATION_ID   =   FR.APPLICATION_ID
AND     FA.APPLICATION_ID   =   FAT.APPLICATION_ID
AND     FUR.RESPONSIBILITY_ID=  FR.RESPONSIBILITY_ID
AND     FUR.USER_ID         =   FU.USER_ID
AND     FU.USER_NAME        =   UPPER(NVL(:USER_NAME,FU.USER_NAME))  --'PO'
AND     EMPLOYEE_ID  IS NOT NULL

Tuesday, 10 July 2012

Fibbonacci Series

Declare
x number;
y number;
z number;
ctr number;
q number;

begin
q:=&q;
x:=0;
y:=1;
ctr :=2;
dbms_output.put_line('........FIBBONACCI SERIES........'||sysdate);
dbms_output.put_line(x);
dbms_output.put_line(y);
for ctr in 1..q
loop
z:=x+y;
dbms_output.put_line(z);
x:=y;
y:=z;
end loop;
end;

Monday, 9 July 2012

How to eliminate particular rows in RTF

<?for-each:G_ICODE?><?if: CF_OPENING_QTY !=0 and RECEIPT_QTY !=0 and ISSUE_QTY !=0?>
<?CF_OPENING_QTY?>
<?RECEIPT_QTY?>
<?ISSUE_QTY?>
<?end if?>
<?end for-each?>
 

How to Call stored procedure from Forms Personalization



1. Personalize the form
2. Create an action of type "BuiltIn"
3. BuiltIn Type for Action should be "Execute a Procedure"
4. Argument should be as below 


='declare
   v_field_value VARCHAR2(200) ;
   begin
       plsql_package.procedurenameHere ;
  end'



or alternately

='declare
   v_field_value VARCHAR2(200) ;
   begin
       apassi_prc ('''||${item.PO_CONTROL_RULES.OBJECT_CODE_DISPLAYED_VALUE.value}||''');
   end'


Note the syntax, after =, entire declare begin end is within single quote.
Also, there is no semi colon after "end"

You can pass field values as
'''||${item.BLOCKNAME.FIELDNAME.value}||'''


Thursday, 28 June 2012

How to Register Oracle XML Reports in oracle apps R12

Overview
Oracle XML Publisher is a template-based publishing solution delivered with the Oracle E-Business Suite. It provides a new approach to report design and publishing by integrating familiar desktop word processing tools with existing E-Business Suite data reporting. At runtime, XML Publisher merges the custom templates with the concurrent request data extracts to generate output in PDF, HTML, RTF, EXCEL or even TEXT for use with EFT and EDI transmissions.

                                                                           STEP -1
Navigation: Login into Oracle Applications –> Go to System Administrator  Responsibility –> Concurrent –>   
Executable


FIELDS:

  • Executable: This is User Understandable Name
  • Short Name: This is Unique and for system reference
  • Application: Under which application you want to register this CONA_PO Program
  • Description: Description
  • Execution Method: Based on this field, your file has to be placed in respective directory or database.
  • Execution File Name: This is the actual Report file name.
Action: Save






                                                               STEP -2
 Create a new concurrent program Purchase Order Report that will call the CONA_PO executable declared above. Make sure that output format is placed as XML.


Navigation: Go to Application Developer Responsibility -> Concurrent ->Program


Note: Output Format should by  'XML' for registering the report in XML.

                                                                      STEP -3
Make sure that the report parameter name and the token name are same.





 
                                                                      STEP -4

Add this new concurrent  program to the corresponding responsibility.
Navigation: Go to System Administrator Responsibility ->Security ->Responsibility->Request










                                                                      STEP -5

Next process is to attach the designed rtf file with the XML code. 
In order to attach the rtf file the user should have the responsibility XML Publisher Administrator assigned to him.

First provide the concurrent program short name as Data Definition name in the  template manager and register the template using the data definition created.


Navigation: Go to XML Publisher Administrator->Data Definitions->Create Data definition.





Note: Make sure the code of the data definition must be the same as the short name of the Concurrent Program we registered for the procedure. So that the concurrent manager can retrieve the templates associated with the concurrent program

  

Now create the template with the help of template manager


                                                                      STEP -6
Navigation: Go to XML Publisher Administrator->Templates ->Create Templates.












Friday, 15 June 2012

How to find Company Address in a database function

/***** Company Ship to Location *********/
Create Or Replace Function COMPANY_ADDRESS(P_LOCATION_ID NUMBER)
RETURN VARCHAR2
AS
COMPANY VARCHAR2(500);
BEGIN
SELECT   Decode (hl.ADDRESS_LINE_1 , null, '', hl.ADDRESS_LINE_1||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_2 , null, '', hl.ADDRESS_LINE_2||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_3 , null, '', hl.ADDRESS_LINE_3||''||CHR(10) )||''||
         Decode (hl.LOC_INFORMATION14 , null, '', hl.LOC_INFORMATION14||''||CHR(10) ) ||''||
         Decode (hl.LOC_INFORMATION15 , null, '',hl.LOC_INFORMATION15)||' - '||hl.POSTAL_CODE INTO COMPANY
FROM HR_LOCATIONS HL
WHERE SHIP_TO_LOCATION_ID = P_LOCATION_ID;
RETURN COMPANY;
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

/***** Company Bill to Location *********/

Create Or Replace Function COMPANY_ADDRESS(P_LOCATION_ID NUMBER)
RETURN VARCHAR2
AS
COMPANY VARCHAR2(500);
BEGIN
SELECT   Decode (hl.ADDRESS_LINE_1 , null, '', hl.ADDRESS_LINE_1||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_2 , null, '', hl.ADDRESS_LINE_2||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_3 , null, '', hl.ADDRESS_LINE_3||''||CHR(10) )||''||
         Decode (hl.LOC_INFORMATION14 , null, '', hl.LOC_INFORMATION14||''||CHR(10) ) ||''||
         Decode (hl.LOC_INFORMATION15 , null, '',hl.LOC_INFORMATION15)||' - '||hl.POSTAL_CODE INTO COMPANY
FROM HR_LOCATIONS HL
WHERE LOCATION_ID = P_LOCATION_ID;
RETURN COMPANY;
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;


/***** Find the Multiple Operating Unit *********/
SELECT LOCATION_ID FROM APPS.HR_ORGANIZATION_UNITS
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID FROM APPS.HR_OPERATING_UNITS HOU
        WHERE HOU.NAME = NVL(:P_OPERATING_UNIT_NAME,HOU.NAME))

Tuesday, 5 June 2012

How to Find Application Top by particular application name

  SELECT    B.APPLICATION_ID,
                    B.APPLICATION_SHORT_NAME,
                    B.BASEPATH APPLICATION_TOP,
                    T.APPLICATION_NAME,
                    T.DESCRIPTION,
                     B.PRODUCT_CODE
     FROM   FND_APPLICATION_TL T
                  ,FND_APPLICATION B
    WHERE   B.APPLICATION_ID = T.APPLICATION_ID
     AND    T.APPLICATION_NAME = :KNOW_APPLICATION_NAME

Friday, 1 June 2012

How to increase oracle froms session times

Session Timeout

Log in : System Administrator

Select the Profile option (Profile - > System -> ICX:Session Timeout) in user level and set time.

Thursday, 16 February 2012

How to Find Total Production of Finished Goods in a Query

SELECT
(Select MSI.Segment1 From MTL_SYSTEM_ITEMS_B MSI where MSI.INVENTORY_ITEM_ID=M.INVENTORY_ITEM_ID AND M.ORGANIZATION_ID=MSI.ORGANIZATION_ID)Item,
(Select MSI.DESCRIPTION From MTL_SYSTEM_ITEMS_B MSI where MSI.INVENTORY_ITEM_ID=M.INVENTORY_ITEM_ID AND M.ORGANIZATION_ID=MSI.ORGANIZATION_ID) DESCRIPTION,
(Select MSI.PRIMARY_UOM_CODE From MTL_SYSTEM_ITEMS_B MSI where MSI.INVENTORY_ITEM_ID=M.INVENTORY_ITEM_ID AND M.ORGANIZATION_ID=MSI.ORGANIZATION_ID) UOM,
NVL(M.Total_qty+NVL(N.Total_QTY,0),0) Total_Quantity
FROM
(SELECT MMT.INVENTORY_ITEM_ID,MMT.ORGANIZATION_ID, NVL(SUM(MMT.PRIMARY_QUANTITY),0) Total_QTY
From MTL_MATERIAL_TRANSACTIONS MMT
WHERE      MMT.ORGANIZATION_ID=86 --ORGANIZATION_ID
AND     MMT.TRANSACTION_TYPE_ID=44 --WIP Complition
AND     MMT.TRANSACTION_SOURCE_TYPE_ID=5  --Job or Schedule
AND     TRUNC(MMT.TRANSACTION_DATE) BETWEEN :start_date AND :end_date
GROUP BY MMT.INVENTORY_ITEM_ID,MMT.ORGANIZATION_ID)M,
(SELECT MMT.INVENTORY_ITEM_ID,MMT.ORGANIZATION_ID, NVL(SUM(MMT.PRIMARY_QUANTITY),0) Total_QTY
From MTL_MATERIAL_TRANSACTIONS MMT
WHERE      MMT.ORGANIZATION_ID=86 --ORGANIZATION_ID
AND     MMT.TRANSACTION_TYPE_ID=17  --WIP Complition Return
AND     MMT.TRANSACTION_SOURCE_TYPE_ID=5  --Job or Schedule
AND     TRUNC(MMT.TRANSACTION_DATE) BETWEEN :start_date AND :end_date
GROUP BY MMT.INVENTORY_ITEM_ID,MMT.ORGANIZATION_ID)N
Where M.INVENTORY_ITEM_ID=N.INVENTORY_ITEM_ID(+)
AND     M.ORGANIZATION_ID=N.ORGANIZATION_ID(+)
Order By Item