Category Archives: OAF

Submitting and Monitoring Concurrent Program from Oracle apps Self Service (SSHR-OAF) pages

I here take example of Payslip where users requirement wants to hide the link in the concurrent program which tells the User ID of the Program. However there are many scenarios and situations that we need a Menu of Function for a Report or Concurrent Program.

I have made Function using a Existing Oracle apps Report and the report short name is XXPAY_SLIP_USER.

Define Function:

Navigation: System Administrator -> Application -> Function

Properties: SSWA jsp function

Web HTML Call:

To submit any concurrent program:

OA.jsp?akRegionApplicationId=0&akRegionCode=FNDCPPROGRAMPAGE&scheduleRegion=Hide&notifyRegion=Hide&printRegion=Hide

To Submit Particular Concurrent Program

OA.jsp?akRegionCode=FNDCPPROGRAMPAGE&akRegionApplicationId=0&programApplName=PAY&programName=XXPAY_SLIP_USER&programRegion=Hide&scheduleRegion=Hide&notifyRegion=Hide&printRegion=Hide&reviewRegion=Hide

Here XXPAY_SLIP_USER is the Concurrent program Short Name. The Parameters of this Concurrent program will be generated automatically

Advertisements

Query to get Executable, Concurrent, Application in oracle apps AOL

Below query gives the Executable, Concurrent, Application info in oracle apps AOL
select fcpv.OUTPUT_FILE_TYPE, fcpv.CONCURRENT_PROGRAM_NAME, fcpv.USER_CONCURRENT_PROGRAM_NAME, substr(fat.application_name, 0,3), EXECUTION_FILE_NAME
from FND_CONCURRENT_PROGRAMS_VL fcpv , fnd_application_tl fat , FND_EXECUTABLES_FORM_V fefv
where
fcpv.application_id = fat.application_id
and fefv.executable_id = fcpv.EXECUTABLE_ID
and fcpv.USER_CONCURRENT_PROGRAM_NAME in (
‘HW Employee Contract Extension Letter’,
‘HW Employment Letter to Embassy’
,’HW Empoyee Education Certificate Letter’,
‘HW Probation Conformation Letter’,
‘HW Salary Certificate Letter’,
‘HW Salary Increment Letter’,
‘HW Salary Increment Or Bonus Letter Report’,
‘HW Sponsorship Visa Letter to Embassy’,
‘HW To Whom it May Concern Letter’,
‘HW Promotion Conformation Letter’,
‘HW Salary Alignment letter’,
‘HW Resignation Letter to Bank’,
‘HW Experience Certificate’,
‘HW Resignation Acceptance Letter’,
‘XXHW Employee Contract Non Renewable Report’,
‘HW Bonus Letter Only’
);

Code for Making a field or Response box mandatory in Workflow

Below is the code that will check the workflow for mandatory of response field.

Here the p_funcmode = ‘VALIDATE’ sets in validate mode and after handling the validation the we can set the appropriate message in the this line

x_resultout := ‘ERROR:You Must the Reason’;

 

if( p_funcmode = ‘VALIDATE’) then
l_nid := wf_engine.context_nid;
v_result :=wf_notification.getattrtext (l_nid, ‘APPROVE_COMMENTS’);
if(v_result is null)
then
x_resultout := ‘ERROR:You Must the Reason’;
end if;
end if;

oracle.apps.fnd.common.PoolException: Exception creating new Poolable object.

Sometimes you may get a Error as

oracle.apps.fnd.common.PoolException: Exception creating new Poolable object.

If you know that there is no Issue in server side and the problem is in your JDeveloper and you have done some trial and error method. I suggest you to change the hostname to IP in the DBC file and sometimes this Issue will get solved.

OAF Page Personalizations – read only issue

I just came across this problem when my collegue got this OAF Read Only Issue.

Read only OAF page

Read only OAF page

It’s frustrating sometimes, to deal with context specific issues with page personalizations. For example, not all field properties are allowed to personalize in all the contexts. This article describes one such issues of page personalizations become read-only and how to change it back to normal mode.

 

Have you also witnessed any such case? Page personalizations become read-only, when trying to edit. Above screenshot shows the personalize page for employee self service -> personal information self service page. Notice that, none of the page options are allowed to change and because of this issue none of existing personalizations take effect.

Well, the problem is because of the following option that is enabled in the XML MDS document of the page definition. Export page definition or XMLExporter command to see the page XML definition.

developerMode="true"

developerMode=”true” – One of the reasons for this string could be because of the profile option FND_PERSONALIZATION_SEEDING_MODE is set to Yes, last time when it was personalized.

Now we know where the problem is, solving it is piece of cake. 🙂 Export the page definition using functional administrator, edit the XML to remove this string and import it back. Needless to say, while importing back the modified XML, make sure to set the profile option FND_PERSONALIZATION_SEEDING_MODE is set to No.

Following screenshots show how the page XML looks like after editing and page becomes editable after import.

remove developerMode

Source

Oracle apps PG or region XML file path

Usually we will find the java files in $JAVA_TOP but the xml files will not be in the $JAVA_TOP. The xml files will be in respective application top, for example im woking in SubLedger Accounting Application and the shortname is XLA and top is XLA_TOP so go to the top by

cd $XLA_TOP/mds/linesinquiry/webui/JeLinesInquiriesPG.xml

A simple VO Iteration of Rows using While loop in OAF

 

A very simple method to  Iterate VO using While Loop.

 

OAViewObject eVO=am.findViewObject(“xxSuppSumVO”);

eVO.first();
while(eVO.hasNext())
{
System.out.println(“Attributes ————————————> “+eVO.getCurrentRow().getAttribute(0)+eVO.getCurrentRow().getAttribute(1)+eVO.getCurrentRow().getAttribute(2));
eVO.next();
}

Prepared Statement and Callable Statement in OAF

We use Prepared Statement to Execute a Query or a SQL Statement and we use callable statement to call a package, function, procedure

In general, to invoke a stored procedure from within an entity object or an application module, you need to:
1. Create a JDBC CallableStatement with the PL/SQL block containing the stored procedure invocation
2. Bind any variables.
3. Execute the statement.
4. Optionally retrieve the values of any OUT parameters.
5. Close the statement.

For Prepared Statement we use directly for any DML operations. To Execute the SELECT Query we have to use the class Result set to get the Data..

