Defining Link Stage for Request Set

Hi,

To trigger one program from another in a set up of Request Set, Link stage plays a Vital role.

One need to mention the excutability depending on 1st program.

For example: If 1st program completes successfully or completes in Warning or Errors Out , 2nd program should trigger appropriately.

It can be setup in
Request set-->> Link Stage step.

Oracle EDI gateway

List of Available EDI Transations.

ASC X12	EDIFACT	Document ID	Description
Inbound Transactions
810	INVOIC	INI	Inbound Invoice
832	PRICAT	CATI	Inbound Price / Sales Catalog
843	QUOTES	RRQI	Inbound Response to Request for Quote
850	ORDERS	POI	Inbound Purchase Order
856	DESADV	ASNI	Inbound Ship Notice / Manifest
857	No equivalent	SBNI	Inbound Shipping and Billing Notice
Outbound Transactions
824	APERAK	ADVO	Outbound Application Advice
810	INVOIC	INO	Outbound Invoice
820	PAYORD / REMADV	PYO	Outbound Payment Order / Remittance Advice
830	DELFOR	SPSO	Outbound Planning Schedule
862	DELJIT	SSSO	Outbound Shipping Schedule
850	ORDERS	POO	Outbound Purchase Order
860	ORDCHG	POCO	Outbound Purchase Order Change Request
856	DESADV	DSNO	Outbound Ship Notice / Manifest

OAF Page Deployment in EBS R12 Server

OAF Page Deployment in R12

File deployment in OAF follows MVC framework as

·       Model: Entity Objects (EO), View Objects (VO), Application Module (AM), View Links (VL), Association Objects (AO)

·       View (MDS i.e. needs to deploy these files in DB server): Page (PG), Regions (RN)

·       Controller: All CO files

Types of files available in OAF from source code point of view: .java, .xml

Application server only recognizes .class file i.e. compiled version of .java files

.XML files -> (MDS) Metadata files

From Local directory, we have to move .class + .xml files to Application server at $JAVA_TOP

Location of files in OAF development

Source Code location	Compiled file location
$JDEV_USER_HOME/myclasses (.java, .xml files)	$JDEV_USER_HOME/myprojects (.class, .xml files)

$JDEV_USER_HOME: Environment variable set for JDeveloper to work.

Steps for deployment:

1.       Copy all the files from local directory to application Server in $JAVA_TOP.

Echo $JAVA_TOP in putty, get the path and open in Server (Winscp path).

2.       Keep the $JDEV_USER_HOME/myclasses folder at left side in winscp and $JAVA_TOP at right side.

3.       Please ensure all the dependent files are kept in sync to migrate in Server.

4.       Copy your folder from Local to JAVA_TOP in Binary Format.

5.       Keep all the xxxPG.xml files in MDS layer i.e. DB Server then only these files will be read reason being View Layer is part of MDS files.

6.       To Achieve above step 5, Go to jdevbin/oaext/bin folder in Jdeveloper Folder directory. Open Command Prompt (CMD) and navigate this whole path in CMD like below.

7.       Give a command ‘Import’ from this location Enter and copy the text from the Field: Example1 in Notepad. Purpose of this command is to deploy your View Component i.e. page.xml in MDS i.e. Database.

8.       Copy the Example1 text and replace with your structure like below.

Example1: import d:\jdeveloper\jdev\myprojects\pages\mypage.xml -rootdir d:\jdeveloper\jdev\myprojects -userId 1 -rootPackage /oracle/apps/ak -username jdr -password jdr -dbconnection "(description = (address_list = (address = (community = tcp.world)(protocol = tcp)(host =incq027bc.idc.oracle.com)(port = 1521)))(connect_data = (sid = des9i2)))" -jdk13

Import C:\A_Shobhit\Donaldson\Development\Development\OAF\p9879989_R12_GENERIC\jdevhome\jdev\myclasses\xyz\oracle\apps\fnd\webui\EmployeeInfoPG.xml -rootdir C:\A_Shobhit\Donaldson\Development\Development\OAF\p9879989_R12_GENERIC\jdevhome\jdev\myclasses -username apps -password apps -dbconnection "(description = (address_list = (address = (community = tcp.world)(protocol = tcp)(host = rdcdxerpdb01.donaldson.com)(port = 1521)))(connect_data = (sid = dev55)))"

