Thursday, March 23, 2017

Concurrent Program Status Code & Phase Code meaning, SQL

Concurrent Program Status Code & Phase Code meaning, SQL

Concurrent Program Status Code & Phase Code meaning


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

PHASE_CODE:

C - Completed
I - Inactive
P - Pending
R - Running

SQL:

  SELECT user_concurrent_program_name,
         DECODE (phase_code,
                 'C', 'Completed',
                 'I', 'Inactive',
                 'P', 'Pending',
                 'R', 'Running') Phase,
         DECODE (status_code,
                 'A', 'Waiting''B',
                 'Resuming''C', 'Normal''D',
                 'Cancelled''E', 'Error''F',
                 'Scheduled''G', 'Warning''H',
                 'On Hold''I', 'Normal''M',
                 'No Manager''Q', 'Standby''R',
                 'Normal''S', 'Suspended''T',
                 'Terminating''U', 'Disabled''W',
                 'Paused''X', 'Terminated''Z',
                 'Waiting') status,
            actual_start_date,
         actual_completion_date,
         completion_text,
         argument_text,
         requestor--, cp.*
    FROM fnd_conc_req_summary_v cp

Oracle Alerts in R12


1  Overview of Oracle Alerts
2  Defining Alerts
3  Type of Alerts
4  Check the Alerts

Overview of Oracle Alerts:

·                      Keep you informed of critical activity in your database
·                      Deliver key information from your applications, in the format you choose
·                      Provide you with regular reports on your database information
·                      Automate system maintenance, and routine online tasks

Overview:


Oracle Alert is your complete exception control solution.

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want. You can define one of two types of alerts: an event alert or a periodic alert.

An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following:

·                      A database event that you want to monitor, that is, an insert and/or an update to a specific database table.
·                      A SQL Select statement that retrieves specific database information as a result of the database event.
·                      Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

A periodic alert, on the other hand, checks the database for information according to a schedule you define. When you create a periodic alert, you specify the following:

·                      A SQL Select statement that retrieves specific database information.
·                      The frequency that you want the periodic alert to run the SQL statement.
·                      Actions that you want Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a
·                     SQL statement script.


Defining Alerts:


Navigator: Alert Vision Manager USA

To define Alerts

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhX0IkdkJCBd_87F6mHFYZ-LiKG9xY9AbQFivaw5caReDo9yk7rpHsNf62gQZ9EphirSe_UmHULD8MdkV2uuVuCPeCPTQd3ey5A11HqLPuW2g_vndzSA0c5k6KkhnIpm7w2Kk0tDh5sygo/s320/image001-734096.png

Types of Alerts:

1. Periodic Alerts


Periodic alerts periodically report key information according to a schedule you define.