if(pageContext.getParameter(EVENT_PARAM).equals(“TEST”))
{
String   valuetobePassed1=repId+””;
String valuetobePassed2 = null;
System.out.println(“Condition called for test”);
CallableStatement callableStatement = txn.createCallableStatement(“declare begin :outVariable := RET_QUERY_REP.RET_QUERY(:2, :3); end;”,OADBTransaction.DEFAULT); //Refer to the Syntax where the String is a procedure
//Create a JDBC CallableStatement with the PL/SQL block containing the stored procedure invocation
//Bind any variables.
try
{
callableStatement.registerOutParameter(1, Types.VARCHAR); //First Bind Varible should give us out Parameter
callableStatement.setString(2, valuetobePassed1); //Passing a variable to Second Variable
callableStatement.setString(3, valuetobePassed2); //Passing a variable to Second Variable
callableStatement.execute(); // Execute the statement.
outParamValue = (callableStatement.getString(1)).toString(); //Retrieve the values of any OUT parameters.
System.out.println(“The Out Parameter Obtained from the Procedure is “+outParamValue);
}
catch (SQLException sqle)  //Be cautious that execute() throws an SQL Exception
{
System.out.println(“Exception occured”);
}
//Sql Validation whether Query is Right or wrong
pageContext.writeDiagnostics(this, repId+outParamValue, 4);
System.out.println(“The output value is “+repId+”This “+outParamValue);
PreparedStatement transaction = (PreparedStatement)am.getOADBTransaction().createPreparedStatement(outParamValue,0);
OracleResultSet rs = null;  //Result Set to get the Data from the Query
try
{
rs=(OracleResultSet)transaction.executeQuery();
if(rs.next())
{
pageContext.writeDiagnostics(this, “Column one value”+rs.getString(1)+”Column two value”+rs.getString(2), 4 ); //We can see the Output
System.out.println(“Column one value”+rs.getString(1)+”Column two value”+rs.getString(2)); //We can see the Output
}
else
{
rs.close(); //Close the statement.
}
}
catch(Exception e)
{
pageContext.writeDiagnostics(this, “Exception Has occured”, 4 );
System.out.println(“Exception came Hence SQL query is wrong”);
}
}

 

 

 

The package that returns a Query from the table which is used in callable statement

 

CREATE OR REPLACE PACKAGE RET_QUERY_REP
IS
FUNCTION RET_QUERY (P_REP_ID NUMBER, P_VAL VARCHAR2)
RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY RET_QUERY_REP
IS
FUNCTION RET_QUERY (P_REP_ID NUMBER, P_VAL VARCHAR2)
RETURN VARCHAR2
IS
QUERYSTR   VARCHAR2 (10000);
TYPE R_CURSOR IS REF CURSOR;
C_REF      R_CURSOR;
LV_TEMP    VARCHAR2 (1000);
LV_TEST    VARCHAR2 (1000);
LV_COLV    VARCHAR2 (1000);
BEGIN
IF P_VAL IS NULL THEN
SELECT   (‘ FROM ‘|| FROM_CLAUSE|| ‘ WHERE ‘|| NVL (WHERE_CLAUSE, ‘1=1’)|| NVL2 (GROUPBY_CLAUSE, ‘ GROUP BY ‘ || GROUPBY_CLAUSE, ”)|| ‘ ORDER BY ‘|| NVL (ORDERBY_CLAUSE, ‘1’))
INTO   QUERYSTR
FROM   XXCUST_REPORT_V
WHERE   REP_ID = P_REP_ID;          –Query without column names
ELSE
SELECT   (‘ FROM ‘|| FROM_CLAUSE|| ‘ WHERE ‘|| NVL2 (WHERE_CLAUSE, WHERE_CLAUSE||’ AND ‘||P_VAL, ‘1=1’)|| NVL2 (GROUPBY_CLAUSE, ‘ GROUP BY ‘ || GROUPBY_CLAUSE, ”)|| ‘ ORDER BY ‘|| NVL (ORDERBY_CLAUSE, ‘1’))
INTO   QUERYSTR
FROM   XXCUST_REPORT_V
WHERE   REP_ID = P_REP_ID;
END IF;

–DBMS_OUTPUT.PUT_LINE (‘THE QUERY OUTLINE ——-> ‘ || QUERYSTR);
OPEN C_REF FOR
SELECT   XRCV.REP_COLUMN
FROM   XXCUST_REPORT_V XRV, XXCUST_REPORT_COLUMN_V XRCV
WHERE   XRV.REP_ID = XRCV.REP_ID AND XRCV.REP_ID = P_REP_ID;
LOOP
FETCH C_REF INTO   LV_COLV;
EXIT WHEN C_REF%NOTFOUND;
LV_TEST := LV_TEST || LV_COLV || ‘, ‘;
–DBMS_OUTPUT.PUT_LINE(‘THE COLUMN NAMES ARE ——-> ‘||LV_TEST);
END LOOP;
CLOSE C_REF;
LV_TEMP := ‘SELECT ‘ || RPAD (LV_TEST, LENGTH (LV_TEST) – 2) || QUERYSTR;
DBMS_OUTPUT.PUT_LINE (‘THE FINAL QUERY IS  ARE ——-> ‘ || LV_TEMP);
RETURN LV_TEMP;
END RET_QUERY;
END RET_QUERY_REP;

 

 

Payroll Intergration API – Absence and Batch Element Entry

Overview

API is a way to insert the data from custom table to Oracle seeded table like interface in oracle apps.

To program a API or Interface we have to know the front end and where the data gets saved(tables). So it is always a good approach to know the requirement, flow and the backend.

This area which we are going to deal is payroll Integration. Here we get the Data from the custom table and then we insert into Oracle seeded table by using the API(Application Programmable Interface). The custom table is that in OTL(Oracle Time and Labour) which is a custom module designed for moiss.

Bascially a Interface or API is used to load a bulk of data to the Oracle Seeded table. The hr_person_absence_api API inserts absence data bulk to the table and pay_batch_element_entry_api API Inserts a bulk of elements based on assignment set. This groups as Inputs that has to be applied for elements.

Here I have developed a package which has both the functionalities of Absence and Batch Element Entry

API’s are

pay_batch_element_entry_api →pay_batch_headers, pay_batch_lines, pay_batch_control_totals

hr_person_absence_api

 

Flow

PAY_BATCH_ELEMENT_ENTRY_API

pay_batch_element_entry_api API inserts data in a batch depending on group of elements or assignment set.

The Navigation is

Mass Information Exchange(MIX) → Batch Element Entry

1.

 

The Navigation is

Mass Information Exchange(MIX) → Batch Element Entry → Element Lines

2

HR_PERSON_ABSENCE_API

The Navigation is

Global HRMS Manager → People → Enter and Maintain → Others → Absence

3

In oracle apps for Integrating the custom data to oracle absence table we have the HR_PERSON_ABSENCE_API API to delete or to update we can also use this API. Before programming this API and moving the data from custom table it is

 

This below screenshot is a example of a sick leave for a person

4

 

Queries Related to HR_PERSON_ABSENCE_API

 

 

To Get Employee Details from PER_ALL_PEOPLE_F table

 

SELECT *

FROM PER_ALL_PEOPLE_F PAPF, PER_ALL_ASSIGNMENTS_F PAAF

WHERE PAPF.EMPLOYEE_NUMBER =‘2108’

AND PAPF.CURRENT_EMPLOYEE_FLAG =‘Y’

AND PAPF.EMPLOYEE_NUMBER ISNOTNULL

AND PAAF.PERSON_ID = PAPF.PERSON_ID

ANDSYSDATEBETWEEN PAAF.EFFECTIVE_START_DATE

AND PAAF.EFFECTIVE_END_DATE;

To Test the API whether it is working or not just click on the API and press F4 now a window gets opened. Just Click on the top on the option Toggle Spec or Toggle Body now take the specification and body seperate compile and check whether it is working or not

5

To Get Employee‘s Absence Details from PER_ALL_PEOPLE_F table

SELECT PAPF.PERSON_ID,

PAPF.EFFECTIVE_START_DATE,

PAPF.EFFECTIVE_END_DATE,

PAPF.LAST_NAME,

PAV.CATEGORY_MEANING

FROM PER_ALL_PEOPLE_F PAPF,

PER_ALL_ASSIGNMENTS_F PAAF,

PER_ABSENCE_ATTENDANCES_V PAV

WHERE PAPF.EMPLOYEE_NUMBER =‘2108’

