how to use like for variable in pl sql

SELECT hc.account_number
     , hp.party_name
     , location_id
     , acct_site.cust_acct_site_id
     ,    hpsv.address1
       || hpsv.address2
       || hpsv.address3
       || hpsv.address4 address
     , hpsv.city
     , hpsv.state
     , hpsv.postal_code
  FROM hz_cust_site_uses_all site
     , apps.hz_cust_accounts_all hc
     , hz_parties hp
     , hz_cust_acct_sites_all acct_site
     , apps.hz_party_sites_v hpsv
 WHERE site.site_use_code = 'BILL_TO'
   AND hpsv.site_use_type = site.site_use_code
   AND site.cust_acct_site_id = acct_site.cust_acct_site_id
   AND hc.party_id = hp.party_id
   AND site.org_id = acct_site.org_id
   AND acct_site.party_site_id = hpsv.party_site_id
   AND hpsv.party_id = hp.party_id
   AND site.primary_flag = 'Y'
   AND hc.cust_account_id = acct_site.cust_account_id
   AND hc.status = 'A'
   AND acct_site.status = 'A'
   AND site.status = 'A'
   AND hpsv.status = 'A'
   AND UPPER (hp.party_name) LIKE    '%' || UPPER (p_party_name) || '%'
   AND UPPER (hpsv.address1) LIKE    '%' || UPPER (p_party_address) || '%'
   AND UPPER (hpsv.city) = UPPER (p_city)
   AND UPPER (hpsv.state) = UPPER (p_state)
   AND hpsv.postal_code = p_postal_code

how to find LOV query in oracle apps form

Step 1: Find the form name (.fmb name). (Help --> About Oracle Applications)
Step 2: click on List of values and enter the filter condition and click Find button.
Step 3: Keep the screen in the above state.
Step 4: Connect to database and run the below SQL Statement.

SELECT (SELECT TO_CHAR (sql_fulltext)
          FROM v$sqlarea
         WHERE sql_id = ses.prev_sql_id)
  FROM v$session ses
     , v$sqlarea sq
 WHERE upper(ses.module) LIKE upper('%FORM_NAME%')
   AND upper(client_identifier) = upper('USER_NAME')
   AND sq.sql_id(+) = ses.sql_id;

What are built-ins used for Processing rows

GET_GROUP_ROW_COUNT(function)
GET_GROUP_SELECTION_COUNT(function)
GET_GROUP_SELECTION(function)
RESET_GROUP_SELECTION(procedure)
SET_GROUP_SELECTION(procedure)
UNSET_GROUP_SELECTION(procedure)

What are the built-ins used for Getting cell values

GET_GROUP_CHAR_CELL (function)
GET_GROUP_DATE_CELL(function)
GET_GROUP_NUMBET_CELL(function)

How to create Materialized Views in Oracle 12

There are two steps to create Materialized Views in 12.2

1.    Create Logical View

2.    Upgrade to Materialized View

Create Logical View
CREATE OR REPLACE VIEW <Custom Schema>.XXXXX_CREATE_CUSTOMER_MV#
AS
<SELECT Statement>;


Upgrade Logical View to Materialized View
exec ad_zd_mview.upgrade('<custom schema>', 'XXXXX_CREATE_CUSTOMER_MV');

How to check if the Materialized Views got created properly in 12.2

SELECT object_type, object_name
FROM dba_objects
WHERE object_name like 'XXXXX_CREATE_CUSTOMER_MV%';

-- 4 rows Selected

These 4 Rows get created in the process for

1. Logical View (Ending with '#') in Custom Schema
2. Materialized View in Custom Schema
3. Table in Custom Schema
4. Synonym in APPS

How to Refresh Materialized Views:


exec DBMS_MVIEW.REFRESH('XXXXX_CREATE_CUSTOMER_MV', method => '?',  atomic_refresh => FALSE, out_of_place => TRUE);

Parameters in detail:

Refresh Method	Parameter	Description
COMPLETE	C	Refreshes by recalculating the defining query of the materialized view.
FAST	F	Refreshes by incrementally applying changes to the materialized view.
		For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.
