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
;