Oracle Apps Query : How to delete Concurrent Program through API

 begin

fnd_program.delete_program('XX_EMP_COST_ITAX','Payroll');

fnd_program.delete_executable('XX_EMP_COST_ITAX','Payroll');

end;

Oracle : SQL Query : Special Character, Substr

 

1) How to remove Special Characters

"REPLACE(REPLACE(TRIM(REGEXP_REPLACE(:FREIGHT_TERMS_CODE,'[^a-z_A-Z ]')),CHR(11),''),CHR(13),'')" 

2) How to remove last 2 character from String

 substr( xpem.period_name,1,length(xpem.period_name)-2)

3) Query to get Sunday?

select

(next_day(last_day(trunc(sysdate)),'?')-7

-next_day(trunc(sysdate,'mm')-1,'?'))/7+1

as "sundays"

from dual;

SELECT TO_CHAR(dat,'DD-MON-RRRR')

FROM

    (SELECT TRUNC(SYSDATE,'MM') + level - 1 dat FROM dual

    connect by level <= LAST_DAY(TRUNC(SYSDATE)) - TRUNC(SYSDATE,'MM') + 1)

    WHERE TO_CHAR(dat,'DY') = 'SUN'

 GROUP BY TO_CHAR(dat,'MON-RRRR')

  

/* Formatted on 2018/04/06 15:18 (Formatter Plus v4.8.8) */

SELECT     TRUNC (:p_to_dat, 'MM') + LEVEL - 1 dat,TO_CHAR (TRUNC (:p_to_dat, 'MM') + LEVEL - 1, 'DY') days,

           CASE

              WHEN TO_CHAR (TRUNC (:p_to_dat, 'MM') + LEVEL - 1, 'DY') =

                                                                    'SUN'

                 THEN 'Holiday'

           END abc

      FROM DUAL

CONNECT BY LEVEL <= LAST_DAY (TRUNC (:p_to_dat)) - TRUNC (:p_to_dat, 'MM') + 1

------------------------------------------------------End Query to get sunday--------------------

Oracle Apps Technical Interview Question : Workflow

 

Workflow

1)      How to Migrate Workflow  ?

2)      What is the Standard Process to Customize Seeded Workflow?

3)      How to Customize Seeded Workflow that after patch it wont get remove.

4)      How to Analyse Workflow Errors?

5)      How to Launch Workflow from PLSQL?

6)      Error Tables for Workflow?

7)      Have u ever Customized Seeded Workflow and How?

8)      How to hide Action History from Notifcations Page?

Ans:- Need to set null to #History Attributes

Oracle Apps Technical Interview Question : PLSQL

 Oracle Apps Technical Interview Question : PLSQL

1)      What is Exception? User Defined Exception

2)      Can we write commit in Trigger? How ?

3)      Is commit Required to submit request from Trigger?

4)      Commit/RollBack,  Save Point?

5)      Difference between VARRAY and Record Type? Advantage of both?

6)      What is object types?

7)      What are the attributes of PLSQL Table.

Ans:- .extend, .count, .delete, .prior, .next, .first, .last

8)      What is the Difference between ROWType and Record Type?

9)      What is Bulk Collect and its exception types, loop

10)   What happen when Expception “When Others Then” placed above “When No_Data_Found”?

Ans:- It will Raise Exception.

EBS : OPM : API to update Batch Step Qty

 

/* Formatted on 2021/05/27 20:12 (Formatter Plus v4.8.8) */

DECLARE

   x_batch_step_rec     gme_batch_steps%ROWTYPE;

   p_batch_step_rec     gme_batch_steps%ROWTYPE;

   p_validation_level   NUMBER                    DEFAULT 100;

   x_message_count      NUMBER;

   x_return_status      VARCHAR2 (240);

   x_message_list       VARCHAR2 (240);

BEGIN

   fnd_global.apps_initialize (1536, 23326, 553);

   p_batch_step_rec.batchstep_no := 11;

   p_batch_step_rec.oprn_id := 74087;

   p_batch_step_rec.actual_start_date := '01-MAY-21';

   p_batch_step_rec.actual_cmplt_date := '03-MAY-21';