FAST_PCT	P	Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.
FORCE	?	Attempts a fast refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE.

Which event will be fire while Booking Sales Orders

When user press the Book button, two predefined event will be triggered.

These event can be captured by using Forms Personalization and CUSTOM.pll.

OM_PRE_BOOK_EVENT: Validation before booking the order.

OM_POST_BOOK_EVENT: some process once order is booked.

Script to generate .csv file from oracle pl sql

DECLARE

CURSOR C1 IS
SELECT e.empno
, e.ename
, TO_CHAR(e.hiredate,'DD-MON-YYYY') hiredate
, e.sal
, e.deptno
, d.dname
  FROM emp e
, dept d
 WHERE e.deptno = d.deptno;

vfile_handle UTL_FILE.file_type;
v_directory     VARCHAR2 (2000);
v_file VARCHAR2 (240);

BEGIN
dbms_output.put_line( 'Program Start');

vfile_handle := UTL_FILE.FOPEN('/usr/tmp/LR12DEV2','Emp_Data.csv','W');

UTL_FILE.PUT_LINE(vfile_handle,''
|| ',' ||''
|| ',' ||'Revenue Amortizations Functional Report'
);


UTL_FILE.NEW_LINE(vfile_handle);
UTL_FILE.NEW_LINE(vfile_handle);

UTL_FILE.PUT_LINE(vfile_handle,
  'EMPNO'   
  || ',' ||'ENAME'   
  || ',' ||'HIREDATE'
  || ',' ||'SAL'     
  || ',' ||'DEPTNO'
  || ',' ||'DNAME'
  );
 
FOR rec IN c1
LOOP
dbms_output.put_line( 'Loop Start- Empno'||rec.empno);

UTL_FILE.PUT_LINE(vfile_handle,
                      '"'||rec.empno||'"' 
  || ',' ||'"'||rec.ENAME||'"'
  || ',' ||'"'||rec.HIREDATE||'"'     
  || ',' ||'"'||rec.SAL||'"'     
  || ',' ||'"'|| rec.DEPTNO||'"' 
  || ',' ||'"'||rec.DNAME||'"'
  );
END LOOP;

UTL_FILE.FCLOSE(vfile_handle);

dbms_output.put_line( 'Program End');

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Program Exception:'||SQLERRM);
END;
/


Sample output
------------------------------------------------------------------
,,Revenue Amortizations Functional Report


EMPNO,ENAME,HIREDATE,SAL,DEPTNO,DNAME
"7782","CLARK","09-JUN-1981","2450","10","ACCOUNTING"
"7934","MILLER","23-JAN-1982","1300","10","ACCOUNTING"
"7839","KING","17-NOV-1981","5000","10","ACCOUNTING"
"7902","FORD","03-DEC-1981","3000","20","RESEARCH"
"7788","SCOTT","09-DEC-1982","3000","20","RESEARCH"
"7566","JONES","02-APR-1981","2975","20","RESEARCH"
"7369","SMITH","17-DEC-1980","800","20","RESEARCH"
"7876","ADAMS","12-JAN-1983","1100","20","RESEARCH"
"7521","WARD","22-FEB-1981","1250","30","SALES"
"7654","MARTIN","28-SEP-1981","1250","30","SALES"
"7844","TURNER","08-SEP-1981","1500","30","SALES"
"7900","JAMES","03-DEC-1981","950","30","SALES"
"7499","ALLEN","20-FEB-1981","1600","30","SALES"
"7698","BLAKE","01-MAY-1981","2850","30","SALES"

PDE-PLI018 Could Not Find Library when Opening CUSTOM.pll in Form Builder

1. Create new folder in your local machine and Copy all .pll from $AU_TOP/resource.

2. Go to system registry by using REGEDIT

 Navigate to path HKEY_LOCAL_MACHINE/SOFTWARE/WOW6432Node/Oracle/<Key for your ORACLE_HOME>/FORMS_PATH

3. Add library directory into FORMS_PATH

  i.e. C:\DevSuiteHome_1\cgenf61\admin;C:\DevSuiteHome_1\forms;C:\pll_files

