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.