To define a periodic alert:
1. Navigate to the Alerts form.
2. Enter the name of the application that owns the alert in the
Application field. This application must reside in the same Oracle database as Oracle Alert.
3. Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
4.Enter a name for the alert that is unique within the application. Use an initial character other than a pound sign (#), a colon (:), or a percentage sign (%).

1.                  Select a frequency for your periodic alert. You can choose from nine frequency options:
2.                  
Frequency :

On Demand             :-We have to run the alert to fire.

Enter which Application u want to specify alerts, name of the alert,
Click whether its periodic or Event

On Day of Month: It will fire last day of every month.
It will start from 12 AM up to 11 AM morning and each 2 hours interval the alert will fire once.
           Exa: 12AM, 2 AM, 4 AM … … … … 8am, 10am.

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLU0w58EYbT2SzfW_7RjxrPqq-vHg7YLJNiALDQQGbWz4jLVdJJbB6SxRJyFlvxhrfyiKgZ0Bms1kU5vzz0M-FW23qKxpV-h6StThPloVUgdKsgpH6U37GBLe1oVin-cyjoyQUJvnm4xQ/s320/image002-737933.jpg

On Day of Week : Same as On day of Month.

Every N Calendar day: It will fire in each two days.

Every N Business Day: It will fire once in each three business day.(It will not count the holidays)

Every Day:  It will fire daily

Every Other Day :  It will fire in a alternative day.

Every Business Day: It will fire every day except holiday.

Every Other Business Day: It will fire in an alternative business (working) day.
i.e  It will not consider holidays

Import :  We can import the files(which contain the query) if the query size is big, if it exceed the memory.
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilEbPj-MsIpszgzxuVFz_NjyblbHpXXbJOd-gqTEkoVszmp2fLsUIKTtZqGQAdtYzPJRfbpLsT26G4UNOqojvXRmw04WuRNGeAFfb7Qu85XncVLsm_hv-yO27404tbphlQ8jH8U_J_T78/s320/image003-742530.jpg

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpcZnh0Fr8PQ5WfZU5WtnjSoWAcuA31bawW1T3HpbF4aSU92tDux1vHlbT-AZ5WHEqKhs03NfPggbsnMUr91T8mTg17HMFydrTisEP2M7Cu6VTqHTBzhVTKKCoSit_I9XCit68shRfiA4/s320/image004-749943.png
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPDcFUIDdqMOmxIQKlyszJW8cCLLcHmuG_kaWZbSR40_-fBYIptKLDO0YFDdg_ygIphqEsHpqIjQg7xUafpf1YfrOp_ZI1Zdy6j-LI57gb38t3FWUXD4J91_dYJoLO_LTuVib1bfWIduI/s320/image005-752458.png


After writing the Query in Select Statement area click on Verify  to verify the query and
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfOSsQO5tyPFBrb61pIQh-7p4-nBbLmnh98ZqsSe3QeBDqc2KCPx-7lv0wU6mfjYTyryMidTgh-HCYhAtR61ldoWJ3l3MXFvOtzaWVszVrzlomijFfkvuJ53IRD-NXPHqjkhSti4GyQqw/s320/image006-765184.jpg

After Click on Verify Button the following message will come.
        Click on OK and Save

Then click on Run  to execute the query.
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYC2vy-o9ZTveGNz92ky_kc4A1-06JwbSfPnW1Pbk80uSXlwrP1hH7nlBN-CPvh2Bdo96ew8muN_LSgYqoWIRYSp9VAdV_QdRhkDLUPaSiBZ9ScdAu9xFntnua62ch_oNzzwpCMbSlXfM/s320/image007-771352.jpg
            Click on Run  button , the following screen will come. And click on OK.

2 . Click on Actions  Button and enter the Action information  like following screen
Details Action perform once for each exception found,
Summary Action perform once for all exception found
No exception performs when no exception was found.

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjj4BIgcZtutx4-h2g3VmlRSgmad8zsrrlceGpOOXzGJbfctcjhbN0aaGNd3zbvksiw1WDvsx0aUaY2lf15RzL4YbUa6p8y2c_5uGlwaNHyDr4GBfo2eZ_OOlkFARwRj91TNIVcqiMb6JM/s320/image008-786989.jpg

2.click on Action Details  and enter the value  and save.
Concurrent Program :  Used to execute the Concurrent program.
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAk9pu0SRJbjW3baBsPPzL3PiolTMURKeM79qjRs9krDEjHThu-8sehpXcWf3Z2BAgM8C_nP9GD7ZqLuJpgFWrGo2Jg5SRyTw1ow-3HpauQ8WBIXdKcHvpmtClAXYnkqVNAIp3ZECeBxw/s320/image009-700468.jpg

Message : Used to send the message via mail.
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQ6dIWCI29YjPXLa-hmIn5wHtn-2ma-mxjc9R8zE3yM3oy6rN4RQW6I-DR3pVK5s6PWlrOIUEZlKjbJ2xFs5o68f35r1m60kmp7PzIefjDlH9Op0CqF3VB177t5m94_St0GrDPeHwlwQI/s320/image010-706753.jpg

Operating System Script:  Used to run the OS Script.
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvEQUjdcg-CdYtF1i15bFWkgGimtEdJNRKNRlTCxGQBeiswjL_ykaYXtGpVHVhaz51zDk_23CaOEZAXaz16Q7eSwAQsZkVCbfvjbojOwCY03CS2eibJZnZLa4pZhmMAVhNbjisOrteeGg/s320/image011-710866.jpg

SQL Statement Script : Used to run the SQL statement Script.
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpJoQkVpNkGJKFhQj1O9p7GN0eUlBqwC3RKq6AtiS9r_NVIwLdrIeHMf-OZwbUNMbGQGuJoHd-V6KG-IM-4WmroeE9-2MvQZN4Kg3k8xE3K-jcNgO1nl0p5613-ldGWFweyHFlryJXe_g/s320/image012-715440.jpg

3. Click on Action Sets   button   from Alert window
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuxzxWdspEFKvPwkFHFfY-mwCtuB6AhigU1cimFUplFwvRjfkMyLtHLjpOpHx7KxfdEl0xpll2ahJ547DWZBvsLANzjHHOaVdupXFMe6iLJFG6lzHv-7CZKxwHbR9pGDyLCRqVvOuf03Y/s320/image013-721165.jpg
Then click on Action Set Details
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdHsFTyhBWrAVsEsxhBLOBcS_VRinmqLKaW5fb1Gx2DzB3VCzbz1toAilkBdEYN7PpebbaGIFvxeR2_mgxfAHqso_dZAyqq5MKrCMUyvnnmGjfosVdnx9gxra3caPvSGQoql2V9bHNakY/s320/image014-726308.jpg
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVzr78eaxJv8xKYjADZ1zyMEnk5T0v6CsfyYBOmDZ3yb9oDZgJNafF9ZMRU6SlW7zGCWxAvXMeY4-KgQGuORTohRqYG2voaeBgmB3W_KKPib7IxsU2MuonBKxMzGin4xpNQFHJ7lrn-T0/s320/image015-731530.jpg

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhFyvjlAk8Z5I4HbTiVuRIQzJ5Ks2XZnHNRYNmNwoPiDpDlWpIcCYX2fo0nbIQIUXIRz0SGQECw-LN95CHOyyIMLls8TTwbVbjB4EuhT_giF4JpEflc27oUyoxmD6nCKmmD4tw3wp5HzM/s320/image016-735958.jpg

4. Then click on Alert Details from the Alert window and enter the value and save.
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1rsKflhB3LMoPzsmiVe27JeoYK4K8zztV7MF3q0y5yIVih19M892QuE7Of73EMPfxtBgoDRrA9Fgs5B1I9xtS0vK3kn11l20U7iLnhLOmnhoylp_Rj0ZG03KpQm6w_uphpa8WZAnZkUE/s320/image017-740070.jpg

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKwZflJXYY0pCnUUD3pXYuUQ_zTJTZVZkQ3cRY9LhMf4o54CI9AFtbCaKRjtOd9Yik1kSE4kLQDIcomRuWnLj7M80A4HrIhOzlsEmVtUFwieIFH1mvr_tcrOEVBlVNk93tYZBZPhFI4KQ/s320/image018-745225.jpg


Event Alerts:        


Creating an Event Alert
To create an event alert, you perform the following tasks in the order listed:
• Define the database events that will trigger your alert
• Specify the details for your alert
• Define actions for your alert
• Create action sets containing the actions you want your alert to perform
This section focuses on the first task of defining the database events that trigger your event alert and divides the task into smaller sub–tasks.

To define an event alert:
1. Navigate to the Alerts form.
2. In the Application field, use the list of values to choose the name of the application that owns the alert. This application must reside in the same Oracle database as Oracle Alert.
3. Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
Enter a name for the alert that is unique within the application. Use an initial character other than a pound sign (#), a colon (:), or a percentage sign (%).
4. In the Type field, choose Event.
5. Check Enabled to enable your event alert.

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjon8zLO4xUxpvyyvX0ldJYNkYA8P96Bve09DQQOBXk7g0xDxJ2EsiGZc64E1zbwosAqhkEwAyowRhOac3KzEHrFHF_9ZvIZRV2y7qeyejCZ9WbBl3Rz8r-E8drEwuteX667QEbZHedSLA/s320/image019-748115.jpg

To specify the alert details for an event or periodic alert:
1. With your alert definition displayed in the Alerts form, choose Alert Details. The Alert Details window that appears contains three tabbed regions: Installations, Inputs, and Outputs.

2. In the Inputs tabbed region, Oracle Alert automatically displays the inputs used in your Select statement, unless they are the implicit

inputs: :ROWID, :MAILID, :ORG_ID and :DATE_LAST_CHECKED.

The values of the implicit inputs are as follows:

• ROWID—Contains the ID number of the row where the insert or update that triggers an event alert occurs.
• MAILID—Contains the email username of the person who enters an insert or update that triggers an event alert.
• ORG_ID—Contains the organization ID that is selected when the alert runs.
• DATE_LAST_CHECKED—Contains the date and time that the alert was most recently checked.

3. You can optionally add a description for each input, but you must specify the data type (either character, number, or date) for the input, because Oracle Alert uses the data type to validate the default values for inputs you enter in the Default Values field and in the Action Set Inputs tabbed region of the Action Sets block.

4. Enter a default value for your input. You can enter up to 240  characters. This value automatically becomes the default value for your input in each action set you define.

5. In the Outputs tabbed region, Oracle Alert automatically displays the outputs used in your alert Select statement without the ampersand (&) and any numbering format. You can add meaningful descriptions for the outputs.

6. You can specify the maximum number of output characters you want to display in your detail or summary message actions. See: Formatting Summary Message Actions:

7. If your output value is numeric, enter the SQL*Plus format mask in the Number Format field.

8. You can also check the Check for Duplicates check box to customize the combination of outputs you want to verify as a possible duplicate exception each time the alert is checked. A duplicate exception is an exception that existed in your database during previous alert checks. You can define Oracle Alert to perform certain actions based on the presence of these duplicate exceptions.

9. In the Installations tabbed region, specify an Oracle ID if you want Oracle Alert to check your alert against that specific Oracle ID. You can select only those Oracle IDs that are associated with the application that owns your alert.
If you do not specify an Oracle ID in this region, Oracle Alert checks your alert against all installations in your database of the application that owns your alert.

10. If you have multiple organizations defined in your Oracle Applications schema, you must specify the organization you want the alert to run against in the Operating Unit field.

Click Action details,

In Action details Navigator
They are four types of Actions

Concurrent Program :       Used to execute the Concurrent program.
Message :                             Used to send the message via mail.
Operating System Script:  Used to run the OS Script.
SQL Statement Script :      Used to run the SQL statement Script.

Check the Alert:

1.                  Go to Alert Manager
(N) Request>Check
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlSUZWPUU8sl-BhBymBUqGx3JEl8s0sqZlwXcrYyxOiuZfr1zzsmfnVkMEXx2ITDnPeYkLuwp6zrp6Tz5scuK4zP2HYXxFviLNrN8HVVEXMArsqlD7RAJSKt_XEPrZr5NPrXLN-k4Pg3U/s320/image020-756217.png

Then View> Request >Find from Alert Manager responsibility.
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSofPAzf0xxn4-WdUHNmsNUYdCvQitH2cIQn_TZeenfzruMSxqQrZDBfX7SDl1-Gqkocad_tA7IwKbN_4IiJXfr17sLczMVCfutbwkNpJlTP0vSMw17H_wKpEkBBCAT11viuvB6s8ZbfY/s320/image021-766373.jpg

After completed Normal check the mail.

Query to Find Out Oracle Alerts

The following query finds all enabled custom alerts. You can comment out the very last two lines (alr.enabled_flag and alr.created_by) to display all both enabled and disabled alerts.

-------------------------------------------------------------------------------
-- Query to find Custom Oracle Alert
-------------------------------------------------------------------------------
SELECT alr.application_id,
alr.alert_id,
alr.alert_name,
alr.start_date_active,
alr.description,
alr.sql_statement_text
FROM alr.alr_alerts alr
WHERE 1=1
AND alr.created_by <> 1 -- show only custom alerts
AND alr.enabled_flag = 'Y'; -- show only enabled alerts


Oracle Apps Alerts Tables:

Oracle Apps Alerts Table
ALR_ACTION_DATA
ALR_ACTION_GROUP_MEMBERS
ALR_ACTION_GROUPS
ALR_ACTION_HISTORY
ALR_ACTION_OUTPUTS
ALR_ACTION_SET_CHECKS
ALR_ACTION_SET_INPUTS
ALR_ACTION_SET_MEMBERS
ALR_ACTION_SET_OUTPUTS
ALR_ACTION_SETS
ALR_ACTIONS
ALR_ACTUAL_RESPONSES
ALR_ALERT_CHECKS
ALR_ALERT_INPUTS
ALR_ALERT_INSTALLATIONS
ALR_ALERT_OUTPUTS
ALR_ALERTS
ALR_ALERTS_A
ALR_DISTRIBUTION_LISTS
ALR_LOOKUPS
ALR_MESSAGE_SYSTEMS
ALR_ORACLE_MAIL_ACCOUNTS
ALR_OUTPUT_HISTORY
ALR_PERIODIC_SET_MEMBERS
ALR_PERIODIC_SETS
ALR_PROFILE_OPTIONS
ALR_RESPONSE_ACTION_HISTORY
ALR_RESPONSE_ACTIONS
ALR_RESPONSE_MESSAGES
ALR_RESPONSE_SETS
ALR_RESPONSE_VARIABLE_VALUES
ALR_RESPONSE_VARIABLES
ALR_VALID_RESPONSES

 

Oracle Alerts interview questions

 What are Oracle Alerts?
Oracle Alerts are used to monitor unusual or critical activity within a designated database. The flexibility of ALERTS allows a database administrator the ability to monitor activities from table space sizing to activities associated with particular applications. Alerts can be created to monitor a process in the database and to notify a specific individual of the status of the process.
2. What are the different types of alerts, Define it?
You can define one of two types of alerts: an event alert or a periodic alert.
Event alert: An event alert immediately notifies you of activity in your database as it occurs.
Periodic alert: A periodic alert, on the other hand, checks the database for information according to a Schedule you define.
3. What are the different business uses of Alerts?
a) Keep you informed of critical activity in your database
b) Deliver key information from your applications, in the format you choose to provide you with regular reports on your database information
c) Automate system maintenance and routine online tasks Information about exception conditions.
4. What can be done with Alerts?
· You can send notifications
· You can send log files as attachments to notifications
· You can call PL/SQL stored procedures.
· You can send approval emails and get the results.
· Print some content dynamically
5. What types of actions can be generated when an Alert is triggered?
When an alert is executed, the alert can send an email message, run a concurrent program, run an operating system script, or run a SQL statement script. Using response processing, Oracle Alerts can solicit a response from a specific individual and perform an action based on the response that it receives.
6. What is On-Demand periodic alert?
It is a periodic alert with frequency as ‘On-Demand’. That means there is no specific period assigned to this alert and you can run this alert at any time you want using Request Periodic Alert Check form.
7. What database events can cause what actions?
An insert and/or an update to a specific database table
8. What actions can you perform in an alert?
An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.
9. How event alert works?
When you define an event alert to monitor a table for inserts and/or updates, any insert or update to the table will trigger the event alert. When an insert or update to an event table occurs, Oracle Alert submits to the concurrent manager, a request to run a concurrent program called Check Event Alert. The concurrent manager runs this request according to its priority in the concurrent queue. When the request is run, Check Event Alert executes the alert Select statement. If the Select statement finds exceptions, Check Event Alert performs the actions defined in the enabled action set for the alert. If the Select statement does not find any exceptions, Check Event Alert performs the No Exception actions in the enabled action set for the alert.
10. What do you specify when creating a Periodic Alert?
a. A SQL Select statement that retrieves specific database information
b. The frequency that you want the periodic alert to run the SQL statement
c. Actions that you want Oracle Alert to perform once it runs the SQL statement.
11. Can you define Alert on Oracle Applications Tables?
Yes
12. What is Periodic Set?
You can create a set of periodic alerts that Oracle Alert checks simultaneously. Use the Request Periodic Alert Check window to check the periodic set. Note that each periodic alert you include in a periodic set continues to run according to its individually defined frequency.
13. How alert is different from database triggers?
a) Code can be modified and viewed in a screen
b) Periodic alert is not possible through Database trigger
c) Oracle Alert will also transfer your entire alert definition across databases. You can instantly leverage the work done in one area to all your systems.
d) Customizable Alert Frequency with Oracle Alert, you can choose the frequencyof each periodic alert. You may want to check some alerts every day, some only once a month, still others only when you explicitly request them.
14. What is Action Set?
An action set can include an unlimited number of actions and any combination of actions and action groups for your alert. You can define as many action sets as you want for each alert. Oracle Alert executes the alert Select statement once for each action set you define. During each action set check, Oracle Alert executes each action set member in the sequence you specify.
15. Can you define detailed or summary actions in alert?
Yes, Detail or Summary Actions you can choose to have Oracle Alert perform actions based on a single exception or a combination of exceptions found in your database.
16. What is Distribution List in Oracle Alert?
Distribution lists let you predefine a set of message recipients for use on many actions. If a recipient changes, you need only adjust it in the distribution list, not in the individual message actions.
17. Can you specify History Maintenance?
Alert History Oracle Alert can keep a record of the actions it takes and the exceptions it finds in your database, for as many days as you specify.
18. Can you perform actions when NO exceptions are found?
No Exception Actions : Oracle Alert can perform actions if it finds no exceptions in your database, same as alert actions.
19. What are the Action Levels for your alert actions?
There are three types of level for your action: Detail, Summary and No Exception.
During an alert check, a detail action performs once for each individual exception found, a summary action performs once for all exceptions found, and a no exception action performs when no exceptions are found.