4. Re-start Forms Builder and test it again.

Query to find the current program request id

select fnd_global.conc_request_id from dual;

How to check the Oracle Database Version

SELECT * FROM V$VERSION

SELECT * FROM V$INSTANCE

REP-0118 unable to create temp file while opening Report Builder

Causes: temp file directory is missing or path is incorrect in the reports_tmp registry.

regedit (start à run  à regedit)

go to hkey_local_machine  à software à WOW6432Node à oracle à KEY_DevSuiteHome1  à reports_tmp

change the directory path

How to assign Operating Unit to Responsibility

Navigation: System Administrator à Profile à System

By using "MO: Operating Unit" profile we can assign org id to responsibility

Query to find Responsibility Name from Org id

SELECT 
         frv.responsibility_name,
         fpov.profile_option_value org_id,
         hou.NAME
    FROM
         apps.fnd_profile_options_vl fpo,
         apps.fnd_responsibility_vl frv,
         apps.fnd_profile_option_values fpov,
         apps.hr_organization_units hou
   WHERE  hou.organization_id =204
     AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
     AND fpo.profile_option_id = fpov.profile_option_id
     AND fpo.user_profile_option_name = 'MO: Operating Unit'
     AND frv.responsibility_id = fpov.level_value
ORDER BY frv.responsibility_name;

Query to find Org id from Responsibility Name

 SELECT 
         frv.responsibility_name,
         hou.NAME,
         fpov.profile_option_value org_id
    FROM apps.hr_organization_units hou,
         apps.fnd_profile_options_vl fpo,
         apps.fnd_profile_option_values fpov,
         apps.fnd_responsibility_vl frv
   WHERE frv.responsibility_name = 'General Ledger Vision'
     AND fpov.level_value = frv.responsibility_id
     AND fpo.profile_option_id = fpov.profile_option_id
     AND fpo.user_profile_option_name = 'MO: Operating Unit'
     AND fpov.profile_option_id = fpo.profile_option_id
     AND hou.organization_id = TO_NUMBER (fpov.profile_option_value)
ORDER BY frv.responsibility_name

Query to convert system date to specific date a time zone

 SELECT
    TO_CHAR( (from_tz(to_timestamp(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),'CST') AT TIME ZONE 'Asia/Calcutta'),'DD-MON-YYYY HH24:MI:SS') DATE_TIME
FROM
    dual

How to Enable Personalization link for OAF Pages

Profile Name	Value
FND: Personalization Region Link Enabled	Yes
Personalize Self-Service Defn	Yes
Disable Self-Service Personal	No

AR invoice adjustment api script ( ar_adjust_pub.create_adjustment)

SET SERVEROUTPUT ON

DECLARE
    v_init_msg_list         VARCHAR2(1000);
    v_commit_flag           VARCHAR2(5) := 'F';
    v_validation_level      NUMBER(4) := fnd_api.g_valid_level_full;
    v_msg_count             NUMBER(4);
    v_msg_data              VARCHAR2(1000);
    v_return_status         VARCHAR2(5);
    v_adj_rec               ar_adjustments%rowtype;
    v_chk_approval_limits   VARCHAR2(5) := 'F';
    v_check_amount          VARCHAR2(5) := 'F';
    v_move_deferred_tax     VARCHAR2(1) := 'Y';
    v_new_adjust_number     ar_adjustments.adjustment_number%TYPE;
    v_new_adjust_id         ar_adjustments.adjustment_id%TYPE;
    v_called_from           VARCHAR2(25) := 'ADJ-API';
    v_old_adjust_id         ar_adjustments.adjustment_id%TYPE;
    l_customer_trx_id       NUMBER;
    l_payment_schedule_id   NUMBER;
    l_receivables_trx_id    NUMBER;
    l_first_char            VARCHAR2(150);
    l_second_char           VARCHAR2(150);