AND PAPF.CURRENT_EMPLOYEE_FLAG =‘Y’

AND PAPF.EMPLOYEE_NUMBER ISNOTNULL

AND PAAF.PERSON_ID = PAPF.PERSON_ID

ANDSYSDATEBETWEEN PAAF.EFFECTIVE_START_DATE

AND PAAF.EFFECTIVE_END_DATE

AND PAV.PERSON_ID = PAPF.PERSON_ID;

 

Queries related to pay_batch_element_entry_api

Getting the Element Type Id and its Information

 

select * from PAY_ELEMENT_TYPES_F where element_name=‘Overtime OTM Special Inputs’

 

SELECT *

FROM PAY_ELEMENT_TYPES_F

WHERE element_name =‘Holiday Pay Special Inputs’;

SELECT *

FROM PAY_ELEMENT_TYPES_F

WHERE element_name =‘Production Bonus’;

 

Getting the info of Batches

SELECT PBH.BATCH_NAME FROM PAY_BATCH_HEADERS PBH, PAY_BATCH_LINES PBL WHERE PBH.BATCH_ID=PBL.BATCH_ID;

SELECT PBL.* ,PBH.* FROM PAY_BATCH_HEADERS PBH, PAY_BATCH_LINES PBL WHERE PBH.BATCH_ID=PBL.BATCH_ID AND BATCH_NAME LIKE‘MY%TEST%’;

Getting the Employee details from custom table

SELECT PAPF.PERSON_ID,

PAPF.EFFECTIVE_START_DATE,

PAPF.EFFECTIVE_END_DATE,

PAPF.LAST_NAME,

PAAF.ASSIGNMENT_ID

FROM PER_ALL_PEOPLE_F PAPF,

PER_ALL_ASSIGNMENTS_F PAAF

WHERE PAPF.EMPLOYEE_NUMBER =‘2108’

AND PAPF.CURRENT_EMPLOYEE_FLAG =‘Y’

AND PAPF.EMPLOYEE_NUMBER ISNOTNULL

AND PAAF.PERSON_ID = PAPF.PERSON_ID

ANDSYSDATEBETWEEN PAAF.EFFECTIVE_START_DATE

AND PAAF.EFFECTIVE_END_DATE;

 

To Check Whether the data is Inserted or not

SELECT PBL.*, PBH.*

FROM PAY_BATCH_HEADERS PBH, PAY_BATCH_LINES PBL

WHERE PBH.BATCH_ID = PBL.BATCH_ID AND BATCH_NAME LIKE‘%WORKSHOP%’;

 

 

SELECT PBL.*, PBH.*

FROM PAY_BATCH_HEADERS PBH, PAY_BATCH_LINES PBL

WHERE PBH.BATCH_ID = PBL.BATCH_ID AND BATCH_NAME LIKE‘%HOLI%’;

 

 

SELECT PBL.*, PBH.*

FROM PAY_BATCH_HEADERS PBH, PAY_BATCH_LINES PBL

WHERE PBH.BATCH_ID = PBL.BATCH_ID AND BATCH_NAME LIKE‘%MOISS_OVERTIME%’;

 

 

 

Insert Statements

 

INSERTINTO xxtest_emp_attendance (employee_id,

att_date,

holiday_overtime,

created_by,

creation_date,

day_type)

VALUES(113,

TO_DATE(’01/02/2012′,‘mm/dd/yyyy’),

5,

1310,

TO_DATE(’01/19/2014′,‘mm/dd/yyyy’),

‘WD’);

 

 

INSERTINTO xxtest_emp_attendance (employee_id,

att_date,

workday_overtime,

created_by,

creation_date,

day_type)

VALUES(113,

TO_DATE(’01/03/2012′,‘mm/dd/yyyy’),

5,

1310,

TO_DATE(’01/19/2014′,‘mm/dd/yyyy’),

‘WD’);

 

 

 

INSERTINTO xxtest_emp_attendance (employee_id,

att_date,

workshop_bonus,

created_by,

creation_date,

day_type)

VALUES(113,

TO_DATE(’01/04/2012′,‘mm/dd/yyyy’),

5,

1310,

TO_DATE(’01/19/2014′,‘mm/dd/yyyy’),

‘WD’);

 

 

 

INSERTINTO xxtest_emp_attendance (employee_id,

att_date,

rig_bonus,

created_by,

creation_date,

day_type)

VALUES(113,

TO_DATE(’01/05/2012′,‘mm/dd/yyyy’),

4,

1310,

TO_DATE(’01/19/2014′,‘mm/dd/yyyy’),

‘WD’);

 

 

Testing Queries

 

select * from xxtest_emp_attendance

General Util Queries from Oracle Apps.

 

select textActual_Query from user_views where view_name=‘PAY_ELEMENT_TYPES_DENORM_V’;

 

Program using API’s

 

Spec of API

 

CREATEORREPLACEPACKAGEMOISS_PAYROLL_INT_API

IS

PROCEDURE MOISS_ABSENCE_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

EMP_NO NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

);

 

 

PROCEDURE MOISS_BONUS_OVERTIME_ALL_E(ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

);

 

PROCEDURE MOISS_WORKDAY_OVERTIME_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

);

 

 

PROCEDURE MOISS_HOLIDAY_OVERTIME_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

);

 

PROCEDURE MOISS_WORKSHOP_BONUS_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

);

 

 

PROCEDURE MOISS_RIG_BONUS_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

);

END;

 

 

 

 

 

Body of API

 

 

/* Formatted on 1/22/2014 11:22:14 AM (QP5 v5.114.809.3010) */

CREATEORREPLACEPACKAGEBODYMOISS_PAYROLL_INT_API

AS

PROCEDURE MOISS_ABSENCE_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

EMP_NO NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

)

IS

CURSOR c

IS

SELECTDISTINCT EMPLOYEE_ID, ATT_DATE

FROMmoiss_emp_attendance

WHERE day_type =‘AB’

AND ATT_DATE BETWEENTRUNC(TO_DATE(START_DATE_V,

‘RRRR/MM/DD HH24:MI:SS’))

ANDTRUNC(TO_DATE( END_DATE_V,‘RRRR/MM/DD HH24:MI:SS’))

AND employee_id = EMP_NO;

 

–SELECT * FROM moiss_emp_attendance

 

x_absence_days number;

x_absence_hours number:=10;

— :=ABS_HOUR_V; If applicable we have to include

x_absence_attendance_type_id number;–ABSENCE ATTENDANCE TYPE ID

— := ATT_TYPE_ID_V; If applicable we have to include

x_absence_attendance_id number;

x_object_version_number number;

x_occurrence number;

x_dur_dys_less_warning boolean;

x_dur_hrs_less_warning boolean;

x_exceeds_pto_entit_warning boolean;

x_exceeds_run_total_warning boolean;

x_abs_overlap_warning boolean;

x_abs_day_after_warning boolean;

x_dur_overwritten_warning boolean;

x_con_count number;

BEGIN

retcode :=0;

errbuf :=‘Success’;

x_con_count :=0;

fnd_file.put_line (fnd_file.LOG,‘Start’);

 

DBMS_OUTPUT.PUT_LINE(

‘THE values ARE IS ‘ || START_DATE_V || END_DATE_V

);

 

— FND_FILE.PUT_LINE(fnd_file.LOG, )

 

–To keep check for the Concurrent —

x_con_count :=0;

