How to run Workflow Background Process from SQL*Plus? And from Unix?

How to run Workflow Background Process from SQL*Plus?

BEGIN
  wf_engine.background (itemtype=>NULL ,
                        process_deferred=>TRUE ,
                        minthreshold=>NULL ,
                        maxthreshold=>NULL ,
                        process_timeout=>FALSE ,
                        process_stuck=>FALSE);
END;

How to run Workflow Background Process from Unix?

$ $FND_TOP/Admin/Sql/wfbkg.sql

Sample shell Script to send concurrent program output an email

#!/bin/ksh

# Parameters.

#----- Standard Parameters: -----#

p_oracle_id=`echo ${1}`

USER_ID=`echo ${2}`

ORACLE_USER=`echo ${3}`

REQUEST_ID=`echo ${4}`

#----- User Parameters: -----#

P_REQUEST_ID=`echo ${5}`

 

v_requestid=$P_REQUEST_ID

echo "v_requestid: "$v_requestid

x=`sqlplus -s $p_oracle_id <<EOF

set feedback off set echo off head off pagesize 0 trimspool on linesize 1000

set heading off

spool temp.txt

SELECT  OUTFILE_NAME

FROM    fnd_concurrent_requests

WHERE   request_id   = $v_requestid;

EXIT;

spool off; 

EOF`

lv_body="Email_body.txt"

echo "Please find the attached log file for xxxx AR Oracle Outbound Interface To xxxx Data Feed." > $lv_body

echo "" >> $lv_body

echo "Please take appropriate action if this is not as expected." >> $lv_body

#mutt  $lv_out_file_path -s "$lv_new"  $TO_ADDRESS < $lv_body1

mutt -a $x -s "Oracle Outbound Interface to xxx Data feed is completed with Error/Warning status" reddi.info@gmail.com < $lv_body

rm $lv_body

--

Query to find the responsibility for quering supplier sites of a perticular operating unit in R12

Finding  the operating unit of a Supplier:

--------------------------------------------------------------

SELECT HZP.PARTY_NAME "VENDOR NAME",hou.name                       operating_unit

, APS.SEGMENT1 "VENDOR NUMBER"

, ASS.VENDOR_SITE_CODE "SITE CODE"

FROM HZ_PARTIES HZP

, AP_SUPPLIERS APS

, HZ_PARTY_SITES SITE_SUPP

, AP_SUPPLIER_SITES_ALL ASS

--, IBY_EXTERNAL_PAYEES_ALL IEP

, hr_all_organization_units       hou

WHERE HZP.PARTY_ID = APS.PARTY_ID

AND HZP.PARTY_ID = SITE_SUPP.PARTY_ID

AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID

AND ASS.VENDOR_ID = APS.VENDOR_ID

and  ass.org_id    = hou.organization_id

AND ASS.VENDOR_SITE_CODE='xxxxx'

 Finding  the responsibility  of a Supplier:

SELECT distinct B.PROFILE_OPTION_VALUE,C.RESPONSIBILITY_NAME,d.SECURITY_PROFILE_NAME, d1.name ou_name

FROM FND_PROFILE_OPTIONS_VL A

, fnd_profile_option_values b

, fnd_responsibility_vl c

, per_security_profiles_v d

, per_organization_structures a1

, PER_ORG_STRUCTURE_VERSIONS b1

, per_org_structure_elements_v c1

, hr_operating_units d1

WHERE A.user_PROFILE_OPTION_NAME LIKE 'MO%Security%'

and d.ORGANIZATION_STRUCTURE_ID = a1.ORGANIZATION_STRUCTURE_ID

and a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID

and TO_NUMBER(b.LEVEL_VALUE)=c.RESPONSIBILITY_ID

and d.SECURITY_PROFILE_ID = b.PROFILE_OPTION_VALUE

--AND upper(c.RESPONSIBILITY_NAME) like 'PAYABLES MANAGER'

and a1.ORGANIZATION_STRUCTURE_ID = b1.ORGANIZATION_STRUCTURE_ID

and b1.ORG_STRUCTURE_VERSION_ID= c1.ORG_STRUCTURE_VERSION_ID

and (d1.organization_id = c1.ORGANIZATION_ID_CHILD

or d1.organization_id = c1.ORGANIZATION_ID_parent)

AND d1.name='14101 Travelscape, LLC'

UNION ALL

SELECT B.PROFILE_OPTION_VALUE,C.RESPONSIBILITY_NAME,d.SECURITY_PROFILE_NAME, d1.name ou_name

FROM FND_PROFILE_OPTIONS_VL A

, fnd_profile_option_values b

, fnd_responsibility_vl c

, per_security_profiles_v d

,PER_SECURITY_ORGANIZATIONS_V c1

, hr_operating_units d1

WHERE A.user_PROFILE_OPTION_NAME LIKE 'MO%Security%'

and a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID

and TO_NUMBER(b.LEVEL_VALUE)=c.RESPONSIBILITY_ID

and d.SECURITY_PROFILE_ID = b.PROFILE_OPTION_VALUE

--AND upper(c.RESPONSIBILITY_NAME) like 'PAYABLES MANAGER'

AND c1.SECURITY_PROFILE_ID = d.SECURITY_PROFILE_ID

and c1.ORGANIZATION_ID = d1.ORGANIZATION_ID