BEGIN
    dbms_output.put_line('Program Start');
    BEGIN
        SELECT
            a.customer_trx_id,
            b.payment_schedule_id,
            substr(interface_header_attribute2,1,instr(interface_header_attribute2,'.') - 1),
            TRIM(regexp_substr(interface_header_attribute2,'(\S*)(\s)',1,2) )
        INTO
            l_customer_trx_id,
            l_payment_schedule_id,
            l_first_char,
            l_second_char
        FROM
            ra_customer_trx_all a,
            ar_payment_schedules_all b
        WHERE
            a.customer_trx_id = b.customer_trx_id
            AND a.trx_number = '167694'
            AND a.org_id = 143;

    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Transaction Number does not exist' );
    END;

    dbms_output.put_line('l_first_char-' || l_first_char);
    dbms_output.put_line('l_second_char-' || l_second_char);
    BEGIN
        SELECT
            receivables_trx_id
        INTO l_receivables_trx_id
        FROM
            ar_receivables_trx_all
        WHERE
            name LIKE '%'
                      || l_first_char
                      || '%'
                      || l_second_char
                      || '%';-- = 'CL.5Q Admin Fee USD- AGDF';--'Adjustment-Data Migration';
  --CL.5Q Admin Fee CLP- AGDF

    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Receivable Transaction  does not exist' || 'Adjustment-Data Migration');
    END;

    dbms_output.put_line('l_receivables_trx_id-' || l_receivables_trx_id);

/*------------------------------------+
| Setting global initialization |
+------------------------------------*/
    mo_global.init('AR');
    mo_global.set_policy_context('S','143');
    fnd_global.apps_initialize(12658,50731,222,0);
/*------------------------------------+
| Setting value to input parameters |
+------------------------------------*/
--Populate v_adj_rec record
    v_adj_rec.customer_trx_id := l_customer_trx_id;
    v_adj_rec.type := 'LINE';
    v_adj_rec.payment_schedule_id := l_payment_schedule_id;
    v_adj_rec.receivables_trx_id := l_receivables_trx_id;
    v_adj_rec.associated_cash_receipt_id := 9643009;--9253005;--cash_receipt_id from AR_CASH_RECEIPTS
    v_adj_rec.associated_application_id := 18118738;--16400596;-- RECEIVABLE_APPLICATION_ID from AR_RECEIVABLE_APPLICATIONS_V
    v_adj_rec.amount :=300;--c1.AMOUNT;
    v_adj_rec.apply_date := SYSDATE;--TO_DATE (c1.apply_date);
    v_adj_rec.gl_date := SYSDATE;--TO_DATE (c1.gl_date);
    v_adj_rec.created_from := 'ADJ-API';
 
 ar_adjust_pub.create_adjustment
    ('AR_ADJUST_PUB',1.0,
    v_init_msg_list,
    v_commit_flag,
    v_validation_level,
    v_msg_count,
    v_msg_data
    ,v_return_status
    ,v_adj_rec,
    v_chk_approval_limits,
    v_check_amount,
    v_move_deferred_tax,
    v_new_adjust_number,
    v_new_adjust_id,
    v_called_from,
    v_old_adjust_id
    );

    dbms_output.put_line('v_msg_count-' || v_msg_count);
    dbms_output.put_line('v_msg_data-' || v_msg_data);
    dbms_output.put_line('v_new_adjust_number-' || v_new_adjust_number);
    dbms_output.put_line('v_new_adjust_id-' || v_new_adjust_id);
    dbms_output.put_line('v_init_msg_list-' || v_init_msg_list);
    dbms_output.put_line('Successfully Completed-' || v_return_status);

    FOR i IN 1..v_msg_count LOOP
        dbms_output.put_line(i
                               || '. '
                               || substr(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,255) );
    END LOOP;

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        FOR i IN 1..v_msg_count LOOP
            dbms_output.put_line(v_msg_data);
            dbms_output.put_line(v_new_adjust_number);
            dbms_output.put_line(v_new_adjust_id);
            dbms_output.put_line(v_init_msg_list);
        END LOOP;
END;
/



Query to find out Adjustment amount
-----------------------------------------------------
select nvl(sum(amount),0)
into v_adjustments_amt
FROM
    ar_adjustments_v
    where customer_trx_id = p_invoice_id;
------------------------------------------------------

FNDLOAD Script for DFF