–Counts the loop of number that is total number of loops that has to be going

 

 

fnd_global.apps_initialize (user_id =>fnd_global.user_id,

resp_id =>fnd_global.resp_id,

resp_appl_id =>fnd_global.resp_appl_id);

 

SELECT absence_attendance_type_id

INTO x_absence_attendance_type_id

FROM per_absence_attendance_types

WHERE business_group_id =fnd_global.per_business_group_id

ANDnameLIKE‘%Personal%’

ANDROWNUM=1;

 

 

FOR I IN C

LOOP

hr_person_absence_api.create_person_absence (

p_validate =>FALSE,

p_effective_date => I.ATT_DATE,

p_person_id => I.EMPLOYEE_ID,

p_business_group_id =>fnd_global.per_business_group_id

,

–101,

p_absence_attendance_type_id => x_absence_attendance_type_id,

–71, —VACATION LEAVE

p_date_projected_start => I.ATT_DATE,

p_time_projected_start =>NULL,

–If applicable we have to include

p_date_projected_end => I.ATT_DATE,

p_time_projected_end =>NULL,

–If applicable we have to include

p_date_start => I.ATT_DATE,

p_time_start =>NULL,

–If applicable we have to include ’18:00′,

p_date_end => I.ATT_DATE,

p_time_end =>NULL,

–If applicable we have to include ’20:00′,

— p_date_start => sysdate,

— p_date_end => sysdate+1,

p_absence_days => x_absence_days,

p_absence_hours => x_absence_hours,

p_absence_attendance_id => x_absence_attendance_id,

p_object_version_number => x_object_version_number,

p_occurrence => x_occurrence,

p_dur_dys_less_warning => x_dur_dys_less_warning,

p_dur_hrs_less_warning => x_dur_hrs_less_warning,

p_exceeds_pto_entit_warning => x_exceeds_pto_entit_warning,

p_exceeds_run_total_warning => x_exceeds_run_total_warning,

p_abs_overlap_warning => x_abs_overlap_warning,

p_abs_day_after_warning => x_abs_day_after_warning,

p_dur_overwritten_warning => x_dur_overwritten_warning

);

 

x_con_count :=1+ x_con_count;

fnd_file.put_line (

fnd_file.LOG,

x_con_count

|| ‘-‘

|| ‘Employee ID – ‘

|| I.EMPLOYEE_ID

|| ‘ – – Att Date- – – ‘

|| I.ATT_DATE

);

ENDLOOP;

 

IF x_con_count =0

THEN

fnd_file.put_line (fnd_file.LOG,‘No Data Found’);

ENDIF;

 

COMMIT;

EXCEPTION

WHENNO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘NO DATA FOUND’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

— WAR_V := errbuf;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

WHENOTHERS

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘Error in Payroll ABSENCE Integration’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

 

— WAR_V := errbuf;

END MOISS_ABSENCE_PAYROLL;

 

— ————————————————————————————————————————————————————————————————————————————————————

 

PROCEDURE MOISS_BONUS_OVERTIME_ALL_E(ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

)

IS

–ERRBUF VARCHAR2(100),

— RETCODE OUT NUMBER,

BEGIN

fnd_file.put_line (fnd_file.LOG,‘Procedure1 Started’);

MOISS_PAYROLL_INT_API.MOISS_WORKDAY_OVERTIME_PAYROLL(ERRBUF, RETCODE,START_DATE_V, END_DATE_V);

–fnd_file.put_line (fnd_file.LOG, ”);

MOISS_PAYROLL_INT_API.MOISS_HOLIDAY_OVERTIME_PAYROLL (ERRBUF, RETCODE, START_DATE_V, END_DATE_V);

MOISS_PAYROLL_INT_API.MOISS_WORKSHOP_BONUS_PAYROLL(ERRBUF, RETCODE, START_DATE_V, END_DATE_V );

MOISS_PAYROLL_INT_API.MOISS_RIG_BONUS_PAYROLL(ERRBUF, RETCODE, START_DATE_V, END_DATE_V);

fnd_file.put_line (fnd_file.LOG,‘Procedure1 Ended’);

EXCEPTION

whenothersthen

fnd_file.put_line (fnd_file.LOG,‘Exe Env’ || DBMS_UTILITY.format_error_backtrace);

END MOISS_BONUS_OVERTIME_ALL_E;

 

 

 

PROCEDURE MOISS_WORKDAY_OVERTIME_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

)

IS

v_batch_id varchar2(60);

x_batch_id number:=0;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

x_con_count number;

x_element_type_id number;

 

CURSOR c

IS

SELECTDISTINCT att_date,

WORKDAY_OVERTIME,

PAF.ASSIGNMENT_ID,

paf.assignment_number

FROMmoiss_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND WORKDAY_OVERTIME ISNOTNULL

AND ATT_DATE BETWEEN START_DATE_V AND END_DATE_V;

BEGIN

–retcode := 0;

–errbuf := ‘Success’;

x_con_count :=0;

fnd_file.put_line (fnd_file.LOG,‘Start’);

 

DBMS_OUTPUT.PUT_LINE(

‘THE values ARE IS ‘ || START_DATE_V || END_DATE_V

);

 

— FND_FILE.PUT_LINE(fnd_file.LOG, )

 

–To keep check for the Concurrent —

x_con_count :=0;

–Counts the loop of number that is total number of loops that has to be going

 

 

fnd_global.apps_initialize (user_id =>fnd_global.user_id,

resp_id =>fnd_global.resp_id,

resp_appl_id =>fnd_global.resp_appl_id);

 

 

SELECTTO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

 

SELECT ELEMENT_TYPE_ID

INTO x_element_type_id

FROM PAY_ELEMENT_TYPES_F

WHERE element_name LIKE‘%Overtime OTM Special Inputs%’

ANDROWNUM=1;

 

FOR i IN c

LOOP

pay_batch_element_entry_api.create_batch_header (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_name =>‘MOISS_OVERTIME’ || v_batch_id,

p_batch_status =>‘U’,— unprocessed

p_business_group_id =>fnd_global.per_business_group_id,

–202, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

p_comments =>NULL,

p_date_effective_changes =>NULL,

p_purge_after_transfer =>‘N’,

p_reject_if_future_changes =>‘Y’,

p_batch_id => x_batch_id,

p_object_version_number => g_ver_no,

p_reject_if_results_exists =>‘Y’,

p_purge_after_rollback =>‘N’,

p_batch_type =>NULL,

p_reject_entry_not_removed =>‘N’,

p_rollback_entry_updates =>‘N’

);

DBMS_OUTPUT.put_line(

‘v_batch_id’ || v_batch_id || x_batch_id || g_ver_no

);

pay_batch_element_entry_api.create_batch_line (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_id => x_batch_id,

p_assignment_id => i.ASSIGNMENT_ID,

p_assignment_number => i.assignment_number,

p_batch_sequence =>NULL,

p_effective_date =>SYSDATE,–l_effective_date

p_element_type_id => x_element_type_id,

–Overtime OTM Special Inputs

p_entry_type =>‘E’,

p_value_1 => i.WORKDAY_OVERTIME,

–holiday over time hrs–

p_value_2 =>’15’,–Assuming Value

–regular over time hrs– ,

p_batch_line_id => x_batch_line_id,

p_object_version_number => x_ver_no_1

);

 

x_con_count :=1+ x_con_count;

