Report output file send to user

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

#|========================================================================================+
#|  INDUSTRIEES. |
#+========================================================================================+
# |
# Filename: REPORT_OUT_EMAIL_SH.prog |
# |
# Description: This script is developed to send the Shipping/Invoice Report |
# output to Email Id.The outfile taken from out directory is |
# sent to the EMAIL Specified. |
# |
# Revision History: |
# |
# Ver Date Author Revision Description |
# === ============ ==================== ==================== |
# 1.0 27-Apr-2011 Mahammad Sulthan Created the program |
# |
# |
# |
# |
#=========================================================================================+
#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
echo "Start of the Program"
EMAIL=`echo $1 | cut -d " " -f9`
P_EMAIL=`echo $EMAIL | cut -d '"' -f2`
PATH_NAME=`echo $1 | cut -d " " -f10`
P_DIR=`echo $PATH_NAME | cut -d '"' -f2`
P_PARENT_REQUEST_ID=`echo $1 | cut -d '"' -f12`
echo "Parameters passed :"
echo "================================================= \n"
echo "Mail id :" $P_EMAIL
echo "Outfile Directory :" $P_DIR
echo "Parent Request Id : $P_PARENT_REQUEST_ID "
echo "================================================ \n"
P_OUT_FILE_NAME="ONTCDELRI_"${P_PARENT_REQUEST_ID}"_1.PDF"
echo " Email File Name : $P_OUT_FILE_NAME "

sqlplus -s $FCP_LOGIN <<!
DECLARE
A NUMBER;
X_req_id number;
X_dev_status VARCHAR2(30) := NULL;
X_phase VARCHAR2(30) := NULL;
X_status VARCHAR2(30) := NULL;
X_dev_phase VARCHAR2(30) := NULL;
X_message VARCHAR2(200) := NULL;
BEGIN
loop
IF FND_CONCURRENT.WAIT_FOR_REQUEST
( request_id => ${P_PARENT_REQUEST_ID}
,interval => 300
,max_wait => 300
,phase => X_phase
,status => X_status
,dev_phase => X_dev_phase
,dev_status => X_dev_status
,message => X_message
)
THEN
exit;
END IF;
end loop;
END;
/

!
#---------------------------------------------------------------------------------------------------
# Checking if output file exists. If yes, then mailing them else displaying no files found
#---------------------------------------------------------------------------------------------------
if test -f $P_DIR/$P_OUT_FILE_NAME;
then
body=" Hi, \n \n This is an automated email, please do not reply to this email. Contact Helpdesk/Support Team for any further help. \n \n Thank You!"
(echo $body;uuencode "$P_DIR/$P_OUT_FILE_NAME" "$P_OUT_FILE_NAME") | mailx -m -s "Delivery Note Report" $P_EMAIL
echo "Report is completed and EMAIL has been sent to $P_EMAIL Successfully. "
else
echo "No Output files found to be sent in the mail"
fi
echo "****************************************************************"

XML Publisher and Data Template - SQL Query to develop BI Publisher

The XML Publisher(XMLP / BI Publisher ) requires a Template layout and Data XML to generate the report in
PDF, EXCEL, RTF or HTML format .The Data XML can be generated from Oracle Report, OA Framework and Data Template.
Using this approach, you can develop BI Publisher reports in Oracle EBS, without having to develop an RDF report.
You can specify an SQL Statement in an XML file, and results of that SQL Statement are published as XMLP Output.

In this article lets understand the usage of Data Template with a small demo. In the next article we will discuss in detail the Data Template XML Schema.

The data template is the XML document whose elements communicate the following information to the data engine.

• Data Query : The SQL query with bind parameter specifying the data to be fetched.
• Bind Parameters : Definition of each bind parameter defined in the query.
• Data Structure : Definition of the Output XML Structure.
• Data Triggers : Triggers that should be executed before or after the data query execution. Triggers are used to either do initialization or do some post query operation.

Hence Data Template is the means by which we inform the data engine to fetch the data of our interest. Apart from the these , data template also contains other elements. To make our example simple and easy to understand, lets deal with the above 4 elements.

Sample Data template :

XML Publisher Data Template Architecture

The Data engine execute the Data template to generate the Data XML file . The Data XML file is then merged with the Template layout by the XMLP engine to generate the XMLP
Report.

Lets try to build a sample XMLP report to display Employee Details. The Following are the steps involved

Step 1: Design the Data Template XML File.
Step 2: Create the Data Definition in XML Publisher and associate it with Data Template
Step 3: Define a Concurrent Program to generate the Data XML File.
Step 4: Define the RTF Template using the Generated Data XML.
Step 5: Registering the Template with XML Publisher
Step 6: Execute the concurrent program to generate the PDF report.

Step 1 : Design the Data Template XML File.

