Receipt API

A simple Example

Declare

l_cr_id number;
l_return_status varchar2(20000);
l_msg_count number;
l_msg_data varchar2(20000);
p_count number;
begin
dbms_output.put_line('Start');
fnd_global.apps_initialize( 1297,20678,222,0);
arp_standard.enable_debug;
Ar_receipt_api_pub.Create_cash(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_receipt_number => '431',
p_customer_bank_account_id=>'10024',
p_amount => 10000,
p_currency_code=>'QAR',
p_commit=>FND_API.G_TRUE,
p_receipt_method_id =>3001,
p_customer_id => 3043,
p_called_from => 'BR_FACTORED_WITH_RECOURSE',
p_receipt_date=>to_date('12-11-2007','dd-mm-yyyy'),
p_maturity_date=>to_date('12-11-2007','dd-mm-yyyy'),
p_gl_date=>to_date('30-6-2004','dd-mm-yyyy'),
p_cr_id => l_cr_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
dbms_output.put_line( l_cr_id

'l');
dbms_output.put_line( l_return_status);
dbms_output.put_line( l_msg_data );
dbms_output.put_line( l_msg_count);
IF l_msg_count = 1 Then
dbms_output.put_line('l_msg_data '

l_msg_data);
ELSIF l_msg_count > 1 Then
LOOP
p_count := p_count+1;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
IF l_msg_data is NULL Then
EXIT;
END IF;
dbms_output.put_line('Message'

p_count

'.'

l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
end;

API-HRMS-II

Simple E.g for Update absence details:

CREATE OR REPLACE PROCEDURE XXRI_UPDATE_ABSENCE(p_person_id in number,
p_date_start in date,
p_date_end in date,
p_absence_attendance_id in number)
AS
   l_effective_date              DATE;
   l_absence_attendance_id       NUMBER;
   l_absence_days                NUMBER;
   l_absence_hours               NUMBER;
   l_object_version_number       NUMBER;
   l_dur_dys_less_warning        BOOLEAN;
   l_dur_hrs_less_warning        BOOLEAN;
   l_exceeds_pto_entit_warning   BOOLEAN;
   l_exceeds_run_total_warning   BOOLEAN;
   l_abs_overlap_warning         BOOLEAN;
   l_abs_day_after_warning       BOOLEAN;
   l_dur_overwritten_warning     BOOLEAN;
   l_del_element_entry_warning   BOOLEAN;
   l_abs_attendance_reason_id    NUMBER;
   l_date_start                  DATE;
   l_date_end                    DATE;
   l_person_id                   NUMBER;
BEGIN
   l_object_version_number := NULL;

   SELECT  person_id,abs_attendance_reason_id, absence_attendance_id
     INTO  l_person_id,l_abs_attendance_reason_id, l_absence_attendance_id
     FROM per_absence_attendances ABS
    WHERE ABS.absence_attendance_id =p_absence_attendance_id;

   BEGIN
      SELECT MAX (object_version_number)
        INTO l_object_version_number
        FROM per_absence_attendances ABS
       WHERE ABS.absence_attendance_id = p_absence_attendance_id;

      hr_person_absence_api.update_person_absence
                  (p_effective_date                 => l_effective_date,
                   p_absence_attendance_id          => l_absence_attendance_id,
                   p_abs_attendance_reason_id       => l_abs_attendance_reason_id,
                   p_date_notification              => l_effective_date,
                   p_date_start                     => p_date_start,
                   p_date_end                       => p_date_end,
                   p_absence_days                   => l_absence_days,
                   p_absence_hours                  => l_absence_hours,
                   p_object_version_number          => l_object_version_number,
                   p_dur_dys_less_warning           => l_dur_dys_less_warning,
                   p_dur_hrs_less_warning           => l_dur_hrs_less_warning,
                   p_exceeds_pto_entit_warning      => l_exceeds_pto_entit_warning,
                   p_exceeds_run_total_warning      => l_exceeds_run_total_warning,
                   p_abs_overlap_warning            => l_abs_overlap_warning,
                   p_abs_day_after_warning          => l_abs_day_after_warning,
                   p_dur_overwritten_warning        => l_dur_overwritten_warning,
                   p_del_element_entry_warning      => l_del_element_entry_warning
                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Inner statement' || ' ' || SQLERRM);
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Outer statement' || ' ' || SQLERRM);
      COMMIT;
END XXRI_UPDATE_ABSENCE;
/
Compile and call the procedure with required parameters

CREATE OR REPLACE PROCEDURE XXRI_CREATE_ABSENCE
(p_person_id IN NUMBER,
p_absence_name IN VARCHAR2,
p_effective_date IN DATE,
p_start_date IN DATE,
p_end_date IN DATE,
P_ABSENCE_ATTENDANCE_ID OUT NUMBER
) AS
-- Essential functionality variables
L_VALIDATE_MODE BOOLEAN := FALSE;
L_PERSON_TYPE_ID NUMBER(15);
L_BUSINESS_GROUP_ID NUMBER;
L_EMPLOYEE_NO NUMBER;
L_DATE1 DATE;

-- API Return Variables
L_PERSON_ID NUMBER;
L_ASSIGNMENT_ID NUMBER;
L_ABS_DAYS NUMBER;
L_ABS_HOURS NUMBER;

L_ABSENCE_ATTENDANCE_ID NUMBER;
L_OBJ_VERSION_NUMBER NUMBER;
L_OCCURRENCE NUMBER;
L_ENTITLEMENT_WARNING BOOLEAN;
L_ABS_DAY_AFTER_WARNING BOOLEAN;
L_ABS_OVERLAP_WARNING BOOLEAN;

-- Constant variables
--L_MODULE_ID CONSTANT VARCHAR2(30) := 'XXFTHR_MIGRATION007';

-- Error Handling variables
L_ERROR_MESSAGE VARCHAR2(150);
L_ERROR_CODE VARCHAR2(30);
L_ERROR_STATEMENT VARCHAR2(50);

-- Count Variables
L_COUNT_TOTAL NUMBER := 0;
L_COUNT_SUCCESS NUMBER := 0; -- Total number of successful rows
L_CNT1 NUMBER := 0;
L_ERRM VARCHAR2(100);
L_ERR_AT_STMT NUMBER;
O_ABSENCE_ATTENDANCE_ID NUMBER;
O_OBJECT_VERSION_NUMBER NUMBER;
O_OCCURRENCE NUMBER;
O_DUR_DYS_LESS_WARNING BOOLEAN;
O_DUR_HRS_LESS_WARNING BOOLEAN;
O_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN;
O_EXCEEDS_RUN_TOTAL_WARNING BOOLEAN;
O_ABS_OVERLAP_WARNING BOOLEAN;
O_ABS_DAY_AFTER_WARNING BOOLEAN;
O_DUR_OVERWRITTEN_WARNING BOOLEAN;
O_ABSENCE_DAYS NUMBER;
O_ABSENCE_HOURS NUMBER;

L_HIRE_DATE NUMBER;
L_BUSINESS_GROUP_ID VARCHAR(50);

L_ABSENCE_ATTENDANCE_TYPE_ID NUMBER;
L_ABS_ATTENDANCE_REASON_ID NUMBER;
L_BATCH_ID INTEGER;
L_CUTOFF_DATE VARCHAR(50) := '23-APR-2008';

X_USER_ID NUMBER;
X_RESP_ID NUMBER;
X_APPL_ID NUMBER;

BEGIN

--L_BUSINESS_GROUP_ID := 102;

--L_ERR_AT_STMT := 10;
--L_EMPLOYEE_NO := '2';
--L_CNT1 := L_CNT1 + 1;

--L_ABS_DAYS := 5;
--L_ABS_HOURS := NULL;
O_ABSENCE_ATTENDANCE_ID := NULL;
O_OBJECT_VERSION_NUMBER := NULL;
O_OCCURRENCE := NULL;
O_DUR_DYS_LESS_WARNING := NULL;
O_DUR_HRS_LESS_WARNING := NULL;
O_EXCEEDS_PTO_ENTIT_WARNING := NULL;
O_EXCEEDS_RUN_TOTAL_WARNING := NULL;
O_ABS_OVERLAP_WARNING := NULL;
O_ABS_DAY_AFTER_WARNING := NULL;
O_DUR_OVERWRITTEN_WARNING := NULL;
O_ABSENCE_DAYS := NULL;
O_ABSENCE_HOURS := NULL;
L_HIRE_DATE := NULL;
--L_PERSON_ID := NULL;
L_ABSENCE_ATTENDANCE_TYPE_ID := NULL;
L_ABS_ATTENDANCE_REASON_ID := NULL;

BEGIN

O_ABSENCE_DAYS := NULL;
L_PERSON_ID := 62;
SELECT USER_ID INTO X_USER_ID FROM FND_USER WHERE USER_NAME = 'SYSADMIN';

SELECT APPLICATION_ID
INTO X_APPL_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'PER';

SELECT RESPONSIBILITY_ID
INTO X_RESP_ID
FROM FND_APPLICATION FA, FND_RESPONSIBILITY_TL FR
WHERE FA.APPLICATION_SHORT_NAME = 'PER'
AND FA.APPLICATION_ID = FR.APPLICATION_ID
AND FR.RESPONSIBILITY_NAME = 'RI HRMS Manager';

FND_GLOBAL.APPS_INITIALIZE(X_USER_ID, X_RESP_ID, X_APPL_ID);

      BEGIN
         SELECT paat.absence_attendance_type_id
           INTO l_absence_attendance_type_id
           FROM per_absence_attendance_types paat
          WHERE UPPER (TRIM (paat.NAME)) = UPPER (TRIM (p_absence_name));
      EXCEPTION
         WHEN OTHERS
         THEN
            L_ERROR_MESSAGE := 'Absence Attendance Type Id is NULL';
       DBMS_OUTPUT.put_line ('E:' || L_ERROR_MESSAGE);
      END;


hr_person_absence_api.create_person_absence
(p_validate => FALSE
,p_effective_date => p_effective_date
,p_person_id => p_person_id
,p_business_group_id => 102
,p_absence_attendance_type_id => l_absence_attendance_type_id
,p_abs_attendance_reason_id => NULL
,p_comments => NULL
,p_date_notification => p_effective_date
,p_date_projected_start => NULL
,p_time_projected_start => NULL
,p_date_projected_end => NULL
,p_time_projected_end => NULL
,p_date_start => p_start_date
,p_time_start => NULL
,p_date_end => p_end_date
,p_time_end => NULL
,p_absence_days => O_ABSENCE_DAYS
,p_absence_hours => O_ABSENCE_HOURS
,p_authorising_person_id => NULL
,p_replacement_person_id => NULL
,p_attribute_category => NULL
,p_attribute1 => NULL
,p_attribute2 => NULL
,p_attribute3 => NULL
,p_attribute4 => NULL
,p_attribute5 => NULL
,p_attribute6 => NULL
,p_attribute7 => NULL
,p_attribute8 => NULL
,p_attribute9 => NULL
,p_attribute10 => NULL
,p_attribute11 => NULL
,p_attribute12 => NULL
,p_attribute13 => NULL
,p_attribute14 => NULL
,p_attribute15 => NULL
,p_attribute16 => NULL
,p_attribute17 => NULL
,p_attribute18 => NULL
,p_attribute19 => NULL
,p_attribute20 => NULL
,p_period_of_incapacity_id => NULL
,p_ssp1_issued => NULL
,p_maternity_id => NULL
,p_sickness_start_date => NULL
,p_sickness_end_date => NULL
,p_pregnancy_related_illness => NULL
,p_reason_for_notification_dela => NULL
,p_accept_late_notification_fla => NULL
,p_linked_absence_id => NULL
,P_ABSENCE_ATTENDANCE_ID => O_ABSENCE_ATTENDANCE_ID,
P_OBJECT_VERSION_NUMBER => O_OBJECT_VERSION_NUMBER,
P_OCCURRENCE => O_OCCURRENCE,
P_DUR_DYS_LESS_WARNING => O_DUR_DYS_LESS_WARNING,
P_DUR_HRS_LESS_WARNING => O_DUR_HRS_LESS_WARNING,
P_EXCEEDS_PTO_ENTIT_WARNING => O_EXCEEDS_PTO_ENTIT_WARNING,
P_EXCEEDS_RUN_TOTAL_WARNING => O_EXCEEDS_RUN_TOTAL_WARNING,
P_ABS_OVERLAP_WARNING => O_ABS_OVERLAP_WARNING,
P_ABS_DAY_AFTER_WARNING => O_ABS_DAY_AFTER_WARNING,
P_DUR_OVERWRITTEN_WARNING => O_DUR_OVERWRITTEN_WARNING);

END;

P_ABSENCE_ATTENDANCE_ID:=O_ABSENCE_ATTENDANCE_ID;
COMMIT;
END;
/

API-HRMS

Using api will help in post production Changes also.

Create a procedure to call the api.Mandatory columns will be explained in the api itself.Pass the values in the procedure so that data will be mapped to seeded table with validations.

Eg. of a HRMS API-We are going to call  hr_person_absence_api.delete_person_absence

CREATE OR REPLACE PROCEDURE xxri_delete_absence (p_absence_attendance_id NUMBER)
   AS
      CURSOR del_abscence
      IS
         SELECT absence_attendance_id, object_version_number
           FROM per_absence_attendances
          WHERE absence_attendance_id = p_absence_attendance_id;
   BEGIN
      --g_procedure_name := 'delete_emp_abscence';

      FOR i IN del_abscence
      LOOP
         BEGIN
            hr_person_absence_api.delete_person_absence
                         (p_validate                   => FALSE,
                          p_absence_attendance_id      => i.absence_attendance_id,
                          p_object_version_number      => i.object_version_number
                         );
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
             --  g_error_code := SUBSTR (SQLERRM, 1, 200);
              -- g_error_msg :=
                --     g_package_name
                  --|| '.'
--                  || g_procedure_name
--                  || '-'
--                  || g_error_code;
--               fnd_file.put_line (fnd_file.LOG, g_error_msg);
fnd_file.put_line (fnd_file.LOG,SQLERRM);
               DBMS_OUTPUT.put_line ('Error deleting Abscence:' || SQLERRM);
         END;
      END LOOP;
   END;
/

Interface Tables

GL INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- ----------------------
GL_BUDGET_INTERFACE TABLE
GL_DAILY_RATES_INTERFACE TABLE
GL_IEA_INTERFACE TABLE
GL_INTERFACE TABLE
GL_INTERFACE_CONTROL TABLE
GL_INTERFACE_HISTORY TABLE

AP INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ------------------------
AP_INTERFACE_CONTROLS TABLE
AP_INTERFACE_REJECTIONS TABLE
AP_INVOICES_INTERFACE TABLE
AP_INVOICE_LINES_INTERFACE TABLE

AR INTERFACE TABLES


TNAME TABTYPE

------------------------------ --------------------------------------
AR_PAYMENTS_INTERFACE_ALL TABLE
AR_TAX_INTERFACE TABLE
HZ_DQM_SYNC_INTERFACE TABLE
HZ_PARTY_INTERFACE TABLE
HZ_PARTY_INTERFACE_ERRORS TABLE


RA_CUSTOMERS_INTERFACE_ALL TABLE
RA_INTERFACE_DISTRIBUTIONS_ALL TABLE
RA_INTERFACE_ERRORS_ALL TABLE
RA_INTERFACE_LINES_ALL TABLE
RA_INTERFACE_SALESCREDITS_ALL TABLE

FA INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- ----------------------


FA_BUDGET_INTERFACE TABLE
FA_INV_INTERFACE TABLE
FA_PRODUCTION_INTERFACE TABLE
FA_TAX_INTERFACE TABLE

INVENTORY INTERFACE TABLES


TNAME TABTYPE


------------------------------ ------- ------------------------------------


MTL_CC_ENTRIES_INTERFACE TABLE
MTL_CC_INTERFACE_ERRORS TABLE
MTL_CI_INTERFACE TABLE
MTL_CI_XREFS_INTERFACE TABLE
MTL_COPY_ORG_INTERFACE TABLE
MTL_CROSS_REFERENCES_INTERFACE TABLE
MTL_DEMAND_INTERFACE TABLE
MTL_DESC_ELEM_VAL_INTERFACE TABLE
MTL_EAM_ASSET_NUM_INTERFACE TABLE
MTL_EAM_ATTR_VAL_INTERFACE TABLE
MTL_INTERFACE_ERRORS TABLE

TNAME TABTYPE


------------------------------ ------- --------------------------------------


MTL_INTERFACE_PROC_CONTROLS TABLE
MTL_ITEM_CATEGORIES_INTERFACE TABLE
MTL_ITEM_CHILD_INFO_INTERFACE TABLE
MTL_ITEM_REVISIONS_INTERFACE TABLE
MTL_ITEM_SUB_INVS_INTERFACE TABLE
MTL_OBJECT_GENEALOGY_INTERFACE TABLE
MTL_RELATED_ITEMS_INTERFACE TABLE
MTL_RESERVATIONS_INTERFACE TABLE
MTL_RTG_ITEM_REVS_INTERFACE TABLE
MTL_SECONDARY_LOCS_INTERFACE TABLE
MTL_SERIAL_NUMBERS_INTERFACE TABLE


TNAME TABTYPE


------------------------------ ------- ------------------------------------
MTL_SO_RMA_INTERFACE TABLE
MTL_SYSTEM_ITEMS_INTERFACE TABLE
MTL_TRANSACTIONS_INTERFACE TABLE
MTL_TRANSACTION_LOTS_INTERFACE TABLE
MTL_TXN_COST_DET_INTERFACE TABLE


PO INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- -------------------------


PO_DISTRIBUTIONS_INTERFACE TABLE
PO_HEADERS_INTERFACE TABLE
PO_INTERFACE_ERRORS TABLE
PO_LINES_INTERFACE TABLE
PO_REQUISITIONS_INTERFACE_ALL TABLE
PO_REQ_DIST_INTERFACE_ALL TABLE
PO_RESCHEDULE_INTERFACE TABLE
RCV_HEADERS_INTERFACE TABLE
RCV_LOTS_INTERFACE TABLE
RCV_SERIALS_INTERFACE TABLE
RCV_TRANSACTIONS_INTERFACE TABLE

BOM INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- ----------------------------------


BOM_BILL_OF_MTLS_INTERFACE TABLE
BOM_INTERFACE_DELETE_GROUPS TABLE
BOM_INVENTORY_COMPS_INTERFACE TABLE
BOM_OP_RESOURCES_INTERFACE TABLE
BOM_OP_ROUTINGS_INTERFACE TABLE
BOM_OP_SEQUENCES_INTERFACE TABLE
BOM_REF_DESGS_INTERFACE TABLE
BOM_SUB_COMPS_INTERFACE TABLE
CST_COMP_SNAP_INTERFACE TABLE
CST_INTERFACE_ERRORS TABLE
CST_ITEM_COSTS_INTERFACE TABLE
CST_ITEM_CST_DTLS_INTERFACE TABLE
CST_PC_COST_DET_INTERFACE TABLE
CST_PC_ITEM_COST_INTERFACE TABLE

WIP INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- --------------------------


WIP_COST_TXN_INTERFACE TABLE
WIP_INTERFACE_ERRORS TABLE
WIP_JOB_DTLS_INTERFACE TABLE
WIP_JOB_SCHEDULE_INTERFACE TABLE
WIP_MOVE_TXN_INTERFACE TABLE
WIP_SCHEDULING_INTERFACE TABLE
WIP_TXN_INTERFACE_ERRORS TABLE

OUTBOUND-UTL PACKAGE

UTL_FILE PACKAGE



The UTL_FILE package provides text file I/O from within PL/SQL. The init.ora file, the initialization parameter UTL_FILE_DIR is set to the accessible directories desired.
UTL_FILE_DIR = directory_name

Function & Procedure Description

• FOPEN A function that opens a file for input or output and returns a file handle used in subsequent I/O operations
• IS_OPEN A function that returns a Boolean value whenever a file handle refers to an open file
• GET_LINE A procedure that reads a line of text from the opened file and places the text in the Output buffer parameter (the maximum size of an input record is 1,023 bytes unlessyou specify a larger size in the overloaded version of FOPEN)
• PUT, PUT_LINE A procedure that writes a text string stored in the buffer parameter to the opened file (no line terminator is appended by put; use new_line to terminate the line, or use PUT_LINE to write a complete line with a terminator)
• PUTF A formatted put procedure with two format specifiers: %s and \n (use %s to substitute a value into the output string. \n is a new line character)
• NEW_LINE Procedure that terminates a line in an output file
• FFLUSH Procedure that writes all data buffered in memory to a file
• FCLOSE Procedure that closes an opened file
• FCLOSE_ALL Procedure that closes all opened file handles for the session
• Exceptions to the UTL_FILE Package Exception Name Description
• INVALID_PATH The file location or filename was invalid.
• INVALID_MODE The OPEN_MODE parameter in FOPEN was invalid.
• INVALID_FILEHANDLE The file handle was invalid.
• INVALID_OPERATION The file could not be opened or operated on as requested.
• READ_ERROR An operating system error occurred during the read operation.
• WRITE_ERROR An operating system error occurred during the write operation.
• INTERNAL_ERROR An unspecified error occurred in PL/SQL.

UTL_FILE procedures can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or
VALUE_ERROR.


SQL * Loader


D:\oracle\prodora\8.0.6\BIN>SQLLDR scott/tiger@prod control='e:\vbbk\myin.ctl'

Interface Tables

GL INTERFACE TABLES


TNAME TABTYPE

------------------------------ ------- ----------------------

GL_BUDGET_INTERFACE TABLE

GL_DAILY_RATES_INTERFACE TABLE

GL_IEA_INTERFACE TABLE

GL_INTERFACE TABLE

GL_INTERFACE_CONTROL TABLE

GL_INTERFACE_HISTORY TABLE



AP INTERFACE TABLES

TNAME TABTYPE

------------------------------ ------- ------------------------

AP_INTERFACE_CONTROLS TABLE

AP_INTERFACE_REJECTIONS TABLE

AP_INVOICES_INTERFACE TABLE

AP_INVOICE_LINES_INTERFACE TABLE

AR INTERFACE TABLES

TNAME TABTYPE

------------------------------ --------------------------------------

AR_PAYMENTS_INTERFACE_ALL TABLE

AR_TAX_INTERFACE TABLE

HZ_DQM_SYNC_INTERFACE TABLE

HZ_PARTY_INTERFACE TABLE

HZ_PARTY_INTERFACE_ERRORS TABLE

RA_CUSTOMERS_INTERFACE_ALL TABLE

RA_INTERFACE_DISTRIBUTIONS_ALL TABLE

RA_INTERFACE_ERRORS_ALL TABLE

RA_INTERFACE_LINES_ALL TABLE

RA_INTERFACE_SALESCREDITS_ALL TABLE



FA INTERFACE TABLES



TNAME TABTYPE

------------------------------ ------- ----------------------

FA_BUDGET_INTERFACE TABLE

FA_INV_INTERFACE TABLE

FA_PRODUCTION_INTERFACE TABLE

FA_TAX_INTERFACE TABLE


INVENTORY INTERFACE TABLES


TNAME TABTYPE

------------------------------ ------- ------------------------------------

MTL_CC_ENTRIES_INTERFACE TABLE

MTL_CC_INTERFACE_ERRORS TABLE

MTL_CI_INTERFACE TABLE

MTL_CI_XREFS_INTERFACE TABLE

MTL_COPY_ORG_INTERFACE TABLE

MTL_CROSS_REFERENCES_INTERFACE TABLE

MTL_DEMAND_INTERFACE TABLE

MTL_DESC_ELEM_VAL_INTERFACE TABLE

MTL_EAM_ASSET_NUM_INTERFACE TABLE

MTL_EAM_ATTR_VAL_INTERFACE TABLE

MTL_INTERFACE_ERRORS TABLE



TNAME TABTYPE

------------------------------ ------- --------------------------------------

MTL_INTERFACE_PROC_CONTROLS TABLE

MTL_ITEM_CATEGORIES_INTERFACE TABLE

MTL_ITEM_CHILD_INFO_INTERFACE TABLE

MTL_ITEM_REVISIONS_INTERFACE TABLE

MTL_ITEM_SUB_INVS_INTERFACE TABLE

MTL_OBJECT_GENEALOGY_INTERFACE TABLE

MTL_RELATED_ITEMS_INTERFACE TABLE

MTL_RESERVATIONS_INTERFACE TABLE

MTL_RTG_ITEM_REVS_INTERFACE TABLE

MTL_SECONDARY_LOCS_INTERFACE TABLE

MTL_SERIAL_NUMBERS_INTERFACE TABLE



TNAME TABTYPE

------------------------------ ------- ------------------------------------

MTL_SO_RMA_INTERFACE TABLE

MTL_SYSTEM_ITEMS_INTERFACE TABLE

MTL_TRANSACTIONS_INTERFACE TABLE

MTL_TRANSACTION_LOTS_INTERFACE TABLE

MTL_TXN_COST_DET_INTERFACE TABLE



PO INTERFACE TABLES





TNAME TABTYPE

------------------------------ ------- -------------------------

PO_DISTRIBUTIONS_INTERFACE TABLE

PO_HEADERS_INTERFACE TABLE

PO_INTERFACE_ERRORS TABLE

PO_LINES_INTERFACE TABLE

PO_REQUISITIONS_INTERFACE_ALL TABLE

PO_REQ_DIST_INTERFACE_ALL TABLE

PO_RESCHEDULE_INTERFACE TABLE

RCV_HEADERS_INTERFACE TABLE

RCV_LOTS_INTERFACE TABLE

RCV_SERIALS_INTERFACE TABLE

RCV_TRANSACTIONS_INTERFACE TABLE



BOM INTERFACE TABLES



TNAME TABTYPE

------------------------------ ------- ----------------------------------

BOM_BILL_OF_MTLS_INTERFACE TABLE

BOM_INTERFACE_DELETE_GROUPS TABLE

BOM_INVENTORY_COMPS_INTERFACE TABLE

BOM_OP_RESOURCES_INTERFACE TABLE

BOM_OP_ROUTINGS_INTERFACE TABLE

BOM_OP_SEQUENCES_INTERFACE TABLE

BOM_REF_DESGS_INTERFACE TABLE

BOM_SUB_COMPS_INTERFACE TABLE

CST_COMP_SNAP_INTERFACE TABLE

CST_INTERFACE_ERRORS TABLE

CST_ITEM_COSTS_INTERFACE TABLE

CST_ITEM_CST_DTLS_INTERFACE TABLE

CST_PC_COST_DET_INTERFACE TABLE

CST_PC_ITEM_COST_INTERFACE TABLE



WIP INTERFACE TABLES



TNAME TABTYPE

------------------------------ ------- --------------------------

WIP_COST_TXN_INTERFACE TABLE

WIP_INTERFACE_ERRORS TABLE

WIP_JOB_DTLS_INTERFACE TABLE

WIP_JOB_SCHEDULE_INTERFACE TABLE

WIP_MOVE_TXN_INTERFACE TABLE

WIP_SCHEDULING_INTERFACE TABLE

WIP_TXN_INTERFACE_ERRORS TABLE



ORDER MANAGEMENT INTERFACE TABLES



TNAME TABTYPE

------------------------------ ------- -----------------------------------

SO_CONFIGURATIONS_INTERFACE TABLE

SO_HEADERS_INTERFACE_ALL TABLE

SO_HEADER_ATTRIBUTES_INTERFACE TABLE

SO_LINES_INTERFACE_ALL TABLE

SO_LINE_ATTRIBUTES_INTERFACE TABLE

SO_LINE_DETAILS_INTERFACE TABLE

SO_PRICE_ADJUSTMENTS_INTERFACE TABLE

SO_SALES_CREDITS_INTERFACE TABLE

SO_SERVICE_DETAILS_INTERFACE TABLE

WSH_DELIVERIES_INTERFACE TABLE

WSH_FREIGHT_CHARGES_INTERFACE TABLE

WSH_PACKED_CONTAINER_INTERFACE TABLE

Data Pump Example Code

Data Pump Used for data upload in HRMS

It has Meta Mapper,Interface Tables,PL/SQL Routines,Data Pump Engine

Meta Mapper Creates the procedure Needed To map to HR API Parameters.Check the example
1) HR_PUMP_UTILS.CREATE_BATCH_HEADER
2) HRDPP_CREATE_EMPLOYEE.INSERT_BATCH_LINES
3) HRDPP_UPDATE_EMP_ASG_CRITERIA.INSERT_BATCH_LINES