--Download
FNDLOAD apps/pwd 0 Y DOWNLOAD @FND:patch/115/import/afffload.lct okl_k_lines_dff.ldt DESC_FLEX APPLICATION_SHORT_NAME="OKL" DESCRIPTIVE_FLEXFIELD_NAME=OKL_K_LINES_DF


--Upload
FNDLOAD apps/pwd 0 Y UPLOAD @FND:patch/115/import/afffload.lct okl_k_lines_dff.ldt

fdfcmp apps/pwd 0 Y D DESC_FLEX APPLICATION_SHORT_NAME="OKL" OKL_K_LINES_DF

FNDLOAD Script for Request Set

--Download
FNDLOAD apps/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct DAILY_AUTOMATION_SET2.ldt REQ_SET REQUEST_SET_NAME='DAILY_AUTOMATION_SET2'

FNDLOAD apps/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct DAILY_AUTOMATION_LNK.ldt REQ_SET_LINKS REQUEST_SET_NAME='DAILY_AUTOMATION_SET2'


--Upload
FNDLOAD apps/pwd 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  DAILY_AUTOMATION_SET2.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD apps/holiday5 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  DAILY_AUTOMATION_LNK.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Script for Concurrent Program

--Download
FNDLOAD apps/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct VAT_INVOICE_PRINTING.ldt PROGRAM APPLICATION_SHORT_NAME="AR" CONCURRENT_PROGRAM_NAME="VAT_INVOICE_PRINTING"


--Upload
FNDLOAD apps/pwd 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct VAT_INVOICE_PRINTING.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Script for Value Set

--Download
$FND_TOP/bin/FNDLOAD apps/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct CONTRACT_NUMBER.ldt VALUE_SET FLEX_VALUE_SET_NAME="CONTRACT_NUMBER"


--Upload
$FND_TOP/bin/FNDLOAD apps/pwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct CONTRACT_NUMBER.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

API to Assign External Bank Account to Supplier (iby_disbursement_setup_pub.set_payee_instr_assignment))

DECLARE
    l_rec_pmt_int_asig    iby_fndcpt_setup_pub.pmtinstrassignment_rec_type := NULL;
    l_rec_payeecontext    iby_disbursement_setup_pub.payeecontext_rec_type := NULL;
    l_num_party_id        NUMBER;
    l_num_vendor_id       NUMBER;
    l_num_assign_id       NUMBER;
    l_num_acct_id         NUMBER;
    l_num_branch_id       NUMBER;
    l_chr_return_status   VARCHAR2(200) := NULL;
    l_num_msg_count       NUMBER := 0;
    l_chr_msg_data        VARCHAR2(200) := NULL;
    l_rec_response        iby_fndcpt_common_pub.result_rec_type := NULL;
    l_msg                 VARCHAR2(2000);
BEGIN
    dbms_output.put_line('program start');
    l_rec_pmt_int_asig.priority := 1;
    l_rec_pmt_int_asig.start_date := SYSDATE;
    l_rec_pmt_int_asig.instrument.instrument_type := 'BANKACCOUNT';
    l_rec_pmt_int_asig.instrument.instrument_id := 224771;--iby_ext_bank_accounts_v.EXT_BANK_ACCOUNT_ID
    l_rec_payeecontext.party_id := 12182106;--l_num_party_id; --ap_suppliers.party_id
    l_rec_payeecontext.payment_function := 'PAYABLES_DISB';
    l_rec_payeecontext.org_type := 'OPERATING_UNIT';
    l_rec_payeecontext.org_id := 142;
    l_rec_payeecontext.party_site_id :=10582852;--ap_supplier_sites_all.party_site_id
    l_rec_payeecontext.supplier_site_id :=1867031;--ap_supplier_sites_all.vendor_site_id
    /*l_rec_payeecontext.org_type := NULL;--'OPERATING_UNIT';
    l_rec_payeecontext.org_id := NULL;--142;--82;
    l_rec_payeecontext.party_site_id := NULL;--10576832;--site_rec.party_site_id;
    l_rec_payeecontext.supplier_site_id := NULL;--1857039;--site_rec.vendor_site_id; */
    dbms_output.put_line('api start');
 
    iby_disbursement_setup_pub.set_payee_instr_assignment
    (p_api_version => 1,
    p_init_msg_list => fnd_api.g_true,
    p_commit => fnd_api.g_true,
    x_return_status=> l_chr_return_status,
    x_msg_count => l_num_msg_count,
    x_msg_data => l_chr_msg_data,
    p_payee => l_rec_payeecontext,
    p_assignment_attribs => l_rec_pmt_int_asig,
    x_assign_id => l_num_assign_id,
    x_response => l_rec_response);

    COMMIT;
    dbms_output.put_line('l_chr_return_status' || l_chr_return_status);
    dbms_output.put_line('l_num_msg_count.' || l_num_msg_count);
    dbms_output.put_line('l_chr_msg_data.' || l_chr_msg_data);
    dbms_output.put_line('l_num_assign_id' || l_num_assign_id);
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('main exception.');
END;


