Oracle Apps KT: December 2022

Sunday, December 25, 2022

Oracle App R12 submit a concurrent request from a FORM module through button and view output

Block in the button form:

declare
url varchar2(2000);
L_TXN_ID_D NUMBER;
begin
L_TXN_ID_D:= :FUJ_IMM_TXN_D.TXN_ID_D;
url:= xximm_submit_report.xx_hr_report_retrun(
p_template =>'XX_IMM_WK' /*XML report template code*/
,p_application => 'XXCUST'
,p_program => 'XX_IMM_WK ' --Concurrent Program Code
,p_arg1 => L_TXN_ID_D --Parameters
);

web.show_document(url);
exception when others then
message(sqlerrm);
end;

CREATE OR REPLACE PACKAGE APPS.xximm_submit_report

PROCEDURE xx_get_mime_type (

ftype IN VARCHAR2,

mtype IN OUT VARCHAR2,

wopen IN OUT BOOLEAN

);

FUNCTION xx_browser_show_retrun (

fname VARCHAR2,

node VARCHAR2,

mtype VARCHAR2,

req_id NUMBER DEFAULT NULL

)

RETURN VARCHAR2;

FUNCTION xx_hr_report_retrun (

p_template IN VARCHAR2 DEFAULT NULL,

p_application IN VARCHAR2 DEFAULT NULL,

p_program IN VARCHAR2 DEFAULT NULL,

p_description IN VARCHAR2 DEFAULT NULL,

p_arg1 IN VARCHAR2 DEFAULT NULL,

p_arg2 IN VARCHAR2 DEFAULT NULL,

p_arg3 IN VARCHAR2 DEFAULT NULL,

p_arg4 IN VARCHAR2 DEFAULT NULL,

p_arg5 IN VARCHAR2 DEFAULT NULL,

p_arg6 IN VARCHAR2 DEFAULT NULL,

p_arg7 IN VARCHAR2 DEFAULT NULL

)

RETURN VARCHAR2;

END xximm_submit_report;

CREATE OR REPLACE PACKAGE BODY APPS.xximm_submit_report
AS

PROCEDURE xx_get_mime_type (
ftype IN VARCHAR2,
mtype IN OUT VARCHAR2,
wopen IN OUT BOOLEAN
)
IS
profile_defined BOOLEAN := FALSE;
errcode NUMBER;
g_size NUMBER;
initial_mtype VARCHAR2 (80); -- initial value for viewer poplist
BEGIN
wopen := FALSE;

-- check profile options for each file format
IF (ftype = 'TEXT')
THEN
IF (fnd_profile.defined ('FS_MIME_TEXT'))
THEN
fnd_profile.get ('FS_MIME_TEXT', mtype);
profile_defined := TRUE;
END IF;
ELSIF (ftype = 'HTML')
THEN
IF (fnd_profile.defined ('FS_MIME_HTML'))
THEN
fnd_profile.get ('FS_MIME_HTML', mtype);
profile_defined := TRUE;
END IF;
ELSIF (ftype = 'PDF')
THEN
IF (fnd_profile.defined ('FS_MIME_PDF'))
THEN
fnd_profile.get ('FS_MIME_PDF', mtype);
profile_defined := TRUE;
END IF;
ELSIF (ftype = 'PS')
THEN
IF (fnd_profile.defined ('FS_MIME_PS'))
THEN
fnd_profile.get ('FS_MIME_PS', mtype);
profile_defined := TRUE;
END IF;
ELSIF (ftype = 'PCL')
THEN
IF (fnd_profile.defined ('FS_MIME_PCL'))
THEN
fnd_profile.get ('FS_MIME_PCL', mtype);
profile_defined := TRUE;
END IF;
ELSIF (ftype = 'XML')
THEN
IF (fnd_profile.defined ('FS_MIME_XML'))
THEN
fnd_profile.get ('FS_MIME_XML', mtype);
profile_defined := TRUE;
END IF;
ELSE
mtype := 'text/plain';
profile_defined := TRUE;
END IF;

IF NOT wopen
THEN
DBMS_OUTPUT.put_line ('Wopen False');
ELSE
DBMS_OUTPUT.put_line ('Wopen true');
END IF;

-- if profile option does not exists then look for fnd_mime_types_vl
-- view for mime type.
IF (NOT profile_defined)
THEN
FOR c IN (SELECT mime_type
FROM fnd_mime_types_vl
WHERE file_format_code = ftype AND ROWNUM = 1)
LOOP
mtype := c.mime_type;
EXIT;
END LOOP;
END IF; -- not profile_defined
END;

