Alerts in Oracle apps in R12

Alerts are basically used automate the system maintenance, provide reports in the format chosen,for database activity/Business Requirement as it occurs..

few scenarios:
- A database event which was supposed to be monitored for instance insert into some table(event based alert).
- A SQL statement which provides specific information (event based alert).
- The frequency we want the report of a SQL statement(periodic alert)

Responsibility Name: Alert Manager

Pre-requisites:
Navigation: Alert Manager --> System --> Options
-- Electronic Account Mail setup
-- Oracle Alert Mailbox
Navigation: Alert Manager --> System --> Installations
-- Setup if there are any CUSTOM application where the Alert needs to be created.

Types of Alerts: Event based and Periodic
-- Both types of alerts are defined by SQL statement specified in the SQL section.



Periodic Alert: Navigate to alert form and select the periodic Alert option and then below tasks to be performed.
- Application Name : Application name that owns the alert
- Alert Name : Name of the alert(up to 50 chars) with some meaningful description(up to 240 chars) and check the enabled field.
- Frequency : Select the frequency of the alert,below are the available options.
On Demand
On Day of the month
On Day of the week
Every N calendar Days
Every Day
Every Other Day
Every N Business Days
Every Business Day
Every Other Business Day
Also Start time and end time, number of times in 24 hours and end date if not required after some date.

- SQL : Enter a SQL Statement that retrieves all the alert information, that can used for actions to be planned.The statement should have INTO clause and one

output field.
Example: Input field    :INPUT_NAME
     Output field   &OUTPUT_NAME
Oracle Alert does not support pl/sql statements, but we have a provision to create the database function and use in the SQL statement.
Example: SELECT PACKAGE.FUNCTION_NAME(:INPUT_VALUE) INTO &OUTPUT_VALUE FROM DUAL;

IMPORT Option: Rather than creating a SQL statement, we can also import from file and can use in the application.


Event Alert: Navigate to alert form and select the Event Alert option and then below tasks to be performed.
- Application Name : Application name that owns the alert
- Alert Name : Name of the alert(up to 50 chars) with some meaningful description(up to 240 chars) and check the enabled field
- Specify the event table name (application be different that the table resides) but privileges should be present.
- Check the insert/update, when the alert has to be fired.

- SQL : Enter a SQL Statement that retrieves all the alert information, that can used for actions to be planned.The statement should have INTO clause and one

output field.
Example: Input field    :INPUT_NAME
     Output field   &OUTPUT_NAME
Oracle Alert does not support pl/sql statements, but we have a provision to create the database function and use in the SQL statement.
Also make sure there is a condition based on the event table in the SQL, by using :ROWID condition.

Example:
SELECT user_name INTO &NEWUSER FROM fnd_user WHERE rowid = :ROWID;

The SQL statement should be verified, run then it should be saved in the alert.


- Alert Details: Provide the input,output and installation/user id details.

- Action: Create the action Name and click on the action details.
Action type can be set as Message,SQL Statement script and operating system script.
and provide the details for the message details like To list and message data.

- Action sets: Once the alert actions are created, those alert actions can be included in the action sets.

Check printing in Oracle apps R12 payables.

Many customers ask for customizing the Check printing report,there could be changes on the layout, like the fields which are required or the format which is

specific to Banking any organization.

Oracle E-Biz does generate the generic layout, this layout can be extended for custom changes. Below are the high lighted steps.


1) Download the existing template from the application.
Navigation:
Payables Manager-->Setup-->Payments-->Payment Adminstrator
-- Oracle Payment Setup page will open.
-- Navigate to "XML Publisher Format Templates" by clicking goto Task button.
-- Search with the name "Standard" and select the "Standard Check Format".
-- Click on the download button, and save the RTF file copy (standard Check printing) as backup in your computer.
-- Now save the template as per the business standard.
-- Modify the check template as per the design required by the client.
-- If there is any design change like putting additional columns/fields then the extension should be done by using the package IBY_FD_EXTRACT_EXT_PUB
-- example of extending columns/fields is present in the package spec the same can be reffered.
-- Setup the modified RTF template with the same above navigation, by using create template option.
-- Data Definition name for the template would be "Oracle Payments Funds Disbursement Payment Instruction Extract 1.0".


2) Create the payment format and select the template name.
-- Next step after uploading the RTF template is to create the payment Format.
-- Navigation: Payables Manager-->Setup-->Payments-->Payment Adminstrator
-- Oracle Payment Setup page will open.
-- Navigate to "Formats" by clicking goto Task button.
-- Payment Format page opens, now click on the create button.
-- Provide the format a name and code and select the template which was uploaded to the application.
-- select the data Extract field as "Oracle Payments Funds Disbursement Payment Instruction Extract 1.0"
-- Click on Apply button, Payment format will be created.


3) create the payment Document.
-- Query the Bank Account where CUSTOM format has to be affected.
-- Click on the "Manage Payment Documents".
-- Click on the create Button.
-- Provide the values for Name,Paper Stock Type,Format and First Available Document Number.
-- The format Name should be the one which was done in the second step.



