Category Archives: Oracle

Class not found error and class format Error in OAF Jdeveloper

I had this problem when doing and Extension I tried to copy the class files from server to my machine and tried to compile it. It was giving me Class not found error. After that tried to import the classes it was giving me as class format error as per the below screenshot.

After some hours of time I was able to find the problem as the compiled versions are of latest version but the jdeveloper was taking the old version of java. I resolved by editing the jdev.conf file in Jdev-R12.2.4\jdevbin\jdev\bin and in that I have commented the version of 1.5 and enabled jdk version 1.8 as shown below.

The Jdk version is that I have already installed in my system and not a preinstalled one. The Instance is oracle apps R12.2.4 Instance.

#
# Directive SetJavaHome is not required by default, except for the base
# install, since the launcher will determine the JAVA_HOME. On Windows
# it looks in ..\..\jdk, on UNIX it looks in the PATH by default.
#
# SetJavaHome C:\Java\jdk1.5.0_04
#SetJavaHome C:\Program Files (x86)\Java\jdk1.8.0_172

Advertisements

Oracle apps Forms not opening with error message ClassNotFoundException FormsLauncher.class

I came across a Error with a new laptop while opening the forms.

I had java 1.6 update 27 and firefox 34. When I tried to open java it was not opening after trying some things then my forms was opened.

Below are the steps

1.Remove all Java versions. Try to install current version of java in my case it is 8 update 172

2.Try to have Firefox ESR version or Recomended IE version. In my case Firefox ESR 52.8.0

3. Disable TLS in Java: In newer Java Versions you have to disable by, Go to Java control Panel> Goto Advanced Tab> Click General or Security Tree. > If you see TLs 1.1 or TLS 1.2 uncheck them.

4. In Internet Explorer Go to Internet Options> Advanced> Scroll down to Security Disable / Un check all Use TLS 1.0, Use TLS 1.1 and Use TLS 1.2.

Note: Firefox ESR 60 will not support java plugin as of now.

Form Personalization to check Supplier Tax Registration number is Present and OU Restriction in Oracle Apps

I have got a requirement to show message to user that when Supplier TRN is not present then to display a message and also this personalization should be restrictricted to Operating Unit Level.

1. For OU(Operating Unit)  Restriction.

I have created a Profile Option in OU Level so whenever Users want they can Enable it

FND_PROFILE.VALUE(‘XXCHECK_VAT_VALIDATION’)=’Y’
AND EXISTS (
SELECT 1
FROM ap_supplier_sites_all
WHERE vendor_site_id = :inv_sum_folder.vendor_site_id
AND vendor_id = :inv_sum_folder.vendor_id
AND vat_registration_num IS NOT NULL)

 

Here the profile option is XXCHECK_VAT_VALIDATION. I have created a custom profile option with YESor NO LOV with all access level. Then in System Administrator I have enabled the profile according to Responsibility wise.

So we have control of the ORG Restriction using profile option.

Thank you.

PO Receipt Interface in Oracle Apps

Below is the code for PO Receipt Interface. The below script is based on Oracle Document ID  DOC ID 1520449.1.

In the Below script has the functionalities as given below

1. Insertion of RCV_TRANSACTIONS_INTERFACE

2.Insertion of RCV_HEADERS_INTERFACE

3. Automating Concurrent Receiving Transaction Processor and using Wait to complete the concurrent

4. Once done giving the Receipt Number

 

declare

L_USER_ID NUMBER :=5685;
L_RESP_ID NUMBER :=52081;
L_APPL_ID NUMBER :=201;
L_ORG_ID NUMBER :=405;
L_LOGIN_ID NUMBER :=5664513;–PROFILE VALUE
L_RCV_HEADER_ID NUMBER;
L_RCV_GROUP_ID NUMBER;
L_RCV_TRANSACTION_ID NUMBER;
L_CHR_LOT_NUMBER VARCHAR2 (50);
LC_RECEIPT_NUMBER NUMBER:= 0;
LN_REQ_ID NUMBER;
LN_REQUEST_ID NUMBER;
LN_TEMP NUMBER;
LB_RESULT1 BOOLEAN;
LV_PHASE VARCHAR2(20);
LV_STATUS1 VARCHAR2(20);
LV_DEV_PHASE VARCHAR2(20);
LV_DEV_STATUS VARCHAR2(20);
LV_MESSAGE VARCHAR2(20);