Script output
------------------------------
program start
api start
l_chr_return_statusS
l_num_msg_count.1
l_chr_msg_data.IBY
l_num_assign_id:10239388


Query
-----------------------------
select * from IBY_PMT_INSTR_USES_ALL where
--INSTRUMENT_ID  =224771     
INSTRUMENT_PAYMENT_USE_ID=10239388


Note: Record will be inserted into IBY_PMT_INSTR_USES_ALL table.

Assign bank to supplier level then pass below parameters to NULL.
l_rec_payeecontext.org_type := NULL;
l_rec_payeecontext.org_id := NULL;
l_rec_payeecontext.party_site_id := NULL;
l_rec_payeecontext.supplier_site_id := NULL;

How To run Environment file

bash
ls -1

. EBSapps.env run

Unix Command to find file in Directory and Subdirectories

find . -name SupplierSiteNameValidator.java

API to Create External Bank Account (IBY_EXT_BANKACCT_PUB.create_ext_bank_acct)

DECLARE
    x_acct_id         NUMBER;
    x_response_rec    iby_fndcpt_common_pub.result_rec_type;
    x_return_status   VARCHAR2(10);
    x_msg_count       NUMBER;
    x_msg_data        VARCHAR2(256);
    x_bank_acct_rec   iby_ext_bankacct_pub.extbankacct_rec_type;
BEGIN
    dbms_output.put_line('Program Start');
    x_bank_acct_rec.country_code := 'IN';
    x_bank_acct_rec.branch_id := 12182104;--ce_bank_branches_v.branch_party_id
    x_bank_acct_rec.bank_id := 12182101;--ce_bank_branches_v.bank_party_id
    x_bank_acct_rec.acct_owner_party_id := 12182106;--ap_suppliers.party_id
    x_bank_acct_rec.currency := 'INR';
    x_bank_acct_rec.bank_account_name := 'Sandy Test Account';---new bank account name
    x_bank_acct_rec.bank_account_num := 999999999;--23593 ;--new bank account number
    x_msg_count := 0;
    x_msg_data := NULL;
    x_return_status := NULL;
 
    iby_ext_bankacct_pub.create_ext_bank_acct
    (p_api_version => 1.0,
    p_init_msg_list => fnd_api.g_true,
    p_ext_bank_acct_rec => x_bank_acct_rec,
    x_acct_id => x_acct_id,
    x_return_status => x_return_status,
    x_msg_count => x_msg_count,
    x_msg_data => x_msg_data,
    x_response => x_response_rec
    );

    COMMIT;
    dbms_output.put_line('External bank account created.');
    dbms_output.put_line('x_acct_id:' || x_acct_id);
    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);
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('main exception-IBY_EXT_BANKACCT_PUB.create_ext_bank_acct.');
END;


Script Output
---------------------------------
Program Start
External bank account created.
x_acct_id224771
x_return_statusS
x_msg_count.0
x_msg_data.


Query to find the record
------------------------------------
SELECT * FROM iby_ext_bank_accounts_v where EXT_BANK_ACCOUNT_ID =224771

API to Create Supplier Site (pos_vendor_pub_pkg.create_vendor_site)

