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.

Supplier Conversion in R12 Oracle apps.

Supplier Conversion in R12 Oracle apps.

While doing supplier conversion in R12, below columns in staging table are mandatory or the related setups. I will mention those and their validation.


Note: c_rec.<column_name> columns are from the staging table

Record types variables for creating supplier and supplier site.

  l_vendor_rec                 ap_vendor_pub_pkg.r_vendor_rec_type;
  l_vendor_site_rec             ap_vendor_pub_pkg.r_vendor_site_rec_type;


  x_vendor_id                   NUMBER;
  x_party_id                    NUMBER;
  x_vendor_site_id              NUMBER;
  x_party_site_id               NUMBER;
  x_location_id                 NUMBER;

---------------------------------------------------
-->> Currency Code <<--
---------------------------------------------------

SELECT currency_code
INTO l_invoice_currency
FROM fnd_currencies
WHERE UPPER(currency_code)=UPPER(TRIM(c_rec.currency)) -- Value from the staging table



---------------------------------------------------
-->> Payment Term <<--
---------------------------------------------------
SELECT ap_terms.term_id
INTO l_payment_terms
FROM ap_terms
WHERE UPPER(ap_terms.name)=UPPER(TRIM(c_rec.payment_terms))
AND enabled_flag ='Y';


---------------------------------------------------
-->> Country Name Validation <<--
---------------------------------------------------

SELECT lookup_code
       INTO l_country_code
       FROM fnd_lookup_values
       WHERE lookup_type='GHR_US_CNTRY_WRLD_CTZN'
      AND meaning        =trim(c_rec.country)
      AND ENABLED_FLAG   ='Y'
      AND LANGUAGE       ='US';

Assign the values to the record type variable before passing to the API.

        l_vendor_rec.vendor_type_lookup_code := c_rec.supplier_type;
        l_vendor_rec.summary_flag         := 'N';
        l_vendor_rec.enabled_flag         := 'Y';
        l_vendor_rec.invoice_currency_code:=TRIM(l_invoice_currency);
        l_vendor_rec.terms_id:=l_payment_terms;


Now call the ap_vendor_pub_pkg.create_vendor for creating the supplier.

          ap_vendor_pub_pkg.create_vendor( p_api_version => 1,
                                            x_return_status => x_return_status,
                                            x_msg_count => x_msg_count,
                                            x_msg_data => x_msg_data,    
                                            p_vendor_rec => l_vendor_rec,
                                            x_vendor_id => x_vendor_id,
                                            x_party_id => x_party_id );



Now for creating the supplier site assign the values to the supplier site record type variable.



                   l_vendor_site_rec.vendor_id             := x_vendor_id;
            l_vendor_site_rec.org_id                := cur_rec.organization_id;
            l_vendor_site_rec.shipping_control      := v_shipping_control;--'BUYER';
            l_vendor_site_rec.purchasing_site_flag  := 'Y';
            l_vendor_site_rec.pay_site_flag         := 'Y';
            l_vendor_site_rec.accts_pay_code_combination_id := v_lib_acc;
            l_vendor_site_rec.prepay_code_combination_id := v_prepay_acc;
            l_vendor_site_rec.pay_group_lookup_code := c_rec.pay_group;
            l_vendor_site_rec.ext_payee_rec.exclusive_pay_flag   := 'N';
            l_vendor_site_rec.ext_payee_rec.default_pmt_method   := 'CHECK';--c_rec.payment_method;
            l_vendor_site_rec.city                  :=TRIM(c_rec.city);
            l_vendor_site_rec.country               :=l_country_code;
            l_vendor_site_rec.address_line1         :=TRIM(c_rec.alt_supplier_site_name);
            l_vendor_site_rec.vendor_site_code      :=c_rec.address_name;
            l_vendor_site_rec.country_of_origin_code:=l_country_code;




Now call the API for supplier site creation.

          ap_vendor_pub_pkg.create_vendor_site( p_api_version => 1,
                                                x_return_status => x_return_status,
                                                x_msg_count => x_msg_count,
                                                x_msg_data => x_msg_data,   
                                                p_vendor_site_rec => l_vendor_site_rec,
                                                x_vendor_site_id => x_vendor_site_id,
                                                x_party_site_id => x_party_site_id,
                                                x_location_id => x_location_id );


Staging table record status has to be updated accordingly based on the API result.

Variable x_return_status gives the status of the API call.

Also for clear understanding, Stats can be printed in the output of the concurrent program, for immediate check.

Oracle Job conversion in HRMS (Human Resources)

The API hr_job_api.create_job creates a job in specific Business Group.

At least one segment of the job key flexfield must have a value.

In the below example I am using p_segment1 parameter, I am passing Job name for segment1.

Below Pre-requisites before calling the Job API

---------------------------------------------------
-->> JOB GROUP ID <<--
---------------------------------------------------

SELECT job_group_id
INTO v_job_group_id
FROM PER_JOB_GROUPS
WHERE displayed_name LIKE 'HR_81';

----------------------------------------------------------------
-->> for checking if the Job definition is already present <<--
----------------------------------------------------------------

SELECT job_id
INTO v_job_id
FROM per_jobs
WHERE name = v_emp_jobs.job_name;

---------------------------------------------------
-->> Calling the API for Job Creation <<--
---------------------------------------------------

