Splitting of words in two statements in SQL using INSTR and SUBSTR functions

Below is the code snippet

 

select substr(‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, 0, case when instr(‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘,45, 1)= 0 then 45 else instr(‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘,45, 1) end) first_var
, substr(‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, instr(‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘,45, 1), case when instr(‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘,45, 1)>45 then instr(‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘,45, 1) else 0 end) sec_var from dual;

Advertisements

Delete or Remove or Clear Approvers in AME using AME_API2

Below is the AME Api to clear the approvers in AME Transaction. So all the approved status will be reversed and will remove status in AME.

AME_API2.CLEARALLAPPROVALS (20003,
‘XXQF_BUYER’,
LN_REQ_ID);

Soon I will wirte a post in detail about the flow of the AME Api that can be used in PLSQL

Approve and Reject Approvers in AME by AME_API2 Api

In AME we will be able to set Approve and Reject status in AME using AME_API2.

So in AME on Test Workbench we can see that the approver gets approved or rejected and AME_API will give approvers based on past approvals.

Below is API to Approve and Reject. Only the parameter is different to Approve and Reject

AME_API2.UPDATEAPPROVALSTATUS2 (
APPLICATIONIDIN => 20003,
TRANSACTIONIDIN => LN_REQ_ID, –?GENERALLY ITEMKEY
APPROVALSTATUSIN => AME_UTIL.APPROVEDSTATUS, –?APPROVED_STATUS_OR_REJECTED_STATUS
APPROVERNAMEIN => LC_USER_NAME,
TRANSACTIONTYPEIN => LC_GET_AME_TRX_TYPE);

AME_API2.UPDATEAPPROVALSTATUS2 (
APPLICATIONIDIN => 20003,
TRANSACTIONIDIN => LN_REQ_ID, –?GENERALLY ITEMKEY
APPROVALSTATUSIN => AME_UTIL.REJECTSTATUS, –?APPROVED_STATUS_OR_REJECTED_STATUS
APPROVERNAMEIN => LC_USER_NAME,
TRANSACTIONTYPEIN => LC_GET_AME_TRX_TYPE);

Thank you.

PLSQL Logic to AME Approver Resultset as Table Type

In some cases when we have to bring the approvers to the display screen to show it is better to have a query so that it can be easily mapped with VO.

For Example as the below query

SELECT SEQ_NUM, FULL_NAME APPRIVER_FULL_NAME, APPROVER_POSITION approver_position, APPROVER_ROLE APPROVER_ROLE, ROWNUM SEQ_N, USER_NAME, PERSON_ID, USER_ID, USED_IN, TENDER_REQUEST_ID, REQUEST_TYPE FROM THE ( SELECT CAST ( XX_GET_APPROVERS_LIST (:1) AS xxqf_et_approvers_table) FROM DUAL)

So In the below query it fetches all the details based on the query getting from table type. So below is the sample format for creating table function based on AME output.

 

CREATE OR REPLACE FUNCTION XX_GET_APPROVERS_LIST(l_request_id in number)
RETURN xx_approvers_table
as
l_approvers_list xx_approvers_table
:= xx_approvers_table ();
— l_request_id NUMBER := 7333;
g_next_approvers ame_util.approversTable2;
l_completeYNO VARCHAR2 (100);
applicationId NUMBER := 201;
LN_USER_NAME VARCHAR2 (1000);
lc_name VARCHAR2 (200);

l_approvers_list xxqf_et_approvers_table
:= xxqf_et_approvers_table ();
l_request_type VARCHAR2 (50);
l_requisition_id NUMBER;
l_rfq_id NUMBER;

cursor get_info_pos_based(p_pos_id in number) is select papf.person_id, fu.user_id, papf.full_name, fu.user_name
from per_all_people_f papf, per_all_assignments_f paaf , fnd_user fu
where papf.person_id = paaf.person_id and fu.employee_id = papf.person_id
and paaf.position_id = p_pos_id
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between paaf.effective_start_date and paaf.effective_end_date
and rownum =1;

l_person_id number;
l_user_id number;
lc_full_name varchar2(2000);
lc_user_name varchar2(5000);
lc_position_name varchar2(5000);
lc_request_type varchar2(1000);

BEGIN

select REQUEST_TYPE into lc_request_type from xxqf_et_tender_eval_request where TENDER_REQUEST_ID = l_request_id;

ame_api2.getAllApprovers7 (
applicationIdIn => 20003,
transactionTypeIn => ‘XX_HIER’,
transactionIdIn => l_request_id,
approvalProcessCompleteYNOut => l_completeYNO,
approversOut => g_next_approvers);

IF g_next_approvers.COUNT > 0
THEN
FOR I IN 1 .. g_next_approvers.COUNT
LOOP
if (substr(g_next_approvers (I).NAME, 0, 4) = ‘POS:’)
then
DBMS_OUTPUT.PUT_LINE (‘AT MR POSITION Based Hierarchy’);
open get_info_pos_based(substr(g_next_approvers (I).NAME, 5));
fetch get_info_pos_based into l_person_id, l_user_id, lc_full_name, lc_user_name;
close get_info_pos_based;
l_approvers_list.EXTEND;
l_approvers_list (l_approvers_list.COUNT) :=
XXQF_ET_APPROVERS_T (
g_next_approvers (I).approver_order_number,
lc_user_name,
l_person_id,
l_user_id,
lc_full_name,
g_next_approvers (I).display_name,
‘MR Appr Hierarchy’,
‘BOTH’,
l_request_id,
lc_request_type);

else
l_approvers_list.EXTEND;
l_approvers_list (l_approvers_list.COUNT) :=
XXQF_ET_APPROVERS_T (
g_next_approvers (I).approver_order_number,
g_next_approvers (I).NAME,
g_next_approvers (I).approver_order_number,
g_next_approvers (I).approver_order_number,
g_next_approvers (I).display_name,
g_next_approvers (I).approval_status,
‘MR Appr Hierarchy’,
‘BOTH’,
l_request_id,
g_next_approvers (I).NAME);
end if;
END LOOP;
END IF;

FOR I IN (SELECT * FROM TABLE (l_approvers_list))
LOOP
NULL;
DBMS_OUTPUT.PUT_LINE (
‘ AFTER EXTEND Testing the Data :’
|| I.USER_NAME
|| ‘ display_name :’); –For Testing the Values
END LOOP;

RETURN l_approvers_list;
END;

 

Unable to launch the Java Virtual Machine Located at path: ..\..\jdk\jre\bin\client\jvm.dl

On running Jdevloper for first time you may see the below error

ERROR:
Unable to launch the Java Virtual Machine
Located at path:
..\..\jdk\jre\bin\client\jvm.dll

SOLUTION:
1. go to Control Panel->System->Advanced System Settings->Environmental Variables
2. Update the ‘Path’ setting to include the following directory: ‘<jdev home dir>\jdevbin\jdk\bin’
3. Reopen the jDeveloper.

Position Hierarchy in AME

To Define Position Hierachy I have created a custom transaction type in AME and in Approvals Management Business Analyst I went to Action types and selected the HR Position level.

Once this is created we have to

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

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