DECLARE

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status     VARCHAR2(10);
l_msg_count     NUMBER;
l_msg_data  VARCHAR2(1000);
l_vendor_site_id    NUMBER;
l_party_site_id     NUMBER;
l_location_id   NUMBER;

BEGIN

--Required
l_vendor_site_rec.vendor_id :='1737017';
l_vendor_site_rec.vendor_site_code := 'Sandy_Supp_001';
l_vendor_site_rec.address_line1 := 'Sandy_Supplier_001';
l_vendor_site_rec.country := 'IN';
l_vendor_site_rec.org_id := '142';
l_vendor_site_rec.purchasing_site_flag:='Y';
l_vendor_site_rec.pay_site_flag :='Y';
l_vendor_site_rec.rfq_only_site_flag :='N';

pos_vendor_pub_pkg.create_vendor_site
(
p_vendor_site_rec => l_vendor_site_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_site_id => l_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id
);

COMMIT;

dbms_output.put_line('return_status: '||l_return_status);
dbms_output.put_line('msg_data: '||l_msg_data);
dbms_output.put_line('vendor_site_id: '||l_vendor_site_id);
dbms_output.put_line('party_site_id: '||l_party_site_id);
dbms_output.put_line('l_location_id: '||l_location_id);

END;

Script Output

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

return_status: S

msg_data: 

vendor_site_id: 1867031

party_site_id: 10582852

l_location_id: 362546

Query

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

select * from ap_supplier_sites_all where vendor_id = 1737017;

API to Create Supplier (pos_vendor_pub_pkg.create_vendor)

DECLARE
    l_vendor_rec      ap_vendor_pub_pkg.r_vendor_rec_type;
    l_return_status   VARCHAR2(10);
    l_msg_count       NUMBER;
    l_msg_data        VARCHAR2(1000);
    l_vendor_id       NUMBER;
    l_party_id        NUMBER;
BEGIN

--Required
    l_vendor_rec.segment1 := NULL;--'10001'; --ID
    l_vendor_rec.vendor_name := 'Sandy Test Supplier'; --Supplier Name
---l_vendor_rec.party_id          := 115422;
    l_vendor_rec.jgzz_fiscal_code := 'ABUFS3022M';
    l_vendor_rec.tax_reference := '09753805106C';
    l_vendor_rec.start_date_active := SYSDATE;
    l_vendor_rec.invoice_currency_code := 'INR';
    l_vendor_rec.payment_currency_code := 'INR';
    l_vendor_rec.pay_group_lookup_code := 'CUSTOMER';
  --  l_vendor_rec.EMPLOYEE_ID := 18635;
   
pos_vendor_pub_pkg.create_vendor
    (p_vendor_rec => l_vendor_rec,
    x_return_status => l_return_status,
    x_msg_count => l_msg_count,
    x_msg_data => l_msg_data,
    x_vendor_id => l_vendor_id,
    x_party_id => l_party_id);

    COMMIT;
    dbms_output.put_line('return_status: ' || l_return_status);
    dbms_output.put_line('msg_data: ' || l_msg_data);
    dbms_output.put_line('vendor_id: ' || l_vendor_id);
    dbms_output.put_line('party_id: ' || l_party_id);
END;



Script Output
--------------------------
return_status: S
msg_data:
vendor_id: 1737017
party_id: 12182106

Query:

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

select aps.Party_id, aps.* from ap_suppliers aps where vendor_id =1737017

API to Create Bank Branch (iby_ext_bankacct_pub.create_ext_bank_branch)

DECLARE
    p_api_version           NUMBER := 1.0;
    p_init_msg_list         VARCHAR2(1) := 'F';
    l_bank_id               NUMBER := 12182101;--530705;
    x_return_status         VARCHAR2(2000);
    x_msg_count             NUMBER(5);
    x_msg_data              VARCHAR2(2000);
    x_branch_id             NUMBER;
    p_count                 NUMBER;
    x_response              iby_fndcpt_common_pub.result_rec_type;
    p_ext_bank_branch_rec   iby_ext_bankacct_pub.extbankbranch_rec_type;