CURSOR C_PO_INFO(P_PO_HEADER_ID IN NUMBER) IS
SELECT DISTINCT PHA.PO_HEADER_ID, PLA.PO_LINE_ID, PHA.AGENT_ID, PHA.ORG_ID,
PLLA.LINE_LOCATION_ID,
PDA.PO_DISTRIBUTION_ID,
PHA.VENDOR_ID, PHA.VENDOR_SITE_ID, PLLA.SHIP_TO_ORGANIZATION_ID, PHA.SEGMENT1 DOCUMENT_NUM,PLA.LINE_NUM DOCUMENT_LINE_NUM, PLA.QUANTITY,
DESTINATION_SUBINVENTORY SUBINVENTORY
FROM PO_HEADERS_ALL PHA, PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA
WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PDA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
AND PHA.PO_HEADER_ID = NVL(P_PO_HEADER_ID, PHA.PO_HEADER_ID);

CURSOR C_PO_HDR(P_PO_HEADER_ID IN NUMBER) IS
SELECT DISTINCT PHA.PO_HEADER_ID, PHA.AGENT_ID, PHA.ORG_ID,

PHA.VENDOR_ID, PHA.VENDOR_SITE_ID, PLLA.SHIP_TO_ORGANIZATION_ID, PHA.SEGMENT1 DOCUMENT_NUM

FROM PO_HEADERS_ALL PHA, PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA
WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PDA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
AND PHA.PO_HEADER_ID = NVL(P_PO_HEADER_ID, PHA.PO_HEADER_ID);

CURSOR GET_RECEIPT_NUMBER(P_PO_HEADER_ID IN NUMBER) IS
SELECT DISTINCT RECEIPT_NUM FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL
WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND RSL.PO_HEADER_ID = P_PO_HEADER_ID;

BEGIN
FND_CLIENT_INFO.SET_ORG_CONTEXT(405);–INITIALIZING ORG_ID
FND_GLOBAL.APPS_INITIALIZE(L_USER_ID, L_RESP_ID, L_APPL_ID,L_LOGIN_ID);
SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
INTO L_RCV_HEADER_ID
FROM DUAL;
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
INTO L_RCV_GROUP_ID
FROM DUAL;
–SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
–INTO L_RCV_TRANSACTION_ID
–FROM DUAL;

DBMS_OUTPUT.PUT_LINE(‘BEFORE CALLING RCV_HEADERS_INTERFACE’);
FOR J IN C_PO_HDR(P_PO_HEADER_ID) LOOP

INSERT INTO RCV_HEADERS_INTERFACE
(HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
TRANSACTION_TYPE,
AUTO_TRANSACT_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
VENDOR_ID,
VENDOR_SITE_ID,
SHIP_TO_ORGANIZATION_ID,
EXPECTED_RECEIPT_DATE,
EMPLOYEE_ID,
VALIDATION_FLAG,
ORG_ID
)
VALUES
(L_RCV_HEADER_ID , –HEADER_INTERFACE_ID
L_RCV_GROUP_ID, –GROUP_ID
‘PENDING’, –PROCESSING_STATUS_CODE
‘VENDOR’, –RECEIPT_SOURCE_CODE
‘NEW’, –TRANSACTION_TYPE
‘RECEIVE’, –AUTO_TRANSACT_CODE
SYSDATE, –LAST_UPDATE_DATE
0, –LAST_UPDATED_BY
0, –LAST_UPDATE_LOGIN
SYSDATE, –CREATION_DATE
5685, –CREATED_BY
J.VENDOR_ID, –VENDOR_ID
J.VENDOR_SITE_ID, –VENDOR_SITE_ID
J.SHIP_TO_ORGANIZATION_ID, –SHIP_TO_ORGANIZATION_ID,
SYSDATE, –EXPECTED_RECEIPT_DATE
J.AGENT_ID, –EMPLOYEE_ID
‘Y’, –VALIDATION_FLAG
J.ORG_ID –OPERATING_UNIT
);

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘BEFORE CALLING RCV_TRANSACTIONS_INTERFACE’);
FOR I IN C_PO_INFO(P_PO_HEADER_ID) LOOP

INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
INTERFACE_SOURCE_CODE,
AUTO_TRANSACT_CODE,
RECEIPT_SOURCE_CODE,
TO_ORGANIZATION_ID,
SOURCE_DOCUMENT_CODE,
PO_HEADER_ID,
PO_LINE_ID,
HEADER_INTERFACE_ID,
DOCUMENT_NUM,
DOCUMENT_LINE_NUM,
VALIDATION_FLAG,
QUANTITY,
— AMOUNT,
SUBINVENTORY
)
SELECT
RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL, –INTERFACE_TRANSACTION_ID
L_RCV_GROUP_ID, –GROUP_ID
SYSDATE, –LAST_UPDATE_DATE
0, –LAST_UPDATED_BY
SYSDATE, –CREATION_DATE
5685, –CREATED_BY
0, –LAST_UPDATE_LOGIN
‘RECEIVE’, –TRANSACTION_TYPE
SYSDATE, –TRANSACTION_DATE
‘PENDING’, –PROCESSING_STATUS_CODE
‘BATCH’, –PROCESSING_MODE_CODE
‘PENDING’, –TRANSACTION_STATUS_CODE
‘FLO’, –INTERFACE_SOURCE_CODE
‘DELIVER’, –AUTO_TRANSACT_CODE
‘VENDOR’, –RECEIPT_SOURCE_CODE
I.SHIP_TO_ORGANIZATION_ID, –TO_ORGANIZATION_ID
‘PO’, –SOURCE_DOCUMENT_CODE
I.PO_HEADER_ID, –PO_HEADER_ID
I.PO_LINE_ID, –PO_LINE_ID
L_RCV_HEADER_ID, –HEADER_INTERFACE_ID
I.DOCUMENT_NUM, –DOCUMENT_NUM
I.DOCUMENT_LINE_NUM, –DOCUMENT_LINE_NUM
‘Y’, –VALIDATION_FLAG
I.QUANTITY, –QUANTITY
— 10, –AMOUNT
NVL(I.SUBINVENTORY,’NF-Staging’ )
FROM DUAL;

END LOOP;
DBMS_OUTPUT.PUT_LINE(‘AFTER CALLING RCV_TRANSACTIONS_INTERFACE’);

COMMIT;

DBMS_OUTPUT.PUT_LINE(‘CALLING CONCURRENT’);
RECEIPT_CONCURRENT( NULL ,L_RCV_GROUP_ID);

DBMS_OUTPUT.PUT_LINE(‘**** EXECUTED SUCCESSFULLY *****’);

OPEN GET_RECEIPT_NUMBER(P_PO_HEADER_ID);
FETCH GET_RECEIPT_NUMBER INTO LC_RECEIPT_NUMBER;
CLOSE GET_RECEIPT_NUMBER;

DBMS_OUTPUT.PUT_LINE(‘Receipt Number is ‘||LC_RECEIPT_NUMBER);

— FND_GLOBAL.APPS_INITIALIZE (USER_ID => FND_GLOBAL.USER_ID,
— RESP_ID => FND_GLOBAL.RESP_ID,
— RESP_APPL_ID => FND_GLOBAL.RESP_APPL_ID);

— L_USER_ID NUMBER :=5685;
–L_RESP_ID NUMBER :=52081;
–L_APPL_ID NUMBER :=201;

FND_GLOBAL.APPS_INITIALIZE (USER_ID => 5685,
RESP_ID => 52081,
RESP_APPL_ID =>201);

