EBS : How to execute Dynamic SQL in PLSQL

 Dynamic SQL in PLSQL

In PL/SQL the SQL statement is created as a string, which is then executed using the EXECUTE IMMEDIATE clause

declare
v_techhards varchar2(2000);
begin

for r_techhards in (select * from user_tables) loop

v_techhards:= 'delete '||r_techhards.table_name;

dbms_output.put_line(v_techhards);

execute immediate v_techhards;

end loop;
end;
/ 

=========================================================================

#Oracle SQL#Oracle PLSQL#Oracle Apps#Oracle Database#SQL #Query #Dynamic SQL #execute immediate #Oracle dynamic DDL

EBS : Important Oracle Database related SQL Query

  Important Oracle Database related SQL Query

• Query to get the name of the instance

select INSTANCE_NAME from v$instance;

• Query to get the User name of theDatabase

select USER_ID from ALL_USERS Order by 1

=========================================================================

#Oracle SQL#Oracle Apps#Oracle Database#SQL #Query #v$instance #ALL_USERS

EBS : OPM (Process Manufacturing) Important Query in r12

Query to get Formula wise All Resource in OPM

SELECT   gor.resources, crm.resource_desc, crd.nominal_cost,

                  NVL (gor.resource_usage, 1) resource_usage,

                  NVL (gor.process_qty, 1) process_qty

             FROM fm_rout_dtl frd,gmd_operation_activities goa,

                  gmd_operation_resources gor,

                  cr_rsrc_mst crm,

                  cm_rsrc_dtl crd

            WHERE frd.oprn_id = goa.oprn_id

              AND routing_id IN (

                     SELECT gr.routing_id

                       FROM mtl_system_items_kfv ic,

                            fm_matl_dtl dtl,

                            gmd_recipes gr

                      WHERE dtl.formula_id = x_formula_id

                        AND line_no = 1

                        AND line_type = 1

                        AND ic.organization_id = p_inv_org_id

                        AND ic.inventory_item_id = dtl.inventory_item_id

                        AND gr.formula_id = dtl.formula_id

                        AND ic.item_type not like '%TEA%' --added 28nov14 TEA Routing Resource will not appeared

                        )

              AND gor.oprn_line_id = goa.oprn_line_id

              AND crm.resources = gor.resources

              AND crm.resources = crd.resources

              AND crd.cost_type_id = p_cost_type_id

              AND period_id = p_period_id

         ORDER BY 1;

=========================================================================

#Oracle Apps#EBS #OPM #Process manufacturing #Formula #Resources 

EBS : How to identify the SQL_ID of a statement in Oracle Apps R12

How to identify the SQL_ID of a statement in Oracle Apps R12

1) Run the below SQL Query.

SELECT /* ORACLE SQL */ * FROM dual;

2) Run below query to get above ran SQL ID.

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
FROM v$sql
WHERE sql_text like 'SELECT /* ORACLE SQL */%'

------Here is the Output---------------

SQL_ID SQL_TEXT
------------- ----------------------------------------
0xzpkrs5gktjs SELECT /* ORACLE SQL */ * FROM dual

EBS : Add Attachment in Supplier Master OAF Page in Oracle Apps (r12)

How to add Attachments at Supplier and Supplier Site Level

Navigation:-

1) Adding attachment at supplier

•      Go to Payables Manager Responsibility (Any super User Responsibility)
•      Search for a particular Supplier
•      Go to Supplier Company Profile > Organization >Attachments > Add Attachment

2)  Adding attachment at supplier site level

•     Go to Payables Manager Responsibility  (Any super User Responsibility.
•     Search for a particular Supplier
•     Go to Supplier Address Book > Click on Manage Sites >Click on Identification Tab > Click on   Attachments  > Click on Add button '+' is Enable

***********************************************************************************

#Oracle Apps #EBS #OAF #Supplier Master Page 

Oracle : Useful SQL Query

 

SQL Query

1) generate Serial number in SQL Query

