API for initialize environment from backend in oracle R12

Pass parameter value user_id, resp_id and resp_appl_id

Begin
fnd_global.APPS_INITIALIZE(fnd_profile.value('USER_ID'),fnd_profile.value('RESP_ID'),fnd_profile.value('RESP_APPL_ID'))
End;

Invoice Payment detail report Query R12

SELECT  aba.batch_name invoice_batch,
        aia.source invoice_source,
        aia.org_id,
        hou.name OU_Name,
        pca.checkrun_name,
        pca.check_date,
        pca.check_number document_number,
        (select user_name from fnd_user where user_id = pca.created_by) checkrun_createdby,
        pca.attribute5 void_rsn,
        pca.attribute7 utr_no,
        pca.attribute8 txn_type,
        pca.bank_Account_num remit_to,
        pca.bank_account_name,
        pca.void_date,
        aia.invoice_num,
        aia.batch_id,
        aia.invoice_id,
        aia.doc_sequence_value voucher_num,
        REPLACE(REPLACE(aia.description,CHR(10),''),CHR(13),'') description,
        aia.invoice_currency_code,
        aia.payment_currency_code,
        aia.invoice_date,
        aia.invoice_amount,
        aia.amount_paid,
        aia.gl_date,
        aipa.payment_num,
        aipa.amount payment_amount,
        aipa.accounting_date payment_date,
        REPLACE(REPLACE(asi.vendor_name,CHR(10),''),CHR(13),'') vendor_name,
        REPLACE(REPLACE(asi.vendor_name_alt,CHR(10),''),CHR(13),'') vendor_name_alt,
        asi.segment1 vendor_number,
        aia.pay_group_lookup_code pay_group,
        REPLACE(REPLACE(assa.vendor_site_code,CHR(10),''),CHR(13),'') vendor_site_code,
        REPLACE(REPLACE(assa.vendor_site_code_alt,CHR(10),''),CHR(13),'') vendor_site_code_alt,
        flv_vendortype.meaning vendor_type,
        flv_invoicetype.meaning invoice_type,
        flv_paytype.meaning payment_type,
        flv_paystatus.meaning payment_status,
        (SELECT gross_amount
                   FROM  AP_PAYMENT_SCHEDULES_ALL
                   WHERE INVOICE_ID  = aia.invoice_id
                   AND   PAYMENT_NUM = aipa.payment_num
                   AND   ROWNUM=1) schedule_amount,
         (SELECT due_Date
                   FROM  AP_PAYMENT_SCHEDULES_ALL
                   WHERE INVOICE_ID  = aia.invoice_id
                   AND   PAYMENT_NUM = aipa.payment_num
                   AND   ROWNUM=1) due_date,
        aia.accts_pay_code_combination_id,
        DECODE(pca.void_date,NULL,'Negotiable','Void') inv_status,
        pca.check_id
FROM    ap_checks_all pca,
        ap_invoice_payments_all aipa,
        ap_invoices_all aia,
        hr_operating_units hou,
        ap_suppliers asi,
        ap_supplier_sites_all assa,
        ap_batches_all aba,
        fnd_lookup_values flv_vendortype,
        fnd_lookup_values flv_invoicetype,
        fnd_lookup_values flv_paytype,
        fnd_lookup_values flv_paystatus
