API Supplier Site Creation

API For supplier/vendor site creation in oracle r12 

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;

CURSOR C1 IS

--select * from XXPCL_SUPPLIER_LIB_PRE_ACT_V
--where 1=1
--and org_id = 141
----and  VENDOR_TYPE = '3RD PARTY'
----and  VENDOR_TYPE = 'CONSULTANT'
----and  VENDOR_TYPE  = 'VENDOR'
----and  VENDOR_TYPE  = 'EMPLOYEE'
--and  VENDOR_TYPE = 'TAX AUTHORITY'


/* 3RD PARTY   and  VENDOR  */
select '451075' VENDOR_ID,'PFCCL' SITE_CODE ,
'.'SITE_ADDRESS_LINE_1,
NULL SITE_ADDRESS_LINE_2,
NULL SITE_ADDRESS_LINE_3,
NULL SITE_CITY_TOWN,
NULL SITE_STATE,
NULL  SITE_PIN,
'141' OPERATING_UNIT,
'385665' LIABILITY_ACCOUNT,
'385633' PREPAYMENT_ACCOUNT
from dual;

/* EMPLOYEE */
--select '3763' VENDOR_ID,'OFFICE' SITE_CODE ,
--'.'SITE_ADDRESS_LINE_1,
--NULL SITE_ADDRESS_LINE_2,
--NULL SITE_ADDRESS_LINE_3,
--NULL SITE_CITY_TOWN,
--NULL SITE_STATE,
--NULL  SITE_PIN,
--'141' OPERATING_UNIT,
--'385660' LIABILITY_ACCOUNT,
--'385889' PREPAYMENT_ACCOUNT
--from dual;

/* CONSULTANT */
--select '401998' VENDOR_ID,'PFCCL' SITE_CODE ,
--'.'SITE_ADDRESS_LINE_1,
--NULL SITE_ADDRESS_LINE_2,
--NULL SITE_ADDRESS_LINE_3,
--NULL SITE_CITY_TOWN,
--NULL SITE_STATE,
--NULL  SITE_PIN,
--'141' OPERATING_UNIT,
--'385071' LIABILITY_ACCOUNT,
--'385633' PREPAYMENT_ACCOUNT
--from dual;

--select *
--from XXPCL_SUP_STAGING_TBL A
--where  1=1
----AND A.SUPPLIER_NAME= 'BSES Rajdhani Power Limited.'
--and exists ( select * from ap_suppliers Z where  Z.VENDOR_ID =  A.VENDOR_ID)
--and not exists ( select * from ap_supplier_sites_all X where X.VENDOR_ID =  A.VENDOR_ID and X.VENDOR_SITE_ID = A.VENDOR_SITE_ID) ;


BEGIN

  fnd_global.apps_initialize(0, 51572, 200);

  FOR I IN C1 LOOP

--Required
l_vendor_site_rec.vendor_id :=I.VENDOR_ID;
l_vendor_site_rec.vendor_site_code := I.SITE_CODE;
l_vendor_site_rec.address_line1 :=I.SITE_ADDRESS_LINE_1;
l_vendor_site_rec.address_line2 := I.SITE_ADDRESS_LINE_2;
l_vendor_site_rec.address_line3 :=I.SITE_ADDRESS_LINE_3;
--l_vendor_site_rec.address_line4 :=I.SITE_ADDRESS_LINE_4;
--l_vendor_site_rec.ADDRESS_LINES_ALT :=I.SITE_ROOM;
l_vendor_site_rec.CITY := I.SITE_CITY_TOWN;
l_vendor_site_rec.STATE :=I.SITE_STATE;
l_vendor_site_rec.ZIP :=I.SITE_PIN;
l_vendor_site_rec.country := 'IN';
l_vendor_site_rec.org_id := I.OPERATING_UNIT;
l_vendor_site_rec.ATTRIBUTE_CATEGORY:='Vendor Site Ref';
l_vendor_site_rec.ATTRIBUTE10 := I.SITE_CODE;
l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID:= I.LIABILITY_ACCOUNT; -- Libility--
l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID :=I.PREPAYMENT_ACCOUNT; -- Prepayment--