***Using API***
1) HR_EMPLOYEE_API.CREATE_EMPLOYEE
2) HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA
3) HR_PUMP_UTILS.CREATE_BATCH_HEADER
4) HRDPP_CREATE_EMPLOYEE.INSERT_BATCH_LINES
5) HRDPP_UPDATE_EMP_ASG_CRITERIA.INSERT_BATCH_LINES


Declare
L_assignment_number varchar2 (20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);/* while update needed for exisiting assigment,use employee id and name as identifier(unique combination) as key*/
l_asg_user_key varchar2(100);
begin
l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','MY Company');
l_pers_user_key := 'Wise' '25-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Wise' feb-1978' ' : ASG USER KEY';
hrdpp_create_employee.insert_batch_lines(
p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'WISE'
,p_sex => 'M'
,p_date_of_birth => '25-feb-1978');
commit;
end;

declare
l_organization_name varchar2(20);
l_language_code varchar2(20);
l_batch_id number;
l_asg_user_key varchar2(100);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2(50);
L_PEOPLE_GROUP_ID NUMBER;
begin

l_batch_id := hr_pump_utils.create_batch_header('Assign Batch Header','My Company');
l_asg_user_key := 'Wise' '25-feb-1978' ' : ASG USER KEY';
L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;
hrdpp_update_emp_asg_criteria.insert_batch_lines
(p_batch_id => l_batch_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

commit;
end;

declare

l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2 (50);
L_PEOPLE_GROUP_ID NUMBER;
begin
l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Aise' '26-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Aise' '26-feb-1978' ' : ASG USER KEY';
hrdpp_create_employee.insert_batch_lines(
p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Aise'
,p_sex => 'M'
,p_date_of_birth => '26-feb-1978');
L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;
hrdpp_update_emp_asg_criteria.insert_batch_lines(
p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

Commit;
End;
Declare

l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2 (50);
L_PEOPLE_GROUP_ID NUMBER;

begin
l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Bise' '20-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Bise' '20-feb-1978' ' : ASG USER KEY';
hrdpp_create_employee.insert_batch_lines(p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key,
p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Bise'
,p_sex => 'M'
,p_date_of_birth => '20-feb-1978');
L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;
hrdpp_update_emp_asg_criteria.insert_batch_lines(
p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_PAY_BASIS_NAME => 'Exempt'
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);
commit;
end;
declare
l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2(50);
L_PEOPLE_GROUP_ID NUMBER;

begin

l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Bise' '20-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Bise' '20-feb-1978' ' : ASG USER KEY';

hrdpp_create_employee.insert_batch_lines(
p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Bise'
,p_sex => 'M'
,p_date_of_birth => '20-feb-1978');

L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;

hrdpp_update_emp_asg_criteria.insert_batch_lines(
p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_JOB_NAME => 'HR009.HR CONSULTANT'
,P_PAY_BASIS_NAME => 'Exempt'
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

commit;

end;


declare

l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2(50);
L_PEOPLE_GROUP_ID NUMBER;
begin

l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Sise' '2-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Sise' '2-feb-1978' ' : ASG USER KEY';

hrdpp_create_employee.insert_batch_lines(

p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Sise'
,p_sex => 'M'
,p_date_of_birth => '2-feb-1978');

L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;

hrdpp_update_emp_asg_criteria.insert_batch_lines(

p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_ORGANIZATION_NAME => 'My Company'
,P_JOB_NAME => 'AA900.Administrative Assistant'
,P_POSITION_NAME => 'AA910.EXECUTIVE ASSISTANT TO CEO'
,P_PAY_BASIS_NAME => 'Exempt'
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

commit;

end;

HRMS API

Using API For inbound:

****************Points To Note*********************
Hr_assignment_api

In Parameters: p_assignment_id,p_effective_date
Required Parameters:
P_Validate: Boolean-> If remains true then data base value will not be changed,If false then assignment is updated.Default value is false.
P_Effective_Date->in Param
P_DateTrack_Update_mode->Correction or Update,In Param
P_Object_Version_Number->In,Out,Copy Param.Remains 1 for new assignment,gets increamented by one to each update.
P_Assignment_Status_type_id->in
P_Change_Reason->Explicitly set to null if no change needed.

************************************************
To change the status of a employee.the status should be active before modifying.


Change the status to suspend and run this script.

Now again status will be changed to active.

Pkg used is hr_assignment_api.activate_emp_asg

Base tables : per_assignments_f

declare

l_object_version_number number:=7;

l_effective_start_date date;

l_effective_end_date date;

begin

hr_assignment_api.activate_emp_asg

(p_validate =>false

,p_effective_date =>'1-JAN-2009'

,p_datetrack_update_mode =>'CORRECTION'

,p_assignment_id =>12854

,p_change_reason =>hr_api.g_varchar2

,p_object_version_number =>l_object_version_number

,p_assignment_status_type_id =>hr_api.g_number

,p_effective_start_date =>l_effective_start_date

,p_effective_end_date =>l_effective_end_date

);

dbms_output.put_line('Object Version Number ' l_object_version_number);

commit;

end;

/

To activate for contract employees

Create a contract employee first.

declare

l_object_version_number number:=5;

l_effective_start_date date;

l_effective_end_date date;

begin

hr_assignment_api.activate_cwk_asg

(p_validate =>false

,p_effective_date =>'1-JAN-2009'

,p_datetrack_update_mode =>'CORRECTION'

,p_assignment_id =>12894

,p_change_reason =>hr_api.g_varchar2

,p_object_version_number =>l_object_version_number

,p_assignment_status_type_id =>hr_api.g_number

,p_effective_start_date =>l_effective_start_date

,p_effective_end_date =>l_effective_end_date

);

dbms_output.put_line('Object Version Number ' l_object_version_number);

commit;

end;

/

Inbound Interface

1. Create a flat file using OUTBOUND or manually. (‘txt’ or ‘dat’ file)


(E:\vbbk\emp.txt)

SMITH$7369$800


ALLEN$7499$1600

WARD$7521$1250

JONES$7566$2975

MARTIN$7654$1250

BLAKE$7698$2850

CLARK$7782$2450

SCOTT$7788$3000

KING$7839$5000

TURNER$7844$1500

ADAMS$7876$1100

JAMES$7900$950

FORD$7902$3000

MILLER$7934$1300

KAARTHIK$$5565

$000$1515

RAMU$5454$

2. Create a staging table with




CREATE TABLE MY_EMP_STAG(ENAME VARCHAR2(10),EMPNO NUMBER(4),SAL NUMBER(7,2),FLAG VARCHAR2(2));



3. Create control file

(E:\vbbk\myin.ctl)



LOAD DATA

INFILE 'E:\VBBK\EMP.TXT'

INTO TABLE "MY_EMP_STAG"

INSERT

FIELDS TERMINATED BY '$'

TRAILING NULLCOLS

(ENAME,EMPNO,SAL)



4. Run SQLLDR to load the data into the table



D:\oracle\prodora\8.0.6\BIN>SQLLDR scott/tiger@prod control='e:\vbbk\myin.ctl'

SQL*Loader: Release 9.2.0.6.0 - Production on Sat Nov 24 11:44:58 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 17

D:\oracle\prodora\8.0.6\BIN>


5. Check the table

select * from my_emp_stag


6. To validate the table create a procedure

(EMP_VALIDATE)



CREATE OR REPLACE PROCEDURE EMP_VALIDATE IS

CURSOR MYCUR IS SELECT * FROM MY_EMP_STAG FOR UPDATE OF FLAG;

MYREC MYCUR%ROWTYPE;

BEGIN

FOR MYREC IN MYCUR LOOP

IF MYREC.ENAME IS NULL OR MYREC.EMPNO IS NULL OR MYREC.SAL IS NULL THEN

UPDATE MY_EMP_STAG SET FLAG='N' WHERE CURRENT OF MYCUR;

ELSE

UPDATE MY_EMP_STAG SET FLAG='Y' WHERE CURRENT OF MYCUR;

END IF;

END LOOP;

END;

7. Run the validation program

BEGIN

EMP_VALIDATE;

END;

8. Check the table

select * from my_emp_stag

Outbound Interface

Outbound Interface process:

Outbound Interface will be used to extract the data from oracle Database tables into the flat files.

Inbound Interface will be used to upload the data from legacy system (Flat files) into Oracle Applications base tables.

While developing the outbound Interface we will use UTL_File to Extract the data.

While Developing the Inbound interface we will use SQL * loader to import the data into base tables.


UTL_FILE Package :
==================

this is One of the PL/SQL Package which will be used to transfer the data from table to files from files to tables
But when we are working for file to table we will use SQl *Loader to transfer from table to file we have no alternative we have to use UTL_FILE.

We will use following three functions to generate the file.

1)Utl_File.fopen = To open (or) Create the file

2)Utl_File.Put_line = To Transfer the data into the File.

3)Utl_File.fclose = To close the File after Data transfer.

outbound Interface Process:
=============================

1)Develop the PL/SQL Program (Either Procedure or Package)

2)Write the Cursor to retrieve the data from database tables.

3)Create file or Open the File by using UTL_File.fopen().

4)Open the Cursor

5)If any validations are there write the validations

6)Transfer the Data into File by using UTL_File.Put_Line().

7)Close the Cursor.

8)Close the File by using UTL_File.fclose();

9)Register the Procedure or Package as Concurrent Program and submit from SRS Window.