fnd_file.put_line (

fnd_file.LOG,

x_con_count || ‘-‘ || ‘Employee ID – ‘ || I.WORKDAY_OVERTIME ||

I.ASSIGNMENT_ID || I.assignment_number || ‘ – – Att Date- – – ‘

|| I.ATT_DATE

);

ENDLOOP;

 

IF x_con_count =0

THEN

fnd_file.put_line (fnd_file.LOG,‘No Data Found’);

ENDIF;

 

COMMIT;

EXCEPTION

WHENNO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

–retcode := -1;

–errbuf :=

— ‘NO DATA FOUND’

— || SUBSTR (SQLERRM, 1, 1000)

— || DBMS_UTILITY.format_error_backtrace;

— WAR_V := errbuf;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

WHENOTHERS

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

— retcode := -1;

— errbuf :=

— ‘Error in Payroll ABSENCE Integration’

— || SUBSTR (SQLERRM, 1, 1000)

— || DBMS_UTILITY.format_error_backtrace;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

 

— WAR_V := errbuf;

END;

 

————————————————————————————————————————————————————————————————————————————————————

 

 

 

PROCEDURE MOISS_HOLIDAY_OVERTIME_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

)

IS

v_batch_id VARCHAR2(60);

x_batch_id number;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

x_con_count number;

x_element_type_id number;

 

CURSOR c

IS

SELECTDISTINCT att_date,

HOLIDAY_OVERTIME,

PAF.ASSIGNMENT_ID,

paf.assignment_number

FROMmoiss_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND MEA.HOLIDAY_OVERTIME ISNOTNULL

AND ATT_DATE BETWEEN START_DATE_V AND END_DATE_V;

BEGIN

retcode :=0;

errbuf :=‘Success’;

x_con_count :=0;

fnd_file.put_line (fnd_file.LOG,‘Start’);

 

DBMS_OUTPUT.PUT_LINE(

‘THE values ARE IS ‘ || START_DATE_V || END_DATE_V

);

 

— FND_FILE.PUT_LINE(fnd_file.LOG, )

 

–To keep check for the Concurrent —

x_con_count :=0;

–Counts the loop of number that is total number of loops that has to be going

 

 

fnd_global.apps_initialize (user_id =>fnd_global.user_id,

resp_id =>fnd_global.resp_id,

resp_appl_id =>fnd_global.resp_appl_id);

 

 

SELECTTO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

 

SELECT ELEMENT_TYPE_ID

INTO x_element_type_id

FROM PAY_ELEMENT_TYPES_F

WHERE element_name LIKE‘%OTL Holiday Pay%’ANDROWNUM=1;

 

 

 

FOR i IN c

LOOP

pay_batch_element_entry_api.create_batch_header (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_name =>‘M_HOLI_OVERTIME’ || v_batch_id,

p_batch_status =>‘U’,— unprocessed

p_business_group_id =>fnd_global.per_business_group_id,

–202, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

p_comments =>NULL,

p_date_effective_changes =>NULL,

p_purge_after_transfer =>‘N’,

p_reject_if_future_changes =>‘Y’,

p_batch_id => x_batch_id,

p_object_version_number => g_ver_no,

p_reject_if_results_exists =>‘Y’,

p_purge_after_rollback =>‘N’,

p_batch_type =>NULL,

p_reject_entry_not_removed =>‘N’,

p_rollback_entry_updates =>‘N’

);

DBMS_OUTPUT.put_line(

‘v_batch_id’ || v_batch_id || x_batch_id || g_ver_no

);

pay_batch_element_entry_api.create_batch_line (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_id => x_batch_id,

p_assignment_id => i.ASSIGNMENT_ID,

p_assignment_number => i.assignment_number,

p_batch_sequence =>NULL,

p_effective_date =>SYSDATE,–l_effective_date

p_element_type_id => x_element_type_id,

–Earnings paid for Holiday-straight time)

p_entry_type =>‘E’,

p_value_1 => i.HOLIDAY_OVERTIME,

–holiday over time hrs–

p_value_2 =>15,

–Assuming Value (Values to be noted……)

–regular over time hrs– ,

p_batch_line_id => x_batch_line_id,

p_object_version_number => x_ver_no_1

);

x_con_count :=1+ x_con_count;

fnd_file.put_line (

fnd_file.LOG,

x_con_count || ‘-‘ || ‘Employee ID – ‘ || I.HOLIDAY_OVERTIME ||

I.ASSIGNMENT_ID || I.assignment_number || ‘ – – Att Date- – – ‘

|| I.ATT_DATE

);

ENDLOOP;

 

IF x_con_count =0

THEN

fnd_file.put_line (fnd_file.LOG,‘No Data Found’);

ENDIF;

 

COMMIT;

EXCEPTION

WHENNO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘NO DATA FOUND’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

— WAR_V := errbuf;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘Error in Payroll ABSENCE Integration’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

 

— WAR_V := errbuf;

END;

 

————————————————————————————————————————————————————————————————————————————————————

 

PROCEDURE MOISS_WORKSHOP_BONUS_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

)

IS

v_batch_id VARCHAR2(60);

x_batch_id number;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

x_con_count number;

x_element_type_id number;

 

CURSOR c

IS

SELECTDISTINCT att_date,

WORKSHOP_BONUS,

PAF.ASSIGNMENT_ID,

paf.assignment_number

FROMmoiss_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND MEA.WORKSHOP_BONUS ISNOTNULL

AND ATT_DATE BETWEEN START_DATE_V AND END_DATE_V;

BEGIN

retcode :=0;

errbuf :=‘Success’;

x_con_count :=0;

fnd_file.put_line (fnd_file.LOG,‘Start’);

 

DBMS_OUTPUT.PUT_LINE(

‘THE values ARE IS ‘ || START_DATE_V || END_DATE_V

);

 

— FND_FILE.PUT_LINE(fnd_file.LOG, )

 

–To keep check for the Concurrent —

x_con_count :=0;

–Counts the loop of number that is total number of loops that has to be going

 

fnd_global.apps_initialize (user_id =>fnd_global.user_id,

resp_id =>fnd_global.resp_id,

resp_appl_id =>fnd_global.resp_appl_id);

SELECT TO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

 

SELECT ELEMENT_TYPE_ID

INTO x_element_type_id

FROM PAY_ELEMENT_TYPES_F

WHERE element_name LIKE‘%Production Bonus%’ANDROWNUM=1;

FOR i IN c

LOOP

pay_batch_element_entry_api.create_batch_header (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_name =>‘WORKSHOP_BONUS’ || v_batch_id,

p_batch_status =>‘U’,— unprocessed

p_business_group_id =>fnd_global.per_business_group_id,

–202, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

p_comments =>NULL,

p_date_effective_changes =>NULL,

p_purge_after_transfer =>‘N’,

p_reject_if_future_changes =>‘Y’,

p_batch_id => x_batch_id,

p_object_version_number => g_ver_no,

p_reject_if_results_exists =>‘Y’,

p_purge_after_rollback =>‘N’,

p_batch_type =>NULL,

p_reject_entry_not_removed =>‘N’,

p_rollback_entry_updates =>‘N’

);

DBMS_OUTPUT.put_line(

‘v_batch_id’ || v_batch_id || x_batch_id || g_ver_no

);