--Optional

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';
l_vendor_site_rec.MATCH_OPTION :='R';
--l_vendor_site_rec.phone := '9876543210';

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('location_id: '||l_location_id);

--UPDATE XXPCL_SUP_STAGING_TBL
--set VENDOR_SITE_ID = l_vendor_site_id,
--PARTY_SITE_ID = l_party_site_id,
--LOCATION_ID = l_location_id
--where VENDOR_ID = I.VENDOR_ID
--and VENDOR_STAGING_ID = I.VENDOR_STAGING_ID;

COMMIT;

END LOOP;

END;


  --POS_VENDOR_PUB_PKG.Update_Vendor_Site
  --POS_VENDOR_PUB_PKG.Create_Vendor_Contact

API Supplier Creation

How to create API For supplier/vendor creation in r12

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;

CURSOR C1 IS

select VENDOR_NAME SUPPLIER_NAME, VENDOR_TYPE SUPPLIER_TYPE
from (
SELECT 'WIPRO' VENDOR_NAME, 'VENDOR' VENDOR_TYPE FROM DUAL
--SELECT 'HELLO' VENDOR_NAME, '3RD PARTY' VENDOR_TYPE FROM DUAL
--SELECT 'HELLO' VENDOR_NAME, 'CONSULTANT' VENDOR_TYPE FROM DUAL
--SELECT 'HELLO' VENDOR_NAME, 'EMPLOYEE' VENDOR_TYPE FROM DUAL
--SELECT 'Ashok L Pathak' VENDOR_NAME, 'EMPLOYEE' VENDOR_TYPE FROM DUAL
--SELECT 'OMM NAMAH SHIVAYA TOURS '||CHR(38)||' TRAVELS' VENDOR_NAME, 'VENDOR' VENDOR_TYPE FROM DUAL  /*  &  */
--SELECT 'OMM NAMAH SHIVAYA TOURS '||CHR(39)||' TRAVELS' VENDOR_NAME, 'VENDOR' VENDOR_TYPE FROM DUAL  /*  '   */
);

--select * from XXPCL_SUP_STAGING_TBL A
--where  1=1
----AND A.SUPPLIER_NAME= 'R Rahman'
--and not exists ( select 1 from ap_suppliers Z where  TRIM(UPPER(Z.VENDOR_NAME)) =  TRIM(UPPER(A.SUPPLIER_NAME)));

--update ap_suppliers
--set vendor_name = REPLACE(vendor_name, CHR(13), '')
--where upper(vendor_name) likE '%MONICA JASSI%'

--select * from ap_suppliers where VENDOR_TYPE_LOOKUP_CODE like 'EMPLOYEE%'
--
--select 'STD', VENDOR_NAME, length(TRIM(VENDOR_NAME))  from ap_suppliers
--where upper(vendor_name) likE '%BINOD%%'
--union
--select 'CST', SUPPLIER_NAME, length(TRIM(SUPPLIER_NAME))  from XXPCL_SUP_STAGING_TBL A
--where upper(SUPPLIER_NAME) likE '%BINOD KUMAR ROY%'

BEGIN

--select * from fnd_responsibility_tl where RESPONSIBILITY_NAME like 'WIPRO%Resource%'

  fnd_global.apps_initialize(0, 51572, 200);

FOR I IN C1 LOOP

--Required
--l_vendor_rec.segment1 := '00002359'; --ID
l_vendor_rec.vendor_name := I.SUPPLIER_NAME;

--Optional
l_vendor_rec.VENDOR_TYPE_LOOKUP_CODE:= I.SUPPLIER_TYPE;
l_vendor_rec.ATTRIBUTE_CATEGORY := 'Vendor Categories';
l_vendor_rec.ATTRIBUTE4 :='COMMMISION';
l_vendor_rec.TERMS_DATE_BASIS := 'Invoice';
l_vendor_rec.match_option:='R' ;

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||SQLERRM);
dbms_output.put_line('msg_data: '||l_msg_data);
dbms_output.put_line('vendor_id: '||l_vendor_id);
dbms_output.put_line('vendor_Name: '||I.SUPPLIER_NAME);
dbms_output.put_line('party_id: '||l_party_id);

