send mail with attachment by using unix script

P_SUBJECT=`echo "$1"|cut -d'"' -f8`
P_ATTFILE=`echo "$1"|cut -d'"' -f10`
P_MAIL_TO=`echo "$1"|cut -d'"' -f12`
P_FILE=`echo "$1"|cut -d'"' -f14`
P_BODY=`echo "$1"|cut -d'"' -f16`
P_ATTFILE1=`echo "$1"|cut -d'"' -f18`
P_FILE1=`echo "$1"|cut -d'"' -f20`
P_FROM=`echo "$1"|cut -d'"' -f22`
CC_LIST=""



echo "Subject   :"$P_SUBJECT
echo "File_Name :"$P_ATTFILE
echo "EMail To  :"$P_MAIL_TO
echo "File      :"$P_FILE
echo "BODY      :"$P_BODY
echo "P_ATTFILE1:"$P_ATTFILE1
echo "P_FILE1   :"$P_FILE1
echo "CC        :"  $CC_LIST
echo "EmailFROM :" $P_FROM

value1=$(cat $P_BODY)
echo "$value1"
(echo "$value1"; uuencode $P_ATTFILE "$P_FILE"; uuencode $P_ATTFILE1 "$P_FILE1") |mailx -s "$P_SUBJECT" -c "$CC_LIST" "$P_MAIL_TO" -- -r "$P_FROM"
if [ $? -ne 0 ]
then
echo ""
echo "Fail to send mailto ${P_MAIL_TO} with mail subject ${P_SUBJECT} and file $P_ATTFILE"
echo ""
exit 1
fi
exit 0

vendor details query

select 
pv.VENDOR_NAME,
pv.segment1 Supplier_number,
pvs.VENDOR_SITE_CODE site_name,
pv.match_option vendor_match_option,
pvs.match_option site_match_option,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3
CITY,
STATE,
ZIP,
PROVINCE,
COUNTRY ,
pv.START_DATE_ACTIVE,
pv.end_DATE_ACTIVE
from
po_vendors pv,
po_vendor_sites_all pvs
where pv.vendor_id = pvs.vendor_id
and pvs.org_id =111

OTL Time card query