--   p_batch_step_rec.BATCH_ID:=466340;

   p_batch_step_rec.batchstep_id := 334733;

   p_batch_step_rec.actual_step_qty := 3;

   gme_api_pub.update_batchstep_qty

                                   (p_api_version           => 1.0,

                                    p_validation_level      => p_validation_level,

                                    p_init_msg_list         => 'F',

                                    p_commit                => 'T',

                                    p_org_code              => 'KT',

                                    p_batch_no              => '20004203'

                                                                          --,P_ACTUAL_START_DATE='3-MAY-2021'

                                                                         -- ,p_add       =>'N'

   ,

                                    p_batch_step_rec        => p_batch_step_rec,

                                    x_batch_step_rec        => x_batch_step_rec,

                                    x_message_count         => x_message_count,

                                    x_message_list          => x_message_list,

                                    x_return_status         => x_return_status

                                   );

   COMMIT;

   DBMS_OUTPUT.put_line (   x_batch_step_rec.actual_cmplt_date

                         || ','

                         || x_batch_step_rec.actual_start_date

                        );

   DBMS_OUTPUT.put_line (   'x_return_status : '

                         || x_return_status

                         || ' x_message_list: '

                         || x_message_list

                        );

   IF x_message_list IS NULL

   THEN

      DBMS_OUTPUT.put_line (SQLERRM);

   END IF;

END;

EBS Metalink Note

 

1) How to Troubleshoot Receivables API Issues (Doc ID 784317.1) 

Oracle Apps Query : Form Personalization related query

 

1) Form Personlization header table

select  rule_key,function_name,sequence ,rule_type,enabled,a.* 

from fnd_form_custom_rules a 

where form_name ='APXINWKB'

2) Form Personalization Action Table

select id, rule_key,function_name,a.sequence ,rule_type,TRIGGER_EVENT, trigger_object,b.*  

from  fnd_form_custom_rules a  ,FND_FORM_CUSTOM_ACTIONS b

where a.ID = b.RULE_ID 

and form_name ='APXINWKB'

Windows File rename: How to rename bulk file through CMD mode

 

Windows File rename: How to rename bulk file through CMD mode

1) Open CMD and go to specific directory

2) type below command to display list of files

dir /b

3) Select all files and copy,

4) Paste the file in Excel and build the dos rename syntax as shown below

5) Now go to the cmd and paste the rename syntax

6) Multiple bulk files renamed

OPM : Query to get Item wise cost in OPM

 OPM : Query to get Item wise cost in OPM

SELECT   ccdv.inventory_item_id, ccdv.organization_id, ccm.cmpnt_group,

         SUM (NVL (cmpnt_cost, 0)) costs

    --ccm.cost_cmpntcls_code,

FROM     cm_cmpt_dtl_vw ccdv,

         cm_cldr_mst_v ccmv,

         cm_cmpt_mst ccm,

         mtl_parameters mp,

         org_organization_definitions ood,

         gl_code_combinations gcc,

         gmf_fiscal_policies gfp

   WHERE 1 = 1

     AND ccdv.period_id = ccmv.period_id

     AND TRUNC (ccmv.start_date) >= :p_start_date  

     AND TRUNC (ccmv.end_date) <= :p_end_date   

     AND ccmv.calendar_code = :p_calendar_code

     AND ccdv.cost_cmpntcls_id = ccm.cost_cmpntcls_id

     AND ccmv.cost_type_id = gfp.cost_type_id

     AND ccdv.organization_id = mp.organization_id

     AND mp.organization_id = ood.organization_id

     AND mp.material_account = gcc.code_combination_id

     AND gcc.segment1 BETWEEN :p_child_low AND :p_child_high

     AND (:p_business_unit IS NULL OR gcc.segment2 = :p_business_unit)

     AND ood.legal_entity = gfp.legal_entity_id

GROUP BY ccdv.inventory_item_id, ccdv.organization_id, ccm.cmpnt_group

OAF : Migration Command

 OAF : Migration Command

java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/xxshp/oracle/apps/xx/transaction/webui/ReqCreate.xml -rootdir $JAVA_TOP -userId 1 -username $APPS_LOGIN -password $APPS_PWD -dbconnection "(description = (address_list = (address = (community = tcp.world)(protocol = tcp)(host =$HOST_NAME)(port = $ORACLE_PORT)))(connect_data = (sid =$ORACLE_SID)))"

How to export OAF Page java oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/fnd/wf/worklist/webui/NotifReassignPG -rootdir /a01/appldev/apps/apps_st/appl -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.0.1.198)(PORT=1500))(CONNECT_DATA=(SID=DEV)))"

TAR File : How to Create TAR FILE