Supplier site creation api (ap_vendor_pub_pkg.create_vendor_site)

This below script used for Roll out project & creating supplier site in new OU for which details avail and copied from existing OU.
======================================================================


CREATE TABLE APPS.XXEEC_SUPPLIER_SITE_API_ECIHC
(
  VENDOR_ID                      NUMBER         NOT NULL,
  VENDOR_SITE_CODE               VARCHAR2(15 BYTE) NOT NULL,
  ADDRESS_LINE1                  VARCHAR2(240 BYTE),
  ADDRESS_LINE2                  VARCHAR2(240 BYTE),
  ADDRESS_LINE3                  VARCHAR2(240 BYTE),
  CITY                           VARCHAR2(60 BYTE),
  STATE                          VARCHAR2(150 BYTE),
  ZIP                            VARCHAR2(60 BYTE),
  PROVINCE                       VARCHAR2(150 BYTE),
  COUNTRY                        VARCHAR2(60 BYTE),
  ACCTS_PAY_CODE_COMBINATION_ID  NUMBER,
  PURCHASING_SITE_FLAG           VARCHAR2(1 BYTE),
  PAY_SITE_FLAG                  VARCHAR2(1 BYTE),
  RFQ_ONLY_SITE_FLAG             VARCHAR2(1 BYTE),
  "Supplier Num"                 VARCHAR2(30 BYTE) NOT NULL,
  "Supplier Name"                VARCHAR2(240 BYTE),
  SEGMENT1                       VARCHAR2(30 BYTE) NOT NULL,
  CREATION_DATE                  DATE           NOT NULL,
  SITE_CREATION_DATE             DATE           NOT NULL,
  CREATION_STATUS                VARCHAR2(150 BYTE),
  ERROR_MESSAGE                  VARCHAR2(150 BYTE),
  ORG                            NUMBER
)

--========================================================================================


DECLARE
   p_api_version        NUMBER;
   p_init_msg_list      VARCHAR2 (200);
   p_commit             VARCHAR2 (200);
   p_validation_level   NUMBER;
   x_return_status      VARCHAR2 (200);
   x_msg_count          NUMBER;
   x_msg_data           VARCHAR2 (200);
   p_vendor_site_rec    apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;
   x_vendor_site_id     NUMBER;
   x_party_site_id      NUMBER;
   x_location_id        NUMBER;
   v_msg_index_out    NUMBER;
 
cursor c1 is

--select * from xxeec_supplier_site_api where CREATION_STATUS='N'; --and vendor_id in(2019); --and rownum=1;
select * from xxeec_supplier_site_api_ecihc where CREATION_STATUS='Y'-- and vendor_id in(6,8)
order by segment1; --and rownum=1; --This table contains below query data only
 /*select
 asp.VENDOR_ID
,aspa.VENDOR_SITE_CODE
,nvl(aspa.ADDRESS_LINE1,'ECIHC')ADDRESS_LINE1
,aspa.ADDRESS_LINE2
,aspa.ADDRESS_LINE3
,aspa.city
,aspa.state
,aspa.zip
,aspa.PROVINCE
,nvl(aspa.COUNTRY,'SA')COUNTRY
,aspa.ACCTS_PAY_CODE_COMBINATION_ID
,aspa.purchasing_site_flag
,aspa.pay_site_flag
,aspa.rfq_only_site_flag
,asp.segment1 "Supplier Num"
,asp.vendor_name "Supplier Name"
,asp.segment1
,asp.CREATION_DATE
,asp.last_update_date "SITE_CREATION_DATE"
,asp.ATTRIBUTE1"CREATION_STATUS"
,asp.ATTRIBUTE2"ERROR_MESSAGE"
,aspa.ORG_ID"ORG"
--,asp.VENDOR_TYPE_LOOKUP_CODE
FROM ap_suppliers asp
,ap_supplier_sites_all aspa where 1=1
and asp.vendor_id = aspa.vendor_id
--and asp.vendor_id=2
and org_id =102
and asp.VENDOR_TYPE_LOOKUP_CODE='VENDOR'*/

 
BEGIN
for rec in c1
loop
   p_api_version := 1.0;
   p_init_msg_list := FND_API.G_TRUE;
   p_commit := FND_API.G_TRUE;
   p_validation_level := FND_API.G_VALID_LEVEL_FULL;
   x_return_status := NULL;
   x_msg_count := NULL;
   x_msg_data := NULL;
   P_VENDOR_SITE_REC.AREA_CODE := '';
   P_VENDOR_SITE_REC.PHONE := '';
   P_VENDOR_SITE_REC.FAX_AREA_CODE := '';
   P_VENDOR_SITE_REC.FAX := '';
   P_VENDOR_SITE_REC.VENDOR_ID :=rec.vendor_id;
   P_VENDOR_SITE_REC.VENDOR_SITE_CODE := rec.VENDOR_SITE_CODE; --'HOME';
   --P_VENDOR_SITE_REC.ORG_ID := 204;
   P_VENDOR_SITE_REC.address_line1 := nvl(rec.address_line1,'ECIHC'); --'Ammam Kovil Street';
   P_VENDOR_SITE_REC.address_line2 :=rec.address_line2; --'Vadapalani';
   P_VENDOR_SITE_REC.address_line3 := rec.address_line3; --'Chennai';    