WHERE   1=1
AND     flv_paystatus.lookup_code       = aia.payment_status_flag
AND     flv_paytype.lookup_code         = pca.payment_type_flag
AND     flv_invoicetype.lookup_code     = aia.invoice_type_lookup_code
AND     flv_vendortype.lookup_code      = asi.vendor_type_lookup_code
AND     aba.batch_id(+)                 = aia.batch_id
AND     assa.org_id                     = aipa.org_id
AND     assa.vendor_site_id             = aia.vendor_site_id
AND     asi.vendor_id                   = aia.vendor_id
AND     aipa.org_id                     = hou.organization_id
AND     hou.set_of_books_id             = aipa.set_of_books_id
AND     aia.invoice_id                  = aipa.invoice_id
and     pca.org_id                      = aipa.org_id
AND     pca.check_id                    = aipa.check_id
AND     flv_invoicetype.language        = 'US'
AND     flv_vendortype.language         = 'US'
AND     flv_paytype.language            = 'US'
AND     flv_paystatus.language          = 'US'
AND     NVL(flv_paystatus.end_date_active,SYSDATE+1) > SYSDATE
AND     NVL(flv_invoicetype.end_date_active,SYSDATE+1) > SYSDATE
AND     NVL(flv_vendortype.end_date_active,SYSDATE+1) > SYSDATE
AND     NVL(flv_paytype.end_date_active,SYSDATE+1) > SYSDATE
AND     flv_invoicetype.lookup_type     = 'INVOICE TYPE'
AND     flv_vendortype.lookup_type      = 'VENDOR TYPE'
AND     flv_paytype.lookup_type         = 'PAYMENT TYPE'
AND     flv_paystatus.lookup_type       = 'INVOICE PAYMENT STATUS'
AND     aipa.set_of_books_id            = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
AND     TRUNC(NVL(aipa.accounting_date,SYSDATE))   BETWEEN NVL (TRUNC (TO_DATE (p_txndate_from,
                                                                                'YYYY/MM/DD HH24:MI:SS')),
                                                                TRUNC (NVL (aipa.accounting_date, SYSDATE)))
                                                   AND NVL (p_txndate_to,
                                                                TRUNC (NVL (aipa.accounting_date, SYSDATE)))
AND     asi.vendor_id                = NVL(p_vendorid,asi.vendor_id)
AND     aia.org_id                   = NVL(p_ou_id, aia.org_id)
AND     asi.vendor_type_lookup_code  = NVL(p_vendortype, asi.vendor_type_lookup_code)                                                            
ORDER BY invoice_num
        ,aipa.payment_num
        ,accounting_date;

How to make Reject comments mandatory in oracle workflow

Use Below Code

PROCEDURE btvl_reject_commnet_mandate (
      p_item_type   IN              VARCHAR2,
      p_item_key    IN              VARCHAR2,
      actid         IN              VARCHAR2,
      funmode       IN              VARCHAR2,
      resultout     OUT NOCOPY      VARCHAR2
   )
   IS
      v_response         VARCHAR2 (2000);
      l_user_comment     VARCHAR2 (2000);
      approval_comment   EXCEPTION;
   BEGIN
/*===================================================================================
This will derive the button by looking at the response from the notification. the WF_ENGINE.CONTEXT_NID variable will tell you the notification ID that is being responded to, and we can derive the response from there.
====================================================================================*/
      v_response :=
                wf_notification.getattrtext (wf_engine.context_nid, 'RESULT');
/*===================================================================================
This will retrieve the value which is stored in the Approval Comments field by the approver when the reject button is pressed.
====================================================================================*/
      l_user_comment :=
         wf_notification.getattrtext (wf_engine.context_nid,
                                      'COMMENTS'
                                     );

/*===================================================================================
This block will be fired when the user has pressed the 'Reject' Button and the comments is null and then it will raise the user defined error. And the workflow will not continue untill the user enters value in the comments field for rejection.
===================================================================================*/
      BEGIN
         IF (    funmode = 'RESPOND'
             AND v_response = 'REJECTED'
             AND l_user_comment IS NULL
            )
         THEN
            RAISE approval_comment;
         END IF;
      EXCEPTION
         WHEN approval_comment
         THEN
            raise_application_error
               (-20010,
                'Approval Comment Field cannot be Null. Please enter reason for rejection and click reject.'
               );
            RAISE;
      END;
   END;

After compiling above procedure then call this procedure to notification 

Query for user responsibility name

SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       frt.RESPONSIBILITY_ID,
       furg.start_date             "Start Date",
       furg.end_date               "End Date",    
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('DEV_TEAM')  -- <change it>
   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
   AND fa.application_short_name =  'WIP'
 ORDER BY frt.responsibility_name;