Wednesday, March 22, 2017

Barcode with BI publisher report

Barcode with BI publisher report


Step#1 Local Machine Font Setup
 
Install the font on the local machine where XML template is being developed.
For barcode I have used font C39P24DmTt (V100025_.TTF)
Download the font and place it under windows font directory (C:\WINNT\Fonts)
Step#2 Assign the barcode font to the field in XML template
 
Add a placeholder and change the font of it to C39P24DmTt, place the asterix (*) in prefix and suffix to the barcode field.
 
Step#3 uploading the font on server
 
A.   Font file creation
Login to BI publisher responsibility
Under Administration tab -> Font Files ->Create Font file
Name the font file as C39P24DmTtand upload the TTF file.
 
 
B.   Create Font Mapping
Login to BI publisher responsibility
Under Administration tab ->Font Mapping->Create Font Mapping
 
C.   assigning the font to the report template
Login to BI publisher responsibility
Templates -> Open the template of the concurrent program you wanted to put barcode -> Click on the edit configuration on the right corner You will see all the configurable options 



Finally run the report you shall see the barcode is being shown on the field

                          XML Publisher report handling special characters 


XML Publisher report handling special characters 
Problem definition- The standard report runs fine when the output format is "Text" but errors out when it "XML".  The XML is generated partially with error message like “The XML page cannot be displayed or Authentication failed.