pay_batch_element_entry_api.create_batch_line (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_id => x_batch_id,

p_assignment_id => i.ASSIGNMENT_ID,

p_assignment_number => i.assignment_number,

p_batch_sequence =>NULL,

p_effective_date =>SYSDATE,–l_effective_date

p_element_type_id => x_element_type_id,–Production Bonus

p_entry_type =>‘E’,

p_value_1 => i.WORKSHOP_BONUS,

–holiday over time hrs–

p_value_2 =>15,

–Assuming Value (Values to be noted……)

–regular over time hrs– ,

p_batch_line_id => x_batch_line_id,

p_object_version_number => x_ver_no_1

);

x_con_count :=1+ x_con_count;

fnd_file.put_line (

fnd_file.LOG,

x_con_count || ‘-‘ || ‘Employee ID – ‘ || I.WORKSHOP_BONUS ||

I.ASSIGNMENT_ID || I.assignment_number || ‘ – – Att Date- – – ‘

|| I.ATT_DATE

);

ENDLOOP;

 

IF x_con_count =0

THEN

fnd_file.put_line (fnd_file.LOG,‘No Data Found’);

ENDIF;

 

COMMIT;

EXCEPTION

WHENNO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘NO DATA FOUND’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

— WAR_V := errbuf;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

WHENOTHERS

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘Error in Payroll ABSENCE Integration’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

 

— WAR_V := errbuf;

END;

 

PROCEDURE MOISS_RIG_BONUS_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

)

IS

v_batch_id VARCHAR2(60);

x_batch_id number;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

x_con_count number;

x_element_type_id number;

 

CURSOR c

IS

SELECTDISTINCT att_date,

RIG_BONUS,

PAF.ASSIGNMENT_ID,

paf.assignment_number

FROMmoiss_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND MEA.WORKSHOP_BONUS ISNOTNULL

AND ATT_DATE BETWEEN START_DATE_V AND END_DATE_V;

BEGIN

retcode :=0;

errbuf :=‘Success’;

x_con_count :=0;

fnd_file.put_line (fnd_file.LOG,‘Start’);

 

DBMS_OUTPUT.PUT_LINE(

‘THE values ARE IS ‘ || START_DATE_V || END_DATE_V

);

 

— FND_FILE.PUT_LINE(fnd_file.LOG, )

 

–To keep check for the Concurrent —

x_con_count :=0;

–Counts the loop of number that is total number of loops that has to be going

 

fnd_global.apps_initialize (user_id =>fnd_global.user_id,

resp_id =>fnd_global.resp_id,

resp_appl_id =>fnd_global.resp_appl_id);

SELECT TO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

SELECT ELEMENT_TYPE_ID

INTO x_element_type_id

FROM PAY_ELEMENT_TYPES_F

WHERE element_name LIKE‘%Production Bonus%’AND ROWNUM=1;

FOR i IN c

LOOP

pay_batch_element_entry_api.create_batch_header (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_name =>‘RIG_BONUS’ || v_batch_id,

p_batch_status =>‘U’,— unprocessed

p_business_group_id =>fnd_global.per_business_group_id,

–202, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

p_comments =>NULL,

p_date_effective_changes =>NULL,

p_purge_after_transfer =>‘N’,

p_reject_if_future_changes =>‘Y’,

p_batch_id => x_batch_id,

p_object_version_number => g_ver_no,

p_reject_if_results_exists =>‘Y’,

p_purge_after_rollback =>‘N’,

p_batch_type =>NULL,

p_reject_entry_not_removed =>‘N’,

p_rollback_entry_updates =>‘N’

);

DBMS_OUTPUT.put_line(

‘v_batch_id’ || v_batch_id || x_batch_id || g_ver_no

);

pay_batch_element_entry_api.create_batch_line (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_id => x_batch_id,

p_assignment_id => i.ASSIGNMENT_ID,

p_assignment_number => i.assignment_number,

p_batch_sequence =>NULL,

p_effective_date =>SYSDATE,–l_effective_date

p_element_type_id => x_element_type_id,

–Production Bonus (Has to be changed for Rig Bonus)

p_entry_type =>‘E’,

p_value_1 => i.RIG_BONUS,

–holiday over time hrs–

p_value_2 =>15,

–Assuming Value (Values to be noted……)

–regular over time hrs– ,

p_batch_line_id => x_batch_line_id,

p_object_version_number => x_ver_no_1

);

x_con_count :=1+ x_con_count;

fnd_file.put_line (

fnd_file.LOG,

x_con_count || ‘-‘ || ‘Employee ID – ‘ || I.RIG_BONUS || I.

ASSIGNMENT_ID || I.assignment_number || ‘ – – Att Date- – – ‘ || I.

ATT_DATE );

END LOOP;

IF x_con_count =0

THEN

fnd_file.put_line (fnd_file.LOG,‘No Data Found’);

ENDIF;

COMMIT;

EXCEPTION

WHENNO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘NO DATA FOUND’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

— WAR_V := errbuf;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

WHENOTHERS

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘Error in Payroll ABSENCE Integration’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

— WAR_V := errbuf;

END;

END MOISS_PAYROLL_INT_API;

Program Body for Testing or Sample data Test

 

CREATEORREPLACEPACKAGEBODYMOISS_PAYROLL_INT_API

AS

PROCEDURE MOISS_ABSENCE_PAYROLL (BUISNESS_G_V NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2,

WAR_V OUTVARCHAR2)

IS

CURSOR c

IS

SELECTDISTINCT EMPLOYEE_ID, ATT_DATE

FROMmoiss_emp_attendance

WHERE day_type =‘WO’

AND ATT_DATE BETWEENTO_DATE(START_DATE_V,‘MM/DD/YYYY’)–TO_DATE (’10/19/2013′, ‘MM/DD/YYYY’)

ANDTO_DATE(END_DATE_V,‘MM/DD/YYYY’)

— TO_DATE (’10/20/2013′, ‘MM/DD/YYYY’)

AND employee_id =30;

 

–SELECT * FROM moiss_emp_attendance

— x_START_DATE_V date=’10/18/2013′;

x_absence_days number;

x_absence_hours number:=10;

— :=ABS_HOUR_V; If applicable we have to include

X_absence_attendance_type_id number:=7;

— := ATT_TYPE_ID_V; If applicable we have to include

x_absence_attendance_id number;

x_object_version_number number;

x_occurrence number;

x_dur_dys_less_warning boolean;

x_dur_hrs_less_warning boolean;

x_exceeds_pto_entit_warning boolean;

x_exceeds_run_total_warning boolean;

x_abs_overlap_warning boolean;

x_abs_day_after_warning boolean;

x_dur_overwritten_warning boolean;

BEGIN

 

DBMS_OUTPUT.PUT_LINE(‘THE values ARE IS ‘||BUISNESS_G_V||START_DATE_V||END_DATE_V);

FOR I IN C

LOOP

 