SELECT --distinct
papf.EMPLOYEE_NUMBER,
papf.FULL_NAME EMPLOYEE_NAME,
to_char(htb1.start_time,'DD-MON-RRRR') START_DATE,
papa.segment1 PROJECT,
pt.TASK_number "Task",
hta.attribute3 "Type",
htb2.measure "Hours",
hts.approval_status "Status"
             FROM hxc_time_building_blocks htb,
                  hxc_time_building_blocks htb1,
                  hxc_time_building_blocks htb2,
                  hxc_time_attribute_usages htau,
                  hxc_time_attributes hta,
                  pa_projects_all papa,
                  hxc_timecard_summary hts,
                  pa_tasks pt,
                  per_all_people_f papf
            WHERE htb1.parent_building_block_id = htb.time_building_block_id
              AND htb1.parent_building_block_ovn = htb.object_version_number
 and HTB.SCOPE  = 'TIMECARD'
              AND htb.date_to = hr_general.end_of_time
              AND htb1.date_to = 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.date_to = 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 papf.person_id = htb.resource_id
              AND papf.EMPLOYEE_NUMBER= nvl(:P_EMPLOYEE_NUMBER,papf.EMPLOYEE_NUMBER)
 and htb1.start_time between papf.effective_start_date and papf.effective_end_Date
              AND ((to_date(TO_CHAR(htb1.start_time ,'dd-mon-rrrr') ,'dd-mon-rrrr') >= to_date(:P_FM_DT,'dd-mon-rrrr') or :P_FM_DT is null)
              AND (to_date(TO_CHAR(htb1.start_time ,'dd-mon-rrrr') ,'dd-mon-rrrr') <= to_date(:P_TO_DT,'dd-mon-rrrr') or :P_TO_DT is null))
              AND hts.approval_status =nvl(:P_STATUS,hts.approval_status)
     AND papa.segment1 =nvl(:P_PROJECT_NO,papa.segment1)
              AND hta.attribute_category = 'PROJECTS'
              AND hta.attribute2 = pt.task_id
              AND hta.attribute1 = pt.project_id
 and exists (select 1 from HXC_TIME_ATTRIBUTE_USAGES HTAU2,HXC_TIME_ATTRIBUTES HTA2
              where HTAU2.TIME_ATTRIBUTE_ID = HTA2.TIME_ATTRIBUTE_ID
              and HTAU2.TIME_BUILDING_BLOCK_ID = HTB2.TIME_BUILDING_BLOCK_ID
              and HTA2.ATTRIBUTE_CATEGORY = 'SECURITY'
              and HTA2.attribute1 = to_char(FND_GLOBAL.ORG_id)

PA Tables

pa_projects_all

pa_tasks

pa_agreements_all 

pa_project_statuses 

XML Data Template file Example

<dataTemplate name="AR_OPEN_INVOICE" defaultPackage="AR_REPORTS" Version="1.0">
    <properties>
        <property name="xml_tag_case" value="upper" />
    </properties>
  <parameters>
   <parameter name="P_AS_OF_DATE" datatype="varchar2" />
   <parameter name="P_SET_OF_BOOKS_ID" datatype="number" />
   <parameter name="P_CUSTOMER_NAME_FROM" datatype="varchar2" />
   <parameter name="P_CUSTOMER_NAME_TO" datatype="varchar2" />
   <parameter name="P_CUSTOMER_NUMBER_LOW" datatype="varchar2" />
   <parameter name="P_CUSTOMER_NUMBER_HIGH" datatype="varchar2" />
   <parameter name="P_CURRENCY" datatype="varchar2" />
   <parameter name="P_MIN_INVOICE_BALANCE" datatype="varchar2" />
   <parameter name="P_MIN_CUSTOMER_BALANCE" datatype="varchar2" />
  <parameter name="P_INCLUDE_ON_ACCOUNT_CREDITS" datatype="varchar2" />
  <parameter name="P_INCLUDE_ON_ACCOUNT_RECEIPTS" datatype="varchar2" />
  <parameter name="P_INCLUDE_UNAPPLIED_RECEIPTS" datatype="varchar2" />
  <parameter name="P_INCLUDE_UNCLEARED_RECEIPTS" datatype="varchar2" />
  <parameter name="P_REFERENCE_NUMBER" datatype="varchar2" />
  <parameter name="P_OPERATING_UNIT" datatype="varchar2" />
  <parameter name="P_DUE_DATE_FROM" datatype="varchar2" />
  <parameter name="P_DUE_DATE_TO" datatype="varchar2" />
  <parameter name="P_COLLECTOR_NAME_FROM" datatype="varchar2" />
  <parameter name="P_COLLECTOR_NAME_TO" datatype="varchar2" />
  <parameter name="P_COLL_ASSIGNED_FROM" datatype="varchar2" />
  <parameter name="P_COLL_ASSIGNED_TO" datatype="varchar2" />
  <parameter name="P_DEBUG_FLAG" datatype="varchar2" />
   <parameter name="P_TRACE_FLAG" datatype="varchar2" />
  </parameters>  
    <lexicals />
    <dataQuery>
        <sqlStatement name="Q_header">
            <![CDATA[SELECT X.OPERATING_UNIT,
X.SET_OF_BOOKS_ID,
X.CUSTOMER_NUMBER,
X.CUSTOMER_NAME,
X.PARTY_SITE_NUMBER,
X.ADDRESS_LINE1,
X.ADDRESS_LINE2,

xml bursting example in oracle apps

<?xml version="1.0" encoding="UTF-8" ?>
 <xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">        
    <xapi:request select="/ARXMLINV/LIST_G_ORDER_BY/G_ORDER_BY">
                <xapi:delivery>
                   <xapi:email server="localhost" port="25" from="deepsan17@gmail.com" reply-to="noreply@gmail.com">
                   <xapi:message id="123" to="${CF_TEST_MAIL}" attachment="true" content-type="text/html" subject="Ar Invoice Report">                  
                   <![CDATA[
<html>
<body>
<p align="left">
<font face="Verdana" size="2" color="black">

Dear customer,<br><br>

Please find attached details of the latest AR Invoice ${TRX_NUMBER}.<br><br>

Regards,<br>
sss team <br><br>

<i>This is an auto-generated message. Please do not respond to this e-mail.</i> <br/>
____________________________________________________<br/>
</font>
 <font face="Verdana" size="2" color="green">
  <strong>Imagination at work </strong><br>
  <i><a>Support the go green initiative by not printing this email .. </a>
 </font>
</p>
</body>
</html>
]]>
                  </xapi:message>
                 </xapi:email>
                 <xapi:print id="print123" printer="ipp://1.1.1.1:9100/INVOICES" copies="1" />
                 </xapi:delivery>
                <xapi:document output="/erpeseg1/erpapp/comn/temp/Customer Invoice XML PRINT_${TRX_NUMBER}.pdf" output-type="pdf" delivery="print123">
                <xapi:template type="rtf" location="xdo://AR.ARXMLINV_US.en.US/?getSource=true" filter=""/>
            </xapi:document>
                <xapi:document output="/erpeseg1/erpapp/comn/temp/Customer Invoice XML Report_${TRX_NUMBER}.pdf" output-type="pdf" delivery="123">
                <xapi:template type="rtf" location="xdo://AR.ARXMLINV_US.en.US/?getSource=true" filter=""/>
            </xapi:document>
         </xapi:request>
      </xapi:requestset>

Query to find the project status and agreement

SELECT ppf.project_number,
ppf.project_name,
pps. project_status_name project_status,
paa.agreement_num,
paa.DESCRIPTION
FROM pa_agreements_all paa,
pa_project_fundings_v ppf,
pa_projects_all ppa,
pa_project_statuses pps
WHERE paa.agreement_id = ppf.agreement_id
AND ppa.project_status_code = pps.project_status_code
AND ppa.project_id = ppf.project_id
and ppa.org_id = paa.org_id
and ppa.org_id =111

How to make responsibilities as read only in oracle apps

There are two ways to make responsibility as ready only:

1. Form Function Method
2. custom.pll

Form Function Method
1. Identify the menu attached to the responsibility that you wish to make it a Read only Responsibility
2. Identify the form functions that are attached to this menu.
3. Define a new form function exactly similar to the existing one but with the option QUERY_ONLY="YES" at the location "Application"-->"function"-->"Form"-->"Parameter".
4. The Parameter sets the function in Query Only mode.
5. Final step is to create a custom menu with the new read only functions created and attach the menu to the  new responsibility.

CUSTOM.pll 

Use the following piece of code in Procedure Event for the Event Name WHEN-NEW-FORM-INSTANCE in Custom.pll. Without much effort you can simply make all the responsibilities of a user read-only. But you need to hardcode the user name, which is a setback. Custom.pll is located in $AU_TOP/resource.

BEGIN 
IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN 
IF fnd_profile.value('RESP_ID')=  502502 THEN 
BEGIN 
COPY('Entering app_form.query_only_mode.','global.frd_debug'); 
COPY('YES', 'PARAMETER.QUERY_ONLY'); 
APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF); 
APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF); 
form_name := NAME_IN('system.current_form'); 
block_name := GET_FORM_PROPERTY(form_name, FIRST_BLOCK); 
WHILE (block_name is not null) LOOP 
IF (GET_BLOCK_PROPERTY(block_name, BASE_TABLE) is not NULL) THEN 
SET_BLOCK_PROPERTY(block_name, INSERT_ALLOWED, PROPERTY_FALSE); 
SET_BLOCK_PROPERTY(block_name, UPDATE_ALLOWED, PROPERTY_FALSE); 
SET_BLOCK_PROPERTY(block_name, DELETE_ALLOWED, PROPERTY_FALSE); 
END IF; 
block_name := GET_BLOCK_PROPERTY(block_name, NEXTBLOCK); 
END LOOP; 
END query_only_mode; 
END IF; 
END IF; 
END; 

How to find customer number, site number, party number from invoice

select rcta.trx_number,
cust_acct.account_number customer_number,
party_site.party_site_number site_number,
hp.party_number
FROM ra_customer_trx_all rcta,
hz_cust_accounts cust_acct,
hz_party_sites party_site,
hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_uses,
hz_parties hp
where  rcta.bill_to_customer_id = cust_acct.cust_account_id
AND rcta.bill_to_customer_id = acct_site.cust_account_id
AND rcta.bill_to_site_use_id = site_uses.site_use_id
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_code = 'BILL_TO'
AND party_site.party_site_id = acct_site.party_site_id
AND hp.party_id = cust_acct.party_id
AND hp.party_id = party_site.party_id
and rcta.trx_number='11111111'