This Data Template selects the employee details from the EMP table. The select query specifies the columns of interest. It uses a bind parameter to filter the employees of a particular department.

For each bind parameter in the query , we need to define a Parameter. In this case we have defined p_DeptNo parameter.

In the data structure element, we have defined the output XML structure. He we map the columns in the select query with the data xml elements.

Step 2 : Create the Data Definition in XML Publisher and associate it with Data Template
Using Responsibility: XML Publisher Administrator --> Home --> Data Definitions --> Create Data Definition

Screen 1 : Enter the data definition Details and click on Apply. Note down the Code. The code should be used as the short name of the concurrent program.

Screen 2: Click on the "Add File" button against the "Data Template" Label.

Screen 3: Upload the Data Template XML file and Click on Apply.
Screen 4 : The Data Template is associated to the Data Definition..

Step 3: Define a Concurrent Program to generate the Data XML File.

Using Responsibility System Administrator --> Concurrent : Program --> Define.

Screen 1: Define a Concurrent Program With executable = XDODTEXE and Output Format = XML.

Screen 2: For each parameter in the Data Template, define a parameter in the concurrent program. The Data Template parameter name should match the concurrent program parameter token.

Screen 3: Associate the Concurrent Program to a request group.




Screen 4 : Execute the concurrent program "Employee Details Data Template Demo"and click on the output button get the Data XML. Save the XML file. We will use it to generate the RTF Template.

Step 4: Define the RTF Template using the Generated Data XML.

Install the Oracle XML Publisher Desktop available via patch 5887917. Open the Microsoft word. You should be able to see the following menus and toolbars.

Using the menu Data -> Load XML Data... , load the XML File generated from Jdeveloper

If the XML files gets loaded successfully, then you should get the below confirmation.

Using the Table Wizard as below to create the 'Table Report Format' with all the columns of EMP.

The Table Report Format Template should be like

Step 5: Registering the Template with XML Publisher
Responsibility: XML Publisher Administrator --> Home --> Templates --> Create Template.
Enter the Template Name and other details. The Data Definition should be the data definition defined in the above steps. Click on the apply button

Step 6 : Execute the concurrent program to generate the PDF report.
Open the concurrent program for submission and click on the option button to specify the XML Publisher Template defined in the previous step and Output type to be used. The options specified once gets stored . Hence if open the option window again, the template name and the output type will be available. Click ok button and run the concurrent program. When the program completes. Click on the Output button to see the PDF report.

XML Publisher using a Data Template and XDODTEXE

Create a concurrent program: Use the output format as XML

Define a Data Definition for the concurrent program:

Concurrent program short name and Data definition code has to be same.

Create the data template file and upload it while defining in the data definition

Attach the concurrent program to a responsibility:

Run the concurrent program:

Save the output as .XML file:

Use the .XML file to design the template definition: Save the file as .rtf file

Create the template definition with the .rtf file:

Change the concurrent program output format to PDF:

Run the concurrent program:

Payment terms

Receivables lets you define standard payment terms for your customers to specify the due date and discount date for their open items.
Payment terms can include a discount percent for early payment and you can assign multiple discounts to each payment term line. For example, the payment term ’2% 10, Net 30’ indicates that a customer is allowed a two percent discount if payment is received within 10 days; after 10 days, the entire balance is due within 30 days of the transaction date with no applicable discount.
Prepayment check box if you are defining a prepayment payment term. Receivables feeder systems, such as Oracle Order Management, can optionally implement business processes around prepayment payment terms to indicate that a particular business transaction requires the capture of funds before the delivery of a product or service.
Enter the Date on which payment is due for this installment term (optional). If you do not complete this field, enter a value for either Due Days or both Day of Month and Months Ahead

Default Payment Terms Hierarchy
Receivables use the following hierarchy to determine the default payment term for your transactions, stopping when one is found:
1. Bill–to site
2. Customer Address
3. Customer
4. Transaction Type
Predefined Payment Terms
Receivables provides the following predefined payment terms:
30 NET: The balance of the transaction is due within 30 days.
IMMEDIATE: The balance of the transaction is due immediately (i.e. on the transaction date). You can use this payment term with your chargeback and debit memos.
To define a payment term:
Navigate to the Payment Terms window and Enter the Name of the payment term.

1. Select the Prepayment check box if you are defining a prepayment payment term.
Receivables feeder systems, such as Oracle Order Management, can optionally implement business processes around prepayment payment terms to indicate that a particular business transaction requires the capture of funds before the delivery of a product or service.
2. To associate a credit check with this payment term, check the Credit Check box. Oracle Order Management uses this information to determine when to place an order on hold.
In Oracle Order Management, if the profile for an address does not have credit checking limits defined in a particular currency but the customer does, then the order passes credit check. If the address does not have limits in the currency and neither does the customer, then the order is compared to the customer limit in that currency.
3. If you do not want to let your customers take discounts for partial payments on items associated with this payment term, then uncheck both the Allow Discount on Partial Payments check box as well as the check box for the Discount on Partial Payment system option.
4. Enter the Discount Basis you want Receivables to use when calculating discounts for your invoices. Choose one of the following discount methods:

• Invoice Amount: Choose this option to calculate the discount amount based on the sum of the tax, freight charges, and line amounts of your invoices.
• Lines Only: Choose this option to calculate the discount amount based on only the line amounts of your invoices.
• Lines, Freight Items and Tax: Choose this option to calculate the discount amount based on the amount of line items, freight, and tax of your invoices, but not freight and charges at the invoice header level.
• Lines and Tax, not Freight Items and Tax: Choose this option to calculate the discount amount based on the line items and their tax amounts, but not the freight items and their tax lines, of your invoices.

5. Enter the Installment Option for items assigned to this payment term. This indicates how Receivables will allocate the freight and tax charged to transactions using this payment term. Choose 'Include tax and freight in first installment' to include all tax and freight charges in the first installment. Choose 'Allocate tax and freight' to distribute tax and freight charges across all installments.
6. Enter the Base Amount for this payment term. The default is 100, but you can change it. The base amount is the denominator for the ratio Receivables uses to determine the amount due for installments of invoices to which you assign this payment term. The sum of the relative amounts for all of the payment schedules that you define for these payment terms must be equal to the value that you specify as a base amount.
If the base amount is different from the relative amount, and you set the Installment Options field for this payment term to 'Allocate tax and freight', Receivables prorates the base amount across the relative amounts of this term's payment schedules based upon the ratio you define. Receivables uses the following equation to determine the original amount due for each installment of invoices to which you assign this payment term:
Amount Due = Relative Amount/Base Amount * Invoice Amount
If you select 'Include tax and freight in first installment' as the Installment Options field value for a payment term, the base amount and the relative amounts that you specify for this term's payment schedules only indicate how the original line amounts of the invoices to which you assign this term are distributed across different installments.
In this case, the original freight and tax amounts are included in the first installment in addition to the line amount allocated by the ratio of the base amount and the relative amount that you specify for the term's first payment schedule. Receivables uses the following equation to determine the original amount due for the first installment of invoices to which you assign this payment term:
Amount Due = (Relative Amount/Base Amount * Base Line Amount) + Base Freight Amount + Base Tax Amount
7. If you want transactions assigned to this payment term to be printed before the due date, enter a number of Print Lead Days. Receivables will print this transaction x number of days before the due date, where x is the number of days you enter here.
8. Enter a range of Effective Dates for this payment term. If you do not enter an end date, this payment term will be active indefinitely.
9.1 Enter a line number for the installment term that you are defining in the 'Seq' field. Enter a higher number for each installment term with a later due date. For example, if you create terms with 50% due in 15 days and 50% in 30 days, enter '1' in this field for the first line and '2' for the second line.
9.2 Enter the Relative Amount for this payment term. This is the numerator of the ratio that Receivables uses to determine the amount due for this installment of these payment terms. The sum of the relative amounts for all of the payment schedules that you define for each payment term must be equal to the base amount for this term.
9.3 Enter the number of Days after the invoice date that payment is due for this installment term (optional). For split payment terms, this number indicates the number of days after the invoice date that an installment is due.
9.4 Enter the Date on which payment is due for this installment term (optional). If you do not complete this field, enter a value for either Due Days or both Day of Month and Months Ahead.
9.5 If you are defining proxima terms, enter the Day of Month that payment is due for this installment term. For example, if payment is due on the fifteenth of each month, enter '15.'
9.6 If you are defining proxima terms and you entered a value for Day of Month, enter the Months Ahead to which this installment term of the proxima terms refer. For example, if you entered '15' for Day of Month and you enter '2' here, an invoice dated in May will have a due date of July 15.
New in R12
If you want to use this payment term for balance forward billing, select an appropriate balance forward billing cycle from the Billing Cycle LOV.

• Balance Forward Billing.
• Balance Forward Billing Cycles.
• Setting Up Balance Forward Billing.

Note: You cannot update the billing cycle, once a balance forward billing payment term is attached to a profile.
Because balance forward bills cannot be split across installments, in the case of a balance forward payment term:
Any value entered in Base Amount defaults to 100.
Installment Options becomes disabled and any data entered before selecting a cycle defaults to Include tax and freight in first installment.
You can populate only one row in the Payment Schedule section and the Sequence Number and Relative Amount values for the row default respectively to 1 and 100.
Date Due becomes disabled. However, you can populate Days, Day of Month, and Months Ahead.
Note: You cannot change existing payment terms from regular payment terms to balance forward billing payment terms and vice versa.