apps.fnd_program.register (program => l_program, application => l_application, enabled => l_enabled, short_name => l_short_name, description => l_description, executable_short_name => l_executable_short_name, executable_application => l_executable_application, execution_options => l_execution_options, priority => l_priority, save_output => l_save_output, print => l_print, cols => l_cols, ROWS => l_rows, STYLE => l_style, style_required => l_style_required, printer => l_printer, request_type => l_request_type, request_type_application => l_request_type_application, use_in_srs => l_use_in_srs, allow_disabled_values => l_allow_disabled_values, run_alone => l_run_alone, output_type => l_output_type, enable_trace => l_enable_trace, restart => l_restart, nls_compliant => l_nls_compliant, icon_name => l_icon_name, language_code => l_language_code, mls_function_short_name => l_mls_function_short_name, mls_function_application => l_mls_function_application, incrementor => l_incrementor, refresh_portlet => l_refresh_portlet );
Monday, March 31, 2014
create concurrent program from backend
Register executable from back end
apps.fnd_program.executable (executable => l_executable, application => l_application, short_name => l_short_name, description => l_description, execution_method => l_execution_method, execution_file_name => l_execution_file_name, subroutine_name => l_subroutine_name, icon_name => l_icon_name, language_code => l_language_code, execution_file_path => l_execution_file_path );
Sunday, March 30, 2014
Concurrent program whose output has to be sent as an Email
we can send concurrent program out put as email by usingg utl_mail.send_attach_varchar2 producer
SEND_ATTACH_VARCHAR2 Procedure
This procedure is the
SEND Procedure overloaded for VARCHAR2 attachments.UTL_MAIL.SEND_ATTACH_VARCHAR2 (
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS,
mime_type IN VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
priority IN PLS_INTEGER DEFAULT NULL
attachment IN RAW,
att_inline IN BOOLEAN DEFAULT TRUE,
att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT
'application/octet',
att_filename IN VARCHAR2 DEFAULT NULL);
| Parameter | Description |
|---|---|
sender |
The email address of the sender.
|
recipients |
The email addresses of the recipient(s), separated by commas.
|
cc |
The email addresses of the CC recipient(s), separated by commas, default is
NULL. |
bcc |
The email addresses of the BCC recipient(s), separated by commas, default is
NULL. |
subject |
A string to be included as email subject string, default is
NULL. |
message |
A text message body.
|
mime_type |
The mime type of the message, default is 'text/plain; charset=us-ascii'.
|
priority |
The message priority, the default is
NULL. |
attachment |
A text attachment.
|
att_inline |
Specifies whether the attachment is inline, default
TRUE. |
att_mime_type |
The mime type of the attachment, default is 'text/plain; charset=us-ascii'.
|
att_filename |
The string specifying a filename containing the attachment, default is
NULL. |
Monday, March 24, 2014
P2P query in oracle apps R12
Give 'PO Number' as an input to the following script.
It will provide all required PO details associated Receipt details, Invoice details, Payment details.
Purchase Order -> Receipts -> Invoices -> Payments
SELECT pha.segment1 po_number,
pla.line_num,
plla.shipment_num,
msi.segment1 ordered_item,
msi.description ordered_item_description,
pla.unit_price po_unit_price,
rt.transaction_type,
rt.transaction_date,
rt.subinventory,
plla.quantity quantity_ordered,
plla.quantity_received,
pda.quantity_delivered,
plla.quantity_Billed,
rsh.receipt_num,
aia.invoice_num,
ail.line_number inv_line_number,
aid.distribution_line_number inv_dist_number,
ail.line_type_lookup_code Line_type,
aid.line_type_lookup_code Dist_line_type,
aid.amount,
aid.quantity_invoiced,
ac.check_number,
aip.payment_num
FROM apps.rcv_transactions rt,
apps.po_headers_all pha,
apps.po_line_locations_all plla,
apps.po_distributions_all pda,
apps.po_lines_all pla,
apps.mtl_system_items msi,
apps.rcv_shipment_headers rsh,
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.ap_invoice_payments_all aip,
apps.ap_checks_all ac
WHERE rt.po_header_id = pha.po_header_id
AND pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND plla.line_location_id = pda.line_location_id
AND rt.po_line_location_id = plla.line_location_id
AND pla.item_id = msi.inventory_item_id
AND rt.po_line_id = pla.po_line_id
AND rt.organization_id = msi.organization_id
AND rsh.shipment_header_id = rt.shipment_header_id
AND aip.check_id = ac.check_id
AND aia.invoice_id = aip.invoice_id
AND aia.invoice_id = ail.invoice_id
AND aia.invoice_id = aid.invoice_id
AND pda.po_distribution_id(+) = aid.po_distribution_id
AND rt.transaction_type = 'DELIVER'
AND ail.line_type_lookup_code = 'ITEM'
AND aid.line_type_lookup_code = 'ACCRUAL'
AND pha.segment1 = '&PO_Number'
It will provide all required PO details associated Receipt details, Invoice details, Payment details.
Purchase Order -> Receipts -> Invoices -> Payments
SELECT pha.segment1 po_number,
pla.line_num,
plla.shipment_num,
msi.segment1 ordered_item,
msi.description ordered_item_description,
pla.unit_price po_unit_price,
rt.transaction_type,
rt.transaction_date,
rt.subinventory,
plla.quantity quantity_ordered,
plla.quantity_received,
pda.quantity_delivered,
plla.quantity_Billed,
rsh.receipt_num,
aia.invoice_num,
ail.line_number inv_line_number,
aid.distribution_line_number inv_dist_number,
ail.line_type_lookup_code Line_type,
aid.line_type_lookup_code Dist_line_type,
aid.amount,
aid.quantity_invoiced,
ac.check_number,
aip.payment_num
FROM apps.rcv_transactions rt,
apps.po_headers_all pha,
apps.po_line_locations_all plla,
apps.po_distributions_all pda,
apps.po_lines_all pla,
apps.mtl_system_items msi,
apps.rcv_shipment_headers rsh,
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.ap_invoice_payments_all aip,
apps.ap_checks_all ac
WHERE rt.po_header_id = pha.po_header_id
AND pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND plla.line_location_id = pda.line_location_id
AND rt.po_line_location_id = plla.line_location_id
AND pla.item_id = msi.inventory_item_id
AND rt.po_line_id = pla.po_line_id
AND rt.organization_id = msi.organization_id
AND rsh.shipment_header_id = rt.shipment_header_id
AND aip.check_id = ac.check_id
AND aia.invoice_id = aip.invoice_id
AND aia.invoice_id = ail.invoice_id
AND aia.invoice_id = aid.invoice_id
AND pda.po_distribution_id(+) = aid.po_distribution_id
AND rt.transaction_type = 'DELIVER'
AND ail.line_type_lookup_code = 'ITEM'
AND aid.line_type_lookup_code = 'ACCRUAL'
AND pha.segment1 = '&PO_Number'
shell script tutorial
Objectives:
step 1:
=======
Place the <name>.prog script under the bin directory for your
applications top directory.
For example, call the script ERPS_DEMO.prog and place it under
$CUSTOM_TOP/bin
step 2:
=======
Make a symbolic link from your script to $FND_TOP/bin/fndcpesr
For example, if the script is called ERPS_DEMO.prog use this:
ln -s $FND_TOP/bin/fndcpesr ERPS_DEMO
This link should be named the same as your script without the
.prog extension.
Put the link for your script in the same directory where the
script is located.
step 3:
=======
Register the concurrent program, using an execution method of
‘Host’. Use the name of your script without the .prog extension
as the name of the executable.
For the example above:
Use ERPS_DEMO
step 4:
=======
Your script will be passed at least 4 parameters, from $1 to $4.
$1 = orauser/pwd
$2 = userid(apps)
$3 = username,
$4 = request_id
Any other parameters you define will be passed in as $5 and higher.
Make sure your script returns an exit status also.
Sample Shell Script to copy the file from source to destination
#Note: If you see # in front of any line it means that it’s a comment line not the actual code
#** ********************************************************************
# Created By : Prudhvi A
# Creation Date : 19-FEB-2008
# Script Name : ERPSCHOOLS.prog
# Description : This Script accepts three parameters
# 1)Data File Name 2)Source Directory Path 3)Target Directory Path
# Then copy the file from source location to target location.
# If copy fails send the error status/message to concurrent program so that user can see status.
#
#
# ========
# History
# ========
# Version 1 Prudhvi A 19-FEB-2008 Created for erpschools.com users
#
#** ********************************************************************
#Parameters from 1 to 4 i.e $1 $2 $3 $4 are standard parameters
# $1 : username/password of the database
# $2 : userid
# $3 : USERNAME
# $4 : Concurrent Request ID
DataFileName=$5
SourceDirectory=$6
TargetDirectory=$7
echo “————————————————–”
echo “Parameters received from concurrent program ..”
echo ” Time : “`date`
echo “————————————————–”
echo “Arguments : ”
echo ” Data File Name : “${DataFileName}
echo ” SourceDirectory : “${SourceDirectory}
echo ” TargetDirectory : “${TargetDirectory}
echo “————————————————–”
echo ” Copying the file from source directory to target directory…”
cp ${SourceDirectory}/${DataFileName} ${TargetDirectory}
if [ $? -ne 0 ]
# the $? will contain the result of previously executed statement.
#It will be 0 if success and 1 if fail in many cases
# -ne represents not “equal to”
then
echo “Entered Exception”
exit 1
# exit 1 represents concurrent program status. 1 for error, 2 for warning 0 for success
else
echo “File Successfully copied from source to destination”
exit 0
fi
echo “****************************************************************”
Basic Shell Script Commands
# Create Directory
mkdir <dirname>
# Remove Directory
rmdir <dirname>
#remove folder with files
rm -r -f <dirname>
# Change Directory
cd <newpath>
# Create new file
vi <newfile.ext>
#insert data into file
vi <openfilename.ext>
esc i <make changes>
#Save file
esc :wq enter
# exit without saving changes
esc :q! enter
# open existing file
vi <existingfilename.ext>
#remove file
rm <filename.ext>
# copy file with same name
cp <sourcedir>/<sourcefilename.ext> <destinationdir>
# copy file with new name
cp <sourcedir>/<sourcefilename.ext> <destinationdir>/<newfilename.ext>
# Move file with same name
mv <sourcedir>/<sourcefilename.ext> <destinationdir>
# move file with data appended to filename in the front
mv <sourcedir>/<sourcefilename.ext> <destinationdir>/`date+%H%M%d%m%y`<filename.ext>
#print line
echo “your text here to print”
#print date
echo `date`
- Steps to Register Shell Script as a
concurrent program
- Sample Shell Script to copy the file
from source to destination
- Basic Shell Script Commands
step 1:
=======
Place the <name>.prog script under the bin directory for your
applications top directory.
For example, call the script ERPS_DEMO.prog and place it under
$CUSTOM_TOP/bin
step 2:
=======
Make a symbolic link from your script to $FND_TOP/bin/fndcpesr
For example, if the script is called ERPS_DEMO.prog use this:
ln -s $FND_TOP/bin/fndcpesr ERPS_DEMO
This link should be named the same as your script without the
.prog extension.
Put the link for your script in the same directory where the
script is located.
step 3:
=======
Register the concurrent program, using an execution method of
‘Host’. Use the name of your script without the .prog extension
as the name of the executable.
For the example above:
Use ERPS_DEMO
step 4:
=======
Your script will be passed at least 4 parameters, from $1 to $4.
$1 = orauser/pwd
$2 = userid(apps)
$3 = username,
$4 = request_id
Any other parameters you define will be passed in as $5 and higher.
Make sure your script returns an exit status also.
Sample Shell Script to copy the file from source to destination
#Note: If you see # in front of any line it means that it’s a comment line not the actual code
#** ********************************************************************
# Created By : Prudhvi A
# Creation Date : 19-FEB-2008
# Script Name : ERPSCHOOLS.prog
# Description : This Script accepts three parameters
# 1)Data File Name 2)Source Directory Path 3)Target Directory Path
# Then copy the file from source location to target location.
# If copy fails send the error status/message to concurrent program so that user can see status.
#
#
# ========
# History
# ========
# Version 1 Prudhvi A 19-FEB-2008 Created for erpschools.com users
#
#** ********************************************************************
#Parameters from 1 to 4 i.e $1 $2 $3 $4 are standard parameters
# $1 : username/password of the database
# $2 : userid
# $3 : USERNAME
# $4 : Concurrent Request ID
DataFileName=$5
SourceDirectory=$6
TargetDirectory=$7
echo “————————————————–”
echo “Parameters received from concurrent program ..”
echo ” Time : “`date`
echo “————————————————–”
echo “Arguments : ”
echo ” Data File Name : “${DataFileName}
echo ” SourceDirectory : “${SourceDirectory}
echo ” TargetDirectory : “${TargetDirectory}
echo “————————————————–”
echo ” Copying the file from source directory to target directory…”
cp ${SourceDirectory}/${DataFileName} ${TargetDirectory}
if [ $? -ne 0 ]
# the $? will contain the result of previously executed statement.
#It will be 0 if success and 1 if fail in many cases
# -ne represents not “equal to”
then
echo “Entered Exception”
exit 1
# exit 1 represents concurrent program status. 1 for error, 2 for warning 0 for success
else
echo “File Successfully copied from source to destination”
exit 0
fi
echo “****************************************************************”
Basic Shell Script Commands
# Create Directory
mkdir <dirname>
# Remove Directory
rmdir <dirname>
#remove folder with files
rm -r -f <dirname>
# Change Directory
cd <newpath>
# Create new file
vi <newfile.ext>
#insert data into file
vi <openfilename.ext>
esc i <make changes>
#Save file
esc :wq enter
# exit without saving changes
esc :q! enter
# open existing file
vi <existingfilename.ext>
#remove file
rm <filename.ext>
# copy file with same name
cp <sourcedir>/<sourcefilename.ext> <destinationdir>
# copy file with new name
cp <sourcedir>/<sourcefilename.ext> <destinationdir>/<newfilename.ext>
# Move file with same name
mv <sourcedir>/<sourcefilename.ext> <destinationdir>
# move file with data appended to filename in the front
mv <sourcedir>/<sourcefilename.ext> <destinationdir>/`date+%H%M%d%m%y`<filename.ext>
#print line
echo “your text here to print”
#print date
echo `date`
FNDLOAD to transfer AOL Objects from one instance to other
The
Generic Loader (FNDLOAD) is a concurrent program that can transfer Oracle
Application entity data between database and text file. The loader reads a
configuration file to determine which entity to access. In simple words FNDLOAD
is used to transfer entity data from one instance/database to other. For
example if you want to move a concurrent program/menu/value sets developed in
DEVELOPMENT instance to PRODUCTION instance you can use this command.
Steps to Move a Concurrent program from one instance(Database) to other
These following are the other entity data types that we can move with FNDLOAD
1 – Printer Styles
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name”
2 – Lookups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”FND”
LOOKUP_TYPE=”lookup name”
3 – Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=COL_ALL:REF_ALL:CTX_ONE:SEG_ALL APPLICATION_SHORT_NAME=”FND” DESCRIPTIVE_FLEXFIELD_NAME=”desc flex name” P_CONTEXT_CODE=”context name”
4 – Key Flexfield Structures
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL APPLICATION_SHORT_NAME=”FND” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”
5 – Concurrent Programs
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”FND” CONCURRENT_PROGRAM_NAME=”concurrent name”
6 – Value Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”
7 – Value Sets with values
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name”
8 – Profile Options
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”FND”
8 – Request Groups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group” APPLICATION_SHORT_NAME=”FND”
10 – Request Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME=”FND” REQUEST_SET_NAME=”request set”
11 – Responsibilities
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility”
12 – Menus
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=”menu_name”
13 – Forms Personalization
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES function_name=FUNCTION_NAME
Note: UPLOAD command is same for all except replacing the .lct and passing any extra parameters if you want to pass
Steps to Move a Concurrent program from one instance(Database) to other
- Define your concurrent program and save it in first instance(for how to register a concurrent program
- Connect to your UNIX box on first
instance and run the following command to download the .ldt file
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”Concurrent program application short name” CONCURRENT_PROGRAM_NAME=”concurrent program short name” - Move the downloaded .ldf file to new
instance(Use FTP)
- Connect to your UNIX box on second
instance and run the following command to upload the .ldt file
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt
These following are the other entity data types that we can move with FNDLOAD
1 – Printer Styles
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name”
2 – Lookups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”FND”
LOOKUP_TYPE=”lookup name”
3 – Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=COL_ALL:REF_ALL:CTX_ONE:SEG_ALL APPLICATION_SHORT_NAME=”FND” DESCRIPTIVE_FLEXFIELD_NAME=”desc flex name” P_CONTEXT_CODE=”context name”
4 – Key Flexfield Structures
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL APPLICATION_SHORT_NAME=”FND” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”
5 – Concurrent Programs
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”FND” CONCURRENT_PROGRAM_NAME=”concurrent name”
6 – Value Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”
7 – Value Sets with values
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name”
8 – Profile Options
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”FND”
8 – Request Groups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group” APPLICATION_SHORT_NAME=”FND”
10 – Request Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME=”FND” REQUEST_SET_NAME=”request set”
11 – Responsibilities
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility”
12 – Menus
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=”menu_name”
13 – Forms Personalization
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES function_name=FUNCTION_NAME
Note: UPLOAD command is same for all except replacing the .lct and passing any extra parameters if you want to pass
FNDLOAD apps/apps 0 Y UPLOAD
$FND_TOP/patch/115/import/corresponding.lct upload_file.ldt
Subscribe to:
Posts (Atom)
-
Approval Management Engine FAQ 1. AME – What is it? i. AME is a simple to use Rules Engine for Defining Approval Policy ii. AME is ...
-
Register Custom Tables in Oracle Apps Say you have a custom table called “ERPS_EMPL...
-
OU & ORG ID & ORG CODE & ORG NAME ================================== select OPERATING_UNIT,ORGANIZATION_ID,ORGANIZATION_CODE...