Change Your instance specific information, like -rootdir, remove userid 1 -rootpackage, username, password, host and Sid.

Copy this import command and run in CMD again.

This will import your structure.

Importing file "C:\A_Shobhit\Donaldson\Development\Development\OAF\p9879989_R12_GENERIC\jdevhome\jdev\myclasses\xyz\oracle\apps\fnd\webui\EmployeeInfoPG.xml" as "/xyz/oracle/apps/fnd/webui/EmployeeInfoPG".

Import command should deploy View component i.e. Page/Region in MDS or Database.

9.       Let’s verify this in DB tables if import command worked successfully or not.

Jdr_paths, jdr_components, jdr_attributes

SELECT * FROM jdr_paths where path_name ='EmployeeInfoPG';--- Hold information about Page and top Level   

                                                                                                                          structure

SELECT * FROM jdr_components where comp_docid=179149; -- Holds info about all the component in page

Select * from jdr_attributes where att_comp_docid=179149; -- Hold info about the attribute of each component.

10.    Now it’s time to register the OAF file in Oracle Application

 Login in Oracle Apps à Application Developer responsibilityà Applicationà Function

Provide function and User Function name any relevant value.

Select “SSWA jsp function” as Type in Property Tab.

Get the OAF Page URL by running Page from Jdeveloper or its the same string generated in Step 8 above and deployed in DB. “/xyz/oracle/apps/fnd/webui/EmployeeInfoPG”, Suffix it with OA.jsp?page=

OA.jsp?page=/xyz/oracle/apps/fnd/webui/EmployeeInfoPG and provide in Web HTML Tab in HTML Call.

11.   Once Function is created, its time to attach in Menu as usual oracle apps Steps. For testing created one Test menu.

12.   Assign This Menu/Function to Responsibility and Access your New OAF Page.

Oracle Business Event Code Example

Hi All,
Recently i had a requirement where client had to get rid of one of the repeated activity for assigning Newly created item to any Ship from Warehouse (Organization) to enable it for Sale.

For example ,

Customer XYZ is asking new product from OU "XYZ_OU".

Steps involved here is to Create New SKU (Item segment) and assign to organization from where we have to ship the inventory following by creating a Sales order and then follow the shipping process.

2nd step i.e. Assigning Item to organization was Automated using Business Event.

So whenever any Item is used in any sales order line and that item is not assigned to Ship from Warehouse at line level, we have to automatically Assign item whenever order is booked.

Code to achieve this functionality




CREATE OR REPLACE function APPS.XXZYX_ITEM_ASSIGN_ACCT_ORG(
                                            p_subscription_guid_in          IN RAW,
                                            p_event_in_out               IN apps.wf_event_t)
   RETURN VARCHAR2
     IS
   pvg_oe_header               VARCHAR2 (25)  := 'HEADER_ID';
   pvg_oe_line                 VARCHAR2 (25)   := 'LINE_ID';
   pvg_event_key               VARCHAR2 (50);
   pvg_parameter_list          apps.wf_parameter_list_t;
   pvg_user_id_num             fnd_user.user_id%TYPE := apps.fnd_global.user_id;
   pvg_login_id_num            NUMBER             := apps.fnd_global.login_id;
   pvg_resp_id_num             NUMBER              := apps.fnd_global.resp_id;
   pvg_resp_appl_id_num        NUMBER         := apps.fnd_global.resp_appl_id;
   lv_oe_header_id             apps.oe_order_headers_all.headeR_id%type;
   lv_org_id                    NUMBER;
   lv_ord_num                   VARCHAR2(100);
   lv_flag                     VARCHAR2(10);
 
   l_getEventName             varchar2(1000);
   l_getEventKey                varchar2(1000);
 
   l_param_name     VARCHAR2(100);
   l_param_value       varchar2(100);
 
 -------------Item Assignment API----------

        g_user_id             fnd_user.user_id%TYPE :=NULL;
        l_appl_id             fnd_application.application_id%TYPE;
        l_resp_id             fnd_responsibility_tl.responsibility_id%TYPE;
        l_api_version   NUMBER := 1.0;
        l_init_msg_list       VARCHAR2(2) := fnd_api.g_false;
        l_commit       VARCHAR2(2) := FND_API.G_FALSE;
        x_message_list        error_handler.error_tbl_type;
        x_return_status   VARCHAR2(2);
        x_msg_count       NUMBER := 0;
 
   BEGIN
   pvg_parameter_list := p_event_in_out.getparameterlist ();

   l_getEventName := p_event_in_out.getEventName ();
   l_getEventKey :=p_event_in_out.getEventKey ();

    IF pvg_parameter_list IS NOT NULL
      THEN
         FOR i IN pvg_parameter_list.FIRST .. pvg_parameter_list.LAST
         LOOP
       
          IF wf_event.getvalueforparameter ('LINE_ID', pvg_parameter_list) IS NULL
           THEN
            --if the line id is null, then this event was fired from header level, insert the record into event table
            IF (pvg_parameter_list (i).getname () = pvg_oe_header)
            THEN
               lv_oe_header_id :=
                               TO_NUMBER (pvg_parameter_list (i).getvalue ());
            end if;

         
     BEGIN

        fnd_global.apps_initialize (USER_ID, RESP_ID, RESP_APPL_ID);