4) Create the payment profile
-- Create the payment Process profile
-- Navigation: Payables Manager-->Setup-->Payments-->Payment Adminstrator
-- Oracle Payment Setup page will open.
-- Navigate to "Payment Process Profiles" by clicking goto Task button.
-- Click on the create Button.
-- Provide the Name and Code as per the Business Specific standards.
-- Select the Payment Instruction Format, the same Format name which was done in 2nd step.
-- Click on Usgae Rules: and select the bank Account for the "Internal Bank Accounts" option.
-- Click on Apply Button for saving the Payment Profile.


Now the check printing extension is completed, test the output for the specified Bank Account to verify the changes.

Please feel free to give the comments.

Customer Conversion in Oracle apps R12.

Below is the usual sequence followed in the Customer conversion, more number of columns would be included in API usage but that depends on the business requirements.
1. Create a party and an account
2. Create a physical location
3. Create a party site using party_id from step 1 and location_id from step 2
4. Create an account site using cust_account_id from step 1 and party_site_id from step 3.
5. Create a definition contact
6. Create an account site use using cust_acct_site_id from step 4 and site_use_code='BILL_TO'

1) Create Party an account:

  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);
  v_organization_rec hz_party_v2pub.organization_rec_type; -- API Parameter
  v_customer_account_rec hz_cust_account_v2pub.cust_account_rec_type; -- API Parameter
  v_party_rec hz_party_v2pub.party_rec_type;
  v_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
  v_location_rec hz_location_v2pub.location_rec_type;
  v_party_site_rec hz_party_site_v2pub.party_site_rec_type;
  v_party_site_use_rec hz_party_site_v2pub.party_site_use_rec_type;
  v_customer_profile_amt hz_customer_profile_v2pub.cust_profile_amt_rec_type;
  v_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
  v_customer_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
  v_customer_ship_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
  v_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
  v_phone_rec hz_contact_point_v2pub.phone_rec_type;
  v_email_rec hz_contact_point_v2pub.email_rec_type;
  v_person_rec hz_party_v2pub.person_rec_type;
  v_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
  x_party_site_id           NUMBER;
  x_party_site_number       VARCHAR2(2000);
  v_party_tax_profile_id    NUMBER;
  x_cust_acct_site_id       NUMBER;
  x_site_use_id             NUMBER;
  x_contact_point_id        NUMBER;
  x_cust_account_id         NUMBER;
  x_account_number          VARCHAR2(2000);
  x_party_id                NUMBER;
  x_party_number            VARCHAR2(2000);
  x_profile_id              NUMBER;
  l_party_id                NUMBER;
  l_success                 NUMBER;
  l_count                   NUMBER;
  l_cust_acc_id             NUMBER;
  v_count                   NUMBER;
  v_customer_account_id     NUMBER;
  x_cust_account_profile_id NUMBER;
  v_cust_act_prof_amt_id    NUMBER;
  v_org_id                  NUMBER;
  v_gl_id_rec               NUMBER;
  x_object_version_number     NUMBER;
  v_profile_class_id hz_cust_profile_classes.profile_class_id%TYPE;


variable assignment are done with the cusror, the data is fetched from the temp table and assigned into rec_cur variable.

        BEGIN
          SELECT profile_class_id
          INTO v_profile_class_id
          FROM hz_cust_profile_classes
          WHERE name = rec_cur.profile_class;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_profile_class_id := 0;
        WHEN OTHERS THEN
          v_profile_class_id := 0;
        END;


        v_organization_rec.organization_name := TRIM(rec_cur.cust_name_arabic);
        v_organization_rec.created_by_module := 'TCA_V1_API';
        v_organization_rec.known_as          := 'N\A';
        v_customer_account_rec.account_name        := rec_cur.cust_name_eng;
        v_customer_account_rec.customer_class_code := rec_cur.Classification;
        v_customer_profile_rec.profile_class_id  := v_profile_class_id;

        hz_cust_account_v2pub.create_cust_account
           ( p_init_msg_list =>FND_API.G_FALSE,
             p_cust_account_rec => v_customer_account_rec, -- Customer Account Record
            p_organization_rec => v_organization_rec,                                    -- Party Organization Record
            p_customer_profile_rec => v_customer_profile_rec,                            -- Customer Profile Record
            p_create_profile_amt => fnd_api.g_true,
        x_cust_account_id => l_cust_acc_id, x_account_number => x_account_number,
        x_party_id => x_party_id, x_party_number => x_party_number, x_profile_id => x_profile_id,
        x_return_status =>x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data );
        COMMIT;
        IF x_return_status = 'S' THEN
          NULL;
        ELSE
          ROLLBACK;
          L_SUCCESS :=2;