--   P_VENDOR_SITE_REC.org_name := 'Vision Operations';
   P_VENDOR_SITE_REC.org_id := 861;
   P_VENDOR_SITE_REC.country := nvl(rec.country,'SA');
   P_VENDOR_SITE_REC.purchasing_site_flag  :=rec.purchasing_site_flag;
   P_VENDOR_SITE_REC.pay_site_flag         :=rec.pay_site_flag;
   P_VENDOR_SITE_REC.rfq_only_site_flag    :=rec.rfq_only_site_flag;      
   x_vendor_site_id := NULL;
   x_party_site_id := NULL;
   x_location_id := NULL;
   apps.ap_vendor_pub_pkg.create_vendor_site (p_api_version,
                                              p_init_msg_list,
                                              p_commit,
                                              p_validation_level,
                                              x_return_status,
                                              x_msg_count,
                                              x_msg_data,
                                              p_vendor_site_rec,
                                              x_vendor_site_id,
                                              x_party_site_id,
                                              x_location_id
                                             );
   DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
   DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || TO_CHAR (x_msg_count));
   DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);
   DBMS_OUTPUT.put_line ('X_VENDOR_SITE_ID = ' || TO_CHAR (x_vendor_site_id));
   DBMS_OUTPUT.put_line ('X_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_id));
   DBMS_OUTPUT.put_line ('X_LOCATION_ID = ' || TO_CHAR (x_location_id));
   DBMS_OUTPUT.put_line ('');
   COMMIT;
 
 
  IF x_return_status = 'S' THEN
  UPDATE
  --xxeec_supplier_site_api
  xxeec_supplier_site_api_ecihc
               SET creation_status = 'Y',
                   error_message = rec.vendor_id ||'---Supplier Site Created',
                   site_creation_date=sysdate
             WHERE vendor_id = rec.vendor_id;
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
  IF x_msg_count > 0 THEN
    FOR v_index IN 1 .. x_msg_count
    LOOP
      fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
      x_msg_data := SUBSTR (x_msg_data, 1, 100);
      DBMS_OUTPUT.put_line (rec.vendor_id||'--'||x_msg_data);
      UPDATE
      --xxeec_supplier_site_api
      xxeec_supplier_site_api_ecihc
               SET creation_status = 'N',
                   error_message = rec.vendor_id ||'-'||x_msg_data||'---Supplier Site NOT AT ALL Created',
                   site_creation_date=sysdate
             WHERE vendor_id = rec.vendor_id;
     
    END LOOP;
  END IF;
  end loop;

EXCEPTION
WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('SQL Error' || SQLERRM);
END;

Customer site creation API ( hz_cust_account_site_v2pub.create_cust_acct_site)

This below script used for Roll out project & creating customer site in new OU for which details avail and copied from existing OU.
======================================================================

CREATE TABLE APPS.XXEEC_CUST_SITE_API
(
  CUST_ACCOUNT_ID     NUMBER(15)                NOT NULL,
  PARTY_SITE_ID       NUMBER(15)                NOT NULL,
  PARTY_NAME          VARCHAR2(360 BYTE)        NOT NULL,
  CUST_ACCT_SITE_ID   NUMBER(15)                NOT NULL,
  ADDRESS1            VARCHAR2(240 BYTE)        NOT NULL,
  ADDRESS2            VARCHAR2(240 BYTE),
  ADDRESS3            VARCHAR2(240 BYTE),
  ADDRESS4            VARCHAR2(240 BYTE),
  POSTAL_CODE         VARCHAR2(60 BYTE),
  STATE               VARCHAR2(60 BYTE),
  COUNTRY             VARCHAR2(80 BYTE),
  BILL_TO_LOCATION    VARCHAR2(40 BYTE),
  SHIP_TO_LOCATION    VARCHAR2(40 BYTE),
  ACCOUNT_NUMBER      VARCHAR2(30 BYTE)         NOT NULL,
  SITE_CREATION_DATE  DATE,
  CREATION_STATUS     VARCHAR2(10 BYTE),
  ERROR_MESSAGE       VARCHAR2(240 BYTE)
)

--==========================================================================================================

CREATE OR REPLACE PROCEDURE APPS.XXEEC_CUST_SITE_CREATION_PROC(
      errbuff       OUT   VARCHAR2,
      retcode       OUT   VARCHAR2,
      p_orgid       in    number
   )
   IS
--Created by Senthil Kalvarayapillai 10-Mar-2013
--Creating only CUSTOMER site , for existing CUSTOMER from old OU TO nEW OU.

   p_cust_acct_site_rec         hz_cust_account_site_v2pub.cust_acct_site_rec_type;
   p_cust_site_use_rec          hz_cust_account_site_v2pub.cust_site_use_rec_type;
   p_customer_profile_rec       hz_customer_profile_v2pub.customer_profile_rec_type;
   x_return_status              VARCHAR2 ( 2000 );
   x_msg_count                  NUMBER;
   x_msg_data                   VARCHAR2 ( 2000 ) ;
   x_cust_acct_site_id          NUMBER;
   p_site_use_id                NUMBER;
   po_return_status1            VARCHAR2 (100);
   po_msg_count1                NUMBER;
   po_msg_data1                 VARCHAR2 (1000);
   x_site_use_id                NUMBER;
   ln_business_group_id         number;
   ln_user_id                   number;
   ln_org_id                    number;
   ln_resp_id                   number;
   v_error_message              varchar2(240);
 
cursor c1 is

--select * from xxeec_cust_site_api where CREATION_STATUS='N'order by account_number; --and vendor_id in(326173);--This table contains below query data only

select * from xxeec_cust_site_api where CREATION_STATUS='N' order by account_number;
--and ACCOUNT_NUMBER in(1000,1001);--This table contains below query data only

/*select distinct hcas.CUST_ACCOUNT_ID,hcas.PARTY_SITE_ID,hp.PARTY_NAME,CUST_ACCT_SITE_ID
,hl.ADDRESS1,hl.ADDRESS2,hl.ADDRESS3,hl.ADDRESS4,hl.POSTAL_CODE,hl.STATE
,(SELECT ftt.territory_short_name
          FROM fnd_territories_tl ftt, hz_locations hl
         WHERE hl.country = ftt.territory_code
           AND hl.location_id = hps.location_id and rownum=1) country
,(SELECT DISTINCT hcsua.LOCATION
                   FROM hz_cust_site_uses_all hcsua
                  WHERE hcsua.cust_acct_site_id = hcas.cust_acct_site_id
                    AND hcsua.site_use_code = 'BILL_TO') bill_to_location,
       (SELECT DISTINCT hcsua.LOCATION
                   FROM hz_cust_site_uses_all hcsua
                  WHERE hcsua.cust_acct_site_id = hcas.cust_acct_site_id
                    AND hcsua.site_use_code = 'SHIP_TO'
  and hcsua.status='A') ship_to_location,HCA.account_number
from
hz_cust_accounts hca
,HZ_CUST_ACCT_SITES_ALL hcas
,hz_parties hp
,hz_party_sites hps
,hz_locations hl
where 1=1
and hca.cust_account_id=hcas.cust_account_id
--and hcas.ORG_ID=801
and hca.PARTY_ID=hp.PARTY_ID
and hca.PARTY_ID=hps.PARTY_ID
and hps.LOCATION_ID=hl.LOCATION_ID
and hcas.ORG_ID=102
order by HCA.account_number
;*/
 
BEGIN
            ln_user_id := fnd_profile.VALUE ('USER_ID');-- Get the user_id
            ln_org_id  := fnd_profile.VALUE ('ORG_ID');  -- Get the Org_id
            ln_resp_id := fnd_profile.VALUE ('RESP_ID');-- Get the Resp_id
           
            FND_FILE.put_line(FND_FILE.output,'user id '||ln_user_id);
            FND_FILE.put_line(FND_FILE.output,'org id ' ||ln_org_id);
            FND_FILE.put_line(FND_FILE.output,'resp id '||ln_resp_id);
            --Initializing the variables
            fnd_global.apps_initialize (user_id           => ln_user_id,
                                        resp_id           => ln_resp_id, --50639,
                                        resp_appl_id      => 800
                                             );
            --mo_global.init ('PER');
            mo_global.set_policy_context ('S', ln_org_id);
           
For REC IN C1
    Loop
    begin  
               
   p_cust_acct_site_rec.cust_account_id   := REC.cust_account_id; --219251;
   p_cust_acct_site_rec.party_site_id     := rec.party_site_id; --212126;
   p_cust_acct_site_rec.org_id            := P_ORGID;
   --p_cust_acct_site_rec.location_id       := 6640;
   --p_cust_acct_site_rec.language          := 'US';
   --The Above Language Column is Obsolete in R12
   p_cust_acct_site_rec.created_by_module := 'TCA_V2_API';
 
 
   p_cust_site_use_rec.site_use_code        :='BILL_TO';
   p_cust_site_use_rec.cust_acct_site_id    :=REC.cust_account_id; --219251; --p_cust_acct_site_id;
   p_cust_site_use_rec.created_by_module    :='HZ_IMPORT';
                                                                 
                                                                 
 
   mo_global.init ( 'AR' ) ;
   mo_global.set_org_context ( P_ORGID, NULL, 'AR' ) ;
   fnd_global.set_nls_context ( 'AMERICAN' ) ;
   mo_global.set_policy_context ( 'S', P_ORGID ) ;
   hz_cust_account_site_v2pub.create_cust_acct_site (    'T'                   ,
                                                         p_cust_acct_site_rec  ,
                                                         x_cust_acct_site_id   ,
                                                         x_return_status       ,
                                                         x_msg_count           ,
                                                         x_msg_data
                                                    ) ;
                                                   
    dbms_output.put_line ( 'x_return_status = '||SUBSTR ( x_return_status, 1, 255 ) );
   dbms_output.put_line ( 'x_msg_count = '||TO_CHAR ( x_msg_count ) ) ;
   dbms_output.put_line ( 'Customer Account Site Id is = '||TO_CHAR ( x_cust_acct_site_id ) ) ;
   dbms_output.put_line ( 'x_msg_data = '|| SUBSTR ( x_msg_data, 1, 255 ) ) ;
   IF x_msg_count >1 THEN
      FOR I      IN 1..x_msg_count
      LOOP
         dbms_output.put_line ( I||'.'||SUBSTR ( FND_MSG_PUB.Get ( p_encoded=> FND_API.G_FALSE ), 1, 255 ) ) ;
         fnd_file.put_line (fnd_file.output,rec.cust_account_id ||'ERROR in customer site creation'||fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false));
         v_error_message :=rec.cust_account_id ||'orgid -'||P_ORGID||'-ERROR in customer site creation----'||fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);
       
         UPDATE xxeec_cust_site_api
               SET creation_status = 'N',
                   error_message = v_error_message,
                   site_creation_date=sysdate
             WHERE CUST_ACCOUNT_ID = rec.cust_account_id;
             commit;
       
      END LOOP;
   ----END IF;
 
   else    
                   dbms_output.put_line('customer Site Created!');
                   fnd_file.put_line (fnd_file.output,'customer Site Created for--'||rec.cust_account_id);
                 
                   UPDATE xxeec_cust_site_api
               SET creation_status = 'Y',
                   error_message = rec.cust_account_id ||'orgid-'||P_ORGID||'---customer Site Created',
                   site_creation_date=sysdate
             WHERE CUST_ACCOUNT_ID = rec.cust_account_id;

            COMMIT;
            end if;
 

      ------------------------------------------------------------
    begin
   p_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
   p_cust_site_use_rec.site_use_code     := 'BILL_TO';
   p_cust_site_use_rec.location          := REC.BILL_TO_LOCATION; --'NEW INV LOCATION-6582';
   p_cust_site_use_rec.created_by_module := 'TCA_V2_API';
 
   hz_cust_account_site_v2pub.create_cust_site_use (  p_init_msg_list          =>   'T'                     ,
                                                      p_cust_site_use_rec      =>   p_cust_site_use_rec     ,
                                                      p_customer_profile_rec  =>   p_customer_profile_rec  ,
                                                      p_create_profile         =>   ''                      ,
                                                      p_create_profile_amt     =>   ''                      ,
                                                      x_site_use_id            =>   x_site_use_id           ,
                                                      x_return_status          =>   x_return_status         ,
                                                      x_msg_count              =>   x_msg_count             ,
                                                      x_msg_data               =>   x_msg_data
                                                   );
                                                 
    dbms_output.put_line ( 'x_return_status = '||SUBSTR ( x_return_status, 1, 255 ) ) ;
   dbms_output.put_line ( 'x_msg_count = '||TO_CHAR ( x_msg_count ) ) ;
   dbms_output.put_line ( 'Site Use Id = '||TO_CHAR ( x_site_use_id ) ) ;
   dbms_output.put_line ( 'Site Use = '|| SUBSTR ( p_cust_site_use_rec.site_use_code, 1, 255 ) ) ;
   dbms_output.put_line ( 'x_msg_data = '|| SUBSTR ( x_msg_data, 1, 255 ) ) ;
   IF x_msg_count >1 THEN
      FOR I      IN 1..x_msg_count
      LOOP
         dbms_output.put_line ( I||'.'||SUBSTR ( FND_MSG_PUB.Get ( p_encoded=> FND_API.G_FALSE ), 1, 255 ) ) ;
      END LOOP;
   END IF;                                                  
 
 
   END LOOP;
 
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE ( 'Error: '||SQLERRM ) ;
END;
END LOOP;
end XXEEC_CUST_SITE_CREATION_PROC;
/