Oracle Payable important tables in r12

select * from ap_batches_all

select * from ap_invoices_All

select * from ap_invoice_lines_all

select * from ap_holds_all

select * from ap_invoice_distributions_all

select * from ap_payment_Schedules_all

select * from ap_payment_history_all

select * from ap_checks_All

select * from ap_invoice_payments_all

select * from ce_bank_accounts

select * from ce_payment_documents

select * from iby_external_payees_all

select * from iby_pmt_instr_uses_All

AP Invoice interface table

AP interface tables 

select * from ap_invoices_interface

select * from ap_invoice_line_interface


AP interface error tables

select * from ap_interface_rejections

select * from ap_interface_controls

AP interface sequence for invoice id 

select ap_invoices_interface_s.nextval from dual

Concurrent program for import invoice from interface table to base tables

Payables open interface import

Run above mention program by passing mandatory parameter and check the output of program with error/success status.  

Move order header different status and their meaning

select lookup_code, substr(meaning, 1, 60) "Meaning"
from mfg_lookups
where lookup_type = 'MTL_TXN_REQUEST_STATUS'
order by lookup_code

1 = Incomplete
2 = Pending Approval
3 = Approved
4 = Not Approved
5 = Closed
6 = Cancelled
7 = Pre-Approved
8 = Partially Approved
9 = Cancelled by Source

Move order important tables

select * from mtl_txn_request_headers

select * from mtl_txn_request_lines

select * from mtl_system_items_b

select * from mtl_item_locations

1. Create move order:
Quantity: 10
Quantity Delivered: NULL
Quantity Detailed: NULL
Quantity Required: NULL
Line Status: 1 (Incomplete)

2. Approve move order:
Quantity: 10
Quantity Delivered: NULL
Quantity Detailed: NULL
Quantity Required: NULL
Line Status: 3 (Approved)

3. Allocate move order for full quantity:
Quantity: 10
Quantity Delivered: NULL
Quantity Detailed: 10
Quantity Required: NULL
Line Status: 3 (Approved)

4. Transact move order:
Quantity: 10
Quantity Delivered: 10
Quantity Detailed: 10
Quantity Required: NULL
Line Status: 5 (Closed)

NOTE: When a move order is allocated, a corresponding record is inserted into the pending table (MTL_MATERIAL_TRANSACTIONS_TEMP as well as lot/serial tables if required).
When the move order is transacted, the record moves from the pending table to the history table (MTL_MATERIAL_TRANSACTIONS).

Delete XMl template in oracle apps

Delete command for deleting xml template from oracle apps

delete   from XDO_TEMPLATES_B        where template_code    = 'template_code'

delete   from XDO_TEMPLATES_TL       where template_code    = 'template_code'

delete   from xdo_lobs               where lob_code         = 'template_code'

delete   from XDO_DS_DEFINITIONS_TL  where data_source_code = 'template_code'

delete   from XDO_DS_DEFINITIONS_b   where data_source_code = 'template_code'

Important tables in oracle property manager

For property details
select * from pn_properties_All

For companies details
SELECT * FROM PN_COMPANIES_ALL

For building details
select * from pn_buildings_v

For the detail of floors in a building
select * from pn_floors_v

For the detail of offices in a single floor
select * from pn_offices_v


For Lease details
SELECT * FROM PN_LEASES_ALL

For payment details
select * from pn_payment_terms_all

For Tenant details
select * from pn_tenancies_all

For Payment schedules
select * from pn_payment_schedules_all

For location details
select * from pn_locations_All 

Oracle Property manager : Property, Location, Lease, AP Invoice

Oracle Property Manager:-

Oracle Property Manager is part of the Oracle Real Estate Management solution. You can use Oracle Property Manager to manage your properties. Oracle Property Manager provides you with tools to organize the information you use to manage major real estate tasks.

Oracle Property Manager includes the following scenarios to organize the information you use to manage major real estate tasks:

• Lease Management
• Space Management
• Workflow Automation
• Integration
• Reporting
• Inquiry

