Script to add employee as buyer in Purchasing

 DECLARE

   l_agent_id          NUMBER;

   l_employee_number   VARCHAR2 (20) := 120273;

   l_buyer_rowid       VARCHAR2 (100);

BEGIN

   BEGIN

      SELECT person_id

        INTO l_agent_id

        FROM apps.per_all_people_f f

       WHERE employee_number = l_employee_number;

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line (   'Unable to find Agent ID for '

                               || l_employee_number

                               || SQLERRM

                              );

   END;

   apps.po_agents_pkg.insert_row (x_rowid                    => l_buyer_rowid,

                                  x_agent_id                 => l_agent_id,

                                  x_last_update_date         => SYSDATE,

                                  x_last_updated_by          => 0,

                                  x_last_update_login        => 0,

                                  x_creation_date            => SYSDATE,

                                  x_created_by               => 0,

                                  x_location_id              => NULL,

                                  x_category_id              => NULL,

                                  x_authorization_limit      => NULL,

                                  x_start_date_active        => SYSDATE,

                                  x_end_date_active          => NULL,

                                  x_attribute_category       => NULL,

                                  x_attribute1               => NULL,

                                  x_attribute2               => NULL,

                                  x_attribute3               => NULL,

                                  x_attribute4               => NULL,

                                  x_attribute5               => NULL,

                                  x_attribute6               => NULL,

                                  x_attribute7               => NULL,

                                  x_attribute8               => NULL,

                                  x_attribute9               => NULL,

                                  x_attribute10              => NULL,

                                  x_attribute11              => NULL,

                                  x_attribute12              => NULL,

                                  x_attribute13              => NULL,

                                  x_attribute14              => NULL,

                                  x_attribute15              => NULL

                                 );

   COMMIT;

   DBMS_OUTPUT.put_line ('Setup as a buyer ' || l_employee_number);

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Error in Setting up as Buyer ' || SQLERRM);

END;

Force LTR to print right aligned in BI Report

 <fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?format-number:<column name>;'###,###,##0.00'?></fo:bidi-override>

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"></fo:bidi-override>

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?TASK_NUMBER?></fo:bidi-override>

Download Concurrent Program LDT File

 Download Concurrent Programs:

FNDLOAD apps/matrix O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Add Responsibility to the user via Script

 

DECLARE

   v_user_name             VARCHAR2 (30)  := '&user_name';

   v_responsibility_name   VARCHAR2 (100) := '&responsibility_name';

   v_application_name      VARCHAR2 (100) := NULL;

   v_responsibility_key    VARCHAR2 (100) := NULL;

   v_security_group        VARCHAR2 (100) := NULL;

   v_description           VARCHAR2 (100) := NULL;

BEGIN

   SELECT fa.application_short_name, fr.responsibility_key,

          fsg.security_group_key, frt.description

     INTO v_application_name, v_responsibility_key,

          v_security_group, v_description

     FROM apps.fnd_responsibility fr,

          fnd_application fa,

          fnd_security_groups fsg,

          fnd_responsibility_tl frt

    WHERE frt.responsibility_name = v_responsibility_name

      AND frt.LANGUAGE = USERENV ('LANG')

      AND frt.responsibility_id = fr.responsibility_id

      AND fr.application_id = fa.application_id

      AND fr.data_group_id = fsg.security_group_id;

   fnd_user_pkg.addresp (username            => v_user_name,

                         resp_app            => v_application_name,

                         resp_key            => v_responsibility_key,

                         security_group      => v_security_group,

                         description         => v_description,

                         start_date          => SYSDATE,

                         end_date            => NULL

                        );

   COMMIT;

   DBMS_OUTPUT.put_line(   'Responsiblity '

                         || v_responsibility_name

                         || ' is attached to the user '

                         || v_user_name

                         || ' Successfully'

                        );

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line

                         (   'Unable to attach responsibility to user due to'

                          || SQLCODE

                          || ' '

                          || SUBSTR (SQLERRM, 1, 100)

                         );

END;

/

Materialized Views in 12.2.X

 There are two steps to create Materialized Views in 12.2

1. Create Logical View
2. Upgrade to Materialized View

Create Logical View
CREATE OR REPLACE <Custom Schema>.XXBLOG_CREATE_LOGICAL_MV#
AS
<SELECT Statement>;


Upgrade Logical View to Materialized View
exec ad_zd_mview.upgrade('<custom schema>', 'XXBLOG_CREATE_LOGICAL_MV');

How to check if the Materialized Views got created properly in 12.2
SELECT object_type, object_name
FROM dba_objects
WHERE object_name like 'XXBLOG_CREATE_LOGICAL_MV%';