for i in
        (select inventory_item_id from
        oe_order_lines_all
        where header_id=lv_oe_header_id)
        loop

        EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(
                   P_API_VERSION          => l_api_version
                ,  P_INIT_MSG_LIST        => l_init_msg_list
                ,  P_COMMIT               => l_commit
                ,  P_INVENTORY_ITEM_ID    => i.inventory_item_id
                ,  p_organization_id      => 163-----Ship from warehouse ID
                ,  X_RETURN_STATUS        => x_return_status
                ,  X_MSG_COUNT            => x_msg_count
            );
        DBMS_OUTPUT.PUT_LINE('Status: '||x_return_status);
        IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
          DBMS_OUTPUT.PUT_LINE('Error Messages :');
          Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
            FOR j IN 1..x_message_list.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE(x_message_list(j).message_text);
            END LOOP;
        END IF;
      end loop;
EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
END;


           --
            l_param_name := pvg_parameter_list (i).getname;
            l_param_value := pvg_parameter_list (i).getvalue;

            --
            INSERT INTO xx_be_debug_log_tmp
                        (text
                        )
                 VALUES (l_param_name || '  :   ' || l_param_value);

            COMMIT;
         END IF;
         --
         END LOOP;
      END IF;

      COMMIT;
      RETURN 'SUCCESS';
   EXCEPTION
   WHEN OTHERS
   THEN
      RETURN 'SUCCESS';
   
   END;
/


Then This function needs to be add as Subscription on Sales order booking event i.e.

 oracle.apps.ont.oip.statuschange.update.

Once Subscription is done properly, Test your case.

Concurrent program Logs to analyze OPP errors/Other standard Exception

Concurrent program Logs

a) Concurrent Request Log:

select logfile_name from fnd_concurrent_requests where request_id = <request_id>;

b) Concurrent Output file:

select outfile_name from fnd_concurrent_requests where request_id = <request_id>;

c) Concurrent Manager Worker Log:

select logfile_name from fnd_concurrent_processes where concurrent_process_id=(select controlling_manager

from fnd_concurrent_requests where request_id=<request_id>);

d) FNDOPP log file

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name

FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp

WHERE fcpp.processor_id = fcp.concurrent_process_id

AND fcpp.action_type = 6

AND fcpp.concurrent_request_id =<request_id>;

API to Setup a Buyer in R12

API to add Employee(Worker) as Buyer --> po_agents_pkg.insert_row.

DECLARE
   l_agent_id         per_all_people_f.person_id%type := 'XXXX'; ---Person ID of an employee
   l_employee_number   VARCHAR2 (20);
   l_buyer_rowid       VARCHAR2 (100);
BEGIN
  

   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 ('Successfully Setup as a buyer ');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error in Setting up as Buyer ' || SQLERRM);

END;

UTL_SMTP Package to send Mails from PL/SQL

CREATE OR REPLACE procedure proc_utl_smtp_mail  (p_from IN varchar2,
                                                                                                p_to IN varchar2,
                                                                                                p_subject IN VARCHAR2,
                                                                                                p_cc IN VARCHAR2 DEFAULT NULL,
                                                                                                p_mailhost VARCHAR2)