hr_person_absence_api.create_person_absence (

p_validate =>FALSE,

p_effective_date => I.ATT_DATE,

p_person_id => I.EMPLOYEE_ID,

p_business_group_id => BUISNESS_G_V,

–101,

p_absence_attendance_type_id => X_absence_attendance_type_id,

–71, —VACATION LEAVE

p_date_projected_start => I.ATT_DATE,

p_time_projected_start =>NULL,

–If applicable we have to include

p_date_projected_end => I.ATT_DATE,

p_time_projected_end =>NULL,

–If applicable we have to include

p_date_start => I.ATT_DATE,

p_time_start =>NULL,

–If applicable we have to include ’18:00′,

p_date_end => I.ATT_DATE,

p_time_end =>NULL,

–If applicable we have to include ’20:00′,

— p_date_start => sysdate,

— p_date_end => sysdate+1,

p_absence_days => x_absence_days,

p_absence_hours => x_absence_hours,

p_absence_attendance_id => x_absence_attendance_id,

p_object_version_number => x_object_version_number,

p_occurrence => x_occurrence,

p_dur_dys_less_warning => x_dur_dys_less_warning,

p_dur_hrs_less_warning => x_dur_hrs_less_warning,

p_exceeds_pto_entit_warning => x_exceeds_pto_entit_warning,

p_exceeds_run_total_warning => x_exceeds_run_total_warning,

p_abs_overlap_warning => x_abs_overlap_warning,

p_abs_day_after_warning => x_abs_day_after_warning,

p_dur_overwritten_warning => x_dur_overwritten_warning

);

ENDLOOP;

 

COMMIT;

EXCEPTION

WHENOTHERS

THEN

WAR_V :=‘ERROR OCCURED’||sqlERRM;

END MOISS_ABSENCE_PAYROLL;

————————————————————————————————————————————————————————————————————————————————————

 

/* Formatted on 1/16/2014 6:13:49 PM (QP5 v5.114.809.3010) */

PROCEDURE MOISS_WORKDAY_OVERTIME_PAYROLL (BUISNESS_G_V NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2,

WAR_V OUTVARCHAR2)

IS

v_batch_id varchar2(60);

x_batch_id number:=0;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

 

CURSOR c

IS

SELECT DISTINCT

WORKDAY_OVERTIME, PAF.ASSIGNMENT_ID, paf.assignment_number

FROM xxtest_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND WORKDAY_OVERTIME ISNOTNULL;

BEGIN

SELECT TO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

FOR i IN c

LOOP

pay_batch_element_entry_api.create_batch_header (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_name =>‘MOISS_OVERTIME’ || v_batch_id,

p_batch_status =>‘U’,— unprocessed

p_business_group_id =>202,

–202, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

p_comments =>NULL,

p_date_effective_changes =>NULL,

p_purge_after_transfer =>‘N’,

p_reject_if_future_changes =>‘Y’,

p_batch_id => x_batch_id,

p_object_version_number => g_ver_no,

p_reject_if_results_exists =>‘Y’,

p_purge_after_rollback =>‘N’,

p_batch_type =>NULL,

p_reject_entry_not_removed =>‘N’,

p_rollback_entry_updates =>‘N’

);

DBMS_OUTPUT.put_line(

‘v_batch_id’ || v_batch_id || x_batch_id || g_ver_no

);

pay_batch_element_entry_api.create_batch_line (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_id => x_batch_id,

p_assignment_id => i.ASSIGNMENT_ID,

p_assignment_number => i.assignment_number,

p_batch_sequence =>NULL,

p_effective_date =>SYSDATE,–l_effective_date

p_element_type_id =>50944,–Overtime OTM Special Inputs

p_entry_type =>‘E’,

p_value_1 => i.WORKDAY_OVERTIME,

–holiday over time hrs–

p_value_2 =>’15’,–Assuming Value

–regular over time hrs– ,

p_batch_line_id => x_batch_line_id,

p_object_version_number => x_ver_no_1

);

END LOOP;

COMMIT;

EXCEPTION

WHENOTHERS

THEN

WAR_V :=‘ERROR OCCURED’||sqlERRM;

dbms_output.put_line(dbms_utility.format_error_backtrace);

END;

————————————————————————————————————————————————————————————————————————————————————

PROCEDURE MOISS_HOLIDAY_OVERTIME_PAYROLL (BUISNESS_G_V NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2,

WAR_V OUTVARCHAR2)

IS

v_batch_id VARCHAR2(60);

x_batch_id number;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

CURSOR c

IS

SELECT DISTINCT

HOLIDAY_OVERTIME, PAF.ASSIGNMENT_ID, paf.assignment_number

FROM xxtest_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND MEA.HOLIDAY_OVERTIME ISNOTNULL;

 

BEGIN

SELECT TO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

FOR i IN c

LOOP

pay_batch_element_entry_api.create_batch_header (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_name =>‘M_HOLI_OVERTIME’|| v_batch_id,

p_batch_status =>‘U’,— unprocessed

p_business_group_id =>202,–fnd_global.per_business_group_id,

–202, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

p_comments =>NULL,

p_date_effective_changes =>NULL,

p_purge_after_transfer =>‘N’,

p_reject_if_future_changes =>‘Y’,

p_batch_id => x_batch_id,

p_object_version_number => g_ver_no,

p_reject_if_results_exists =>‘Y’,

p_purge_after_rollback =>‘N’,

p_batch_type =>NULL,

p_reject_entry_not_removed =>‘N’,

p_rollback_entry_updates =>‘N’

);

DBMS_OUTPUT.put_line(

‘v_batch_id’ || v_batch_id || x_batch_id || g_ver_no

);

pay_batch_element_entry_api.create_batch_line (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_id => x_batch_id,

p_assignment_id => i.ASSIGNMENT_ID,

p_assignment_number => i.assignment_number,

p_batch_sequence =>NULL,

p_effective_date =>SYSDATE,–l_effective_date

p_element_type_id =>50749,–Earnings paid for Holiday-straight time)

p_entry_type =>‘E’,

p_value_1 => i.HOLIDAY_OVERTIME,

–holiday over time hrs–

p_value_2 =>15,–Assuming Value (Values to be noted……)

–regular over time hrs– ,

p_batch_line_id => x_batch_line_id,

p_object_version_number => x_ver_no_1

);

ENDLOOP;

COMMIT;

 

EXCEPTION

WHEN OTHERS

THEN

dbms_output.put_line(dbms_utility.format_error_backtrace);

WAR_V :=‘ERROR OCCURED’||sqlERRM;

END;

————————————————————————————————————————————————————————————————————————————————————

PROCEDURE MOISS_WORKSHOP_BONUS_PAYROLL (BUISNESS_G_V NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2,

WAR_V OUTVARCHAR2)

IS

v_batch_id VARCHAR2(60);

x_batch_id number;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

 

CURSOR c

IS

 

SELECT DISTINCT

WORKSHOP_BONUS, PAF.ASSIGNMENT_ID, paf.assignment_number

FROM xxtest_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND MEA.WORKSHOP_BONUS ISNOTNULL;

 

BEGIN

 

SELECTTO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

 

FOR i IN c

LOOP

pay_batch_element_entry_api.create_batch_header (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_name =>‘WORKSHOP_BONUS’ || v_batch_id,

p_batch_status =>‘U’,— unprocessed

p_business_group_id =>202,–fnd_global.per_business_group_id,

–202, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

p_comments =>NULL,

p_date_effective_changes =>NULL,

p_purge_after_transfer =>‘N’,

p_reject_if_future_changes =>‘Y’,

p_batch_id => x_batch_id,

p_object_version_number => g_ver_no,

p_reject_if_results_exists =>‘Y’,

p_purge_after_rollback =>‘N’,

p_batch_type =>NULL,

p_reject_entry_not_removed =>‘N’,

p_rollback_entry_updates =>‘N’

);

DBMS_OUTPUT.put_line(

‘v_batch_id’ || v_batch_id || x_batch_id || g_ver_no

);