Solution-
Open the underlying RDF file and update the below value as
<?xml version="1.0" encoding="ISO-8859-1"?>
Once changed save the report , migrate it and test the XML output.


                                                      Code 128 Barcode with XML Publisher

STEP#1 XML Tags and template 
 
Create or use existing oracle reports concurrent program. Change the output format under concurrent program definition to XML, run the concurrent program and get the XML tags. Map the RTF based on the XML tags generated.

STEP#2 Getting barcode fonts 
 
For barcode I have used Code 128 from IDAutomation (you can download from their site)
After installing the font on local machine assign one of the IDAutomation font to the field in RTF template (for example an order number and font can be eg: IDAutomationSC128M)

STEP#3 Barcode font reading by handheld or scanner 
 
To make the barcode to be read by scanner, the encoder java code provided by vendor has to be compiled and convert it into a java class file.
A.      Below is the class code save the code in a notepad and rename it as BarcodeUtil.java
B.      Move the class file with EBSO file structure, put it under JAVA_TOP ($JAVA_TOP/oracle/apps/xdo/template/rtf/util)
C.      Compile the Java file with .class file as
$ cd $JAVA_TOP/oracle/apps/xdo/template/rtf/util  
$ javac -g BarcodeUtil.java  This command will generate the class file as BarcodeUtil.class.