BEGIN
    dbms_output.put_line('Program Start');
    p_ext_bank_branch_rec.bch_object_version_number := 1.0;
    p_ext_bank_branch_rec.branch_name := 'Bank Test Branch';
    p_ext_bank_branch_rec.branch_type := 'ABA';
    p_ext_bank_branch_rec.bank_party_id := l_bank_id;
   
iby_ext_bankacct_pub.create_ext_bank_branch(
    p_api_version => p_api_version,
    p_init_msg_list => p_init_msg_list,
    p_ext_bank_branch_rec => p_ext_bank_branch_rec,
    x_branch_id => x_branch_id,
    x_return_status => x_return_status,
    x_msg_count => x_msg_count,
    x_msg_data => x_msg_data,
    x_response => x_response);

    dbms_output.put_line('x_branch_id: ' || x_branch_id);
    dbms_output.put_line('x_return_status: ' || x_return_status);
    IF
        ( x_msg_count = 1 )
    THEN
        dbms_output.put_line('x_msg_data ' || x_msg_data);
    ELSIF ( x_msg_count > 1 ) THEN
        LOOP
            p_count := p_count + 1;
            x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);
            IF
                ( x_msg_data IS NULL )
            THEN
                EXIT;
            END IF;
            dbms_output.put_line('Message:' || p_count|| ' ---' || x_msg_data);
        END LOOP;
    END IF;

    COMMIT;
    dbms_output.put_line('Program End');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        dbms_output.put_line('Main Exception Error:'||sqlerrm);
END;
/


Script output
---------------------------------------
Program Start
x_branch_id: 12182104
x_return_status: S
Program End



Query to check
------------------------------
select * from ce_bank_branches_v where BRANCH_PARTY_ID=12182104

select * from iby_ext_bank_branches_v where BRANCH_PARTY_ID=12182104

API to Create Bank (iby_ext_bankacct_pub.create_ext_bank)

DECLARE
    l_output          VARCHAR2(4000);
    lc_msg_dummy       VARCHAR2(3000);
    lc_return_status   VARCHAR2(3000);
    lc_msg_data        VARCHAR2(3000);
    ln_bank_id         NUMBER;
    ln_msg_count       NUMBER;
    lr_extbank_rec     apps.iby_ext_bankacct_pub.extbank_rec_type;
    lr_response_rec    apps.iby_fndcpt_common_pub.result_rec_type;
BEGIN
    dbms_output.put_line('Progam start');
    --lc_return_status := '';
    --ln_msg_count := '';
    --lc_msg_data := '';
    lr_extbank_rec.bank_name := 'Bank Test';
    lr_extbank_rec.bank_number := 'BT0001';
    lr_extbank_rec.country_code := 'IN';

    iby_ext_bankacct_pub.create_ext_bank
    ( 
    p_api_version => 1.0,
    p_init_msg_list => fnd_api.g_true,
    p_ext_bank_rec => lr_extbank_rec,
    x_bank_id => ln_bank_id,
    x_return_status => lc_return_status,
    x_msg_count => ln_msg_count,
    x_msg_data => lc_msg_data,
    x_response => lr_response_rec
    );

dbms_output.put_line('ln_bank_id:' || ln_bank_id);
dbms_output.put_line('lc_return_status:' || lc_return_status);
    l_output := ' ';
    IF
        ( lc_return_status <> 'S' )
    THEN
        FOR i IN 1..ln_msg_count LOOP
            apps.fnd_msg_pub.get(i,apps.fnd_api.g_false,lc_msg_data,lc_msg_dummy);

            l_output := l_output || ( TO_CHAR(i) || ': ' || substr(lc_msg_data,1,250) );
        END LOOP;
        dbms_output.put_line('l_output:' || l_output);
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        dbms_output.put_line('Main Exception Error:'||sqlerrm);
END;
/



Script Output
-------------------------------------
Progam start
ln_bank_id:12182101
lc_return_status:S

Query to Check
-------------------------------------
select * from CE_BANKS_V  WHERE BANK_PARTY_ID=12182101
     
select * FROM iby_ext_banks_v WHERE BANK_PARTY_ID=12182101