FNDLOAD Examples

/****************************************************/
Below example for fndlookups
================================
Lookup Type :XXRCIL_MARKETING_FEASIBILITY
App short Name : XXRCIL
Mode      :Download

Go to putty==> /home/CRPERP/appl/apps/apps_st/appl/fnd/12.0.0/patch/115/import
execute the below command
/****************************************************/
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXRCIL_MARKETING_FEASIBILITY.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXRCIL" LOOKUP_TYPE="XXRCIL_MARKETING_FEASIBILITY"

Go to winscp ==>/home/CRPERP/appl/apps/apps_st/appl/fnd/12.0.0/patch/115/import
find the XXRCIL_MARKETING_FEASIBILITY.ldt file and move to local system

once Move to local system

/****************************************************/
Lookup Type :XXRCIL_MARKETING_FEASIBILITY
App short Name : XXRCIL
Mode      :upload

Go to winscp ==>/d01/product/appldev/apps/apps_st/appl/fnd/12.0.0/patch/115/import
find the XXRCIL_MARKETING_FEASIBILITY.ldt file from your local system and move to above path

Go to putty==> /d01/product/appldev/apps/apps_st/appl/fnd/12.0.0/patch/115/import
execute the below command
/****************************************************/

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXRCIL_MARKETING_FEASIBILITY.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