--Update the staging table status based on the status.

          -- 2. Create a physical location

          v_location_rec.country           := 'US'; -- Country location code.
          v_location_rec.address1          := rec_cur.cust_no;
          v_location_rec.address2          := rec_cur.site_Address2;
          v_location_rec.city              := rec_cur.cust_city;
          v_location_rec.created_by_module := 'TCA_V1_API';

          hz_location_v2pub.create_location(P_INIT_MSG_LIST => 'T', P_LOCATION_REC => v_location_rec, X_LOCATION_ID => X_LOCATION_ID,
        X_RETURN_STATUS =>X_RETURN_STATUS, X_MSG_COUNT => X_MSG_COUNT, X_MSG_DATA => X_MSG_DATA);

          -- 3. Create a party site using party_id from step 1 and location_id from step 2

          IF x_location_id                            IS NOT NULL THEN -- Location ID not Null
            v_party_site_rec.party_id                 := x_party_id;
            v_party_site_rec.location_id              := x_location_id;
            v_party_site_rec.party_site_number        := NULL;
            v_party_site_rec.identifying_address_flag := 'Y';
            v_party_site_rec.created_by_module        := 'TCA_V1_API';
            hz_party_site_v2pub.create_party_site( P_INIT_MSG_LIST => 'T', P_PARTY_SITE_REC => v_party_site_rec,
        X_PARTY_SITE_ID => X_PARTY_SITE_ID, X_PARTY_SITE_NUMBER => X_PARTY_SITE_NUMBER,
        X_RETURN_STATUS => X_RETURN_STATUS, X_MSG_COUNT => X_MSG_COUNT, X_MSG_DATA => X_MSG_DATA);



            -- 4. Create an account site using cust_account_id from step 1 and party_site_id from step 3.
            IF x_party_site_id IS NOT NULL THEN -- Party Site ID not null
              FOR rec_cur_ou   IN cur_ou
              LOOP
                v_org_id                             := rec_cur_ou.organization_id;
                v_cust_acct_site_rec.cust_account_id := l_cust_acc_id;
                v_cust_acct_site_rec.party_site_id   := x_party_site_id;
                v_cust_acct_site_rec.org_id          := v_org_id;
                --          p_cust_acct_site_rec.orig_system_reference := p_cust_acct_site_id;
                v_cust_acct_site_rec.created_by_module := 'TCA_V1_API';
                hz_cust_account_site_v2pub.create_cust_acct_site(P_INIT_MSG_LIST => 'T', P_CUST_ACCT_SITE_REC => v_cust_acct_site_rec,
            X_CUST_ACCT_SITE_ID => X_CUST_ACCT_SITE_ID, X_RETURN_STATUS => X_RETURN_STATUS, X_MSG_COUNT => X_MSG_COUNT,
            X_MSG_DATA => X_MSG_DATA);
                IF x_cust_acct_site_id IS NOT NULL THEN -- Customer Account Site ID is not null
                  -- v_gl_id_rec                                    := NULL;
                  v_customer_ship_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
                  v_customer_ship_site_use_rec.site_use_code     := 'SHIP_TO';
                  --        v_customer_site_use_rec.gl_id_rec         := 7006;
                  v_customer_ship_site_use_rec.org_id            := v_org_id;
                  v_customer_ship_site_use_rec.created_by_module := 'TCA_V1_API';
                  hz_cust_account_site_v2pub.create_cust_site_use ( 'T', v_customer_ship_site_use_rec, v_customer_profile_rec, '', '',
            x_site_use_id, x_return_status, x_msg_count, x_msg_data);
                  v_customer_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
                  v_customer_site_use_rec.site_use_code     := 'BILL_TO';
                  v_customer_site_use_rec.gl_id_rec         := rec_cur.ar_receivable_account;--7006;
                  v_customer_site_use_rec.org_id            := v_org_id;
                  v_customer_site_use_rec.created_by_module := 'TCA_V1_API';
                  hz_cust_account_site_v2pub.create_cust_site_use ( 'T', v_customer_site_use_rec,
        v_customer_profile_rec, '', '', x_site_use_id, x_return_status, x_msg_count, x_msg_data);
                END IF;--X_CUST_ACCT_SITE_ID

              -- 5. Create a definition contact

              IF rec_cur.cust_phone1                   IS NOT NULL THEN -- Phone No 1 not null
                v_contact_point_rec.owner_table_name   := 'HZ_PARTY_SITES';
                v_contact_point_rec.owner_table_id     := x_party_site_id;
                v_contact_point_rec.created_by_module  := 'TCA_V1_API';
                v_contact_point_rec.contact_point_type := 'PHONE';
                v_PHONE_REC.phone_number               := rec_cur.cust_phone1;
                v_PHONE_REC.phone_line_type            := 'GEN';
                hz_contact_point_v2pub.create_phone_contact_point(p_init_msg_list => 'T', p_contact_point_rec => v_contact_point_rec,
        p_phone_rec => v_phone_rec, x_contact_point_id => x_contact_point_id, x_return_status =>x_return_status,
        x_msg_count => x_msg_count, x_msg_data => x_msg_data );

THe API return status should be used appropriately for updating the staging table.