-- 4 rows Selected

These 4 Rows get created in the process for

1. Logical View (Ending with '#') in Custom Schema
2. Materialized View in Custom Schema
3. Table in Custom Schema
4. Synonym in APPS

Possible Issue:
Create mview gives the following error when the mview definition query refers to editioned function:
ORA-38818: illegal reference to editioned object %.%
Possible Solution:
Re-create the referenced pl/sql objects in a schema where editioning is not turned on
If the referenced objects are be placed in a non editioned schema that is different from the schema where the mview is to be created, then grant execute permission on the package to the mview owner.References:
12.2 E-Business Suite Upgrade Of Custom Materialized Views Fails Due To Changes Required For 12.2 ADOP Editioned Objects (Doc ID 2205375.1)
ORA-38818: Illegal Reference To Editioned Object When Creating Materialized View (Doc ID 1556379.1)

How to Refresh Materialized Views:
exec DBMS_MVIEW.REFRESH('XXBLOG_HOWTOREFRESH_MV', method => '?',  atomic_refresh => FALSE, out_of_place => TRUE);


Parameters in detail:

Refresh Method	Parameter	Description
COMPLETE	C	Refreshes by recalculating the defining query of the materialized view.
FAST	F	Refreshes by incrementally applying changes to the materialized view.
		For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.
FAST_PCT	P	Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.
FORCE	?	Attempts a fast refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE.

Out-of-place refresh: It uses outside tables during refresh as opposed to the existing "in-place" refresh that directly applies changes to the materialized view container table. Out-of-place refresh is particularly effective when handling situations with large amounts of data changes, where conventional DML statements do not scale well.

PRC: Generate Revenue Accounting Events(PATTGL) ends with ORA-01732 Error

When working with PA Accounting, the standard program "PRC: Generate Revenue Accounting Events" failed with the Error - ORA-01732

To fix the above error, we have 2 recommendations

Recommendation 1:

Update the init.ora parameter to a value of 'False':
This can be done by editing the init.ora file which typically requires bouncing the database when completed.
(Option 1 requires DB bounce)

Recommendation 2:

Modify the session by running this SQL -- alter system set optimizer_secure_view_merging = false;
(NO DATABASE BOUNCE REQUIRED for Option 2)

Query to check the DB Parameter value,

select * from v$parameter where name like '%optimizer_secure_view_merging%';

Value: FALSE

Journal entry cannot be reversed as its not transferred to GL (Error in xla_DataFixes_Pub.Reverse_Journal_entries)

When working with Accounts Payable Trial Balance Report (ATBR) and you're getting the error while reversing the journal though Online Accounting then follow the below steps,

Error:

Journal entry cannot be reversed as its not transferred to General Ledger.
"-: XLA-95103: An internal error occurred. Please inform your system administrator or support representative that:

An internal error has occurred in the program xla.plsql.xla_datafixes_pub.reverse_journal_entries. Journal entry cannot be reversed as its not transferred to General Ledger.."

l_Return_Status = U

Undo_Accounting : Error in xla_DataFixes_Pub.Reverse_Journal_entries
:FND?FND_AS_UNEXPECTED_ERROR?N?PKG_NAME?xla.plsql.xla_datafixes_pub?N?PROCEDURE_NAME?reverse_journal_entries?N?ERROR_TEXT?ORA-20001: -: XLA-95103: An internal error occurred. Please inform your system administrator or support representative 

Step 1 : Check the current version of the file xlajedfp.pkb using the below string

strings -a $XLA_TOP/patch/115/sql/xlajedfp.pkb |grep '$Header'

(Login to APP Tier and execute the command under the Application Manager Access)

If it is less then the header version /* $Header: xlajedfp.pkb 120.14.12020000.11 2015/01/19 07:07:32 tasrivas ship $ */ then 

Step 2: kindly apply the below patch based on the EBS version 

For R12.1.x: xlajedfp.pkb 120.1.12010000.25
For R12.2.x: xlajedfp.pkb 120.14.12020000.11

Note: Before applying the patch kindly verify the patch is applied or not in your database using the query,

select ad_patch.is_patch_applied('R12',-1,19793092) from dual; 
NOT_APPLIED

Step 3: After Patch applied, re-confirm the header string and make sure its matches with updated one,

strings -a $XLA_TOP/patch/115/sql/xlajedfp.pkb |grep '$Header'

After patch applied, the patch verification will return the code EXPLICIT

select ad_patch.is_patch_applied('R12',-1,19793092) from dual; 
EXPLICIT