FUNCTION xx_browser_show_retrun (
fname VARCHAR2,
node VARCHAR2,
mtype VARCHAR2,
req_id NUMBER DEFAULT NULL
)
RETURN VARCHAR2
IS
base VARCHAR2 (255);
url VARCHAR2 (255);
ID VARCHAR2 (32);
gwyuid VARCHAR2 (32);
two_task VARCHAR2 (64);
fs_enabled VARCHAR2 (2);
pos NUMBER;
svc VARCHAR2 (240);
x_mode VARCHAR2 (30) := 'BINARY';
oftype VARCHAR2 (4);
published_req BOOLEAN := FALSE;
pub_reqid VARCHAR2 (32);
int_request_id NUMBER;
base_type VARCHAR2 (50);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
int_request_id := req_id;
DBMS_OUTPUT.put_line ('node ===>' || node);
DBMS_OUTPUT.put_line ('mtype ===>' || mtype);
DBMS_OUTPUT.put_line ('req_id ===>' || req_id);
fnd_profile.get ('TWO_TASK', two_task);
fnd_profile.get ('GWYUID', gwyuid);

IF (fnd_profile.defined ('APPS_CGI_AGENT'))
THEN
fnd_profile.get ('APPS_CGI_AGENT', base);
END IF;
IF (base IS NULL)
THEN
fnd_profile.get ('APPS_WEB_AGENT', base);
base_type := 'WEB';
END IF;

IF (fnd_profile.defined ('FS_SVC_PREFIX'))
THEN
fnd_profile.get ('FS_SVC_PREFIX', svc);

IF (svc IS NOT NULL)
THEN
svc := SUBSTR (svc || node, 1, 255);
ELSE
svc := 'FNDFS_' || node;
END IF;
ELSE
svc := 'FNDFS_' || node;
END IF;

IF (req_id IS NOT NULL)
THEN
BEGIN
SELECT file_type
INTO oftype
FROM fnd_conc_req_outputs
WHERE concurrent_request_id = req_id AND ROWNUM = 1;

IF (oftype IN ('PDF', 'PS', 'PCL', 'EXCEL'))
THEN
x_mode := 'BINARY';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
BEGIN
SELECT NVL (output_file_type, 'TEXT')
INTO oftype
FROM fnd_concurrent_requests
WHERE request_id = req_id;

IF (oftype IN ('PDF', 'PS', 'PCL','XML'))
THEN
x_mode := 'BINARY';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
x_mode := 'TEXT';
END;
END;

END IF;

DBMS_OUTPUT.put_line ('X_mode:' || x_mode);

IF published_req = TRUE
THEN

int_request_id := pub_reqid;
DBMS_OUTPUT.put_line ('published_req ===>true');
ELSE
DBMS_OUTPUT.put_line ('published_req ===>false');
END IF;

ID :=
fnd_webfile.create_id (fname,
svc,
10,
mtype,
NVL (int_request_id, req_id),
x_mode,
'Y'
);
DBMS_OUTPUT.put_line ('fname ===>' || fname);
DBMS_OUTPUT.put_line ('svc ==>' || svc);
DBMS_OUTPUT.put_line ('mtype' || mtype);
DBMS_OUTPUT.put_line ('ID ===>' || ID);
DBMS_OUTPUT.put_line ('pub_reqid ==>' || pub_reqid);
DBMS_OUTPUT.put_line ('ID after publish' || ID);
base := LTRIM (RTRIM (base));

IF (base_type = 'WEB')
THEN
-- Strip any file path from the base URL by truncating at the
-- third '/'.
-- This leaves us with something like 'http://ap363sun:8000'.
pos := INSTR (base, '/', 1, 3);

IF (pos > 0)
THEN
base := SUBSTR (base, 1, pos - 1);
END IF;

-- 2638328 - security violation - removing login information from URL
url := base || '/OA_CGI/FNDWRR.exe?' || 'temp_id=' || ID;
ELSIF (base_type = 'CGI')
THEN
IF (SUBSTR (base, LENGTH (base)) <> '/')
THEN
base := base || '/';
END IF;

url := base || 'FNDWRR.exe?' || 'temp_id=' || ID;
END IF;