now check in application(Target instance)==> Go to application developer==>Application==>Lookups==>common==>F11==>XXRCIL_MARKETING_FEASIBILITY
------------------------------------------------------------------------------------------------------------------------------------------------------

Query for taking Custom scripts(Package,function,procedure,Tables) Backup from the projects

Query for taking Custom scripts(Package,function,procedure) Backup from the projects
================================================================
declare
v_text varchar2(4000);

 begin
 FOR rec_c1 IN 
 (select * from all_objects where object_name like '%XX%'AND OBJECT_TYPE in ('PACKAGE BODY','FUNCTION','PROCEDURE')--and rownum<3 br=""> )
 
  LOOP
     
     DBMS_OUTPUT.put_line ('*************Start---'||rec_c1.OBJECT_NAME||'---'||rec_c1.OBJECT_TYPE||'**********************************');
      DBMS_OUTPUT.put_line ('                                                                    ');
      DBMS_OUTPUT.put_line ('create');
    FOR rec_c2 IN     
    (select * from dba_source where name=rec_c1.Object_name)
    loop
   
    DBMS_OUTPUT.put_line(rec_c2.text);
  end loop;
  DBMS_OUTPUT.put_line ('                                                                    ');
  DBMS_OUTPUT.put_line ('*************--------Ends--------**********************************');
  DBMS_OUTPUT.put_line ('                                                                    ');
  DBMS_OUTPUT.put_line ('                                                                    ');
  end loop;
 end;

Query for taking Custom scripts(Tables) Backup from the projects
====================================================

declare

v_text varchar2(4000);

 begin

 FOR rec_c1 IN

 (select * from all_objects where 1=1

  --and object_name like '%XX%'

    AND OBJECT_TYPE in ('TABLE')

    and object_name in

    ('XXX_AP_INVOICE_CONV_STG'

    )

    )

  LOOP

     

      DBMS_OUTPUT.put_line ('/*************Start---'||rec_c1.OBJECT_NAME||'---'||rec_c1.OBJECT_TYPE||'*****************************/');

      DBMS_OUTPUT.put_line ('                                                                    ');

     DBMS_OUTPUT.put_line ('create table '||rec_c1.OBJECT_NAME||' (');

     

    FOR rec_c2 IN    

    (select * from dba_tab_columns where table_name=rec_c1.Object_name order by column_id asc)

    loop

   

    DBMS_OUTPUT.put_line(rec_c2.column_name||'  '||rec_c2.data_type||'('||rec_c2.data_length||'),');

  end loop;

  DBMS_OUTPUT.put_line (');');

  DBMS_OUTPUT.put_line ('                                                                    ');

  DBMS_OUTPUT.put_line ('/*************--------Ends--------**********************************/');

  DBMS_OUTPUT.put_line ('                                                                    ');

  DBMS_OUTPUT.put_line ('                                                                    ');

   end loop;

 end;

per_medical_assessment_api.create_medical_assessment-HRMS-Create Medical Records through API.