hr_job_api.create_job
(p_validate=>v_validate
,p_business_group_id=>v_business_group_id                                                                  
,p_date_from=>v_date_from
,p_job_group_id=>v_job_group_id                                            
,p_segment1=>v_emp_jobs.job_name
,p_language_code=>v_language_code
,p_job_id=>v_job_id
,p_object_version_number=>v_object_version_number
,p_job_definition_id=>v_job_definition_id
,p_name=>v_name
);

How to load Japanese characters into database using SQL loader

By using below character set Japanese char can be loaded into database.
JA16SJIS


Example:

LOAD DATA
CHARACTERSET JA16SJIS

How to call SQL Loader from shell script --- for begginer

The shell script should be registered as a concurrent program, user can submit the program from request form.

Parameters from the program provided by the system.

$1 apps user name and password.
$2 user id (apps)
$3 user name
$4 Request id



Any other parameters which are aded in the concurrent program start from $5 and so on.


Basic parameters used while loading the data thorough SQL loader are datafile Name,control fil directory,Data file directory,temp control file directory,archive directory and error directory etc, below are the parameters created and are assigned to the variables.


DATA_DIR=$XX_TOP/$DATA_FILENAME_DIR
export DATA_DIR
echo 'Data Directory        ='$DATA_DIR

CTL_DIR=$XX_TOP/bin
export CTL_DIR
echo 'Control File Directory ='$CTL_DIR

ERR_DIR=$XX_TOP/error
export ERR_DIR
echo 'Error Directory       ='$ERR_DIR

ARCHIVE_DIR=$XX_TOP/archive
export ARCHIVE_DIR
echo 'Error Directory       ='$ARCHIVE_DIR

DATA_FILENAME=$DATA_DIR/$DATA_FILENAME_LIKE
export DATA_FILE_NAME
echo 'Datafile name         ='$DATA_FILENAME

CTL_FILENAME=$CTL_DIR/$CTL_FILE_NAME
export CTL_FILENAME
echo 'Control File name     ='$CTL_FILENAME

TEMP_CTL_FILENAME=/tmp/$4$CTL_FILE_NAME
export TEMP_CTL_FILENAME
echo 'Temp Control Filename ='$TEMP_CTL_FILENAME


For calling the sqlldr with the above parameters.

         . $APPL_TOP/APPSORA.env
         echo 'New ORACLE_HOME for 9i is '$ORACLE_HOME
         sqlldr userid=$1 control=$TEMP_CTL_FILENAME data=$Datafile_NAME log=$LOGFILE bad=$BADFILE discard=$DISFILE errors=999999

Contigent Worker HRMS Conversion

In HRMS, there are different types of conversions at times we may get requirement for loading the contingent workers into the Oracle Instance. For more information you can check in the Employee types.