DBMS_OUTPUT.put_line ('Before : ' || url);
-- FND_WEBFILE.GET_URL(FND_WEBFILE.request_out,ID,gwyuid,two_task,100);
url :=
fnd_webfile.get_url (file_type => fnd_webfile.request_out,
ID => req_id,
gwyuid => gwyuid,
two_task => two_task,
expire_time => 100 -- minutes, security!.
);
DBMS_OUTPUT.put_line ('after : ' || url);
/* pos := INSTR(base, '/', 1, 3);

IF (pos > 0) THEN
base := SUBSTR(base, 1, pos - 1);
END IF;

url := base || '/OA_CGI/FNDWRR.exe?' || 'temp_id=' || ID ;--|| '&' || 'login=' || gwyuid || '@' || two_task;
*/

/*
htp.htmlOpen;
htp.headOpen;
htp.title('Get request output');
htp.headClose;
htp.bodyOpen;
htp.p('<A HREF='||url||'>Click to get request output</a>');
htp.bodyclose;
htp.htmlclose;
*/
DBMS_OUTPUT.put_line (url);
RETURN url;
END;

FUNCTION xx_hr_report_retrun (
p_template IN VARCHAR2 DEFAULT NULL,
p_application IN VARCHAR2 DEFAULT NULL,
p_program IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL,
p_arg1 IN VARCHAR2 DEFAULT NULL,
p_arg2 IN VARCHAR2 DEFAULT NULL,
p_arg3 IN VARCHAR2 DEFAULT NULL,
p_arg4 IN VARCHAR2 DEFAULT NULL,
p_arg5 IN VARCHAR2 DEFAULT NULL,
p_arg6 IN VARCHAR2 DEFAULT NULL,
p_arg7 IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2
IS
l_req_id NUMBER;
l_user_id NUMBER := fnd_profile.VALUE ('USER');
l_resp_id NUMBER := 52168;--53237;
l_resp_appl_id NUMBER := 800;

l_url VARCHAR2 (3000);
ftype VARCHAR2 (30) := 'XML';
mtype VARCHAR2 (80);
wopen BOOLEAN := FALSE;
filename fnd_concurrent_requests.outfile_name%TYPE;
node fnd_concurrent_requests.outfile_node_name%TYPE;
l_request_completed BOOLEAN := FALSE;
l_req_phase VARCHAR2 (20);
l_req_status VARCHAR2 (1000);
l_req_dev_phase VARCHAR2 (1000);
l_req_dev_status VARCHAR2 (1000);
l_req_message VARCHAR2 (1000);
b_success BOOLEAN;
v_set_layout_option BOOLEAN;
v_error_message VARCHAR (2000);
v_count NUMBER;
v_template_type VARCHAR2 (200);
v_cycle_name VARCHAR2 (200);
v_traning_v VARCHAR2 (200);
v_comb VARCHAR2 (200);
BEGIN

-- v_template_type := 'FUJ_HR_HOTEL_WKPERMIT';
fnd_global.apps_initialize(2605,52168, 800);

v_set_layout_option :=
fnd_request.add_layout (template_appl_name => p_application,
template_code => p_template,
--p_program,
template_language => 'en',
template_territory => '',
output_format => 'PDF'
);

IF (NOT v_set_layout_option)
THEN
fnd_file.put_line (fnd_file.LOG,
'Unable to apply template'
);
fnd_file.put_line (fnd_file.LOG, v_error_message);
ELSE
l_req_id :=
fnd_request.submit_request (application => p_application,
program => p_program,
description => null,--p_description,
start_time => NULL,
argument1 => p_arg1,
argument2 => p_arg2,
argument3 => p_arg3,
argument4 => p_arg4,
argument5 => p_arg5,
argument6 => p_arg6,
argument7 => p_arg7
);

DBMS_OUTPUT.put_line ('l_req_id ===> ' || l_req_id);
COMMIT;
END IF;

l_request_completed :=
fnd_concurrent.wait_for_request (request_id => l_req_id,
INTERVAL => 1,
phase => l_req_phase,
status => l_req_status,
dev_phase => l_req_dev_phase,
dev_status => l_req_dev_status,
MESSAGE => l_req_message
);
COMMIT;

FOR c IN (SELECT NVL (output_file_type, 'PDF') ftype,
outfile_name filename, outfile_node_name node,
ofile_size
FROM fnd_concurrent_requests
WHERE request_id = l_req_id)
LOOP
ftype := c.ftype;
filename := c.filename;
node := c.node;
EXIT;
END LOOP;

xx_get_mime_type (ftype => ftype, mtype => mtype, wopen => wopen);

IF NOT wopen
THEN
l_url := xx_browser_show_retrun (filename, node, mtype, l_req_id);
END IF;

RETURN l_url;
END;

END xximm_submit_report;
/

Wednesday, December 21, 2022

Oracle SQL Query for Customer Details

SELECT DISTINCT hp.party_name "CUSTOMER_NAME",

hca.account_number,

hca.status,

hca.cust_account_id customer_id,

HOU.NAME "OPERATING_UNIT"

FROM apps.hz_parties hp,

apps.hz_party_sites hps,

apps.hz_locations hl,

apps.hz_cust_accounts hca,

apps.hz_cust_acct_sites_all hcas,

apps.hz_cust_site_uses_all hcsu,

--apps.hz_customer_profiles hcp,

--apps.hz_cust_profile_classes hcpc,

--apps.ar_payment_schedules_all ps,

apps.hr_operating_units hou

WHERE hp.party_id = hca.party_id(+)

--AND hp.party_id = hcp.party_id

AND hp.party_id = hps.party_id

AND hps.party_site_id = hcas.party_site_id

AND hps.location_id = hl.location_id

AND hca.cust_account_id = hcas.cust_account_id

AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

--AND hca.cust_account_id = hcp.cust_account_id

--AND hca.cust_account_id = ps.customer_id

--AND hcp.profile_class_id = hcpc.profile_class_id

--AND ps.customer_site_use_id =hcsu.site_use_id

AND hcsu.org_id = hou.organization_id

AND hcsu.ORG_ID = 2352

Oracle Apps R12 Assignment Mode Status Function

FUNCTION GET_MODE_ASSGINMENT_F(P_ASSIGNMENT_ID NUMBER, P_DATE DATE)

RETURN VARCHAR2 AS

lb_correction BOOLEAN;

lb_update BOOLEAN;

lb_update_override BOOLEAN;

lb_update_change_insert BOOLEAN;

lc_dt_ud_mode varchar2(200);

L_ERROR varchar2(2000);

BEGIN

dt_api.find_dt_upd_modes

( p_effective_date => P_DATE,

p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',

p_base_key_column => 'ASSIGNMENT_ID',

p_base_key_value => P_ASSIGNMENT_ID,

-- Output data elements

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

p_correction => lb_correction,

p_update => lb_update,

p_update_override => lb_update_override,

p_update_change_insert => lb_update_change_insert );

IF ( lb_correction = TRUE )

THEN lc_dt_ud_mode := 'CORRECTION';

end if;

IF ( lb_update = TRUE )

THEN lc_dt_ud_mode := 'UPDATE';

END IF;

IF ( lb_update_override = TRUE )

THEN lc_dt_ud_mode := 'UPDATE_OVERRIDE';

end if;

IF (lb_update_change_insert = TRUE)

THEN lc_dt_ud_mode := 'UPDATE_CHANGE_INSERT';

end if;

RETURN lc_dt_ud_mode;

EXCEPTION WHEN OTHERS THEN

L_ERROR := SQLERRM;

END;

Oracle Apps R12 Object Version Number Function

FUNCTION GET_OVN_F (P_ASSIGNMENT_ID NUMBER, P_DATE DATE) RETURN NUMBER IS

L_RETURN NUMBER;

BEGIN

SELECT OBJECT_VERSION_NUMBER

INTO L_RETURN

FROM PER_ALL_ASSIGNMENTS_F PAAF

WHERE ASSIGNMENT_ID = P_ASSIGNMENT_ID

AND P_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

AND PRIMARY_FLAG = 'Y'

;

RETURN L_RETURN;

EXCEPTION WHEN OTHERS THEN RETURN 0;

END;

Use as Below in any package:

ln_object_number := 0;

ln_object_number :=GET_OVN_F(:ASSIGNMENT_ID,:RES_DATE);

Oracle Apps R12 for Create Element Entry api

PROCEDURE FUJ_CRT_ELEMENT_ENTRY

(

L_ASSIGNMENT_ID NUMBER,

L_PAYROLL_ID NUMBER,

L_EFF_DATE DATE,

L_AMOUNT NUMBER,

L_ELEMENT_NAME VARCHAR2,

L_INPUT_NAME VARCHAR2

) AS

--L_ASSIGNMENT_ID NUMBER := 45570;

--L_PAYROLL_ID NUMBER := 60;

--L_ELEMENT_NAME VARCHAR2(200) := 'XX123 Allowance';

--L_INPUT_NAME VARCHAR2(200) := 'Override Amount';

--L_EFF_DATE DATE := TO_DATE('20-DEC-2022');

--L_AMOUNT NUMBER := 200;

--local variables

l_input_value_id NUMBER;

l_link_id NUMBER;

l_effective_end_date DATE := NULL;

l_effective_start_date DATE := NULL;

l_object_version_number NUMBER := NULL;

l_element_entry_id NUMBER := NULL;

l_create_warning BOOLEAN;

lc_dt_ud_mode VARCHAR2(100) := NULL;

L_OVN NUMBER;

L_ELEMENT_EXISTS VARCHAR2(200);

V_ERROR_MSG varchar2(2000);

begin

SELECT XXI.input_value_id, element_link_id,

INTO l_input_value_id, l_link_id

FROM

PAY_INPUT_VALUES_F XXI,

PAY_ELEMENT_TYPES_F XXT,

PAY_ELEMENT_LINKS_F XXL

WHERE XXT.ELEMENT_TYPE_ID = XXI.ELEMENT_TYPE_ID

AND XXT.ELEMENT_TYPE_ID = XXL.ELEMENT_TYPE_ID

AND XXL.PAYROLL_ID = L_PAYROLL_ID

AND XXT.ELEMENT_NAME = L_ELEMENT_NAME--in ('XX123 Allowance')

and XXI.NAME = L_INPUT_NAME

AND ROWNUM =1

;

pay_element_entry_api.create_element_entry (

p_validate => FALSE,

p_effective_date => L_EFF_DATE,

p_business_group_id => 81,

p_assignment_id => L_ASSIGNMENT_ID,

p_element_link_id => l_link_id,

p_entry_type => 'E',

p_input_value_id1 => l_input_value_id

,p_entry_value1 => L_AMOUNT,

p_effective_start_date => l_effective_start_date,

p_effective_end_date => l_effective_end_date,

p_element_entry_id => l_element_entry_id,

p_object_version_number => l_object_version_number,

p_create_warning => l_create_warning

);

commit;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

V_ERROR_MSG := sqlerrm;

END;

Tuesday, December 6, 2022

Apps R12 Delete Absence API

DECLARE

CURSOR get_absence

SELECT absence_attendance_id, object_version_number

FROM per_absence_attendances

WHERE PERSON_ID IN (SELECT PERSON_ID FROM PER_ALL_ASSIGNMENTS_F WHERE PAYROLL_ID =1002);

BEGIN

FOR fetch_absence IN get_absence

LOOP

BEGIN

hr_person_absence_api.

delete_person_absence (

p_validate => FALSE,

p_absence_attendance_id => fetch_absence.absence_attendance_id,

p_object_version_number => fetch_absence.object_version_number);

COMMIT;

DBMS_OUTPUT.

put_line (

fetch_absence.absence_attendance_id

|| ' has been deleted Successfully !!!');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.

put_line (

'Inner Exception: '

|| fetch_absence.absence_attendance_id

|| ' - - '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

end;

Apps R12 HR Delete SIT API

DECLARE

CURSOR c1

SELECT paa.person_analysis_id, paa.object_version_number,PER.EMPLOYEE_NUMBER

FROM per_person_analyses paa,

per_analysis_criteria pac,

fnd_id_flex_structures_vl fifv,

per_all_people_f per

WHERE paa.analysis_criteria_id = pac.analysis_criteria_id

AND fifv.id_flex_num = paa.id_flex_num

AND UPPER (fifv.id_flex_structure_code) = UPPER('XX_Leave_Details')

AND fifv.id_flex_code = 'PEA'

AND paa.date_from BETWEEN per.effective_start_date

AND per.effective_end_date

AND paa.person_id = per.person_id

AND PER.PERSON_ID IN (SELECT PERSON_ID FROM PER_ALL_ASSIGNMENTS_F WHERE PAYROLL_ID =1002)

ORDER BY 3 DESC

;

BEGIN

FOR i IN c1

LOOP

BEGIN

hr_sit_api.

delete_sit (

p_validate => FALSE,

p_person_analysis_id => i.person_analysis_id,

p_pea_object_version_number => i.object_version_number);

COMMIT;

DBMS_OUTPUT.

put_line (

i.person_analysis_id || ' has been deleted successfully !!!');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.

put_line (

'Failure: ' || i.person_analysis_id || ' - ' || SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;

Search This Blog

Sunday, December 25, 2022

Wednesday, December 21, 2022

Tuesday, December 6, 2022