LN_REQ_ID := FND_REQUEST.SUBMIT_REQUEST ( ‘PO’ –APPLICATIONI SHORT NAME;
,’RVCTP’ –PROGRAM SHORT NAME;
,
,
,FALSE
,’BATCH’
,P_GROUP_ID
,P_ORG_ID
);

COMMIT;
DBMS_OUTPUT.PUT_LINE(LN_REQ_ID);

LB_RESULT1 := FND_CONCURRENT.WAIT_FOR_REQUEST (REQUEST_ID => LN_REQ_ID , INTERVAL => 5, PHASE => LV_PHASE, STATUS => LV_STATUS1, DEV_PHASE => LV_DEV_PHASE, DEV_STATUS => LV_DEV_STATUS, MESSAGE => LV_MESSAGE );
COMMIT;
IF LV_DEV_PHASE = ‘COMPLETE’ AND LV_DEV_STATUS = ‘NORMAL’ THEN
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘SQLLOADER PROGRAM COMPLETED SUCCESSFULLY’ || LN_REQUEST_ID );
DBMS_OUTPUT.PUT_LINE(‘Concurrent Request completed successfully’);
— PO_RETURNS(ERRBUF , RETCODE , ‘1’ );
ELSE
DBMS_OUTPUT.PUT_LINE(‘Error while running Concurrent Request’);
ROLLBACK;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ERROE’);
DBMS_OUTPUT.PUT_LINE(‘ERROR’||SQLERRM);

END;

 

Thank You

Query to get Profile Value at Responsibility level

SELECT po.profile_option_name “NAME”, po.user_profile_option_name,
DECODE (TO_CHAR (pov.level_id),
‘10001’, ‘SITE’,
‘10002’, ‘APP’,
‘10003’, ‘RESP’,
‘10005’, ‘SERVER’,
‘10006’, ‘ORG’,
‘10004’, ‘USER’,
‘???’
) “LEVEL”,
DECODE (TO_CHAR (pov.level_id),
‘10001’, ”,
‘10002’, app.application_short_name,
‘10003’, rsp.responsibility_key,
‘10005’, svr.node_name,
‘10006’, org.NAME,
‘10004’, usr.user_name,
‘???’
) “CONTEXT”,
pov.profile_option_value “VALUE”
FROM apps.fnd_profile_options_vl po,
apps.fnd_profile_option_values pov,
apps.fnd_user usr,
apps.fnd_application app,
apps.fnd_responsibility rsp,
apps.fnd_nodes svr,
apps.hr_operating_units org
WHERE 1 = 1
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id(+) = pov.level_value
AND rsp.application_id(+) = pov.level_value_application_id
AND rsp.responsibility_id(+) = pov.level_value
AND app.application_id(+) = pov.level_value
AND svr.node_id(+) = pov.level_value
AND org.organization_id(+) = pov.level_value
and pov.level_id = 10003
–and po.profile_option_name like ‘CSP%’;

AP Supplier Site tax Update Oracle Apps R12

Below is the script to AP Supplier Site tax Update script.

 