CREATE TABLE APPS.XXAYTB_MEDICAL_MASTER_DATA
(
  SR_NO                   VARCHAR2(240 BYTE),
  EMPLOYEE_NUMBER         VARCHAR2(240 BYTE),
  CONSULTATION_DATE       DATE,
  TYPE                    VARCHAR2(240 BYTE),
  RESULT                  VARCHAR2(240 BYTE),
  EXAMINER_NAME           VARCHAR2(240 BYTE),
  NEXT_CONSULTATION_DATE  VARCHAR2(240 BYTE),
  DESCRIPTION             VARCHAR2(240 BYTE),
  TIME_IN                 VARCHAR2(50 BYTE),
  TIME_OUT                VARCHAR2(50 BYTE),
  SICK_LEAVE_FROM         DATE,
  SICK_LEAVE_TO           DATE,
  CASE_A                  VARCHAR2(240 BYTE),
  REFER_TO                VARCHAR2(240 BYTE),
  MEDICINE_COST           VARCHAR2(100 BYTE),
  THIRD_PARTY_COST        VARCHAR2(100 BYTE),
  REMARKS                 VARCHAR2(240 BYTE),
  API_ERROR               VARCHAR2(240 BYTE),
  PROCESS_FLAG            VARCHAR2(30 BYTE),
  ERROR_MESSAGE           VARCHAR2(4000 BYTE),
  PROCESS_DATE            DATE,
  REQUEST_ID              NUMBER,
  CREATED_BY              NUMBER,
  PERSON_ID               NUMBER,
  OBJECT_VERSION_NUMBER   NUMBER
)

--*****************************************************

CREATE OR REPLACE PROCEDURE APPS.XXAYTB_MEDICAL_PROC(
       errbuff      OUT   VARCHAR2,
      retcode       OUT   NUMBER
   )
   IS
--Created by Senthil Kalvarayapillai wipro 10-Mar-2013
/* Out variables */
   p_validate                       boolean    default false;
   p_medical_assessment_id          number;
   p_object_version_number          number;
  
  
   /* In variables*/
  
   p_person_id                      number;
   p_reference                      varchar2(240);
   p_type                           varchar2(240);
   p_status                         varchar2(240);
   p_status_reason                  varchar2(240)  default null;
   p_organization_id                NUMBER   DEFAULT NULL;
   p_next_consultation_date         DATE     DEFAULT NULL;
   l_medical_assessment_id          number;
   l_object_version_number          number;
  
  
   /* Error Handling Variables */
   v_error_message            varchar2(240);
   v_err_count                number := 0;
   v_cnt                      NUMBER          := 0;
   v_err_code                 VARCHAR2 (50);
   v_err_msg                  VARCHAR2 (2000);
   ln_business_group_id       number;
   ln_user_id                 number;
   ln_org_id                  number;
   ln_resp_id                 number;  
   ln_error                   NUMBER := 0;
   lv_user_msg_err            VARCHAR2(10000) := NULL;
   v_effective_start_date     date;
   v_effective_date           date;
   v_bg_id                    NUMBER;
   v_status                   VARCHAR2 (100);
   l_error_message            VARCHAR2 (2000);
   l_error_code               VARCHAR2 (2000);
  
   CURSOR C1 IS
  
           SELECT distinct ppf.person_id ppf_person_id,
           decode(to_char(xmmd.sick_leave_from,'YYYY')||'/'||to_char(xmmd.sick_leave_from,'MM')||'/'||to_char(xmmd.sick_leave_from,'DD'),'//','',to_char(xmmd.sick_leave_from,'YYYY')||'/'||to_char(xmmd.sick_leave_from,'MM')||'/'||to_char(xmmd.sick_leave_from,'DD'))sick_leave_from_aa,
           decode(to_char(xmmd.sick_leave_to,'YYYY')||'/'||to_char(xmmd.sick_leave_to,'MM')||'/'||to_char(xmmd.sick_leave_to,'DD'),'//','',to_char(xmmd.sick_leave_to,'YYYY')||'/'||to_char(xmmd.sick_leave_to,'MM')||'/'||to_char(xmmd.sick_leave_to,'DD'))sick_leave_to_aa,
           xmmd.*
        FROM
        APPS.XXAYTB_MEDICAL_MASTER_DATA xmmd
        ,per_all_people_f ppf
        where substr(xmmd.EMPLOYEE_NUMBER,3,15)=ppf.EMPLOYEE_NUMBER
        --and xmmd.SR_NO='823'
        --AND XMMD.EMPLOYEE_NUMBER='BC23481'
        AND XMMD.CONSULTATION_DATE IS NOT NULL
        and xmmd.CREATED_BY=666
        --AND XMMD.PROCESS_FLAG IS NULL --29693
--AND XMMD.PROCESS_FLAG ='E' --29693
           ;
  
