Specified pseudocolumn or operator not allowed here. in package PO_DOCUMENT_CHECKS_PVT

While approving the requisition the follow error may occur.

"Specified pseudocolumn or operator not allowed here. in package PO_DOCUMENT_CHECKS_PVT"

Issue was caused by conversion rate missing for the conversion between 2 currencies

Reference : Error ORA-00976 While Approving Approving Internal Purchase Requisition (Doc ID 2321227.1)

Simple script to convert a string into records

Script:

Output:

NUMBERS
--------------------
123
124
125
126

Script to Add Responsibilities

SET serveroutput ON
DECLARE
  ln_user_id NUMBER;
  ln_resp_id NUMBER;
  ln_app_id  NUMBER;
  CURSOR c1
  IS
    SELECT responsibility_id,
      application_id,
      responsibility_name
    FROM fnd_responsibility_tl
    WHERE responsibility_name   IN ( 'Application Developer', 'Functional Administrator','System Administrator')
    AND responsibility_name NOT IN
      ( SELECT DISTINCT rtl.responsibility_name
      FROM apps.fnd_compiled_menu_functions cmf,
        apps.fnd_form_functions ff,
        apps.fnd_form_functions_tl ffl,
        apps.fnd_responsibility r,
        apps.fnd_responsibility_tl rtl,
        apps.fnd_user_resp_groups urg,
        apps.fnd_user u
      WHERE cmf.function_id     = ff.function_id
      AND r.menu_id             = cmf.menu_id
      AND urg.responsibility_id = r.responsibility_id
      AND rtl.responsibility_id = r.responsibility_id
      AND cmf.grant_flag        = 'Y'
      AND r.application_id      = urg.responsibility_application_id
      AND u.user_id             = urg.user_id
      AND u.user_name           = 'KAARTHIB'
      AND ff.function_id        = ffl.function_id
      );
BEGIN
  SELECT user_id INTO ln_user_id FROM fnd_user WHERE user_name = 'KAARTHIB';
  FOR i IN c1
  LOOP
    fnd_user_resp_groups_api.insert_assignment (ln_user_id, i.responsibility_id, i.application_id, NULL, SYSDATE, NULL, NULL );
  END LOOP;
  COMMIT;
END;
/

Script to update Application User Password

SET SERVEROUTPUT ON
DECLARE
  v_user_name    VARCHAR2(30):=upper('USER');
  v_new_password VARCHAR2(30):='PASSWORD';
  v_status       BOOLEAN;
BEGIN
  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name, newpassword => v_new_password );
  IF v_status =true THEN
    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
    COMMIT;
  ELSE
    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
  END IF;
END;
/

FRM-10102: Cannot attach PL/SQL library

Hi All , Many of you might have faced the "FRM-10102: Cannot attach PL/SQL library" issue while opening a oracle form locally in your machine.To avoid the issue (Forms 10g) we should perform the following steps.

Assuming you stored all your FMB and PLL files in D:\resource folder (copied from your server).

Add the path D:\resource in the FORMS_PATH registry entry.
Or
Create a Environment variable named FORMS_PATH and add the value D:\resource

You might still face the issue if you didn't download the complete resource folder from your server.

Copying the complete resource folder from server is a time consuming process. Instead copy the required PLL which was shown in the error window. After closing the PLL missing error window, try to manually add any one PLL once you open the form.It will start asking the basic PLL files ( like APPCORE,JL,JA,FNDSQF,etc) . It will be about 30 to 40 files which you need to download and move to the resource folder. After that close the form and reopen it. Now it will open without any error.

Please update your comments if you still face any issues even after performing the above steps.

ORA-04091: table XXX is mutating, trigger/function may not see it

Let me explain how Mutating error happens with  an example

Let us take a business scenarios. Assume that there is a table with ID column , Transaction Date , Transaction Type and Transaction Value.

The business need is, if any record inserted into the table with Transaction Type as "Invoice" then it should automatically insert another record with Transaction Type as "Tax" with Transaction value as 10% of the Invoice Transaction Value.

Below is the script initially built for the requirement

DROP TABLE mytransactions;

CREATE TABLE mytransactions
  (
    id     NUMBER,
    tdate  DATE ,
    ttype  VARCHAR2(20) ,
    tvalue NUMBER
  );

DROP TRIGGER mytransactions_ai;

CREATE OR REPLACE TRIGGER mytransactions_ai AFTER
  INSERT ON mytransactions FOR EACH row
BEGIN
    IF (:new.ttype='Invoice') THEN
      INSERT INTO mytransactions VALUES
        (:new.id,sysdate,'Tax',:new.tvalue*0.1 );
      dbms_output.put_line('Tax Inserted');
    END IF;
END;
/

INSERT INTO mytransactions VALUES (1,sysdate,'Invoice',100);

When we try to execute the above insert script, we might end up with the below error.

ORA-04091: table MYTRANSACTIONS is mutating, trigger/function may not see it
ORA-06512: at "MYTRANSACTIONS_AI", line 3
ORA-04088: error during execution of trigger 'MYTRANSACTIONS_AI'

To overcome the mutating error, we should use AUTONOMOUS_TRANSACTION in the trigger.

CREATE OR REPLACE TRIGGER mytransactions_ai AFTER
  INSERT ON mytransactions FOR EACH row
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    IF (:new.ttype='Invoice') THEN
      INSERT INTO mytransactions VALUES
        (:new.id,sysdate,'Tax',:new.tvalue*0.1 );
      dbms_output.put_line('Tax Inserted');
      COMMIT;
    END IF;
END;
/

After modifying the trigger we should able to insert the records successfully.


        ID TDATE     TTYPE                    TVALUE
---------- --------- -------------------- ----------
         1 11-JAN-17 Invoice                     100
         1 11-JAN-17 Tax                          10

Incompatibility Setting for a Concurrent Program

- Incompatibility settings are used to prevent the program running parallel
- This can be set at Global or at Domain Level
- When we set it at Global, the program cannot be run parallel at any point of time. It is taken care by the Conflict Manager.
- To set the incompatibility at Domain level, we should understand about conflict domain.

Conflict Domain is nothing but a logic partition of the data available in the database. The data can be partitioned at many level. For example, SOB, OU or INV level.

For example, if the Program needs to be run parallel across the operating unit but sequential within the operating unit then we need to define the Conflict Domain

Steps to define Conflict Domain at Operating Unit level.
1. Create a concurrent program
2. Add parameters
3. Select one parameter as Conflict Domain in the setup (Available in the parameter window itself). The parameter should be defaulted from profile "MO: Operating Unit"
4. Set the incompatibility for the program for itself at Domain level

 By following the above setup, we can achieve the requirement.