Oracle Reports : How to add Barcode in rtf Template Report

 How to add Barcode in rtf Template Report

1) Create form felds , change the font and write below syntax in the field properties.

2) Font uploading and mapping in xml publisher Administrator

WEBADI : How to add Multiple column in LOV

 How to add Multiple column in LOV WEBADI

1) Go to Components

2) Click on Update and modify as shown below. Click on apply.

table-column-alias=ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID

table-columns=ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID

table-headers=ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID

3) Same column mapping need to be provided on  description LOV section of Integrator Interface as shown below

description =ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID

3) Table Value set view

 

Output

Oracle APPS: AP : Query to get Document sequence Name and generate value

How to get document Sequence name

1) Write query to get db sequence name

SELECT seq.db_sequence_name

       -- INTO l_sequence_name

        FROM fnd_document_sequences seq, fnd_doc_sequence_assignments sa

       WHERE seq.doc_sequence_id = sa.doc_sequence_id

         AND sa.application_id = 200

         AND sa.category_code ='AP Invoice'-- :l_document_name

         AND (sa.method_code = 'A' )

         

2)  get sequence value using below query

    BEGIN

      l_query :=

           'select ' || l_sequence_name || '.nextval into :next_val from sys.dual';

      EXECUTE IMMEDIATE l_query

                   INTO l_doc_sequence_value;

    END;       

    

    return l_doc_sequence_value;

    

Oracle APPS : AP : Due Date Derivation Logic based on Payment Terms

 

1) Payment Terms

2) Special Calendars

Navigation:-  Below Screenshot

Table Name :-  AP_OTHER_PERIOD_TYPES, AP_OTHER_PERIODS

-------------------------------------Query--------------------------------------------

SELECT apt.NAME,atl.calendar, atl.fixed_date, atl.due_day_of_month, atl.due_days

  FROM ap_terms apt, ap_terms_lines atl

 WHERE apt.term_id = atl.term_id  

 AND apt.NAME in ('RTEST')

select * from  AP_OTHER_PERIOD_TYPES

where MODULE ='PAYMENT TERMS'

and period_type =:lv_payment_term

select due_date from  AP_OTHER_PERIODS

where MODULE ='PAYMENT TERMS'

and period_type =:lv_payment_term

and :p_invoice_date between start_date and end_date

--------------------------------------------------------------------------------------------------------------------------

Logic Explained to derive Due date based on Invoice Date and Payment Term

Oracle APPS : HRMS : Query

HRMS Query

1) How to fetch Employee Latest Grade?

SELECT paaf.full_name || ' ' || paaf.employee_number emp_name,

       paaf.person_id emp_id, pg.NAME grade

  FROM per_all_people_f paaf,

       per_all_assignments_f pa,

       per_grades pg,

       fnd_user fu

 WHERE paaf.person_id = pa.person_id

   AND paaf.effective_end_date IN (SELECT MAX (paafs.effective_end_date)

                                     FROM per_all_people_f paafs

                                    WHERE paafs.person_id = paaf.person_id)

   AND pa.effective_end_date IN (

          SELECT MAX (paaf.effective_end_date)

            FROM per_all_assignments_f paafs

           WHERE paafs.assignment_id = pa.assignment_id

             AND paafs.person_id = pa.person_id)

   AND pa.grade_id(+) = pg.grade_id

   AND pa.person_id = fu.employee_id

   AND fu.user_id = :UserId;

2) 

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)