BEGIN
            ln_user_id := fnd_profile.VALUE ('USER_ID');-- Get the user_id
            ln_org_id :=  fnd_profile.VALUE ('ORG_ID');  -- Get the Org_id
            ln_resp_id := fnd_profile.VALUE ('RESP_ID');-- Get the Resp_id
            FND_FILE.put_line(FND_FILE.output,'user id '||ln_user_id);
            FND_FILE.put_line(FND_FILE.output,'org id '||ln_org_id);
            FND_FILE.put_line(FND_FILE.output,'resp id '||ln_resp_id);
            --Initializing the variables
            fnd_global.apps_initialize (user_id           => ln_user_id,
                                        resp_id           => 50639,
                                        resp_appl_id      => 800
                                             );
            --mo_global.init ('PER');
            mo_global.set_policy_context ('S', ln_org_id);
           
            For REC IN C1
    Loop
    begin      
            per_medical_assessment_api.create_medical_assessment
        ( p_validate => FALSE
        ,p_effective_date => TRUNC(SYSDATE)
        ,p_person_id => rec.ppf_person_id
        ,p_consultation_date => rec.consultation_date
        ,p_consultation_type => 'RMC' --nvl(rec.type,'AM')
        ,p_consultation_result => '' --rec.result
        ,p_examiner_name => rec.examiner_name
        ,p_organization_id => p_organization_id -- med_ass_det_rec.service_provider_id
        ,p_next_consultation_date => p_next_consultation_date
        ,p_description => rec.description
        ,p_attribute1   =>rec.time_in
        ,p_attribute2   =>rec.time_out
        ,p_attribute3   =>rec.sick_leave_from_aa
        ,p_attribute4   =>rec.sick_leave_to_aa
        ,p_attribute5   =>rec.refer_to
        ,p_attribute6   =>rec.medicine_cost
        ,p_attribute7   =>rec.third_party_cost
        ,p_attribute8   =>rec.remarks
        ,p_attribute9   =>rec.CASE_A
        ,p_medical_assessment_id => l_medical_assessment_id
        ,p_object_version_number => l_object_version_number
        );       
       
        dbms_output.put_line('Medical loaded for the employee '||rec.employee_number);
      FND_FILE.put_line(FND_FILE.output,'Medical loaded for the employee '||rec.employee_number);
     
    UPDATE APPS.XXAYTB_MEDICAL_MASTER_DATA
            SET PROCESS_FLAG = 'S',
                error_message = 'SUCCESS--Medical ID IS '||p_medical_assessment_id,
                REQUEST_ID=p_medical_assessment_id               
             WHERE EMPLOYEE_NUMBER = REC.EMPLOYEE_NUMBER
             and SR_NO=rec.SR_NO;
         DBMS_OUTPUT.put_line (   'Medical Created: '
                               || REC.EMPLOYEE_NUMBER
                               || ' '
                               || '-'
                               || ' '
                               || p_medical_assessment_id
                              );
         p_medical_assessment_id := NULL;
         p_object_version_number := NULL;
        
      EXCEPTION
         WHEN OTHERS
         THEN
            v_err_msg := SUBSTR (SQLERRM, 1, 100);
            v_err_code := SQLCODE;
            DBMS_OUTPUT.put_line (   'Medical NOT Created:Error in API.'
                                  || '-'
                                  || REC.EMPLOYEE_NUMBER
                                 );
            FND_FILE.put_line(FND_FILE.output,'Medical NOT loaded for the employee: '||rec.employee_number);                                
            UPDATE XXAYTB_MEDICAL_MASTER_DATA
               SET process_flag = 'E',
                   error_message = v_err_msg
             WHERE EMPLOYEE_NUMBER = REC.EMPLOYEE_NUMBER
             and SR_NO=rec.SR_NO
             ;
            p_medical_assessment_id := NULL;
            p_object_version_number := NULL;
           
      END;
   END LOOP;
   end;
/

Delete concurrent program,executable from Back end

Begin
fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
commit;
End;

query for item onhand quantity & Lot wise query

SELECT  msi.segment1 item_name,mq.subinventory_code, b.SEGMENT1||'.'||b.SEGMENT2||'.'||b.SEGMENT3||'.'||b.SEGMENT4 locator,
SUM (mq.transaction_quantity) on_hand,msi.INVENTORY_ITEM_ID,msi.organization_id
    FROM apps.org_organization_definitions ood,
         apps.mtl_onhand_quantities mq,
         apps.mtl_system_items_b msi,
         mtl_item_locations b
   WHERE 1 = 1
     AND mq.organization_id = msi.organization_id
     AND ood.organization_id(+) = msi.organization_id
     AND mq.inventory_item_id = msi.inventory_item_id
     AND mq.locator_ID = b.Inventory_location_id
     --AND mq.inventory_item_id = b.Inventory_item_id
     --and mq.organization_id = b.Organization_id
     AND msi.segment1 = nvl(:P_ITEM_CODE,msi.segment1)
     and msi.organization_id=nvl(:p_organization_id,msi.organization_id)  
     and mq.subinventory_code=nvl(:p_subinvcode,mq.subinventory_code)
GROUP BY msi.segment1, ood.organization_name, mq.subinventory_code,msi.organization_id,msi.INVENTORY_ITEM_ID,b.SEGMENT1,b.SEGMENT2,b.SEGMENT3,b.SEGMENT4

============================Lotwise Query===============================

select --*
 moqd.ORGANIZATION_ID
,OOD.ORGANIZATION_NAME
,moqd.SUBINVENTORY_CODE
,msib.SEGMENT1 item
,SUM (moqd.primary_transaction_quantity) on_hand
,SUM (moqd.secondary_transaction_quantity) secondary_onhand
,msib.PRIMARY_UOM_CODE
,moqd.secondary_uom_code
,moqd.LOT_NUMBER
,(SELECT EXPIRATION_DATE FROM MTL_LOT_NUMBERS WHERE LOT_NUMBER=moqd.LOT_NUMBER
AND inventory_item_id =MOQD.inventory_item_id AND organization_id =MOQD.organization_id )LOT_EXPIRY_DATE
FROM
 mtl_onhand_quantities_detail moqd
,mtl_system_items_b msib
,ORG_ORGANIZATION_DEFINITIONS OOD
   WHERE 1 = 1
     --AND moqd.organization_id = 143
     /*AND moqd.subinventory_code = 'R DRY HALA'
     AND moqd.inventory_item_id = 9095
     AND moqd.owning_organization_id = 143
     AND moqd.planning_organization_id = 143*/
     AND moqd.INVENTORY_ITEM_ID=msib.INVENTORY_ITEM_ID
     AND moqd.organization_id=msib.organization_id
     AND OOD.organization_id=msib.organization_id
group by
moqd.ORGANIZATION_ID
,moqd.SUBINVENTORY_CODE
,msib.SEGMENT1  
,moqd.secondary_uom_code
,moqd.LOT_NUMBER
,msib.PRIMARY_UOM_CODE
,OOD.ORGANIZATION_NAME
,MOQD.inventory_item_id