IS
l_message Long;
l_return_status VARCHAR2(10);
l_return_msg VARCHAR2(1000);
l_reply      UTL_SMTP.reply;
l_smtp_port NUMBER := 25;
l_mail_con  UTL_SMTP.CONNECTION;
l_crlf VARCHAR2(100):= chr(13) || chr(10);
l_cnt NUMBER;
BEGIN
l_message := 'Testing Procedure for UTL_SMTP Mail Package.';
l_mail_con := SYS.utl_smtp.open_connection (p_mailhost,l_smtp_port);
UTL_SMTP.helo (l_mail_con,p_mailhost);
utl_smtp.mail (l_mail_con,p_from);
utl_smtp.rcpt (l_mail_con,p_to);
utl_smtp.rcpt (l_mail_con,p_cc);
utl_smtp.open_data (l_mail_con);
utl_smtp.write_data (l_mail_con, 'TO:'           ||p_from || l_CRLF);
utl_smtp.write_data (l_mail_con, 'SUBJECT:'           ||p_subject || l_CRLF);
utl_smtp.write_data (l_mail_con , 'X-Priority :' ||      '1' || l_crlf); --- To Send Mails as High Priority

utl_smtp.write_data (l_mail_con, l_crlf
                     || 'Mr. XXXX'
                     || l_crlf
                     || l_crlf
                     || 'Subject: '
                     || p_subject
                     || l_crlf
                     || l_crlf
                     || 'Message : '
                     || l_message
                     || l_crlf
                     || l_crlf
                     || ' Do Not reply this message.'
                     || l_crlf
                     || 'Thanks & Rgards'
                     || l_crlf
                     || 'Oracle Team'
                     );
utl_smtp.close_data (l_mail_con);
l_reply := UTL_SMTP.QUIT (l_mail_con);

dbms_output.put_line ('l_reply :' || l_reply.text);
END;
/

Query to get OTL Time sheet Data

  SELECT ppf.full_name Resource_Name,
         --  paaf.supervisor_id,
         ppf1.full_name Manager_Name,
         papa.name Project_Name,
         papa.segment1 project_num,
         pt.task_name,
         TO_CHAR (hts.start_time, 'DD-MON-YYYY') Week_Start,
         --hts.resource_id,
         htb2.measure Hours_Charge,
         hts.approval_status,
         --  hts.submission_date,
         htb1.start_time Individual_date,
         TO_CHAR (htb1.start_time, 'DAY') Individual_day
    FROM hxc.hxc_time_building_blocks htb,
         hxc.hxc_time_building_blocks htb1,
         hxc.hxc_time_building_blocks htb2,
         hxc.hxc_time_attribute_usages htau,
         hxc.hxc_time_attributes hta,
         apps.pa_projects_all papa,
         hxc.hxc_timecard_summary hts,
         apps.per_all_people_f ppf,
         apps.per_all_people_f ppf1,
         apps.pa_tasks pt,
         apps.per_all_assignments_f paaf
   WHERE     htb1.parent_building_block_id = htb.time_building_block_id
         AND htb1.parent_building_block_ovn = htb.object_version_number
         AND ppf.person_id = hts.resource_id
         --     AND htb.date_to = apps.hr_general.end_of_time
         AND htb.SCOPE = 'TIMECARD'
         AND htb1.SCOPE = 'DAY'
         --    AND htb1.date_to = apps.hr_general.end_of_time
         AND htb2.parent_building_block_id = htb1.time_building_block_id
         AND htb2.parent_building_block_ovn = htb1.object_version_number
         AND htb2.SCOPE = 'DETAIL'
         AND paaf.person_id = ppf.person_id
         AND paaf.organization_id = XXX
         --     AND htb2.date_to = apps.hr_general.end_of_time
         AND htau.time_building_block_id = htb2.time_building_block_id
         AND htau.time_building_block_ovn = htb2.object_version_number
         AND htau.time_attribute_id = hta.time_attribute_id
         AND papa.project_id = hta.attribute1
         AND hts.start_time = htb.start_time
         AND hts.resource_id = htb.resource_id
         AND htb2.approval_status = 'SUBMITTED'
         --AND htb.resource_id = p_resource_id
         --  AND hts.timecard_id = :p_timecard_id
         AND hta.attribute_category = 'PROJECTS'
         AND paaf.supervisor_id = ppf1.person_id
         AND hts.start_time >= SYSDATE -XXX(Days)
         --  AND hts.approval_status = 'WORKING'
         AND hta.attribute2 = pt.task_id
         AND hta.attribute1 = pt.project_id
         AND pt.project_id = pt.project_id
         --   and hts.resource_id=46787
         AND TO_CHAR (htb1.start_time, 'DAY') NOT IN ('SATURDAY ', 'SUNDAY   ')
         AND ppf.effective_end_date > SYSDATE
         AND ppf1.effective_end_date > SYSDATE
         AND paaf.effective_end_date > SYSDATE