select row_number() over(partition by part_no order by part_no,decode(ORDER_TYPE_TEXT,'On Hand','01','02') from dual;

*************************************************************************************

#SQL Query #Oracle #Oracle Apps #Oracle #

EBS : Oracle Forms Compilation Scripts in Oracle Apps (r12)

How to compile forms in Oracle Apps

Forms Compilation scripts

frmcmp_batch module=/oraTECHHARDS/oracle/PERSONAL/fs1/EBSapps/appl/au/12.0.0/forms/US/XX_AR_INV_FORM.fmb module_type=form output_file=/oraTECHHARDS/oracle/PERSONAL/fs1/EBSapps/appl/ap/12.0.0/forms/US/XX_AR_INV_FORM.fmx userid=apps/apps batch=no compile_all=special 

Steps:-

1) Deploy the .fmb in AU_TOP 

2) Compile the forms using above scripts to generate .fmx in the Custom_TOP (like XX, AP, AR).

=========================================================================

#Oracle Forms #Oracle Apps #EBS #Forms Compilation

EBS : Useful AOL Query in Oracle Apps (r12)

Query to find Concurrent Request Details against input Concurrent Program

SELECT concurrent_program_name, fcr.*

  FROM fnd_concurrent_programs fcp, fnd_concurrent_requests fcr

 WHERE fcp.concurrent_program_id = fcr.concurrent_program_id

   AND fcp.concurrent_program_name = 'XX_TECHHARDS';

***********************************************************************************

#Oracle Apps #EBS #Concurrent Request #Concurrent Programs

#How to get concurrent Request Details

EBS : WHEN-TAB-PAGE-CHANGED change code in oracle forms in Oracle Apps (r12)

Oracle Forms Code for TAB WHEN-TAB-PAGE-CHANGED

DECLARE

   v_tp_nm   VARCHAR2 (30);

   v_tp_id   tab_page;

   v_tp_lb   VARCHAR2 (30);

BEGIN

   v_tp_nm := GET_CANVAS_PROPERTY ('TECHHARDS_TABS', topmost_tab_page);---TECHHARDS_TABS is your tab canvas

   v_tp_id := FIND_TAB_PAGE (v_tp_nm);   --this is tab page which you just had clicked to activate

   v_tp_lb := GET_TAB_PAGE_PROPERTY (v_tp_id, label);

                        --you get the lable for that particular tab page here

--here is to check the page name and associate your auto-query options--

   IF v_tp_lb LIKE 'TechHards WB%'

   THEN

      GO_BLOCK ('XX_TECHHARDS_KAB');

      EXECUTE_QUERY (no_validate);

   ELSIF v_tp_lb LIKE 'TechHards WB Pro%'

   THEN

      GO_BLOCK ('XX_TECHHARDS_PAB');

      EXECUTE_QUERY (no_validate);

   ELSIF v_tp_lb LIKE 'TechHards WB Free%'

   THEN

      GO_BLOCK ('XX_TECHHARDS_DAB');

      EXECUTE_QUERY (no_validate);

   ELSE

      NULL;

   END IF;

END;

=========================================================================

#Oracle Forms #Oracle Apps #EBS #Forms Trigger #WHEN-TAB-PAGE-CHANGED

EBS : Step to change supplier name prompt in AP Invoice Workflow in Oracle Apps (r12)

 Step to change supplier name prompt in AP Invoice Workflow

Go to below table and update the display name

before change

After Change

***********************************************************************************

#Oracle Workflow #Oracle Apps # EBS #WF Notifications 

Important WF Tables:-

WF_Notifications 

WF_Notification_attributes

Oracle : How to convert Column to Row in SQL in Oracle Apps (r12)

Convert Text (column) to Row in Oracle Apps

SELECT REGEXP_SUBSTR(p_inv_org_id,'[^,]+', 1, LEVEL) COL1 FROM DUAL

                               CONNECT BY LEVEL <= REGEXP_COUNT(p_inv_org_id, ',') + 1

===========================================================================

#SQL QUERY #Oracle Apps #EBS #Column to Row conversion #PLSQL #r12 #REGEXP