--UPDATE XXPCL_SUP_STAGING_TBL
--SET VENDOR_ID = l_vendor_id
--WHERE VENDOR_STAGING_ID = I.VENDOR_STAGING_ID;

COMMIT;

END LOOP;

END;

Package in Oracle

What is Package?  What are the benefits?  How to create /used package:-

      Package are named PL/SQL Blocks and data base schema object

      Package stored into database schema permanently.

      Package complied and stored as a database object.

      Package can be referenced or reused by your program.

IN SIMPLE WAY

               Package is collection of Procedure, Function, Cursor, Type and Variable.

               Related of function and procedure are grouping in single unit that is called Package.

Benefits of Package:-

1.       Package support to overloading.

2.       Package support to hidden.

3.       Easy to manage.

4.       Improve performance.

Components of Packages

    PL/SQL package has two components.

1.       Package Specification

2.       Package Body

Package Specification:-

Package specification collection of declaration of all the public variables, cursors, objects, procedures, functions, and exception

Or

Package specification contain of declaration:-

1.       Declaration of procedure.

2.       Declaration of function.

3.       Declaration of Variables

4.       Declaration of Cursor.

Package Body:-

Package body is containing of definitions:-

1.       Procedure definition

2.       Function definition

3.       Cursor definition  

Package specification example:-

CREATE OR REPLACE package APPS.TECHNICAL_ISRAR_PKG

is

function F_ISRAR RETURN VARCHAR2;

PROCEDURE PROC_EMP (A  NUMBER, B  VARCHAR2 ,C VARCHAR2);

END TECHNICAL_ISRAR_PKG;

/

Package body example:-

CREATE OR REPLACE PACKAGE BODY APPS.TECHNICAL_ISRAR_PKG

IS

   FUNCTION F_ISRAR  RETURN VARCHAR2

   IS

   BEGIN

      RETURN 'techisrar.blogspot.com';

   END F_ISRAR;

   PROCEDURE proc_emp (A  NUMBER, B  VARCHAR2,C  VARCHAR2)

   IS

   BEGIN

      INSERT INTO EMP (EMPNO, ENAME ,SAL) VALUES (A,B, C);     

   END PROC_EMP;

END TECHNICAL_ISRAR_PKG;

/

Note:- EMP IS Table Name

SELECT * FROM EMP 

OUTPUT

EXECUTE THE FUNCTION 

BEGIN

DBMS_OUTPUT.PUT_LINE(TECHNICAL_ISRAR_PKG.F_ISRAR);

END;

Output with screenshot

 2. EXECUTE THE PROCEDURE

BEGIN

TECHNICAL_ISRAR_PKG.PROC_EMP(555,'TECHISRAR','DELHI');

END;

Output :-

Link Cash Receipt table and Transaction table

How to find the link between AR_CASH_RECEIPTS_ALL AND RA_CUSTOMER_TRX_ALL

Simple query find the transaction number(ar) from receipt number(ar)

SELECT  ACR.RECEIPT_NUMBER RECEIPT_NO, RCT.TRX_NUMBER INVOICE_NO
  FROM   AR_RECEIVABLE_APPLICATIONS_ALL ARA,
                 AR_CASH_RECEIPTS_ALL ACR,
                 RA_CUSTOMER_TRX_ALL RCT
 WHERE     ARA.STATUS = 'APP'
                    AND ARA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
                    AND ARA.APPLIED_CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID

PL/SQL Oracle Report

How to Create the PL/SQL Oracle Report :-

Report Name               :- Wipro Supplier Data Report.
Shore Name                 :- XXPCL_VENDOR_DETAILS_PROC
Procedure Name           :-  XXPCL_VENDOR_DETAILS_PROC

Benefit of PL/SQL REPORT:-
 No required Report builder tool
 No required RDF.
 No Required WINSCP Tool.

Follow Step By Step :- 
Firstly open the Oracle Toad Tool then write the Procedure given below :-

-----------------------code start --------------------------------------

CREATE OR REPLACE PROCEDURE APPS.XXPCL_VENDOR_DETAILS_PROC1(
RETCODE OUT VARCHAR2,
ERRBUF OUT VARCHAR2,
P_ORG_ID                      IN VARCHAR2,
P_VENDOR_ID         IN VARCHAR2
)
AS
P_V_COUNT  NUMBER;

