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;

 

 

Firefox OS: Devices and Dark Matter

Originally posted on dietrich.blog:

UPDATE: Scroll down for update on May 26, 2013.

Since beginning work on the Firefox OS project, the number one question I’m asked is “Does it run on my phone?”. Sadly, the answer for almost everyone is “no”. The question itself is interesting though, and shows how people – even geeky technical people – don’t have a good understanding of how mobile devices work, nor the whole business and technical ecosystem that brings these things into the hands of consumers (hm, maybe that’ll be my next blog post). Porting an operating system to a device is tricky work in the best of circumstances and when done without the direct assistance of the various business entities involved in the stack for any single device (OEM, chipset manufacturer, original OS vendor), involves a lot of, well, fiddling around. The kind of fiddling around that voids warranties and turns $600 hardware into a…

View original 536 more words

Oracle: Description for GL_CODE_COMBINATIONS Segments

Originally posted on oraclecafe:

In Oracle E-Business 11.10.5, the GL_CODE_COMBINATIONS segments details are stored in FND_FLEX_VALUES_VL. For each segment we have to map the segment value with the FLEX_VALUE column of the above table. Suppose if there is a code combination id of 8 segments then our query would look like
1
SELECT gcc.segment1
|| ‘-’
|| gcc.segment2
|| ‘-’
|| gcc.segment3
|| ‘-’
|| gcc.segment4
|| ‘-’
|| gcc.segment5
|| ‘-’
|| gcc.segment6
|| ‘-’
|| gcc.segment7
|| ‘-’
|| gcc.segment8 account,
a1.description
|| ‘-’
|| a2.description
|| ‘-’
|| a3.description
|| ‘-’
|| a4.description
|| ‘-’
|| a5.description
|| ‘-’
|| a6.description
|| ‘-’
|| a7.description
|| ‘-’
|| a8.description
description
FROM fnd_flex_values_vl a1,
fnd_flex_values_vl a2,
fnd_flex_values_vl a3,
fnd_flex_values_vl a4,
fnd_flex_values_vl a5,
fnd_flex_values_vl a6,
fnd_flex_values_vl a7,
fnd_flex_values_vl a8,
gl_code_combinations gcc
WHERE a1.flex_value = gcc.segment1
AND a2.flex_value = gcc.segment2
AND a3.flex_value = gcc.segment3
AND a4.flex_value = gcc.segment4
AND a5.flex_value = gcc.segment5
AND a6.flex_value =…

View original 145 more words

Error on report Builder (Unable to Connect to the specifed Database, ORA-12541: TNS: no listener)

Unable to Connect to the specifed Database
ORA-12541: TNS: no listener

1

You may get this problem due to the TNS listener not entered or a wrong user name, password, database or Incorrect Database name

For TNS names Entry
Get the TNS entry in the tnsnames file.

Go to the location C:\DevSuiteHome_1\NETWORK\ADMIN\tnsnames.ora (check your location where DevSuiteHome is installed)

If there is not a file named tnsnames.ora create a file and that should be inside DevSuiteHome.

This DevSuiteHome that where your Oracle k2k gets installed .

You can refer to the sample file(tnsnames.ora ) in sample folder for reference.

Below is the format where

TEST → Database

Hostname → Hostname has to be given(IP address or Host address)

1523 → Port number

TEST → SID

 

TEST=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=Hostname)
(PORT=1523)
)
(CONNECT_DATA=
(SID=TEST)
)
)

 

Most Annoying things !

Most Annoying things !.

FreeTamilEbooks.com – Let us free the Tamil Ebooks

Originally posted on The Roads I Go:

Ebooks Readers:

There are many handheld devices to read ebooks now. Kindle, Nook, Android Tablets are the big players in this domain. The cost of these devices are getting low as around 4000-6000 INR. Many people are buying these devices now.

Ebooks in English:

There are tons of ebooks in english available now. Most of the latest ebooks are released in the following formats. PDF, EPUB, MOBI, AZW3. So that we can read them in these devices.

Ebooks in Tamil:

In Tamil, latest books are not available as ebooks. ProjectMadurai.com team is doing a great job of providing ebooks in Tamil, which are available in Public Domain. But these ebooks are very very old.

We can not get the latest content.

Currently, When any Tamil reader buys these ebook readers, he can not get any ebooks in tamil for free.

How to get latest content for Tamil?

Nowadays, there are…

View original 1,000 more words

Chennai Ruby Meet 15th Feb

Hello everyone,

The Chennai.rb community will be having its February meetup on the 15th
February (Saturday) (2 – 5 PM).

Agenda:

1. Building Rich Rails apps with EmberJS by Vysakh Sreenivasan (@vysakh0)
2. Introduction to AngularJS by Santhosh Kumar (

https://github.com/santhotech)

RSVP: http://www.meetup.com/chennai-ruby/events/164707822/

Venue:

Techaffinity Global Private Ltd.
SP Infocity Block A, 8th Floor, Module 4B, 40, MGR Salai, Kandanchavadi,
Chennai 600096

Landmark :
It is the road opposite to Hi Style near Perungudi, behind Lifeline
hospital, next to RMZ Millenia Tech Park.

Security Pass:
To get a visitor’s pass please inform the security at the security
office that you are visiting Techaffinity for a meetup.

For all queries regarding the event please contact Santhosh Kumar
(9941497246).

All are invited to come!

Source
ILUGC

Dart Flight School Code lab invites for codelab event

Here comes the much awaited Dart Flight School codelab event from GDG
Chennai with a day-longTech Talk (Overview on Dart) and Code Lab (Dart
and AngularJS) on this Saturday (15 Feb 2014)

LEARN DART AND ANGULAR JS, GET YOUR WEB DEVELOPMENT WINGS.
Go from runway to take-off through a guided hands-on Code Lab.

Dart helps you build modern web apps with a new language, core
libraries, and tools such as a package manager, editor, and a compiler
to JavaScript. Use Angular with Dart for extra jet fuel.

Don’t forget to enroll here  -  http://goo.gl/dl3yue

PM me if you wish to give a lightning talk at the event.

Regards
- Jeyanthan

Manager  |   Google   Developer  Group  Chennai

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.

Follow

Get every new post delivered to your Inbox.

Join 365 other followers