Load Customers using TCA API

Load Customers using TCA API

Overview:

Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. This document provides information about how to create a customer using TCA API. These APIs utilize the new TCA model, inserting directly to the HZ tables. 





Architecture


Create Organization

DECLARE

p_organization_rec   hz_party_v2pub.organization_rec_type;

x_return_status      VARCHAR2 (2000);

x_msg_count          NUMBER;

x_msg_data           VARCHAR2 (2000);

x_party_id           NUMBER;

x_party_number       VARCHAR2 (2000);

x_profile_id         NUMBER;

BEGIN

p_organization_rec.organization_name := ’erpschools’;

p_organization_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_v2pub.create_organization (‘T’,

p_organization_rec,

x_return_status,

x_msg_count,

x_msg_data,

x_party_id,

x_party_number,

x_profile_id

);

DBMS_OUTPUT.put_line (‘party id ‘ || x_party_id);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_parties table and one record in hz_organization_profiles table. Similarly you can call hz_party_v2pub.create_person to create a record in the HZ_PARTIES and one record in HZ_PERSON_PROFILES tables. 

Create a Location

DECLARE

p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;

x_location_id NUMBER;

x_return_status VARCHAR2(2000);

x_msg_count NUMBER;

x_msg_data VARCHAR2(2000);

BEGIN