Integration: - Oracle Property Manager is integrated with other Oracle applications, including:

• Oracle General Ledger
• Oracle Sub ledger Accounting
• Oracle Payables
• Oracle Receivables
• Oracle E-Business Tax
• Oracle Human Resources
• Oracle Enterprise Asset Management
• Oracle Alert
• Oracle Workflow
• Oracle Projects

This integration enables you to use your Oracle Property Manager records as the source of payments and billings, and to use your Human Resources records as a source for employee information in Oracle Property Manager.

Lease Management

Lease management is at the center of the real estate management function. With Oracle, you can control and oversee a variety of lease management tasks such as:

Abstracting basic lease information from lease documents

• Modifying and amending leases
• Calculating lease amounts
• Creating invoice schedules
• Exporting invoices to Oracle Payables and Oracle Receivables

Type of lease

Three types of lease as follow:- 

• Expense
• Revenue
• Sublease 

Steps for Building creation, location and lease:

Buildings Setup

Goto, "Property Manager" responsibility 

Property Definitions --> Property --> New 

Property Creation

Building Creation 

Buildings Setup – Features

Buildings Setup – Contacts

Floors Setup

Offices Setup – Main Floor

Offices Setup – Second Floor

Lease Setup

Lease Setup – cont

Lease – Details

Lease – Contacts

Lease – Locations

Lease – Insurance

Lease – Rights

Lease – Obligations

Lease – Options

Lease – Payments

Lease – Payments cont

Lease – Term Details

Lease – Notes

Lease – Status

Lease – Payment Schedule    

Lease – Payment Schedule Request

Lease – Payment Schedule Report

Authorize Payment

Payment manager responsibility --> Lease and document --> Payments-->Authorize  

Payment Status – Approved

View Payment Schedule Details

Payment Export to Payables
Payment manager responsibility --> Lease and document --> Payments-->Export to payable 

Payment Export – Request

Run Below program 

Payables Interface Request

Check Report "Payable Import Report" and invoice number also mention in report. 

Check invoice number in payable. 

Locator creation API in Oracle Property manager

V_ROWID                ROWID;
v_location_id          NUMBER;
v_addr_id              NUMBER;
v_return_status        VARCHAR2(10);
v_return_MSG           VARCHAR2(1000);
v_code                 VARCHAR2(1000);
v_uom_code             VARCHAR2(1000);
v_uom                  VARCHAR2(1000);
v_city_code            VARCHAR2(1000);
v_city                 VARCHAR2(1000);
v_state_code           VARCHAR2(1000);
v_state                VARCHAR2(1000);
v_operator_name        VARCHAR2(1000);
v_operator_description VARCHAR2(1000);
v_site_type1           VARCHAR2(1000);
v_site_type1_desc      VARCHAR2(1000);
v_site_type2           VARCHAR2(1000);
v_site_type2_desc      VARCHAR2(1000);
v_in_outdoor           VARCHAR2(1000);
v_in_outdoor_desc      VARCHAR2(1000);
v_error_flag           VARCHAR2(10):='N';
v_error_msg            VARCHAR2(10000);
v_circle_id            NUMBER;
-----------------------------------
/*Begin executing MAIN procedure*/
-----------------------------------
BEGIN
--fnd_global.apps_initialize (48634, 62408, 240);


    FND_FILE.PUT_LINE(FND_FILE.LOG,'*******************************************************************');
    FND_FILE.PUT_LINE(FND_FILE.LOG,'                   Airtel Symphony Locator Creation');
    FND_FILE.PUT_LINE(FND_FILE.LOG,'*******************************************************************');
FOR r_get_locator_rec
 IN c_get_locator_rec
LOOP