AND d1.name='xxxxx'

--

Deleting a template from back end

declare   

   l_templateCode    varchar2 (100) := 'XX_AR_XXXREPORT'; -- Template Code   

begin

   for r in (select t1.application_short_name template_app_name,

                    t1.data_source_code,

                    t1.application_short_name def_app_name

               from xdo_templates_b t1

              where t1.template_code = l_templateCode)

   loop

     

      xdo_templates_pkg.delete_row (r.template_app_name, l_templateCode);

      delete from xdo_lobs

            where lob_code = l_templateCode

                  and application_short_name = r.template_app_name

                  and lob_type in ('TEMPLATE_SOURCE', 'TEMPLATE');

      delete from xdo_config_values

            where application_short_name = r.template_app_name

                  and template_code = l_templateCode

                  and data_source_code = r.data_source_code

                  and config_level = 50;

  end loop;

end;

--
Best Regards,
Boge Prasanth kumar Reddy.

Concurrent Program Phase codes and Status Codes

Concurrent Program Phase Codes:

SELECT lookup_code, meaning

  FROM fnd_lookup_values

 WHERE lookup_type = 'CP_PHASE_CODE' AND LANGUAGE = 'US'

   AND enabled_flag = 'Y';

LOOKUP_CODE	MEANING
C	Completed
I	Inactive
P	Pending
R	Running

Concurrent Program Status Codes:

SELECT lookup_code, meaning

  FROM fnd_lookup_values

 WHERE lookup_type = 'CP_STATUS_CODE' AND LANGUAGE = 'US'

   AND enabled_flag = 'Y';

LOOKUP_CODE	MEANING
R	Normal
I	Normal
Z	Waiting
D	Cancelled
U	Disabled
E	Error
M	No Manager
C	Normal
H	On Hold
W	Paused
B	Resuming
P	Scheduled
Q	Standby
S	Suspended
X	Terminated
T	Terminating
A	Waiting
G	Warning

--

How to enable About this page in oracle apps

How to enable About this page in oracle apps

Navigate to System Administrator --> Profile --> System

Profile: FND: Diagnostics 
Set the site value to Yes




Logout and log back in.

Note:This information provided courtesy of <http://oracleappsengineering.blogspot.ie>.
-- 

RICE/CEMLI Terminology

RICE/CEMLI Terminology

In my Oracle career I often hear confusion over RICE/CEMLI terminologies, what they stands for ? what is the meaning of...blah blah blah....so with the help of my very good friend Charan, I have decided to write my take on this.

AIM (Applications Implementation Methodology) => During packaged ERP(enterprise resource planning) implementations, Clients often have additional requirements apart from the existing (standard) business process, for which they need to create/change theVanila system (Unchanged ERP Implemented system) processes, and its these changes that come under RICE/RICEW components. AIM is the methodology/standards/published guidelines, which Oracle suggests it's Clients to follow while developing 
RICE/RICEW components for their business requirements.

RICE stands for Reports, Interfaces, Conversions, Enhancements / Extensions
-----
 > Sometimes extended to FRICE > F for Forms
                                         -------
                                        OR  
                                        RICEW > W for Workflow.
                                         -------

Forms/Reports/Workflows : Create/Change existing forms/reports/workflows available in  ERP system to  meet the Clients business requirements.

Interfaces : Linking (Programs) between other systems to ERP system in order to synchronize the Data.They can be Manual, Batch or Real-Time. Interfaces can be either outbound or inbound. An outbound interface reads data from Oracle Apps tables and usually creates output files in the third party tool specified format. An inbound interface reads data from flat files (usually) and calls Oracle APIs to upload data into Oracle Apps.

Conversion : It is converting the data structure and data design of legacy system data to satisfy the customer's business rules before importing it into Oracle .It is like a one time run of an inbound interface except that the amount of data processed during conversion could be potentially huge since all the required data from the legacy system would be transferred to Oracle.

Enhancements/Extensions : Please see below.

>> In R12 RICE components have been extended to CEMLI components.
                                                                             --------

CEMLI Stands for Configurations/Customization, Extension, Modification, Localization, and Integration.


Configurations : Configure the existing, pre-built application features according to your client's requirement.Changing setups and profile values can be the example of configurations.

Customization : Customization means altering/changing the standard objects or creation of custom object to meet client's business need. It may be Extensions or Modifications.

Extensions : Extension means creating custom code from scratch, existing objects (views, packages and java classes etc) can be used. It is having different behaviour from seeded one.

Modifications : Modifications is enhancing/changing the existing code to meet the client's requirements. It is the modification of seeded behaviour.

Localization :  It is to define the different legislative support provided by oracle Applications based on country/region/language requirements. 

Integration : It can be Data Integration or Application Integration, options for these two are Open Interface tables, APIs, EAI(Enterprise Application Integration Tools), BPEL, AQ, EDI etc.

             >> Apart from these there is one more term and that is Personalization.

Personalization : Tailoring the layout or visibility of page content to meet client requirements is Personalization. Changing the user interface (UI) look-and-feel, making any field visible/enabled/disabled/mandatory/non mandatory comes under Personalization.



>> There are many guidelines and risks related to RICE/CEMLI components, but that's for some other day, here I'll restrict myself to overview of these terminologies.

Note: This above information is courtesy  of http://abhayappssolution.blogspot.ie

--