Tuesday, September 17, 2019

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

                                            p_subscription_guid_in          IN RAW,
                                            p_event_in_out               IN apps.wf_event_t)
   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;
   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
         FOR i IN pvg_parameter_list.FIRST .. pvg_parameter_list.LAST
          IF wf_event.getvalueforparameter ('LINE_ID', pvg_parameter_list) IS NULL
            --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)
               lv_oe_header_id :=
                               TO_NUMBER (pvg_parameter_list (i).getvalue ());
            end if;


        fnd_global.apps_initialize (USER_ID, RESP_ID, RESP_APPL_ID);

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

                   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 :');
            FOR j IN 1..x_message_list.COUNT LOOP
            END LOOP;
        END IF;
      end loop;
          dbms_output.put_line('Exception Occured :');

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

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

         END IF;
         END LOOP;
      END IF;


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


Once Subscription is done properly, Test your case.

Monday, June 10, 2019

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>;

Thursday, May 09, 2019

API to Setup a Buyer in R12

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

   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);

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


Tuesday, April 23, 2019

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)
l_message Long;
l_return_status VARCHAR2(10);
l_return_msg VARCHAR2(1000);
l_reply      UTL_SMTP.reply;
l_smtp_port NUMBER := 25;
l_crlf VARCHAR2(100):= chr(13) || chr(10);
l_cnt NUMBER;
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);

Friday, March 29, 2019

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,
         TO_CHAR (hts.start_time, 'DD-MON-YYYY') Week_Start,
         htb2.measure Hours_Charge,
         --  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

Friday, January 11, 2019

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
 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 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

       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"
       ap.ap_suppliers              s,
       ap.ap_supplier_sites_all     st,
       hr_operating_units           ou,
       iby.iby_external_payees_all  iepa
   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

Monday, September 10, 2018

Description for GL Interface Error Status

If you need to find the description of various statuses in GL INTERFACE generated from Journal Import Program in GL,  it can be done via Standard Lookup Called 'psp_susp_ac_errors'

SELECT Description,Lookup_code FROM  fnd_lookups where lookup_type='PSP_SUSP_AC_ERRORS'


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 S...