V_ROWID                :=NULL;
v_location_id          :=NULL;
v_addr_id              :=NULL;
v_return_status        :=NULL;
v_return_MSG           :=NULL;
v_code                 :=NULL;
v_uom_code             :=NULL;
v_uom                  :=NULL;
v_city_code            :=NULL;
v_city                 :=NULL;
v_state_code           :=NULL;
v_state                :=NULL;
v_operator_name        :=NULL;
v_operator_description :=NULL;
v_site_type1           :=NULL;
v_site_type1_desc      :=NULL;
v_site_type2           :=NULL;
v_site_type2_desc      :=NULL;
v_in_outdoor           :=NULL;
v_in_outdoor_desc      :=NULL;
v_error_msg            :=NULL;
v_circle_id            :=NULL;


--Validate Circle
        BEGIN
            SELECT organization_id
            INTO   v_circle_id
            FROM   hr_operating_units
            WHERE  name  = r_get_locator_rec.operating_unit
            ;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_error_msg:=v_error_msg||'Circle'||r_get_locator_rec.operating_unit||'  is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
        v_error_msg:=v_error_msg||'Error in Validating Circle '||r_get_locator_rec.operating_unit||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;
 g_org_id:=v_circle_id;

-- Fetch responsibility Details for apps initialization
        BEGIN
                SELECT fr.responsibility_id,fr.application_id
                INTO   g_resp_id,g_appl_id
                FROM   fnd_profile_options_vl fpo, fnd_profile_option_values fpov, fnd_responsibility_vl fr
                WHERE  fpo.profile_option_name = 'ORG_ID'
                AND    fpo.profile_option_id = fpov.profile_option_id
                AND    fpov.profile_option_value = to_char(v_circle_id)
                AND    fpov.level_value = fr.responsibility_id
                AND    upper(fr.responsibility_name) like upper('%Property Lease Initiator%');


        EXCEPTION
        WHEN OTHERS THEN
            v_error_msg:=v_error_msg||'Error in fetching Responsibility, termintaing the process';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
                  UPDATE btvl_sym_locator_stg
                  SET    status = 'ERROR'
                        ,message = v_error_msg
                  WHERE  rowid = r_get_locator_rec.rowid;
                  COMMIT;
           -- RAISE_APPLICATION_ERROR(-2001,v_error_msg);

        END;
        dbms_output.put_line(g_user_id||','|| g_resp_id||','||g_appl_id);
        fnd_global.apps_initialize (g_user_id, g_resp_id,g_appl_id);
        g_invorg_id :=FND_PROFILE.VALUE('BTVL_PN_INV_ORG_ID');
        g_bg_id     :=fnd_profile.value('PER_BUSINESS_GROUP_ID');
        g_org_id    :=fnd_profile.value('ORG_ID');

--Fetch Location Code
        SELECT btvl_utilities_pkg.get_location_code(g_invorg_id, r_get_locator_rec.area_code, g_bg_id)
        into   v_code
        FROM   DUAL;

        FND_FILE.PUT_LINE(FND_FILE.LOG,'Location Code is '||v_code);
        dbms_output.put_line('Location Code is '||v_code);