=================Using API=====================================

create or replace function XXMADN_GET_OHQTY(
p_inv_item_id in varchar2,
p_org_id number,
p_qty_type          IN VARCHAR2
)
return number is

   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   v_item_id               NUMBER;
   v_organization_id       NUMBER;
   v_qoh                   NUMBER;
   v_rqoh                  NUMBER;
   v_atr                   NUMBER;
   v_att                   NUMBER;
   v_qr                    NUMBER;
   v_qs                    NUMBER;
   v_lot_control_code      BOOLEAN;
   v_serial_control_code   BOOLEAN;
   L_QTY NUMBER;
BEGIN
   SELECT   inventory_item_id, mp.organization_id
     INTO   v_item_id, v_organization_id
     FROM   mtl_system_items_b msib, mtl_parameters mp
    WHERE       msib.inventory_item_id = p_inv_item_id
            AND msib.organization_id = mp.organization_id
            AND msib.organization_id =p_org_id; -- :organization_code;


   v_qoh := NULL;
   v_rqoh := NULL;
   v_atr := NULL;
   v_lot_control_code := FALSE;
   v_serial_control_code := FALSE;


   fnd_client_info.set_org_context (1);


   inv_quantity_tree_pub.query_quantities (
      p_api_version_number    => 1.0,
      p_init_msg_lst          => 'F',
      x_return_status         => x_return_status,
      x_msg_count             => x_msg_count,
      x_msg_data              => x_msg_data,
      p_organization_id       => v_organization_id,
      p_inventory_item_id     => v_item_id,
      p_tree_mode             => apps.inv_quantity_tree_pub.g_transaction_mode,
      p_is_revision_control   => FALSE,
      p_is_lot_control        => v_lot_control_code,
      p_is_serial_control     => v_serial_control_code,
      p_revision              => NULL,                          -- p_revision,
      p_lot_number            => NULL,                        -- p_lot_number,
      p_lot_expiration_date   => SYSDATE,
      p_subinventory_code     => NULL,                 -- p_subinventory_code,
      p_locator_id            => NULL,                        -- p_locator_id,
      p_onhand_source         => 3,
      x_qoh                   => v_qoh,                    -- Quantity on-hand
      x_rqoh                  => v_rqoh,         --reservable quantity on-hand
      x_qr                    => v_qr,
      x_qs                    => v_qs,
      x_att                   => v_att,               -- available to transact
      x_atr                   => v_atr                 -- available to reserve
   );

 IF
p_qty_type='OHQ' THEN --On Hand qty
L_QTY :=v_qoh; --v_QuantityOnhand;
ELSE IF
p_qty_type='ATR' THEN --Available to Reserve
L_QTY :=v_atr;
ELSE IF
p_qty_type='ATT' THEN --Available to Transact
L_QTY :=v_att;
END IF;
END IF;
END IF;

 return L_QTY;
 --return v_atr;

   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);
   DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);


EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END XXMADN_GET_OHQTY;

select XXMADN_GET_OHQTY(183645,1404,'OHQ')from dual

Date format in reports

In reports Parameter use the below format

TRUNC(prh.creation_date)>=nvl(FND_DATE.CANONICAL_TO_DATE(:p_from_date),TRUNC(prh.creation_date))
AND TRUNC(prh.creation_date)<=nvl(FND_DATE.CANONICAL_TO_DATE(:p_to_date),TRUNC(prh.creation_date))

For the above format use the FND_DATE4_REQUIRED_STANDARD valueset.

Query for flex_value valueset

SELECT ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffvs.validation_type,
    ffv.flex_value_id ,
    ffv.flex_value ,
    ffvt.flex_value_meaning ,
    ffvt.description value_description
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_values ffv ,
    fnd_flex_values_tl ffvt
WHERE
    ffvs.flex_value_set_id     = ffv.flex_value_set_id
    and ffv.flex_value_id      = ffvt.flex_value_id
    AND ffvt.language          = USERENV('LANG');

Update expense account for the item in mtl_system_items_interface

Register as concurrent program to get the result. Trying from Back end is not advisable.
===============================================================

For the below custom table having itemcode(
mtl_system_items_interface.segment1 and new expense account)
CREATE TABLE APPS.XXAYTB_GLCODE_UPDATE
(
  ITEMCODE              VARCHAR2(240 BYTE),
  EXPENSE_ACCOUNT_CODE  VARCHAR2(240 BYTE),
  STATUS                VARCHAR2(240 BYTE),
  ERROR_MESSAGE         VARCHAR2(240 BYTE)
)

CREATE TABLE APPS.XXAYTB_GLCODE_CCID_CHECK
(
  SEGMENT1         VARCHAR2(240 BYTE),
  SEGMENT2         VARCHAR2(240 BYTE),
  SEGMENT3         VARCHAR2(240 BYTE),
  SEGMENT4         VARCHAR2(240 BYTE),
  SEGMENT5         VARCHAR2(240 BYTE),
  SEGMENT6         VARCHAR2(240 BYTE),
  NEWCCID          VARCHAR2(240 BYTE),
  OLDCCID          VARCHAR2(240 BYTE),
  ITEMCODE         VARCHAR2(240 BYTE),
  ORGID            VARCHAR2(240 BYTE),
  EXPENSE_ACCOUNT  VARCHAR2(240 BYTE)
)

CREATE TABLE APPS.XXAYTB_GLCODE_UPDATE_STATUS
(
  INVENTORY_ITEM_ID    VARCHAR2(240 BYTE),
  ORGANIZATION_ID      VARCHAR2(240 BYTE),
  ITEMCODE             VARCHAR2(240 BYTE),
  MSIB_CURRVAL         VARCHAR2(240 BYTE),
  CODE_COMBINATION_ID  VARCHAR2(240 BYTE),
  STATUS               VARCHAR2(240 BYTE)
)