Below is the API  which is used for loading the Contingent Employee, for non-Saudi 'SA' can be removed.


   HR_SA_CONTINGENT_WORKER_API.CREATE_SA_CWK (
   p_validate                    =>false
  ,P_START_DATE                  =>I.HIRE_DATE
  ,p_business_group_id           => L_BUSINESS_GROUP_ID
  ,p_family_name                  => I.FAMILY_NAME
  ,P_PERSON_TYPE_ID               =>L_PERSON_TYPE_ID
  ,P_NPW_NUMBER                   =>L_EMPLOYEE_NUMBER --IN OUT NOCOPY VARCHAR2
  ,P_BACKGROUND_CHECK_STATUS       =>NULL
  ,P_BACKGROUND_DATE_CHECK         =>NULL
  ,P_BLOOD_TYPE                    =>NULL
  ,P_COMMENTS                      =>NULL
  ,P_CORRESPONDENCE_LANGUAGE       =>NULL
  ,P_COUNTRY_OF_BIRTH              =>L_COUNTRY_OF_BIRTH
  ,P_DATE_OF_BIRTH                 =>I.DATE_OF_BIRTH
  ,P_DATE_OF_DEATH                 =>NULL
  ,P_DPDNT_ADOPTION_DATE           =>NULL
  ,P_DPDNT_VLNTRY_SVCE_FLAG        =>NULL
  ,P_EMAIL_ADDRESS                 =>I.EMAIL
  ,P_FIRST_NAME                    =>I.FIRST_NAME
  ,P_FTE_CAPACITY                  =>NULL
  ,P_HONORS                        =>NULL
  ,P_INTERNAL_LOCATION             =>I.location
  ,P_KNOWN_AS                      =>NULL
  ,P_LAST_MEDICAL_TEST_BY          =>NULL
  ,P_LAST_MEDICAL_TEST_DATE        =>NULL
  ,P_MAILSTOP                      =>NULL
  ,P_MARITAL_STATUS                =>L_MARITAL_STATUS
  ,P_NATIONAL_IDENTIFIER           =>L_NATIONAL_IDENTIFIER
  ,P_NATIONALITY                   =>L_NATIONALITY
  ,P_OFFICE_NUMBER                 =>I.OFFICE
  ,P_ON_MILITARY_SERVICE           =>NULL
  ,P_PARTY_ID                      =>NULL
  ,P_PREVIOUS_LAST_NAME            =>NULL
  ,P_PROJECTED_PLACEMENT_END       =>NULL
  ,P_RECEIPT_OF_DEATH_CERT_DATE    =>NULL
  ,P_REGION_OF_BIRTH               =>I.REGION_OF_BIRTH
  ,P_REGISTERED_DISABLED_FLAG      =>L_DISABLED
  ,P_RESUME_EXISTS                 =>NULL
  ,P_RESUME_LAST_UPDATED           =>NULL
  ,P_SECOND_PASSPORT_EXISTS        =>NULL
  ,p_sex                           =>L_GENDER
  ,P_STUDENT_STATUS                =>NULL
  ,P_TITLE                         =>L_TITLE
  ,P_TOWN_OF_BIRTH                 => I.TOWN_OF_BIRTH
  ,P_USES_TOBACCO_FLAG             =>NULL
  ,P_VENDOR_ID                     =>NULL
  ,P_WORK_SCHEDULE                 =>NULL
  ,P_WORK_TELEPHONE                =>NULL
  ,P_EXP_CHECK_SEND_TO_ADDRESS     =>NULL
  ,P_HOLD_APPLICANT_DATE_UNTIL     =>NULL
  ,P_DATE_EMPLOYEE_DATA_VERIFIED   =>NULL
  ,P_BENEFIT_GROUP_ID              =>NULL
  ,P_COORD_BEN_MED_PLN_NO          =>NULL
  ,P_COORD_BEN_NO_CVG_FLAG         =>NULL
  ,P_ORIGINAL_DATE_OF_HIRE         =>NULL
  ,P_ATTRIBUTE_CATEGORY            =>NULL
  ,P_ATTRIBUTE1                    =>I.TEMPORARY_RESOURCE
  ,P_ATTRIBUTE2                    =>I.SHIFT_REGULAR_EMLOYEE
  ,P_ATTRIBUTE3                    =>I.TICKET_DESTINATION
  ,P_ATTRIBUTE4                    =>I.NUMBER_OF_TICKET_EMPLOYEE
  ,P_ATTRIBUTE5                    =>I.NUMBER_OF_TICKET_DEPENDENT
  ,P_ATTRIBUTE6                    =>I.AGE_ON_HIJRAH_DATE
  ,P_ATTRIBUTE7                    =>NULL
  ,P_ATTRIBUTE8                    =>NULL
  ,P_ATTRIBUTE9                    =>NULL
  ,P_ATTRIBUTE10                   =>NULL
  ,P_FATHER_NAME                    =>I.FATHER_NAME
  ,P_GRANDFATHER_NAME              =>I.GRAND_FATHER_NAME
  ,P_ALT_FIRST_NAME                =>I.FIRST_NAME_ARABIC
  ,P_ALT_FATHER_NAME               =>I.SECONED_NAME_ARABIC
  ,P_ALT_GRANDFATHER_NAME          =>I.THIRD_NAME_ARABIC
  ,P_ALT_FAMILY_NAME                  =>I.FAMILY_NAME_ARABIC
  ,P_RELIGION                      =>L_RELIGION
  ,P_HIJRAH_BIRTH_DATE             =>I.HIJRAH_DATE_OF_BIRTH
  ,P_EDUCATION_LEVEL               =>L_EDUCATION_LEVEL
  ,p_person_id                      => p_person_id
  ,p_per_object_version_number        => P_PER_OBJECT_VERSION_NUMBER
  ,p_per_effective_start_date       => P_PER_EFFECTIVE_START_DATE
  ,P_PER_EFFECTIVE_END_DATE          => P_PER_EFFECTIVE_END_DATE
  ,p_pdp_object_version_number     =>p_pdp_object_version_number--   out nocopy   number
  ,P_FULL_NAME                      => P_FULL_NAME
  ,p_comment_id                    =>  p_comment_id
  ,p_assignment_id                     => p_assignment_id
  ,p_asg_object_version_number       => P_ASG_OBJECT_VERSION_NUMBER
  ,p_assignment_sequence              => P_ASSIGNMENT_SEQUENCE
  ,p_assignment_number             => P_ASSIGNMENT_NUMBER
  ,p_name_combination_warning      => P_NAME_COMBINATION_WARNING
  );

FTP Data file from one instance to current instance in Oracle apps

In the below sample script I am trying to transfer the data from source system to the current instance where I would be running the program.

The below script should be registered as a Concurrent program in Oracle apps.

I have 6 parameters ($5,$6,$7,$8,$9,$10) which I would be passing in the program.

The file will be transferred from the location specified in the first parameter i.e, $5 to the location specified in $6 i.e, second parameter in the concurrent program.

Below is the script.

XXSOURCE=$5
export XXSOURCE
echo $XXSOURCE
XXTARGET_DIR=$6
export XXTARGET_DIR
echo $XXTARGET_DIR
DATAFILE_NAME=${7}
export DATAFILE_NAME
echo 'Data Filename like       ='$DATAFILE_NAME
IP_Address=$8
export IP_Address
echo $IP_Address
User_Name=$9
export User_Name
echo $User_Name
PASSWORD=${10}
export PASSWORD
#echo $PASSWORD

ftp -in $IP_Address <<_FTP>>$FTPLOG
quote USER $User_Name
quote PASS $PASSWORD
bin
cd  $XXSOURCE
lcd $XXCUST_TOP/$XXTARGET_DIR
mget $DATAFILE_NAME*