p_location_rec.country := ’US’;

p_location_rec.address1 := ’2500 W Higgins Rd’;

p_location_rec.address2 := ’Suite 920′;

p_location_rec.city := ’Thumuluru’;

p_location_rec.postal_code := ’60118′;

p_location_rec.state := ’IL’;

p_location_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_location_v2pub.create_location(

‘T’,

p_location_rec,

x_location_id,

x_return_status,

x_msg_count,

x_msg_data);

dbms_output.put_line(‘location id ‘||x_location_id);

dbms_output.put_line(SubStr(‘x_return_status = ‘||x_return_status,1,255));

dbms_output.put_line(‘x_msg_count = ‘||TO_CHAR(x_msg_count));

dbms_output.put_line(SubStr(‘x_msg_data = ‘||x_msg_data,1,255));

IF x_msg_count >1 THEN

FOR I IN 1..x_msg_count

LOOP

dbms_output.put_line(I||’. ‘||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));

END LOOP;

END IF;

END

Note: The above API shall create an address record in hz_locations table.

Create a Party Site:

Use the organization_id and location_id created above and create a party site.

DECLARE

p_party_site_rec      hz_party_site_v2pub.party_site_rec_type;

x_party_site_id       NUMBER;

x_party_site_number   VARCHAR2 (2000);