pay_batch_element_entry_api.create_batch_line (

p_validate =>FALSE,

p_session_date =>SYSDATE,

p_batch_id => x_batch_id,

p_assignment_id => i.ASSIGNMENT_ID,

p_assignment_number => i.assignment_number,

p_batch_sequence =>NULL,

p_effective_date =>SYSDATE,–l_effective_date

p_element_type_id =>51661,–Production Bonus

p_entry_type =>‘E’,

p_value_1 => i.WORKSHOP_BONUS,

–holiday over time hrs–

p_value_2 =>15,–Assuming Value (Values to be noted……)

–regular over time hrs– ,

p_batch_line_id => x_batch_line_id,

p_object_version_number => x_ver_no_1

);

DBMS_OUTPUT.put_line(

‘v_batch_id’ || x_batch_line_id || x_ver_no_1 );

ENDLOOP;

 

COMMIT;

 

EXCEPTION

WHENOTHERS

THEN

dbms_output.put_line(dbms_utility.format_error_backtrace);

WAR_V :=‘ERROR OCCURED’||sqlERRM;

END;

END MOISS_PAYROLL_INT_API;

 

Program Execution

 

DECLARE

VAR_V VARCHAR2(500);

begin

–MOISS_PAYROLL_INT_API.MOISS_ABSENCE_PAYROLL(202, TO_DATE (’01/02/2014′, ‘mm/dd/yyyy’), TO_DATE (’01/02/2014′, ‘mm/dd/yyyy’), VAR_V )

–MOISS_PAYROLL_INT_API.MOISS_WORKDAY_OVERTIME_PAYROLL(202, TO_DATE (’01/02/2012′, ‘mm/dd/yyyy’), TO_DATE (’01/02/2012′, ‘mm/dd/yyyy’), VAR_V );

MOISS_PAYROLL_INT_API.MOISS_BONUS_OVERTIME_ALL_E(’01-MAR-2012′,’31-MAR-2012′);

MOISS_PAYROLL_INT_API.MOISS_ABSENCE_PAYROLL(29,’01-MAR-2012′,’31-MAR-2012′);

–MOISS_PAYROLL_INT_API.MOISS_WORKSHOP_BONUS_PAYROLL(202, TO_DATE (’01/02/2012′, ‘mm/dd/yyyy’), TO_DATE (’01/02/2012′, ‘mm/dd/yyyy’), VAR_V );

dbms_output.put_line(‘We have this Error’||var_v);

end;

 

Note:

To run as PLSQL procedure it there is no need for ErrBuf and retcode elements. But they are mandatory while using in the oracle apps.

 

Now we have to deploy this procedure. Just get the last post which is deployment.

Registering a PLSQL stored procedure as concurrent program

To register a PLSQL procedure as concurrent program we have four steps

Step : 1 → Create Executable

Step : 2 → Create concurrent program and link to Executable

Step : 3 → Create Parameters and link Value Sets

Step : 4 → Assign the registered Concurrent Program to a request group

Step : 1 → Create Executable

Concurrent is a program that user will invoke directly from the oracle apps. Imagine that each time to run a package or procedure a customer has to login to his database and running and for that we have to educate the client. Instead of this, registering a concurrent is best method to run the API because user will be assigned to responsibility and then he can run the program. It is a way to deliver to customer.

To register a program as concurrent open Oracle apps and go to the following Navigation

The Navigation is

Login into Oracle Applications –> Go to Application Developer Responsibility –> Concurrent –> Executable

This Executable is for running a program.

1

FIELDS:

  • Executable: This is User Understandable Name
  • Short Name: This is Unique and for system reference
  • Application: Under which application you want to register this Conc. Program
  • Description: Description
  • Execution Method: Based on this field, your file has to be placed in respective directory or database.
  • Execution File Name: This is the actual Report file name. If you register a PL/SQL Procedure in a package you have to give the packagename.procedure. You don’t need to specify any parameters in procedure here.

Action: Save

Step : 2 → Create concurrent program and link to Executable

The Navigation is

Application Developer –> Concurrent –> Program

2

FIELDS:

  • Program: User Understandable Program Name
  • Short Name: This should be unique name and for system reference
  • Application: Enter the application under which you want to register this conc.prog
  • Executable Name: Enter the User Understandable Executable Name
  • Method: This will be populated automatically from Executable Definition
  • Output Format: Select the format of the output you want
  • Output Style: Select A4 to print on A4 Paper
  • Printer: You can default any printer or you can enter while submitting concurrent program. 

Make sure you link the Executable’s short name and Concurrent program’s Executable name.

Step : 3 → Create Parameters and link Value Sets

Create Parameters and it is Better to Create Value Set

To create parameter the Navigation is

Application Developer –> Application –> Validation → Set

3

There are many types to get List of Values. So I go with the table.

To get data from table,select validation type as table and click the Edit Information Button Enter the Value and ID to be passed.

4

Linking value set to parameter and the Navigation is

Application Developer –> Concurrent –> Program

Enter the following here. And click on parameters to define parameters and in parameter form you can also enter default type

5

The above is Example for creating a parameter.

Just observe the below screenshot. Here There is a selection in Enabled. If it is

Checked then the parameter gets enabled and When the Display button is checked we can see the display in Oracle apps concurrent page.We can also give the Default type.

6

The below is another sample of a parameter. It is better to choose FND_DATE_STANDARD and we have to check for the format. This format is (DD-MON-YYYY)

7

To Enter a Date and if we are not in need of Value Set

Number : Click on LOV in Value Set and search by typing %Num%

Char : Click on LOV in Value Set and search by typing %CHAR%

DateTime : Click on LOV in Value Set and search by typing %Stan%Date%

8

Query to Check for Concurrent and the info about the Log file

select cp.plsql_dir, cp.plsql_out, cp.plsql_log

from fnd_concurrent_requests cr, fnd_concurrent_processes cp

where cp.concurrent_process_id = cr.controlling_manager — and cr.request_id = <running request id>

Till now we have created a Executable and Concurrent Program. Now our Objective is to attach inside a request group to run the concurrent.

Step : 4 → Assign the registered Concurrent Program to a request group

Now select a responsibility for which the concurrent has to run and that responsibility has the Request set. We have to make sure that we attach the concurrent program to the responsibility. I have chosen the Responsibility as Global HRMS Manager. Now when you query it you will get the Request group as shown in the below screenshot.

The Navigation is

System Administrator –> Security –> Responsibility Define

9

Now you have to attach this to a Request Group and make it run.

Get the Request group and attach you concurrent here.

The Navigation is

System Administrator –> Security –> Responsibility Request

10

Now go to your Responsibility to run concurrent program.

Note:

When you are using your custom application it may not be in Data group(standard). You may not get it listed while searching in the submit request.

So Just go to the Below Navigation and check the responsibility Data group. In the below screen shot it is above the marking. The mark represents the Request group and top of it si Data group. So if your application is in Data group then you can see in Request Group.

Refer till two below screen shots.

11

To add to Request group go to the Data group and Query the attached name. In my case it is standard.

12

Note over

13

Submit a new Request and run your concurrent. Here you may be prompted the parameter. Just Remember we had three parameters and we disabled the one so now we got two left.

14

15

After submission we get the Request Id for our reference.

16

Now we have our output. We can view the output but ours is a procedure so there will be nothing. But we can view the log whether this program worked or not.

 

Thats it your API is deployed. Enjoy it.