SQL statement to fetch element details for all employees

Below SQL fetches the element entry and values for all the employees present in the Organization.

Note: value for the condition pivf.name might require a change in your requirement.

select ppf.employee_number,ppf.full_name,ppf.person_id
,petf.element_name
,peef.element_entry_id
,petf.element_type_id
,pivf.input_value_id
,peevf.screen_entry_value
from PER_PEOPLE_F ppf,per_all_assignments_f paaf
,pay_element_entries_f peef
,PAY_ELEMENT_TYPES_F petf
,pay_input_values_f pivf
,pay_element_entry_values_f peevf
where paaf.person_id= ppf.person_id
AND paaf.assignment_id = peef.assignment_id
AND peef.element_type_id = petf.element_type_id
AND pivf.name IN ('Multiplier','Pay Value')
AND peef.element_type_id = pivf.element_type_id
AND pivf.input_value_id = peevf.input_value_id
AND peef.element_entry_id = peevf.element_entry_id
AND to_date(NVL(to_char(ppf.effective_start_date,'DD-MON-YYYY'),SYSDATE)) <= SYSDATE  AND to_date(NVL(to_char(ppf.effective_end_date,'DD-MON-YYYY'),SYSDATE)) >= SYSDATE
AND to_date(NVL(to_char(paaf.effective_start_date,'DD-MON-YYYY'),SYSDATE)) <= SYSDATE  AND to_date(NVL(to_char(paaf.effective_end_date,'DD-MON-YYYY'),SYSDATE)) >= SYSDATE
AND to_date(NVL(to_char(peef.effective_start_date,'DD-MON-YYYY'),SYSDATE)) <= SYSDATE  AND to_date(NVL(to_char(peef.effective_end_date,'DD-MON-YYYY'),SYSDATE)) >= SYSDATE
AND to_date(NVL(to_char(petf.effective_start_date,'DD-MON-YYYY'),SYSDATE)) <= SYSDATE  AND to_date(NVL(to_char(petf.effective_end_date,'DD-MON-YYYY'),SYSDATE)) >= SYSDATE
AND to_date(NVL(to_char(peevf.effective_start_date,'DD-MON-YYYY'),SYSDATE)) <= SYSDATE  AND to_date(NVL(to_char(peevf.effective_end_date,'DD-MON-YYYY'),SYSDATE)) >= SYSDATE
AND to_date(NVL(to_char(pivf.effective_start_date,'DD-MON-YYYY'),SYSDATE)) <= SYSDATE  AND to_date(NVL(to_char(pivf.effective_end_date,'DD-MON-YYYY'),SYSDATE)) >= SYSDATE
ORDER by ppf.person_id
;

Update Salary Basis for HR employee

Salary Basis is required for employee before running the payroll,once the payroll basis is assigned to an employee the data will be stored in the table per_pay_proposals.

The link between per_pay_proposals and per_all_assignments_f is based on the assignment_id.

Below is the API(with sample parameters) for updating or creating the salary basis for an employee.

  hr_maintain_proposal_api.insert_salary_proposal (
    p_pay_proposal_id              => l_proposal_id,
    p_assignment_id                => i.assignment_id,
    p_business_group_id           => i.business_group_id,
    p_object_version_number    => l_object_version_number,
    p_multiple_components             => 'N',
    p_change_date                  =>  to_date('01-JAN-2013','DD/MM/YYYY'),--l_effective_date , --to_date('01-JAN-2013','DD/MM/YYYY'), --
    p_proposal_reason  => i.reason,
    p_proposed_salary_n        => i.salary,
    p_approved                            => i.approved,
    p_element_entry_id                 => l_element_entry_id,
    p_validate                    => l_validate,
    p_inv_next_sal_date_warning =>   l_inv_next_sal_date_warning,
    p_proposed_salary_warning => l_proposed_salary_warning,
    p_approved_warning => l_approved_warning,
    p_payroll_warning        => l_payroll_warning);

72 ORA-20001: HR_FLEX_VALUE_MISSING: N, COLUMN, SEGMENT3, N, PROMPT, GOSI Annuities

While loading the HR employees for Saudi region or while updating the HR employees, there is a possibility for this error.

If the below API is used for updating the employee details, there is a soft coded KFF. Search in the KFF screen with %Soft% and check the mapping for "GOSI Annunities".

HR_ASSIGNMENT_API.UPDATE_EMP_ASG

Add the P_SEGMENT3 in the parameter, example is shown as below.

         HR_ASSIGNMENT_API.UPDATE_EMP_ASG
                                (p_effective_date              => l_effective_date
                                ,p_datetrack_update_mode       => 'CORRECTION',
                                p_assignment_id               => i.assignment_id  ,
                                p_object_version_number       => l_asg_ovn,
                P_SEGMENT1 => 201,
                P_SEGMENT3 => i.people_group_value,-- GOSI Annunities
                P_SEGMENT5 => i.people_group_value,-- GOSI hazards
                                p_comment_id                  => ln_EMP_comment_id,
                                p_soft_coding_keyflex_id      => ln_EMP_soft_coding_keyflex_id,
                                p_effective_start_date        => ln_EMP_effective_start_date,
                                p_effective_end_date          => ln_EMP_effective_end_date,
                                p_concatenated_segments       => ln_EMP_conc_segments,
                                p_no_managers_warning         => ln_EMP_no_managers_warning,
                                p_other_manager_warning       => ln_EMP_other_manager_warning
                                );