--*==================================================+
--| PACKAGE XXPCL_VENDOR_DETAILS_PROC
--+--------------------------------------------------------------------------+
--| DESCRIPTION This package is used to designed for xml for L trial
--|           
--+--------------------------------------------------------------------------+
--| HISTORY
--| Author              | Date              | DESCRIPTION
--+--------------------------------------------------------------------------+
--| Creaede by | 13-AUG-2018| 1.0
-- technical.israr@gmail.com

--+==================================================*/

CURSOR C1 IS 

SELECT DISTINCT APS.VENDOR_ID,
                  ASSA.VENDOR_SITE_ID,
                  APS.VENDOR_NAME,
                  HR.NAME ORG_NAME,
                  APS.SEGMENT1 VENDOR_NUM,
                  APS.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE,
                  ASSA.VENDOR_SITE_CODE VENDOR_SITE,
                  ASSA.ATTRIBUTE13 GST_STATUS,
                  ASSA.ATTRIBUTE12 GST_NO,
                  PAN_NO,
                  ASSA.ADDRESS_LINE1,
                  ASSA.ADDRESS_LINE2,
                  ASSA.ADDRESS_LINE3,
                  ASSA.CITY,
                  ASSA.STATE,
                  ASSA.ZIP,
                  ASSA.EMAIL_ADDRESS,
                  ASSA.PHONE PHONE_NO,
                  IEBA.BANK_NAME,
                  IEBA.BANK_ACCOUNT_NUMBER,
                  IEBA.BRANCH_NUMBER,
                  IEBA.BANK_NUMBER,
                  IEBA.DESCRIPTION,
                  IEBA.BANK_BRANCH_NAME
    FROM APPS.AP_SUPPLIERS APS,
         APPS.AP_SUPPLIER_SITES_ALL ASSA,
         APPS.IBY_PAYEE_ASSIGNED_BANKACCT_V IPAB,
         APPS.IBY_ACCOUNT_OWNERS IAO,
         APPS.IBY_EXT_BANK_ACCOUNTS_V IEBA,
         APPS.HR_OPERATING_UNITS HR,
         APPS.JAI_AP_TDS_VENDOR_HDRS JAI
   WHERE     1 = 1
         AND APS.VENDOR_ID = ASSA.VENDOR_ID
         AND ASSA.ORG_ID = HR.ORGANIZATION_ID
         --AND HR.SET_OF_BOOKS_ID = 2121
         AND ASSA.ORG_ID = NVL (P_ORG_ID, ASSA.ORG_ID)  --organiztion paramerter
          AND APS.VENDOR_ID = NVL(P_VENDOR_ID,APS.VENDOR_ID)   --vendor paramerts
--AND APS.VENDOR_ID IN (1383,376968,358955,440027,416013,358955)
         AND JAI.VENDOR_SITE_ID(+) = ASSA.VENDOR_SITE_ID
         AND ASSA.VENDOR_SITE_ID = IPAB.SUPPLIER_SITE_ID
         AND APS.PARTY_ID = ipab.party_id
         AND IPAB.EXT_BANK_ACCOUNT_ID = IEBA.EXT_BANK_ACCOUNT_ID
         AND APS.END_DATE_ACTIVE IS NULL
         AND ASSA.INACTIVE_DATE IS NULL
         AND IEBA.EXT_BANK_ACCOUNT_ID(+) = IAO.EXT_BANK_ACCOUNT_ID
         AND IAO.ACCOUNT_OWNER_PARTY_ID(+) = APS.PARTY_ID
         AND IAO.END_DATE IS NULL
         AND IEBA.END_DATE IS NULL
         AND IPAB.END_DATE IS NULL
ORDER BY 3;