/* Formatted on 2013/02/24 09:36 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE apps.xxinv_item_assignment_v1 (
   errbuff   OUT   VARCHAR2,
   retcode   OUT   NUMBER
)
---procedure created for updating expense account for an item  senthil kalvarayapillai wipro 20-feb-2013
IS
   v_error_msg             VARCHAR2 (2000);
   v_msg_cnt               NUMBER;
   v_category_cnt          NUMBER;
   v_set_process_id        NUMBER;
   v_code_combination_id   NUMBER;
   l_segment1              VARCHAR2 (100);
   l_segment2              VARCHAR2 (100);
   l_segment3              VARCHAR2 (100);
   l_segment4              VARCHAR2 (100);
   l_segment5              VARCHAR2 (100);
   l_segment6              VARCHAR2 (100);
   l_item                  VARCHAR2 (100);
   l_ccid                  VARCHAR2 (100);
   l_organization_id       VARCHAR2 (100);
   l_inventory_item_id     VARCHAR2 (100);
   v_charge_ccid_id        NUMBER;
BEGIN
   fnd_file.put_line
      (fnd_file.output,
       '============================INSERTED Records==============================='
      );
   FOR rec_c1 IN (SELECT *
                    FROM xxaytb_glcode_update)
   LOOP
      DBMS_OUTPUT.put_line (rec_c1.itemcode);
      FOR rec_c2 IN (SELECT glcc.segment1, glcc.segment2, glcc.segment3,
                            glcc.segment4, glcc.segment5, glcc.segment6,
                            msib.segment1 item,
                            glcc.code_combination_id oldccid,
                            msib.organization_id, msib.inventory_item_id,
                            rec_c1.expense_account_code expacc,
                            rec_c1.itemcode
                       FROM mtl_system_items_b msib,
                            gl_code_combinations glcc
                      WHERE 1 = 1
                        AND msib.expense_account = glcc.code_combination_id
                        AND msib.segment1 = rec_c1.itemcode)
      LOOP
         DBMS_OUTPUT.put_line (   rec_c2.oldccid
                               || ' - orgid is - '
                               || rec_c2.organization_id
                              );
         v_charge_ccid_id := NULL;
         DBMS_OUTPUT.put_line (   rec_c2.inventory_item_id
                               || ' - '
                               || rec_c2.organization_id
                               || ' - '
                               || rec_c2.itemcode
                               || ' - '
                               || rec_c2.oldccid
                              );
         fnd_file.put_line (fnd_file.output,
                               'inventory_item_id- '
                            || rec_c2.inventory_item_id
                            || 'organization_id- '
                            || rec_c2.organization_id
                            || 'itemcode- '
                            || rec_c2.itemcode
                            || 'oldccid-'
                            || rec_c2.oldccid
                           );
         INSERT INTO xxaytb_glcode_ccid_check
                     (segment1, segment2, segment3,
                      segment4, segment5, segment6,
                      oldccid, itemcode,
                      orgid, expense_account
                     )
              VALUES (rec_c2.segment1, rec_c2.segment2, rec_c2.segment3,
                      rec_c2.segment4, rec_c2.segment5, rec_c2.segment6,
                      rec_c2.oldccid, rec_c2.itemcode,
                      rec_c2.organization_id, rec_c2.expacc
                     );
         BEGIN
            v_charge_ccid_id :=
               fnd_flex_ext.get_ccid
                                 (application_short_name      => 'SQLGL',
                                  key_flex_code               => 'GL#',
                                  structure_number            => 50368,
                                  validation_date             => TO_CHAR
                                                                    (SYSDATE,
                                                                     'DD-MON-YYYY'
                                                                    ),
                                  concatenated_segments       =>    rec_c2.segment1
                                                                 || '.'
                                                                 || rec_c2.segment2
                                                                 || '.'
                                                                 || rec_c2.segment3
                                                                 || '.'
                                                                 || rec_c2.expacc
                                                                 || '.'
                                                                 || rec_c2.segment5
                                                                 || '.'
                                                                 || rec_c2.segment6
                                 );
            COMMIT;
            DBMS_OUTPUT.put_line ('v_charge_ccid_id is:= ' || v_charge_ccid_id);
         END;
         IF v_charge_ccid_id > 0
         THEN
            INSERT INTO mtl_system_items_interface
                        (inventory_item_id, organization_id,
                         segment1, process_flag, transaction_type,
                         set_process_id,
                         expense_account
                        )
                 VALUES (rec_c2.inventory_item_id, rec_c2.organization_id,
                         rec_c2.itemcode, 1, 'UPDATE'               --'CREATE'
                                                     ,
                         mtl_system_items_intf_sets_s.NEXTVAL,
                         v_charge_ccid_id
                        );
            COMMIT;
            INSERT INTO xxaytb_glcode_update_status
                        (inventory_item_id, organization_id,
                         itemcode,
                         msib_currval,
                         code_combination_id, status
                        )
                 VALUES (rec_c2.inventory_item_id, rec_c2.organization_id,
                         rec_c2.itemcode,
                         mtl_system_items_intf_sets_s.CURRVAL,
                         v_charge_ccid_id, 'Success'
                        );
            COMMIT;
         ELSE
            INSERT INTO xxaytb_glcode_update_status
                        (inventory_item_id, organization_id,
                         itemcode,
                         msib_currval,
                         code_combination_id, status
                        )
                 VALUES (rec_c2.inventory_item_id, rec_c2.organization_id,
                         rec_c2.itemcode,
                         mtl_system_items_intf_sets_s.CURRVAL,
                         v_charge_ccid_id, 'Error'
                        );
            COMMIT;
         END IF;
         DBMS_OUTPUT.put_line ('---------------');
      END LOOP;
   END LOOP;
END;
/

Regisatered the concurrent program for the above procedure & submit the program.

Once completed Run the Import items program from inventory Responsibility.