I have used this and worked for me.

How to compile Oracle forms in R12

1) Login to application server.
2) Navigate to the path $AU_TOP/forms/US
3) transfer the .fmb file in binary mode
4) run the below command, .fmx file will be generated if there are no errors.
frmcmp_batch userid=apps/$PASSWORD module=<Form_Name>.fmb output_file=<Form_Name>.fmx module_type=form batch=no compile_all=yes
5) Transfer the .fmx file to the respective directory.

Update Sales order line details using OE_ORDER_PUB.PROCESS_ORDER API

In the below shown example I am updating the request_date to sysdate, also I am considering only the first line of the sales Order.

Logic can be modified accordingly for updating any number of lines in the sales Order

v_header_rec                   oe_order_pub.header_rec_type;
v_line_tbl                     oe_order_pub.line_tbl_type;

BEGIN
-- Line Record --
v_line_tbl(1)                           := OE_ORDER_PUB.G_MISS_LINE_REC;--oe_order_pub.G_MISS_LINE_TBL;
v_line_tbl(1).last_update_date       := sysdate;
v_line_tbl(1).last_updated_by        := FND_GLOBAL.USER_ID;
v_line_tbl(1).last_update_login      := FND_GLOBAL.LOGIN_ID;
v_line_tbl(1).header_id              := l_header_id; --fetch the value
v_line_tbl(1).line_id                := l_line_id; --fetch the value
v_line_tbl(1).operation              := oe_globals.G_OPR_UPDATE;
v_line_tbl(1).change_reason          := 'MANUAL';
v_line_tbl(1).request_date           := sysdate;


OE_ORDER_PUB.PROCESS_ORDER (
p_api_version_number            => v_api_version_number
, p_header_rec                  => v_header_rec
, p_line_tbl                    => v_line_tbl
-- OUT variables
, x_header_rec                  => v_header_rec_out
, x_header_val_rec              => v_header_val_rec_out
, x_header_adj_tbl              => v_header_adj_tbl_out
, x_header_adj_val_tbl          => v_header_adj_val_tbl_out
, x_header_price_att_tbl        => v_header_price_att_tbl_out
, x_header_adj_att_tbl          => v_header_adj_att_tbl_out
, x_header_adj_assoc_tbl        => v_header_adj_assoc_tbl_out
, x_header_scredit_tbl          => v_header_scredit_tbl_out
, x_header_scredit_val_tbl      => v_header_scredit_val_tbl_out
, x_line_tbl                    => v_line_tbl_out
, x_line_val_tbl                => v_line_val_tbl_out
, x_line_adj_tbl                => v_line_adj_tbl_out
, x_line_adj_val_tbl            => v_line_adj_val_tbl_out
, x_line_price_att_tbl          => v_line_price_att_tbl_out
, x_line_adj_att_tbl            => v_line_adj_att_tbl_out
, x_line_adj_assoc_tbl          => v_line_adj_assoc_tbl_out
, x_line_scredit_tbl            => v_line_scredit_tbl_out
, x_line_scredit_val_tbl        => v_line_scredit_val_tbl_out
, x_lot_serial_tbl              => v_lot_serial_tbl_out
, x_lot_serial_val_tbl          => v_lot_serial_val_tbl_out
, x_action_request_tbl          => v_action_request_tbl_out
, x_return_status               => v_return_status
, x_msg_count                   => v_msg_count
, x_msg_data                    => v_msg_data
);

END;

How to Populate charges(Header level) field in Sales Order form.

Here the user wants a menu, once he clicks on the menu then charges field should be populated.

Below things need to developed for the requirement.
1) setup for discounts at header level.
2) Database procedure for calling the API to populate the charges in OE_PRICE_ADJUSTMENTS table.
3) Personalization for creating menu, call the procedure by using the menu.

Solution:
1) Login to Order Management Super user, and navigate to Modifiers.
Pricing--> Modifiers
 Setup the Modifier Type,Name and Number in header section, and in the line I have provided level as Order since my requirement is to populate at header

level.

Once setup is done the values can be queried in the tables QPFV_MODIFIER_HEADERS and QPFV_MODIFIER_LINES.


2) Create the database procedure to call the API OE_ORDER_PUB.PROCESS_ORDER for populating the charges.
In my requirement Sales Order is already present, which was created by user manually. Now I need to update the charges, in the manual process for populating

the charges user has to click on the Actions Button and then enter the charge Name, Amount and rest details, and there by the charges field will be populated

with the value.

In the automated process, now I am populated the charges lines by using the process_order API.

For this procedure, provide the Order Number as the parameter.

create or replace procedure XXAJ_CREATESALESORDER(p_so_num VARCHAR2) IS
v_api_version_number           NUMBER  := 1;
v_return_status                VARCHAR2 (2000);
v_msg_count                    NUMBER;
v_msg_data                     VARCHAR2 (2000);