BEGIN
      fnd_file.put_line (fnd_file.output, '<?xml version="1.0"?>');
      fnd_file.put_line (fnd_file.output, '<PCL_VD_DT>');
 

      FOR I IN C1 LOOP
         fnd_file.put_line (fnd_file.output, '<VD_HEADER>');
         fnd_file.put_line (fnd_file.output,'<VENDOR_ID>' || I.VENDOR_ID || '</VENDOR_ID>' );
         fnd_file.put_line (fnd_file.output,'<VENDOR_SITE_ID>' || I.VENDOR_SITE_ID || '</VENDOR_SITE_ID>' );
       
         fnd_file.put_line (fnd_file.output,'<VENDOR_NAME>' || trim(REPLACE (I.VENDOR_NAME, '&', '&' || 'amp;')) || '</VENDOR_NAME>' );
         fnd_file.put_line (fnd_file.output,'<ORG_NAME>' || trim(REPLACE (I.ORG_NAME, '&', '&' || 'amp;')) || '</ORG_NAME>' );
         fnd_file.put_line (fnd_file.output,'<VENDOR_NUM>' || I.VENDOR_NUM || '</VENDOR_NUM>' );
         fnd_file.put_line (fnd_file.output,'<VENDOR_TYPE>' ||  trim(REPLACE (I.VENDOR_TYPE, '&', '&' || 'amp;'))  || '</VENDOR_TYPE>' );
        -- fnd_file.put_line (fnd_file.output,'<VENDOR_SITE>' || I.VENDOR_SITE || '</VENDOR_SITE>' );
       
         fnd_file.put_line (fnd_file.output,'<VENDOR_SITE>' ||  trim(REPLACE (I.VENDOR_SITE, '&', '&' || 'amp;'))  || '</VENDOR_SITE>' );     
       
         fnd_file.put_line (fnd_file.output,'<PAN_NO>' || I.PAN_NO || '</PAN_NO>' );
         fnd_file.put_line (fnd_file.output,'<ADDRESS_LINE1>' ||  trim(REPLACE (I.ADDRESS_LINE1, '&', '&' || 'amp;'))  || '</ADDRESS_LINE1>' );
         fnd_file.put_line (fnd_file.output,'<ADDRESS_LINE2>' ||  trim(REPLACE (I.ADDRESS_LINE2, '&', '&' || 'amp;'))  || '</ADDRESS_LINE2>' );
         fnd_file.put_line (fnd_file.output,'<ADDRESS_LINE3>' ||  trim(REPLACE (I.ADDRESS_LINE3, '&', '&' || 'amp;'))  || '</ADDRESS_LINE3>' );
         fnd_file.put_line (fnd_file.output,'<CITY>' || I.CITY || '</CITY>' );
         fnd_file.put_line (fnd_file.output,'<STATE>' ||  trim(REPLACE (I.STATE, '&', '&' || 'amp;'))  || '</STATE>' );
                   
         fnd_file.put_line (fnd_file.output,'<EMAIL_ADDRESS>' ||  trim(REPLACE (I.EMAIL_ADDRESS, '&', '&' || 'amp;'))  || '</EMAIL_ADDRESS>' );
         fnd_file.put_line (fnd_file.output,'<PHONE_NO>' || I.PHONE_NO || '</PHONE_NO>' );
         fnd_file.put_line (fnd_file.output,'<BANK_NAME>' ||  trim(REPLACE (I.BANK_NAME, '&', '&' || 'amp;'))  || '</BANK_NAME>' );
         fnd_file.put_line (fnd_file.output,'<BANK_ACCOUNT_NUMBER>' ||  trim(REPLACE (I.BANK_ACCOUNT_NUMBER, '&', '&' || 'amp;'))  || '</BANK_ACCOUNT_NUMBER>' );
         fnd_file.put_line (fnd_file.output,'<BRANCH_NUMBER>' ||  trim(REPLACE (I.BRANCH_NUMBER, '&', '&' || 'amp;'))  || '</BRANCH_NUMBER>' );
         fnd_file.put_line (fnd_file.output,'<BANK_NUMBER>' ||  trim(REPLACE (I.BANK_NUMBER, '&', '&' || 'amp;'))  || '</BANK_NUMBER>' );
         fnd_file.put_line (fnd_file.output,'<DESCRIPTION>' || I.DESCRIPTION || '</DESCRIPTION>' );
         fnd_file.put_line (fnd_file.output,'<BANK_BRANCH_NAME>' ||  trim(REPLACE (I.BANK_BRANCH_NAME, '&', '&' || 'amp;'))  || '</BANK_BRANCH_NAME>' );
         fnd_file.put_line (fnd_file.output,'<PHONE_NO>' || I.PHONE_NO || '</PHONE_NO>' );
     P_V_COUNT:=1;
              fnd_file.put_line (fnd_file.output, '</VD_HEADER>');
      END LOOP;
      fnd_file.put_line (fnd_file.output, '</PCL_VD_DT>');
      EXCEPTION WHEN OTHERS THEN fnd_file.put_line (fnd_file.LOG, 'Entered INTO exception' || SQLCODE || SQLERRM  );