x_return_status       VARCHAR2 (2000);

x_msg_count           NUMBER;

x_msg_data            VARCHAR2 (2000);

BEGIN

p_party_site_rec.party_id := 1272023;

p_party_site_rec.location_id := 359086;

p_party_site_rec.identifying_address_flag := ’Y';

p_party_site_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_site_v2pub.create_party_site (‘T’,

p_party_site_rec,

x_party_site_id,

x_party_site_number,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (‘party site id ‘ || x_party_site_id);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_party_sites table.

Create Party Site Use

Use the above party site created

DECLARE

p_party_site_use_rec   hz_party_site_v2pub.party_site_use_rec_type;

x_party_site_use_id    NUMBER;

x_return_status        VARCHAR2 (2000);

x_msg_count            NUMBER;

x_msg_data             VARCHAR2 (2000);

BEGIN

p_party_site_use_rec.site_use_type := ’SHIP_TO’;

p_party_site_use_rec.party_site_id := 349327;

p_party_site_use_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_site_v2pub.create_party_site_use (‘T’,

p_party_site_use_rec,

x_party_site_use_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create a Contact Point

DECLARE

p_contact_point_rec   hz_contact_point_v2pub.contact_point_rec_type;

p_edi_rec             hz_contact_point_v2pub.edi_rec_type;

p_email_rec           hz_contact_point_v2pub.email_rec_type;

p_phone_rec           hz_contact_point_v2pub.phone_rec_type;

p_telex_rec           hz_contact_point_v2pub.telex_rec_type;

p_web_rec             hz_contact_point_v2pub.web_rec_type;

x_return_status       VARCHAR2 (2000);

x_msg_count           NUMBER;

x_msg_data            VARCHAR2 (2000);

x_contact_point_id    NUMBER;

BEGIN

p_contact_point_rec.contact_point_type := ’PHONE’;

p_contact_point_rec.owner_table_name := ’HZ_PARTIES’;

p_contact_point_rec.owner_table_id := ’1272023′;

p_contact_point_rec.primary_flag := ’Y';

p_contact_point_rec.contact_point_purpose := ’BUSINESS’;

p_phone_rec.phone_area_code := ’650′;

p_phone_rec.phone_country_code := ’1′;

p_phone_rec.phone_number := ’506-7000′;

p_phone_rec.phone_line_type := ’GEN’;

p_contact_point_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_contact_point_v2pub.create_contact_point (‘T’,

p_contact_point_rec,

p_edi_rec,

p_email_rec,

p_phone_rec,

p_telex_rec,

p_web_rec,

x_contact_point_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create an Org Contact:

DECLARE

p_org_contact_rec   hz_party_contact_v2pub.org_contact_rec_type;

x_org_contact_id    NUMBER;

x_party_rel_id      NUMBER;

x_party_id          NUMBER;

x_party_number      VARCHAR2 (2000);

x_return_status     VARCHAR2 (2000);

x_msg_count         NUMBER;

x_msg_data          VARCHAR2 (2000);

BEGIN

p_org_contact_rec.department_code := ’ACCOUNTING’;

p_org_contact_rec.job_title := ’ACCOUNTS OFFICER’;

p_org_contact_rec.decision_maker_flag := ’Y';

p_org_contact_rec.job_title_code := ’APC’;

p_org_contact_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

p_org_contact_rec.party_rel_rec.subject_id := 16077;

p_org_contact_rec.party_rel_rec.subject_type := ’PERSON’;

p_org_contact_rec.party_rel_rec.subject_table_name := ’HZ_PARTIES’;

p_org_contact_rec.party_rel_rec.object_id := 1272023;

p_org_contact_rec.party_rel_rec.object_type := ’ORGANIZATION’;

p_org_contact_rec.party_rel_rec.object_table_name := ’HZ_PARTIES’;

p_org_contact_rec.party_rel_rec.relationship_code := ’CONTACT_OF’;

p_org_contact_rec.party_rel_rec.relationship_type := ’CONTACT’;

p_org_contact_rec.party_rel_rec.start_date := SYSDATE;

hz_party_contact_v2pub.create_org_contact (‘T’,

p_org_contact_rec,

x_org_contact_id,

x_party_rel_id,

x_party_id,

x_party_number,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_org_contacts table and one record in hz_relationships table. When a contact is created, a record in hz_parties table gets created with party_type as ‘PARTY_RELATIONSHIP’.

Create a Customer Account:

DECLARE

p_cust_account_rec       hz_cust_account_v2pub.cust_account_rec_type;

p_person_rec             hz_party_v2pub.person_rec_type;

p_customer_profile_rec   hz_customer_profile_v2pub.customer_profilerec_type;

x_cust_account_id        NUMBER;

x_account_number         VARCHAR2 (2000);

x_party_id               NUMBER;

x_party_number           VARCHAR2 (2000);

x_profile_id             NUMBER;

x_return_status          VARCHAR2 (2000);

x_msg_count              NUMBER;

x_msg_data               VARCHAR2 (2000);

BEGIN

p_cust_account_rec.account_name := ’John”s A/c’;

p_cust_account_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

p_person_rec.person_first_name := ’John’;

p_person_rec.person_last_name := ’Smith’;

hz_cust_account_v2pub.create_cust_account (‘T’,

p_cust_account_rec,

p_person_rec,

p_customer_profile_rec,

‘F’,

x_cust_account_id,

x_account_number,

x_party_id,

x_party_number,

x_profile_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note:

This routine is used to create a Customer Account. The API creates a record in the HZ_CUST_ACCOUNTS table for party type Person or Organization. Account can be created for an existing party by passing party_id of the party. Alternatively, this routine creates a new party and an account for the party.

Customer profile record in the HZ_CUSTOMER_PROFILES can also be created while calling this routine based on value passed in p_customer_profile_rec. The routine is overloaded for Person and Organization.

Create a Customer Account Site

Use an existing Party Site

DECLARE

p_cust_acct_site_rec   hz_cust_account_site_v2pub.cust_acct_site_rec_type;

x_return_status        VARCHAR2 (2000);

x_msg_count            NUMBER;

x_msg_data             VARCHAR2 (2000);

x_cust_acct_site_id    NUMBER;

BEGIN

p_cust_acct_site_rec.cust_account_id := 3472;

p_cust_acct_site_rec.party_site_id := 1024;

p_cust_acct_site_rec.LANGUAGE := ’US’;

p_cust_acct_site_rec.created_by_module := ’TCA-EXAMPLE’;

hz_cust_account_site_v2pub.create_cust_acct_site (‘T’,

p_cust_acct_site_rec,

x_cust_acct_site_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create Customer Account Site Use Code:

DECLARE

p_cust_site_use_rec      hz_cust_account_site_v2pub.cust_site_use_rec_type;

p_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;

x_site_use_id            NUMBER;

x_return_status          VARCHAR2 (2000);

x_msg_count              NUMBER;

x_msg_data               VARCHAR2 (2000);

BEGIN

p_cust_site_use_rec.cust_acct_site_id := 3580;

p_cust_site_use_rec.site_use_code := ’INV’;

p_cust_site_use_rec.LOCATION := ’TCA’;

p_cust_site_use_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_cust_account_site_v2pub.create_cust_site_use (‘T’,

p_cust_site_use_rec,

p_customer_profile_rec,

”,

”,

x_site_use_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

More Customer API’s:


Org Contact RoleHz_party_contact_v2pub.Create_Org_Contact_Role
RelationshipsHZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
Customer ProfileHZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile
Customer Profile AmountHZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt
Customer Credit RatingHZ_PARTY_INFO_V2PUB.create_credit_rating
Sales PersonJTF_RS_SALESREPS_PUB.CREATE_SALESREP
Sales reps TerritoriesJTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES
Customer contactsHZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Customer Contact RoleHZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...