-- IN Variables --
v_header_rec                   oe_order_pub.header_rec_type;
v_line_tbl                     oe_order_pub.line_tbl_type;
v_action_request_tbl           oe_order_pub.request_tbl_type;
v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;
v_hdr_adj_tbl               oe_order_pub.Header_Adj_Tbl_Type;

   v_list_header_id number;
  v_list_line_id   number;
  v_operand        varchar2(10); 
  v_ln_type_code   varchar2(10);
  v_operator       varchar2(10);
  v_phase_id       number;
  v_mod_level_code varchar2(10);
  t_line_id number;
  t_header_id number;
  t_org_id number;

  l_pricing_quantity NUMBER;
  l_unit_selling_price NUMBER;
  l_attribute1 NUMBER;
  l_header_id NUMBER;
  l_unit_price NUMBER;
  l_operand NUMBER;


-- OUT Variables --
v_header_rec_out               oe_order_pub.header_rec_type;
v_header_val_rec_out           oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out                 oe_order_pub.line_tbl_type;
v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out       oe_order_pub.request_tbl_type;

v_msg_index                    NUMBER;
v_data                         VARCHAR2 (2000);
v_loop_count                   NUMBER;
v_debug_file                   VARCHAR2 (200);
b_return_status                VARCHAR2 (200);
b_msg_count                    NUMBER;
b_msg_data                     VARCHAR2 (2000);

BEGIN

DBMS_OUTPUT.PUT_LINE('Starting of script');


SELECT hdr.list_header_id,
             lin.list_line_id
    into v_list_header_id,
             v_list_line_id
             FROM QPFV_MODIFIER_HEADERS hdr,
             QPFV_MODIFIER_LINES lin
     WHERE 1=1
        and lin.list_header_id = hdr.list_header_id
        AND lin.list_line_type_code ='FREIGHT_CHARGE'
        AND lin.modifier_level_code = 'ORDER'
        AND hdr.name = 'Transport Charge -3';

select l.pricing_quantity,l.unit_selling_price,l.attribute1,h.header_id
INTO   l_pricing_quantity ,
  l_unit_selling_price ,
  l_attribute1 ,
  l_header_id
from OE_ORDER_HEADERS_ALL h,OE_ORDER_lines_ALL l
where l.header_id  = h.header_id 
AND h.order_number = p_so_num;

select l.unit_price INTO  l_unit_price from po_headers_all h, po_lines_all l
where segment1 = l_attribute1
AND h.po_header_id = l.po_header_id;

-- Adjustment Variables
v_hdr_adj_tbl                      :=oe_order_pub.G_MISS_HEADER_ADJ_TBL;
v_hdr_adj_tbl(1).header_id            := l_header_id;--3778;
v_hdr_adj_tbl(1).automatic_flag       := 'N';
v_hdr_adj_tbl(1).list_line_type_code  := 'FREIGHT_CHARGE';
v_hdr_adj_tbl(1).change_reason_code   := 'MANUAL';
v_hdr_adj_tbl(1).operand              := 5;  -- The value which appears in charges field at header level
v_hdr_adj_tbl(1).charge_type_code     := 'FREIGHT';
v_hdr_adj_tbl(1).change_reason_text     := 'Manually applied adjustments';
v_hdr_adj_tbl(1).modifier_level_code     := 'ORDER';
v_hdr_adj_tbl(1).updated_flag     := 'Y';
v_hdr_adj_tbl(1).applied_flag      := 'Y';
v_hdr_adj_tbl(1).arithmetic_operator     := 'LUMPSUMP';
v_hdr_adj_tbl(1).adjusted_amount     := '5';
v_hdr_adj_tbl(1).price_adjustment_id     := oe_price_adjustments_s.nextval;
v_hdr_adj_tbl(1).creation_date     := sysdate;
v_hdr_adj_tbl(1).created_by     := FND_GLOBAL.USER_ID;
v_hdr_adj_tbl(1).last_update_date     := sysdate;
v_hdr_adj_tbl(1).last_updated_by      := FND_GLOBAL.USER_ID;
v_hdr_adj_tbl(1).last_update_login      := FND_GLOBAL.LOGIN_ID;
v_hdr_adj_tbl(1).list_header_id      := v_list_header_id;
v_hdr_adj_tbl(1).list_line_id      := v_list_line_id;
v_hdr_adj_tbl(1).arithmetic_operator      := 'LUMPSUM';
v_hdr_adj_tbl(1).orig_sys_discount_ref     := 'OE_PRICE_ADJUSTMENTS'||v_hdr_adj_tbl(1).price_adjustment_id;

v_hdr_adj_tbl(1).operation     := oe_globals.g_opr_create;

v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;


DBMS_OUTPUT.PUT_LINE('Starting of API');

-- Calling the API to update the header details of an existing Order --