ORDER BY ppf.full_name, htb1.start_time, htb1.start_time

Supplier Contact Information Queries

Hi Everyone,
Recently i had got a requirement to gather all the Email ids , those can be attached to any supplier.


Their are 4 places, we can store Email Addresses for any supplier in Oracle R12.

1. Address Book--> Communication Field.
2. Contact Directory
3. Remittance Email id at Site Level
4. Remittance Email id at Header Level

Queries : 

• Address Book --> Communication Field

select ass.vendor_name, ass.segment1 supplier_num,hcp.email_address

from hz_party_sites hps,

hz_contact_points hcp,

ap_suppliers ass,

ap_supplier_sites_all assa

where 1=1 

and ass.vendor_id=assa.vendor_id

and hcp.owner_table_name='HZ_PARTY_SITES'

and hps.party_id= ass.party_id

and hcp.owner_table_id= hps.party_site_id

and hcp.contact_point_type='EMAIL'

and assa.org_id = xxxx;

--AND ass.segment1='77439'

• Contact Directory

SELECT  asu.segment1 Supp_Num

,asu.vendor_name

,hpcp.email_address

FROM

 hz_relationships hr

,ap_suppliers asu

,ap_supplier_sites_all assa

--,ap_supplier_contacts asco

,hz_org_contacts hoc

,hz_parties hpc

,hz_parties hpr

,hz_contact_points hpcp

WHERE hoc.party_relationship_id = hr.relationship_id

AND hr.subject_id = asu.party_id

AND hr.relationship_code = 'CONTACT'

AND hr.object_table_name = 'HZ_PARTIES'

AND asu.vendor_id = assa.vendor_id

AND hr.object_id = hpc.party_id

AND hr.party_id = hpr.party_id

AND hpr.party_type='PARTY_RELATIONSHIP'
AND hpcp.contact_point_type='EMAIL'

AND assa.org_id = xxxx

AND hpr.party_id = hpcp.owner_table_id

AND hpcp.owner_table_name = 'HZ_PARTIES'

--AND asu.segment1='77439'

• Remittance Email id at Site Level

  SELECT

       s.vendor_name              "Vendor Name",

       s.segment1                 "Vendor Number",

       s.vendor_type_lookup_code  "Vendor Type",

       st.vendor_site_code        "Vendor Site Code",

       ou.name                    "Operating Unit",

       iepa.remit_advice_delivery_method  "Remittance Delivery Method",

       iepa.remit_advice_email            "Remittance Advice Email"

  FROM

       ap.ap_suppliers              s,

       ap.ap_supplier_sites_all     st,

       hr_operating_units           ou,

       iby.iby_external_payees_all  iepa

 WHERE

       1=1

   AND TRUNC (SYSDATE) BETWEEN TRUNC (s.start_date_active) AND TRUNC (NVL (s.end_date_active, SYSDATE+1))

   AND s.enabled_flag = 'Y'

   AND iepa.supplier_site_id = st.vendor_site_id

   AND iepa.payee_party_id = s.party_id

   AND st.org_id = ou.organization_id

   AND st.vendor_id = s.vendor_id

  -- and s.segment1='77439'

   and st.org_id= xxx

 ORDER BY s.vendor_name, st.vendor_site_code;

• Remittance Email id at Header Level

SELECT aps.vendor_name, aps.segment1 supplier_num,iepa.remit_advice_email 

       FROM  APPS.ap_suppliers aps,

          apps.ap_supplier_sites_all apss

       ,APPS.iby_external_payees_all iepa

       WHERE  iepa.payee_party_id = aps.party_id

       and aps.vendor_id= apss.vendor_id

         AND default_payment_method_code is null

        -- AND  aps.segment1='77439'

        and apss.org_id = xxx

         and iepa.party_site_id IS null