END;
/

--------------------------Code End----------------------------------------

*Create the excusable 

Register the concurrent program

Create the parameters in which defined in procedure (XXPCL_VENDOR_DETAILS_PROC1)

Parameter 1

Parameter 2

now assign the Wipro Vendor report concurrent program  in any request group
then submit request 

Both are parameters is not mandatory field, you can insert the value or not 
because in procedure handle the NVL Fuction in Parameter 

After completed the Wipro supplier data report , click the view output
Note :-  Output will be in XML Format 

Supplier Data With Bank

How to find the suppliers data with bank details :-

Very Simple query :-

SELECT DISTINCT APS.VENDOR_ID,
                  ASSA.VENDOR_SITE_ID,
                  APS.VENDOR_NAME,
                  HR.NAME ORG_NAME,
                  APS.SEGMENT1 VENDOR_NUM,
                  APS.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE,
                  ASSA.VENDOR_SITE_CODE VENDOR_SITE,
                  ASSA.ATTRIBUTE13 GST_STATUS,
                  ASSA.ATTRIBUTE12 GST_NO,
                  PAN_NO,
                  ASSA.ADDRESS_LINE1,
                  ASSA.ADDRESS_LINE2,
                  ASSA.ADDRESS_LINE3,
                  ASSA.CITY,
                  ASSA.STATE,
                  ASSA.ZIP,
                  ASSA.EMAIL_ADDRESS,
                  ASSA.PHONE PHONE_NO,
                  IEBA.BANK_NAME,
                  IEBA.BANK_ACCOUNT_NUMBER,
                  IEBA.BRANCH_NUMBER,
                  IEBA.BANK_NUMBER,
                  IEBA.DESCRIPTION,
                  IEBA.BANK_BRANCH_NAME
    FROM APPS.AP_SUPPLIERS APS,
         APPS.AP_SUPPLIER_SITES_ALL ASSA,
         APPS.IBY_PAYEE_ASSIGNED_BANKACCT_V IPAB,
         APPS.IBY_ACCOUNT_OWNERS IAO,
         APPS.IBY_EXT_BANK_ACCOUNTS_V IEBA,
         APPS.HR_OPERATING_UNITS HR,
         APPS.JAI_AP_TDS_VENDOR_HDRS JAI
   WHERE     1 = 1
         AND APS.VENDOR_ID = ASSA.VENDOR_ID
         AND ASSA.ORG_ID = HR.ORGANIZATION_ID
        -- AND HR.SET_OF_BOOKS_ID = 2121
         AND JAI.VENDOR_SITE_ID(+) = ASSA.VENDOR_SITE_ID
         AND ASSA.VENDOR_SITE_ID = IPAB.SUPPLIER_SITE_ID
         AND APS.PARTY_ID = ipab.party_id
         AND IPAB.EXT_BANK_ACCOUNT_ID = IEBA.EXT_BANK_ACCOUNT_ID
         AND APS.END_DATE_ACTIVE IS NULL
         AND ASSA.INACTIVE_DATE IS NULL
         AND IEBA.EXT_BANK_ACCOUNT_ID(+) = IAO.EXT_BANK_ACCOUNT_ID
         AND IAO.ACCOUNT_OWNER_PARTY_ID(+) = APS.PARTY_ID
         AND IAO.END_DATE IS NULL
         AND IEBA.END_DATE IS NULL
         AND IPAB.END_DATE IS NULL

ORDER BY 3;

Supplier Data