OE_ORDER_PUB.PROCESS_ORDER (
p_api_version_number            => v_api_version_number
, p_header_rec                  => v_header_rec
, p_line_tbl                    => v_line_tbl
, p_Header_Adj_tbl        => v_hdr_adj_tbl
-- OUT variables
, x_header_rec                  => v_header_rec_out
, x_header_val_rec              => v_header_val_rec_out
, x_header_adj_tbl              => v_header_adj_tbl_out
, x_header_adj_val_tbl          => v_header_adj_val_tbl_out
, x_header_price_att_tbl        => v_header_price_att_tbl_out
, x_header_adj_att_tbl          => v_header_adj_att_tbl_out
, x_header_adj_assoc_tbl        => v_header_adj_assoc_tbl_out
, x_header_scredit_tbl          => v_header_scredit_tbl_out
, x_header_scredit_val_tbl      => v_header_scredit_val_tbl_out
, x_line_tbl                    => v_line_tbl_out
, x_line_val_tbl                => v_line_val_tbl_out
, x_line_adj_tbl                => v_line_adj_tbl_out
, x_line_adj_val_tbl            => v_line_adj_val_tbl_out
, x_line_price_att_tbl          => v_line_price_att_tbl_out
, x_line_adj_att_tbl            => v_line_adj_att_tbl_out
, x_line_adj_assoc_tbl          => v_line_adj_assoc_tbl_out
, x_line_scredit_tbl            => v_line_scredit_tbl_out
, x_line_scredit_val_tbl        => v_line_scredit_val_tbl_out
, x_lot_serial_tbl              => v_lot_serial_tbl_out
, x_lot_serial_val_tbl          => v_lot_serial_val_tbl_out
, x_action_request_tbl          => v_action_request_tbl_out
, x_return_status               => v_return_status
, x_msg_count                   => v_msg_count
, x_msg_data                    => v_msg_data
);

DBMS_OUTPUT.PUT_LINE('Completion of API');


IF v_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Order Header Updation Success : '||v_header_rec_out.header_id);
ELSE
    DBMS_OUTPUT.put_line ('Order Header Updation failed:'||v_msg_data);
    ROLLBACK;
    FOR i IN 1 .. v_msg_count
    LOOP
      v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| v_msg_data);
    END LOOP;
END IF;
END xxaj_createsalesorder;


3) Now the final part is to call the database procedure from the forms personalization, here I have two steps.
i) Create a new Menu
When new form instance, go to actions and provide the Menu label for any Special Trigger.
ii) Now In condition the trigger event will be special event, go to actions click on built in , builtin type is execute procedure. Call the procedure with

the  Order Number as parameter.
Also add another action execute_query to refersh the form, so that charges will appear immediately.

Now the requirement is accomplished.

Let me know your comments... this has worked perfectly for me.

Why we cannot use commit in a trigger.

1) Trigger is a part of larger transaction, mean it is called by a parent transaction.
2) For instance we used a commit in trigger (child transaction), and processed a transaction,but parent transaction got into error for some reason still the data will be commited in child because of the commit statement.
3) This may mislead the expectation, So it is not suggested to use the commit in triggers because of the parent and child transaction relationship.
4) But if you are OK with the result then by using AUTONOMOUS_TRANSATION, commit can be used.
5) The usage may hurt if properly not used.

How to compile CUSTOM.pll in R12, below is the command.

How to compile CUSTOM.pll in R12, below is the command.
---------------------------------------------------------
frmcmp_batch module=CUSTOM.pll userid=apps/<pwd> output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

Path for CUSTOM.pll:
-------------------------
$AU_TOP/resource

Background
----------------
The CUSTOM library allows extension of Oracle E-Business Suite without modification of Oracle E-Business Suite code.

Comparison between CUSTOM.pll and Form personalization s
-----------------------------------------------------------
http://pboracletechnical.blogspot.com/2013/03/forms-personalization-and-custompll.html


For more information on CUSTOM.pll check the below link.
http://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T458265.htm

How to change Item price in Sales Order form by some formula in Sales Order Form

Here we will learn how to change the price of the item by formula.The formula here would be some calculation based on the Client requirement.
Like adding some tax to the actual Item price.

For instance if Item A price is 10$, the requirement is like make it 12$ by adding the Tax.

This can be accomplished by doing the following steps.

1) There is a package QP_CUSTOM, which is a Oracle standard package, modify it and add the formula there for calculating the New price.
2) return the price as the parameter.
3) Navigate to Order Management Super User and create a new Pricing Formula and the Component Should be "Get Custom Price"
4) Create a new price list and attach the New Formula to the Price List, also check the Item where the price list need to be affected.

Forms Personalization and CUSTOM.pll introduction

Some Introduction on Forms personalization and CUSTOM.pll

Forms personalization: is a new feature provided by Oracle starting from 11.5.10 version. This is a user interface for altering/customizing the oracle apps behavior.
Below is the navigation for Forms Personalization.

Menu Navigation: Help-->Diagnostics-->Custom Code-->Personalize.

The personalization form should be used to implement the custom rules on a specific form. The
specific form refers to the desired form on which you want to apply the custom business logic or
modify the form behavior.

CUSTOM.pll: is a pl/sql library which is present in UNIX Box in $AU_TOP/resource. It is a single library which is used for altering/Customizing the Oracle apps behavior.

Below tasks can be performed by both Forms personalization and CUSTOM.pll

1) Disabling the Button/field or any item on the form
2) Make the fields/DFF read only.
3) Pop some warning/error messages/ interactive messages.
4) populate other fields based on the other input field.
5) create some LOV on fly
6) Change the navigation/block in the form.


Advantages of Forms personalization

i) Forms personalization can be applied on any form easily with simple navigation from the form.
Menu Navigation: Help-->Diagnostics-->Custom Code-->Personalize.
ii) Multiple users can work on Forms personalization
iii) Can be easily migrated from instance to instance by using FNDLOAD
iv) The forms personalization rules are stored in the FND tables