/* Formatted on 2017/12/29 21:19 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE apps.xx_test1vendor_upd
AS
p_api_version NUMBER;
p_init_msg_list VARCHAR2 (200);
p_commit VARCHAR2 (200);
p_validation_level NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
lr_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;
lr_existing_vendor_site_rec ap_supplier_sites_all%ROWTYPE;
p_vendor_site_id NUMBER;
p_calling_prog VARCHAR2 (200);

CURSOR c_vendor_info
IS
SELECT assa.vendor_site_id, assa.org_id, xx.tax_classification_code,xx.EMIRATE,xx.VENDOR_NAME,
DECODE (allow_offset_tax,
‘YES’, ‘Y’,
‘NO’, ‘N’
) allow_offset_tax,
DECODE (allow_offset_tax,
‘YES’, ‘Y’,
‘NO’, ‘N’
) auto_tax_calc_flag,
xx.ROWID row_id
FROM xx_supplier_info xx,
ap_suppliers asup,
ap_supplier_sites_all assa
WHERE xx.vendor_id = asup.vendor_id
AND asup.vendor_id = assa.vendor_id
— AND assa.vat_code IS NULL
and xx.upload_date is null
— and xx.error_status <> ‘S’
AND assa.vendor_site_code = xx.vendor_site_code;
— AND ROWNUM < 6;
BEGIN
— Initialize apps session
fnd_global.apps_initialize (0, 20639, 200);
mo_global.init (‘SQLAP’);
fnd_client_info.set_org_context (101);
— Assign Basic Values
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_true;
p_validation_level := fnd_api.g_valid_level_full;

FOR i IN c_vendor_info
LOOP
p_vendor_site_id := i.vendor_site_id; — to be end dated
p_calling_prog := ‘XXCUSTOM’;
— Assign Vendor Site Details
lr_vendor_site_rec.vendor_site_id := i.vendor_site_id;
lr_vendor_site_rec.last_update_date := SYSDATE;
lr_vendor_site_rec.last_updated_by := -1;
— lr_vendor_site_rec.vendor_id := lr_existing_vendor_site_rec.vendor_id;
lr_vendor_site_rec.vat_code := i.tax_classification_code;
lr_vendor_site_rec.OFFSET_TAX_FLAG := i.allow_offset_tax;
lr_vendor_site_rec.auto_tax_calc_flag := ‘Y’;
lr_vendor_site_rec.COUNTRY := i.EMIRATE;
–lr_vendor_site_rec.VAT_REGISTRATION_NUM := ‘SUPP_TRN’;
lr_vendor_site_rec.org_id := i.org_id;
–lr_vendor_site_rec.inactive_date := ”;
ap_vendor_pub_pkg.update_vendor_site
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => lr_vendor_site_rec,
p_vendor_site_id => p_vendor_site_id,
p_calling_prog => p_calling_prog
);
DBMS_OUTPUT.put_line (‘X_RETURN_STATUS = ‘ || x_return_status);
DBMS_OUTPUT.put_line (‘X_MSG_COUNT = ‘ || x_msg_count);
DBMS_OUTPUT.put_line (‘X_MSG_DATA = ‘ || x_msg_data);

UPDATE xx_supplier_info
SET error_status = NVL (x_return_status, 0),
error_message = x_msg_data,
upload_date = sysdate
WHERE ROWID = i.row_id;
commit;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Inside Exception’);
END;
/

AR Invoice Customer Tax Information query in Oracle apps

I had a requirement to build a report for AR Invoice Customer Tax Information. We have done the query below

SELECT customer_name,
hl.address1||’, ‘||hl.address2||’, ‘||hl.address3 address, hl.city, hl.country,
(SELECT DISTINCT hcsua.tax_reference tax_reg_num
FROM hz_cust_accounts hz,
ar_customers ac,
hz_parties hp,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsua,
hz_party_sites hps,
hz_locations hl
WHERE hz.cust_account_id = ac.customer_id
AND hz.party_id = hp.party_id
AND hp.party_id = hps.party_id
AND hcas.party_site_id(+) = hps.party_site_id
AND hl.location_id(+) = hps.location_id
AND ac.customer_id = rc.customer_id
AND hcsua.org_id = rcta.org_id
AND hcsua.org_id = hcas.org_id
AND hz.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsua.cust_acct_site_id
AND site_use_code = ‘BILL_TO’
AND hcsua.tax_code IS NOT NULL) tax_reg_num,
(SELECT DISTINCT hcsua.tax_code
FROM hz_cust_accounts hz,
ar_customers ac,
hz_parties hp,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsua,
hz_party_sites hps,
hz_locations hl
WHERE hz.cust_account_id = ac.customer_id
AND hz.party_id = hp.party_id
AND hp.party_id = hps.party_id
AND hcas.party_site_id(+) = hps.party_site_id
AND hl.location_id(+) = hps.location_id
AND ac.customer_id = rc.customer_id
AND hcsua.org_id = rcta.org_id
AND hcsua.org_id = hcas.org_id
AND hz.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsua.cust_acct_site_id
AND site_use_code = ‘BILL_TO’
AND hcsua.tax_code IS NOT NULL) tax_code,
rcta.trx_number, trx_date,
— rsu.cust_acct_site_id, hl.city, hl.state, hl.country, hl.postal_code,
rctla.description description,
rctla.quantity_invoiced,
rctla.unit_selling_price,
rctla.extended_amount line_amt, rct_tax.extended_amount tax_line_amt,
rct_tax.tax_rate, — , hps.Party_id –, (select ) —,
rctla.extended_amount+rct_tax.extended_amount total_price,
le_info.address, le_info.entity_name, le_info.registration_number
FROM ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla,
ar_customers rc,
hz_cust_site_uses_all rsu,
hz_cust_acct_sites_all hcsa,
hz_party_sites hps,
hz_locations hl,
ra_customer_trx_lines_all rct_tax,
(SELECT distinct
po_hr_location.get_formatted_address(hr_loc.location_id) address,
xep.name entity_name,
reg.registration_number ,
xep.legal_entity_id
FROM
xle_entity_profiles xep,
xle_registrations reg, —
hr_operating_units hou,
— hr_all_organization_units hr_ou,
hr_all_organization_units_tl hr_outl,
hr_locations_all hr_loc,
gl_legal_entities_bsvs glev
WHERE
1=1
AND xep.transacting_entity_flag = ‘Y’
AND xep.legal_entity_id = reg.source_id
AND reg.source_table = ‘XLE_ENTITY_PROFILES’
AND reg.identifying_flag = ‘Y’
AND xep.legal_entity_id = hou.default_legal_context_id
AND reg.location_id = hr_loc.location_id
AND xep.legal_entity_id = glev.legal_entity_id
AND hr_outl.organization_id = hou.organization_id) le_info

WHERE rcta.customer_trx_id = rctla.customer_trx_id
AND rc.customer_id = rcta.bill_to_customer_id
AND rcta.customer_trx_id = :p_customer_trx_id
AND rsu.cust_acct_site_id = hcsa.cust_acct_site_id
— AND TRUNC (rcta.trx_date) BETWEEN ’01-DEC-2017′ AND ’31-DEC-2017′
AND rcta.bill_to_site_use_id = rsu.site_use_id
AND hcsa.party_site_id = hps.party_site_id
AND rsu.site_use_code = ‘BILL_TO’
AND hps.location_id = hl.location_id
AND rctla.line_type = ‘LINE’
and le_info.legal_entity_id = rcta.legal_entity_id
AND rct_tax.link_to_cust_trx_line_id = rctla.customer_trx_line_id;

Jumping Employee Number Sequence in Oracle apps

Now we observe we have wrongly created a employee(005736) and we delete and purge the data.

 

Now we have to maintain the correct sequence so what we do is

Go to Work Structures -> Organization -> Description

Query the organization and go to Additional Organization Information and change the employee Number generation to Manual

 

Now change the sequence by  creating the person as in the below screenshot

So update the sequence as below

update PER_NUMBER_GENERATION_CONTROLS

set next_value = 5735   –Current Sequence Number

where type = ‘EMP’ — Assuming you are updating the employee number generation to automatic.

and business_group_id = “Your BG ID”;

Now we have to change the next sequence to 005736

After updating the script Now we have to run a concurrent Program

After running the program test it by creating a Employee

Queries to get path of Concurrent Request Log, Output, Manager Log and FNDOPP log

Below are the queries to get path of Concurrent Request Log, Output, Manager Log and FNDOPP log

a) Concurrent Request Log:
select logfile_name from fnd_concurrent_requests where request_id = <request_id>;

b) Concurrent Output file:
select outfile_name from fnd_concurrent_requests where request_id = <request_id>;

c) Concurrent Manager Worker Log:
select logfile_name from fnd_concurrent_processes where concurrent_process_id=(select controlling_manager
from fnd_concurrent_requests where request_id=<request_id>);

d) FNDOPP log file

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id =<request_id>;