--Validate UOM Code
        BEGIN
            SELECT lookup_code,meaning
            INTO   v_uom_code,v_uom
            FROM   fnd_lookup_values
            WHERE  lookup_type  = 'PN_UNITS_OF_MEASURE'
            AND    enabled_flag = 'Y'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
                                      AND TRUNC(NVL(end_date_active,SYSDATE))
            AND    lookup_code  = r_get_locator_rec.uom_code;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_error_msg:=v_error_msg||'UOM Code'||v_uom_code||' entered is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
        v_error_msg:=v_error_msg||'Error in Validating UOM Code '||v_uom_code||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

 --Validate City
        BEGIN
            SELECT lookup_code, meaning
            INTO   v_city_code,v_city
            FROM   fnd_lookups
            WHERE  lookup_type  = 'PN_CITIES'
            AND    enabled_flag = 'Y'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
                                      AND TRUNC(NVL(end_date_active,SYSDATE))
            AND    UPPER(lookup_code)  = UPPER(r_get_locator_rec.city)
            AND    ROWNUM=1;

        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_error_msg:=v_error_msg||'City Code'||v_city_code||' is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
        v_error_msg:=v_error_msg||'Error in Validating City Code '||v_city_code||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

 --Validate State
        BEGIN
            SELECT lookup_code, meaning
            INTO   v_state_code,v_state
            FROM   fnd_lookups
            WHERE  lookup_type  = 'PN_STATE'
            AND    enabled_flag = 'Y'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
                                      AND TRUNC(NVL(end_date_active,SYSDATE))
            AND    UPPER(lookup_code)  = UPPER(r_get_locator_rec.state) ;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_error_msg:=v_error_msg||'State '||v_state_code||' is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
        v_error_msg:=v_error_msg||'Error in Validating State '||v_state_code||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

 --Validate Name of Operator
        BEGIN
            SELECT ffvt.flex_value_meaning,ffvt.description
            INTO   v_operator_name,v_operator_description
            FROM  fnd_flex_value_sets ffvs ,
                  fnd_flex_values ffv ,
                  fnd_flex_values_tl ffvt
            WHERE 1=1
            AND   ffvs.flex_value_set_id = ffv.flex_value_set_id
            AND   ffv.flex_value_id      = ffvt.flex_value_id
            AND   ffvt.language          = USERENV('LANG')
            AND   flex_value_set_name    ='BTVL_PN_NAME_OPERATOR' --attribute2
            AND   ffv.enabled_flag <> 'N'
            AND   ffv.flex_value         = r_get_locator_rec.name_operator_attr2
            ORDER BY flex_value asc;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
             v_error_msg:=v_error_msg||'Name of Operator'||v_operator_name||' is not found . Please verify!!';
             FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
             v_error_flag := 'Y';
        WHEN OTHERS THEN
             v_error_msg:=v_error_msg||'Error in Validating Name of Operator'||v_operator_name||', Error Message ->'||sqlerrm;
             FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
             v_error_flag := 'Y';
        END;

 --Validate Site Type 1
        BEGIN
            SELECT ffvt.flex_value_meaning,ffvt.description
            INTO   v_site_type1,v_site_type1_desc
            FROM   fnd_flex_value_sets ffvs ,
                   fnd_flex_values ffv ,
                   fnd_flex_values_tl ffvt
            WHERE  1=1
            AND    ffvs.flex_value_set_id   = ffv.flex_value_set_id
            AND    ffv.flex_value_id        = ffvt.flex_value_id
            AND    ffvt.language            = USERENV('LANG')
            AND    flex_value_set_name      ='BTVL_PN_SITE_TYPE1' --attribute3
            AND    ffv.enabled_flag <> 'N'
            AND    ffv.flex_value           = r_get_locator_rec.site_Type1_attr3
            ORDER BY flex_value asc;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_error_msg:=v_error_msg||'Site Type 1'||v_site_type1||' is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
            v_error_msg:=v_error_msg||'Error in Validating Site Type 1 '||v_site_type1||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

  --Validate Site Type 2
        BEGIN
            SELECT ffvt.flex_value_meaning,ffvt.description
            INTO   v_site_type2,v_site_type2_desc
            FROM   fnd_flex_value_sets ffvs ,
                   fnd_flex_values ffv ,
                   fnd_flex_values_tl ffvt
            WHERE  1=1
            AND    ffvs.flex_value_set_id = ffv.flex_value_set_id
            AND    ffv.flex_value_id      = ffvt.flex_value_id
            AND    ffvt.language          = USERENV('LANG')
            AND    flex_value_set_name    ='BTVL_PN_SITE_TYPE2' --attribute4
            AND    ffv.flex_value = r_get_locator_rec.site_Type2_attr4
            AND    ffv.enabled_flag <> 'N'
            ORDER BY flex_value asc;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_error_msg  :=v_error_msg||'Site Type 2'||v_site_type2||' is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
            v_error_msg  :=v_error_msg||'Error in Validating Site Type 1 '||v_site_type2||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

  --Validate Indoor/Outdoor New
        BEGIN
            SELECT ffvt.flex_value_meaning,ffvt.description
            INTO   v_in_outdoor,v_in_outdoor_desc
            FROM   fnd_flex_value_sets ffvs ,
                   fnd_flex_values ffv ,
                   fnd_flex_values_tl ffvt
            WHERE  1=1
            AND    ffvs.flex_value_set_id = ffv.flex_value_set_id
            and    ffv.flex_value_id      = ffvt.flex_value_id
            AND    ffvt.language          = USERENV('LANG')
            and    flex_value_set_name    ='BTVL_PN_INDOOR_OUTDOOR' --attribute6
            AND    ffv.flex_value         = r_get_locator_rec.inout_door_attr12
            and    ffv.enabled_flag <> 'N'
            ORDER BY flex_value asc;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_error_msg  :=v_error_msg||'Indoor/Outdoor New'||v_in_outdoor||'  is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
            v_error_msg  :=v_error_msg||'Error in Validating Indoor/Outdoor New '||v_in_outdoor||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

  --If validation is Success then execute API
        IF v_error_flag <> 'Y'
        THEN
        dbms_output.put_line('error flag >'||v_error_flag);
          PNT_LOCATIONS_PKG.insert_row (
                                  x_rowid                       =>    v_rowid         --out parameter
                                 ,x_org_id                      =>    v_circle_id
                                 ,x_LOCATION_ID                 =>    v_location_id   --out parameter
                                 ,x_LAST_UPDATE_DATE            =>    sysdate
                                 ,x_LAST_UPDATED_BY             =>    -1
                                 ,x_CREATION_DATE               =>    sysdate
                                 ,x_CREATED_BY                  =>    -1
                                 ,x_LAST_UPDATE_LOGIN           =>    -1
                                 ,x_LOCATION_PARK_ID            =>    NULL
                                 ,x_LOCATION_TYPE_LOOKUP_CODE   =>    'BUILDING'
                                 ,x_SPACE_TYPE_LOOKUP_CODE      =>    NULL
                                 ,x_FUNCTION_TYPE_LOOKUP_CODE   =>    NULL
                                 ,x_STANDARD_TYPE_LOOKUP_CODE   =>    NULL
                                 ,x_LOCATION_ALIAS              =>    v_code
                                 ,x_LOCATION_CODE               =>    v_code
                                 ,x_BUILDING                    =>    r_get_locator_rec.area_code
                                 ,x_LEASE_OR_OWNED              =>    'L'
                                 ,x_CLASS                       =>    NULL
                                 ,x_STATUS_TYPE                 =>    NULL
                                 ,x_FLOOR                       =>    NULL
                                 ,x_OFFICE                      =>    NULL
                                 ,x_ADDRESS_ID                  =>    v_addr_id       --out parameter
                                 ,x_MAX_CAPACITY                =>    0
                                 ,x_OPTIMUM_CAPACITY            =>    0
                                 ,x_GROSS_AREA                  =>    NULL
                                 ,x_RENTABLE_AREA               =>    0
                                 ,x_USABLE_AREA                 =>    0
                                 ,x_ASSIGNABLE_AREA             =>    NULL
                                 ,x_COMMON_AREA                 =>    NULL
                                 ,x_SUITE                       =>    NULL
                                 ,x_ALLOCATE_COST_CENTER_CODE   =>    NULL
                                 ,x_UOM_CODE                    =>    r_get_locator_rec.uom_code
                                 ,x_DESCRIPTION                 =>    NULL
                                 ,x_PARENT_LOCATION_ID          =>    NULL
                                 ,x_INTERFACE_FLAG              =>    NULL
                                 ,x_REQUEST_ID                  =>    NULL
                                 ,x_PROGRAM_APPLICATION_ID      =>    NULL
                                 ,x_PROGRAM_ID                  =>    NULL
                                 ,x_PROGRAM_UPDATE_DATE         =>    NULL
                                 ,x_STATUS                      =>    'A'
                                 ,x_PROPERTY_ID                 =>    NULL
                                 ,x_ATTRIBUTE_CATEGORY          =>    'BUILDING'
                                 ,x_ATTRIBUTE1                  =>    r_get_locator_rec.technicl_siteattr1
                                 ,x_ATTRIBUTE2                  =>    r_get_locator_rec.name_operator_attr2
                                 ,x_ATTRIBUTE3                  =>    r_get_locator_rec.site_Type1_attr3
                                 ,x_ATTRIBUTE4                  =>    r_get_locator_rec.site_Type2_attr4
                                 ,x_ATTRIBUTE5                  =>    r_get_locator_rec.indus_infra_attr5
                                 ,x_ATTRIBUTE6                  =>    r_get_locator_rec.circuit_name_Attr6
                                 ,x_ATTRIBUTE7                  =>    NULL
                                 ,x_ATTRIBUTE8                  =>    NULL
                                 ,x_ATTRIBUTE9                  =>    NULL
                                 ,x_ATTRIBUTE10                 =>    NULL
                                 ,x_ATTRIBUTE11                 =>    NULL
                                 ,x_ATTRIBUTE12                 =>    r_get_locator_rec.inout_door_attr12
                                 ,x_ATTRIBUTE13                 =>    NULL
                                 ,x_ATTRIBUTE14                 =>    NULL
                                 ,x_ATTRIBUTE15                 =>    NULL
                                 ,x_address_line1               =>    r_get_locator_rec.address_line1
                                 ,x_address_line2               =>    r_get_locator_rec.address_line2
                                 ,x_address_line3               =>    r_get_locator_rec.address_line3
                                 ,x_address_line4               =>    r_get_locator_rec.address_line4
                                 ,x_county                      =>    NULL
                                 ,x_city                        =>    r_get_locator_rec.city
                                 ,x_state                       =>    r_get_locator_rec.state
                                 ,x_province                    =>    r_get_locator_rec.province
                                 ,x_zip_code                    =>    r_get_locator_rec.zip_code
                                 ,x_country                     =>    r_get_locator_rec.country
                                 ,x_territory_id                =>    NULL
                                 ,x_addr_last_update_date       =>    NULL
                                 ,x_addr_last_updated_by        =>    NULL
                                 ,x_addr_creation_date          =>    NULL
                                 ,x_addr_created_by             =>    NULL
                                 ,x_addr_last_update_login      =>    NULL
                                 ,x_addr_attribute_category     =>    NULL
                                 ,x_addr_attribute1             =>    NULL
                                 ,x_addr_attribute2             =>    NULL
                                 ,x_addr_attribute3             =>    NULL
                                 ,x_addr_attribute4             =>    NULL
                                 ,x_addr_attribute5             =>    NULL
                                 ,x_addr_attribute6             =>    NULL
                                 ,x_addr_attribute7             =>    NULL
                                 ,x_addr_attribute8             =>    NULL
                                 ,x_addr_attribute9             =>    NULL
                                 ,x_addr_attribute10            =>    NULL
                                 ,x_addr_attribute11            =>    NULL
                                 ,x_addr_attribute12            =>    NULL
                                 ,x_addr_attribute13            =>    NULL
                                 ,x_addr_attribute14            =>    NULL
                                 ,x_addr_attribute15            =>    NULL
                                 ,x_common_area_flag            =>    NULL
                                 ,x_active_start_date           =>    SYSDATE
                                 ,x_active_end_date             =>    NULL
                                 ,x_return_status               =>    v_return_status   --out parameter
                                 ,x_return_message              =>    v_return_msg      --out parameter
                                 ,x_bookable_flag               =>    NULL
                                 ,x_change_mode                 =>    NULL
                                 ,x_occupancy_status_code       =>    'Y'
                                 ,x_assignable_emp              =>    'Y'
                                 ,x_assignable_cc               =>    'Y'
                                 ,x_assignable_cust             =>    'Y'
                                 ,x_disposition_code            =>    NULL
                                 ,x_acc_treatment_code          =>    NULL
                                 ,x_source                      =>    NULL
                           --     , x_address_style              =>    NULL
                                );