How to find the supplier master data from the data base 

 SELECT APS.VENDOR_ID,
         ASSA.VENDOR_SITE_ID,
         APS.VENDOR_NAME,
         HR.NAME ORG_NAME,
         APS.SEGMENT1,
         APS.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE,
         ASSA.VENDOR_SITE_CODE VENDOR_SITE,
         PAN_NO SITE_PAN_NO,
         GL.CONCATENATED_SEGMENTS LIABILITY_ACCT,
         (SELECT CONCATENATED_SEGMENTS
             FROM APPS.GL_CODE_COMBINATIONS_KFV GL1
                    WHERE GL1.CODE_COMBINATION_ID = ASSA.PREPAY_CODE_COMBINATION_ID)  PREPAYMENT_ACCT,
         ASSA.ADDRESS_LINE1,
         ASSA.ADDRESS_LINE2,
         ASSA.ADDRESS_LINE3,
         ASSA.CITY,
         ASSA.STATE,
         ASSA.ZIP,
         ASSA.EMAIL_ADDRESS,
         ASSA.PHONE PHONE_NO
    FROM APPS.AP_SUPPLIERS APS,
         APPS.AP_SUPPLIER_SITES_ALL ASSA,
         APPS.HR_OPERATING_UNITS HR,
         APPS.JAI_AP_TDS_VENDOR_HDRS JAI,
         APPS.GL_CODE_COMBINATIONS_KFV GL
   WHERE     1 = 1
         AND APS.VENDOR_ID = ASSA.VENDOR_ID
         AND ASSA.ORG_ID = HR.ORGANIZATION_ID
         --AND          HR.SET_OF_BOOKS_ID=2121
       --AND          JAI.VENDOR_ID=APS.VENDOR_ID
         AND JAI.VENDOR_SITE_ID(+) = ASSA.VENDOR_SITE_ID
         AND APS.END_DATE_ACTIVE IS NULL
         AND ASSA.INACTIVE_DATE IS NULL
         AND GL.CODE_COMBINATION_ID = ASSA.ACCTS_PAY_CODE_COMBINATION_ID
         --AND ASSA.ORG_ID =:P_ORG_ID
         --AND          APS.VENDOR_ID=:P_VENDOR_ID
ORDER BY 3


Let me know if any query

Oracle Forms Intro

Oracle Forms Introduction

Components of Forms:

Oracle has provided Forms Developer Suite that enable business developers to easily and quickly construct sophisticated database forms and business logic with a minimum of effort. It consists of the following programs, or components:

1.     Forms Developer

2.     Form Compiler

3.     Forms Runtime

Components of Form builder

Object Navigator: It is hierarchical browsing and editing interface that enables you locate and manipulate application objects quickly and easily.

Property Palette: It is used set and modify the properties for all objects in form modules.

Layout Editor: It is graphical design facility for creating and arranging interface items and graphical objects in your application.

PL / SQL Editor: It is the integrated functionality of oracle procedure builder that exists with in form builder.

Form Module types:

 Form Module: It is a collection of objectives such as block, canvas, items and event based PL/SQL code blocks called trigger.

 Menu Module: It is a collection of menu items. It can be main menu or sub menu.

 PL / SQL Libraries: The library module is a collection of PL/SQL function and package stored in a single library file. This library file is the attached to form / menu modules. All other objects in the form or menu can now access share the collection of PL/SQL functions and procedures.

Object Libraries: It is a collection of form objects that you can use in other modules. You can create it to store, maintain and distribute standard objects that can be reuse across the entire development organization.

Objects of forms:

Blocks: Block is logical owner of items. It provides a mechanism for grouping related items into a functional unit for storing, displaying and manipulating records.

Items: These are interface objects that present data values to the user or enable the user to interact with the form.

Canvas: A canvas is the background object upon which interface items appear.

Frames: Frames are used to arrange items with in a block.

Windows: Windows contains for all visual objects that make up a form builder application.

PL/SQL Code Block: It is used for event driven code. That code automatically executes when a specific event occurs.

Data Blocks:

In Forms Builder there are two main types of blocks

Database Blocks: A data block is associated with a specific database table (or view), a stored procedure, a FROM clause query, or transactional triggers.

Control Blocks: A control block is not associated with a database, and its items do not relate to any columns within any database table. Its items are called control items.

Canvas Types:

Form Builder provides four types of canvases, all of which can be displayed in the same window at runtime.  A canvas' type defines how Form Builder will display it in the window to which it is assigned.

Content Canvas:  The most common canvas type is the content canvas (the default type).  A content canvas is the "base" view that occupies the entire content pane of the window in which it is displayed.  You must define at least one content canvas for each window you create.

Stacked Canvas:  A stacked canvas is displayed a top of on the content canvas assigned to the current window.

Tab Canvas:  A tab canvas made up of one or more tab pages allows you to group and display a large amount of related information on a single dynamic Form Builder canvas object.

Toolbar Canvas:  A toolbar canvas often is used to create toolbars for individual windows.  You can create two types of toolbar canvases:  horizontal or vertical.  Horizontal toolbar canvases are displayed at the top of a window, just under its menu bar, while vertical toolbars are displayed along the far left edge of a window.

Form Module Hierarchy

Using trigger and program units, form functionality will be added.Trigger can be written at different levels in form module

TEMPLATE.fmb

Oracle has provided one template form (TEMPLATE.fmb) and stated that this form should be the starting point for all development of new forms in Oracle Application.

Oracle has already written minimum but all code which is required to implement Oracle Application Standard Functionality. The pre-written components of the template form includes platform-independent references to:

Object groups in the APPSTAND form

Libraries

Special triggers

Predefined Program Units

Applications Color Palette

APPSTAND

The APPSTAND form contains the master copy of the shared objects. It contains the following Object Groups

STANDARD_PC_AND_VA : contains the Visual Attributes and Property Classes required to implement much of the user interface for Forms-Based Products

STANDARD_TOOLBAR : Contains the windows, canvasses, blocks, and items of the Applications Toolbar

STANDARD_CALENDAR : contains the windows, canvasses, blocks, and items of the Applications Calendar

QUERY_FIND : contains a window, canvas, block, and items used as a starting point for coding a Find Window

Libraries in the TEMPLATE Form

The TEMPLATE form includes platform-independent attachments of several libraries. Few of these libraries are:

APPCORE: It contains the packages and procedures that are required of all forms to support the menu, Toolbar, and other required standard behaviors

APPCORE2 : It is a near-duplicate of APPCORE intended for use with the CUSTOM library. Oracle recommends that you should use the corresponding routine in the APPCORE2 library in place of APPCORE. The various routines available are APP_ITEM_PROPERTY2, APP_DATE2, APP_SPECIAL2

APPDAYPK: It contains the packages that control the Calendar feature

FNDSQF :It contains packages and procedures for Message Dictionary, flexfields, profiles

CUSTOM:It allows extension of forms without modification of form code

GLOBE: It allows developers to incorporate regional features into forms. The GLOBE library calls routines in the JA (Asia/Pacific region), JE (Europe/Middle East/Africa), and JL (Latin America region) libraries

Form Creation Steps in oracle apps

     

1.    Open Template.fmb

2.    Delete BLOCKNAME from Windows, Canvas and Data Blocks

3.    Create New Window and set Subclass in formation as ‘Window’

4.    Create Canvas and set Subclass in formation as ‘Canvas’

5.    Attach Window in Canvas and canvas in window (Property palate)

6.    Create the Data Block using wizard

7.    Set the First Navigation block in Module Property

8.    Open PRE-FORM Trigger and provide the WINDOW NAME in place of BLOCKNAME.

app_window.set_window_position(' BLOCKNAME ', 'FIRST_WINDOW');

9.    Open APP_CUSTOM Package provide the WINDOW NAME in place of your first window

         if (wnd = '<your first window>') then app_window.close_first_window;

10. Compile the form.

Generate FMX

Transfer (in binary format) the FMB file to server at $AU_TOP/forms/US

Login to UNIX environment and run below command (from $AU_TOP/forms/US) to generate FMX

For 11i

f60gen module=XXForm.fmb module_type=form userid=apps/<apps_password> compile_all=special output_file=$XXQCS_TOP/forms/US/XXForm.fmx

For R12

frmcmp_batch module=XXFORM.fmb userid=apps/<apps_password> output_file=$XXQCS_TOP/forms/US/XXFORM.fmx