Link between OE_ORDER_HEADERS_ALL and RA_CUSTOMER_TRX_ALL

RA_CUSTOMER_TRX_ALL will have header information

INTERFACE_HEADER_CONTEXT = 'ORDER ENTRY'
INTERFACE_HEADER_ATTRIBUTE1 = ORDER_NUMBER

RA_CUSTOMER_TRX_LINES_ALL will have invoice lines

INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
INTERFACE_LINE_ATTRIBUTE1 = ORDER_NUMBER
INTERFACE_LINE_ATTRIBUTE6 = OE_ORDER_LINES_ALL.LINE_ID
INTERFACE_LINE_ATTRIBUTE3 = WSH_NEW_DELIVERIES.DELIVERY_ID

API to change the password of an Application User

Use the below code to change the password for all the application users. I have used this to change passwords of all application users who has been created after 01-Jan-2015.

To change password for single user remove the for loop and un-comment the local variables. Amend as per your requirement.

DECLARE
--v_user_name varchar2(30):=upper('&Enter_User_Name');
--v_new_password varchar2(30):='&Enter_New_Password';
   v_status   BOOLEAN;

   CURSOR c1
   IS
      SELECT user_name
        FROM fnd_user
       WHERE TRUNC (creation_date) > '01-Jan-2015';

BEGIN
   FOR i IN c1
   LOOP
      v_status :=
         fnd_user_pkg.changepassword (username         => i.user_name, --v_user_name
                                      newpassword      => 'newpassword' --v_new_password
                                     );

      IF v_status = TRUE
      THEN
         DBMS_OUTPUT.put_line
                          (   'The password reset successfully for the User:'
                           || i.user_name
                          );
         COMMIT;
      ELSE
         DBMS_OUTPUT.put_line (   'Unable to reset password due to'
                               || SQLCODE
                               || ' '
                               || SUBSTR (SQLERRM, 1, 100)
                              );
         ROLLBACK;

      END IF;

   END LOOP;

END;

How to get Concurrent Program Output file and Log file from Database.

This post will guide you how to get concurrent program log files and output files from database. As you are aware whenever you submit concurrent program , after successful completion of it, Two files will be generated  which are nothing but LOG files and OUTPUT files. We can get those files by calling function  FND_WEBFILE.GET_URL

This function will return output value which is nothing but the  Link for the Log file or Output File , So only thing we need to do is to just paste the link in browser to view Log File or Output file. During this activity you don't need to be login on to the Oracle Application Window

Below code will help you out in Generating Log file , Pass request id  as  input parameter'

SET SERVEROUTPUT ON

DECLARE
   l_request_id   NUMBER          := :p_req_id;             -- The request id
   l_two_task     VARCHAR2 (256);
   l_gwyuid       VARCHAR2 (256);
   l_url          VARCHAR2 (1024);
BEGIN
   -- Get the value of the profile option named, Gateway User ID (GWYUID)
   --- l_gwyuid := fnd_profile.VALUE ('APPLSYSPUB/PUB');
   SELECT profile_option_value
     INTO l_gwyuid
     FROM fnd_profile_options o, fnd_profile_option_values ov
    WHERE profile_option_name = 'GWYUID'
      AND o.application_id = ov.application_id
      AND o.profile_option_id = ov.profile_option_id;

   -- Get the value of the profile option named, Two Task(TWO_TASK)
   SELECT profile_option_value
     INTO l_two_task
     FROM fnd_profile_options o, fnd_profile_option_values ov
    WHERE profile_option_name = 'TWO_TASK'
      AND o.application_id = ov.application_id
      AND o.profile_option_id = ov.profile_option_id;

   l_url :=
      fnd_webfile.get_url
         (file_type        => fnd_webfile.request_log,
                          -- for log file. Use request_out to view output file
          ID               => l_request_id,
          gwyuid           => l_gwyuid,
          two_task         => l_two_task,
          expire_time      => 500                       -- minutes, security!.
         );
   DBMS_OUTPUT.put_line (l_url);
END;

Output will be one link , Just paste the output in Browser and you will be able to view Log file output.


Below code will help you out in Generating Out file for the specific Concurrent request , Pass request id as input parameter

SET SERVEROUTPUT ON