Advantages of CUSTOM.pll
a) Not all triggers can be performed by Forms personalization, like using 'WHEN-NEW-ITEM-INSTANCE'
b) User interactive messages and actions can be added.

How to get Request Id for the program run

Below SQL gives the Concurrent Request information,  parameter is concurrent program name.

select fcr.request_id request_id,
fcp.user_concurrent_program_name,substr(resp.responsibility_name ,1,50) responsibility_name,
to_char(fcr.ACTUAL_START_DATE, 'DD-MON-YYYY HH24:MI:SS') Start_date,
to_char(fcr.ACTUAL_COMPLETION_DATE, 'DD-MON-YYYY HH24:MI:SS') completion_date,
round(((fcr.ACTUAL_COMPLETION_DATE-fcr.ACTUAL_START_DATE)*24*60),2) Time_taken,
substr(users.description,1,30) user_description,
substr(fcp.user_concurrent_program_name,1,55) program_name,
fcr.phase_code,fcr.status_code,users.user_name,fcr.argument_text
from apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_tl fcp,
apps.fnd_user users,
apps.fnd_responsibility_tl resp
where 1 = 1
and fcr.responsibility_id = resp.responsibility_id
and fcr.requested_by = users.user_id
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.request_date > (sysdate - 200)
and fcp.user_concurrent_program_name like &User_Conc_prog_name
order by fcr.request_date desc ;

How to get object definition from database

Below statement gets the database object definition from database.

select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','SCHEMA') from dual;

Object_type,Object_name and Schema should be in Caps.

How to set Org Context in R12

Set org context in R12


The SQL command to set the ORG_ID prior to running a script is:


SQL> exec mo_global.init('AR');
exec mo_global.set_policy_context('S','&org_id');
Enter the org_id when prompted.


The procedure - mo_global.set_policy_context has two parameters
p_access_mode & p_org_id


p_access_mode          Description
S In case you want your current session to work against Single ORG_ID
M In case you want your current session to work against multiple ORG_IDs


p_org_id: Only applicable if p_access_mode is passed value of "S"


If using Toad
Begin
mo_global.set_policy_context(‘S’, &org_id);
End;


Set org context in 11i:


The SQL command to set the ORG_ID prior to running a script is:
SQL> execute dbms_application_info.set_client_info(&org_id);
Enter the org_id when prompted.
If using Toad
Begin
fnd_client_info.set_org_context(&org_id);
End;

How to create element entries and value in Payroll using API

1) API for creating element entries is PAY_ELEMENT_ENTRY_API.create_element_entry.
2) The procedure has lot of parameters, it mainly depends from project to project which all parameters value have to be passed.
3) There are few mandatory parameters which can be found in the PAY_ELEMENT_ENTRY_API spec.

4) Below is the example API for creating element entry with out any value.

         PAY_ELEMENT_ENTRY_API.create_element_entry
                           (p_validate                    => FALSE
                           ,p_effective_date              => to_date('01-JAN-2013','DD/MM/YYYY')--trunc('01-JAN-2013')
                           ,p_business_group_id           => emp_rec.business_group_id
                           ,p_assignment_id               => emp_rec.assignment_id
                           ,p_element_link_id             => element_rec.element_link_id
                           ,p_entry_type                  => 'E'
                           ,p_input_value_id1             => element_rec.input_value_id
                           ,p_entry_value1                => NULL
                           ,p_effective_start_date        => ld_effective_start_date
                           ,p_effective_end_date          => ld_effective_end_date
                           ,p_element_entry_id            => ln_element_entry_id
                           ,p_object_version_number       => ln_object_version_number
                           ,p_create_warning              => lc_create_warning
                            );

5) Also refer the table pay_element_types_f  ,pay_element_links_f   ,pay_input_values_f   for much understanding about the element entries.

How to disable Correction or Update button on Oracle HRMS screen

1) In Oracle apps HRMS Employee screen, after any changes to the employee, two options popup.
Correction and Update.

2) I have seen some clients asking to disable the correction button, the button cannot be removed by Personalization.

3) This can be done by using CUSTOM Pll.

4) Go to procedure style in CUSTOM Pll, and add the condition on DT_SELECT_MODE.
   if event_name = 'DT_SELECT_MODE'
        then
return custom.after;
else
return custom.standard;
end if;

5) Then after that go to event and add the below to disable the Correction Button.

  if (event_name = 'DT_SELECT_MODE') then
    if name_in('GLOBAL.G_DT_CORRECTION') = 'TRUE' then
    copy('FALSE', 'GLOBAL.G_DT_CORRECTION');
    copy('TRUE', 'GLOBAL.G_DT_UPDATE');
    end if;
    end if;

6) This can be applied to any button after changing the data in Employee screen.

For more details, refer the implementation guide.
http://docs.oracle.com/cd/E18727_01/doc.121/e13535.pdf

Oracle apps initialize

Oracle apps initialize

fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
                                                   resp_id=>l_resp_id,
                                                resp_appl_id=>l_resp_appl_id);

    l_user_id is the fnd user ID which will be utilized during the call.
    l_resp_id is the responsibility ID
    l_resp_appl_id is the responsibility application ID.