DECLARE
   l_request_id   NUMBER := :P_REQ_ID;                       -- The request id
   l_two_task     VARCHAR2 (256);
   l_gwyuid       VARCHAR2 (256);
   l_url          VARCHAR2 (1024);
BEGIN
   -- Get the value of the profile option named, Gateway User ID (GWYUID)
   --- l_gwyuid := fnd_profile.VALUE ('APPLSYSPUB/PUB');

   SELECT   profile_option_value
     INTO   l_gwyuid
     FROM   fnd_profile_options o, fnd_profile_option_values ov
    WHERE       profile_option_name = 'GWYUID'
            AND o.application_id = ov.application_id
            AND o.profile_option_id = ov.profile_option_id;


   -- Get the value of the profile option named, Two Task(TWO_TASK)

   SELECT   profile_option_value
     INTO   l_two_task
     FROM   fnd_profile_options o, fnd_profile_option_values ov
    WHERE       profile_option_name = 'TWO_TASK'
            AND o.application_id = ov.application_id
            AND o.profile_option_id = ov.profile_option_id;


   l_url :=
      fnd_webfile.get_url (file_type     => fnd_webfile.request_out, -- for out file
                           ID            => l_request_id,
                           gwyuid        => l_gwyuid,
                           two_task      => l_two_task,
                           expire_time   => 500-- minutes, security!.
                           );

   DBMS_OUTPUT.put_line (l_url);
END;

Output will be one link , Just paste the output in Browser and you will be able to view output file .

Sample Api Code For Ar_Receipt_Api_Pub.Apply

DECLARE
   l_return_status   VARCHAR2 (1);
   l_msg_count       NUMBER;
   l_msg_data        VARCHAR2 (240);
   l_count           NUMBER;
   l_msg_data_out    VARCHAR2 (240);
   l_mesg            VARCHAR2 (240);
   p_count           NUMBER;
   l_rec_num         VARCHAR2 (100);
   l_trx_num         VARCHAR2 (100);
BEGIN
   mo_global.set_policy_context ('S', 82);                    -- your org id
   mo_global.init ('AR');
   ar_receipt_api_pub.APPLY
                           (p_api_version           => 1.0,
                            p_init_msg_list         => fnd_api.g_true,
                            p_commit                => fnd_api.g_true,
                            p_validation_level      => fnd_api.g_valid_level_full,
                            p_cash_receipt_id       => 98654,
                                                        ---
                            p_customer_trx_id       => 87654,
                                                         ----
                            p_org_id                => 82,        -->
                            x_return_status         => l_return_status,
                            x_msg_count             => l_msg_count,
                            x_msg_data              => l_msg_data
                           );
   DBMS_OUTPUT.put_line ('Status ' || l_return_status);
   DBMS_OUTPUT.put_line ('Message count ' || l_msg_count);

   IF l_msg_count = 1
   THEN
      DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);
   ELSIF l_msg_count > 1
   THEN
      LOOP
         p_count := p_count + 1;
         l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

         IF l_msg_data IS NULL
         THEN
            EXIT;
         END IF;

         DBMS_OUTPUT.put_line ('Message' || p_count || '.' || l_msg_data);
      END LOOP;
   END IF;
END;

ORA-01791: not a SELECTed expression during valeset creation

I have come across this error while i was trying to use a select statement in the table column while creating the Table value set.I used the below sql statement

SELECT DISTINCT XIH.BATCH_NO FROM XXMCNG_IREQ_HEADERS XIH ORDER BY XIH.REQ_DATE;

Cause : There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.

Action :
Remove the order by clause from the SQL statement.
(or)
create a view with the same query and the call the view in the Table column while creating the Table value set.

Note :
=====
Restrictions on the ORDER BY Clause.The following restrictions apply to the ORDER BY clause:

•If you have specified the DISTINCT operator in the statement,then this clause cannot refer to columns unless they appear in the select list.
•An order_by_clause can contain no more than 255 expressions.
•You cannot order by a LOB, LONG, or LONG RAW column, nested table, or varray.
•If you specify a group_by_clause in the same statement, then this order_by_clause is restricted to the following expressions:

◦Constants
◦Aggregate functions
◦Analytic functions
◦The functions USER, UID, and SYSDATE
◦Expressions identical to those in the group_by_clause
◦Expressions comprising the preceding expressions that evaluate to the same value for all rows in a group.

 Also See : Creating Table Valuesets from backend in Oracle Applications.

Challa.