Bank and Bank Branch Conversion

Sample Data File
------------------
ICICI BANK|ICICI BEGUMPET|US|03-SEP-2013|
ICICI BANK|ICICI KPHB|USN|03-SEP-2013|
SBI BANK|SBI BEGUMPET|US|03-SEP-2013|
SBI BANK|SBI KPHB|USX|03-SEP-2013|
SBH BANK|ICICI BEGUMPET|US|03-SEP-2013|
ICICI BANK|ICICI SRNAGAR|US|03-SEP-2013|

Control File
------------------------
LOAD DATA
INFILE "&1"
APPEND INTO TABLE XXI_BANK_BRANCH_STG_SK
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(BANK_NAME,
BANK_BRANCH_NAME ,
COUNTRY,
START_DATE,
CREATED_BY    CONSTANT"-1",
CREATION_DATE    "SYSDATE",
LAST_UPDATED_BY    CONSTANT "-1",
LAST_UPDATE_DATE    "SYSDATE",
PROCESS_FLAG    CONSTANT"1")

Package Code
------------------------

CREATE OR REPLACE PACKAGE I_CE_BANK_BRANCH_PKG_SK IS
PROCEDURE MESSAGE(P_MESSAGE IN VARCHAR2);
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
               RETCODE OUT NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY I_CE_BANK_BRANCH_PKG_SK IS

PROCEDURE MESSAGE(P_MESSAGE IN VARCHAR2) IS
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,P_MESSAGE);
END;

PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
               RETCODE OUT NUMBER) IS
              
ln_bank_id              NUMBER;
lv_return_status        VARCHAR2(1);
ln_msg_count            NUMBER;
lv_msg_data             VARCHAR2(4000);
lr_response             IBY_FNDCPT_COMMON_PUB.Result_rec_type;  
lr_ext_bank_rec         IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
lr_ext_bank_branch_rec  IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
ln_branch_id            NUMBER;
              
BEGIN

        FOR rec_bank IN (SELECT DISTINCT bank_name,country
                           FROM XXI_BANK_BRANCH_STG_SK
                          WHERE process_flag =1)
        LOOP
       
       
        lr_ext_bank_rec.bank_name := rec_bank.bank_name;
        lr_ext_bank_rec.institution_type := 'BANK';
        lr_ext_bank_rec.country_code := rec_bank.country;
        lr_ext_bank_rec.object_version_number := '1.0';
       
       
         IBY_EXT_BANKACCT_PUB.create_ext_bank (
                    '1.0',
                    FND_API.G_TRUE,
                    lr_ext_bank_rec,
                    ln_bank_id  ,
                    lv_return_status ,
                    ln_msg_count,
                    lv_msg_data ,
                    lr_response ); 
           MESSAGE('                       ');
           MESSAGE('lv_return_status bank:-'||lv_return_status);  
            
           IF lv_return_status = 'S' THEN
              COMMIT;
              MESSAGE('Bank id:-'||ln_bank_id);

            FOR rec_branch IN (SELECT bank_branch_name,
                                      country,
                                      start_date
                                 FROM XXI_BANK_BRANCH_STG_SK
                                WHERE process_flag =1
                                  AND bank_name = rec_bank.bank_name)
              LOOP
            
               
                lv_return_status := NULL;
                ln_msg_count     := NULL;
                lv_msg_data      := NULL;
                lr_response      := NULL;
               
               
                lr_ext_bank_branch_rec.bank_party_id := ln_bank_id;
                lr_ext_bank_branch_rec.branch_name   := rec_branch.bank_branch_name;
                lr_ext_bank_branch_rec.branch_type   := 'SWIFT';

               
                IBY_EXT_BANKACCT_PUB.create_ext_bank_branch (
                '1.0',
                 FND_API.G_TRUE,
                 lr_ext_bank_branch_rec,
                 ln_branch_id,
                 lv_return_status,
                 ln_msg_count,
                 lv_msg_data,
                 lr_response
                    );
                   
                   
                MESSAGE('lv_return_status bank branch:-'||lv_return_status);  
            
               IF lv_return_status = 'S' THEN
                    COMMIT;
                      MESSAGE('Bank Branch id:-'||ln_branch_id);
             
                        ELSE
          
                            MESSAGE('ln_msg_count branch:-'||ln_msg_count);
                            MESSAGE('lv_msg_data  branch:-'||lv_msg_data);          
                                 
                        END IF;
        
               END LOOP;      
            
           ELSE
              MESSAGE('Error Message Count For Bank:-'||ln_msg_count);
              MESSAGE('Error Msg Data For Bank:-'||lv_msg_data);

              END IF;
                   
       
        END LOOP;
EXCEPTION
        WHEN OTHERS THEN
             MESSAGE('Err msg:-'||SQLERRM);
             ROLLBACK;
             RETCODE := 2;              
END;
END;

Check Base Tables
---------------------------
SELECT * FROM CE_BANKS_V WHERE BANK_NAME IN('ICICI BANK','SBI BANK','SBH BANK')

SELECT * FROM CE_BANK_BRANCHES_V WHERE BANK_PARTY_ID IN(424995,425001,425005,410933)

Purchase Order Interface

Purchase Order Interface/Conversion.

Application.
    Purchasing PO.

Validations.
    . Purchasing category should not be null.
    . Agent name should not be null.
    . Organization should exists for ship to organization.
    . Address1 should not be null.
    . Vendor name should not be null.
    . Vendor site code should not be null.
    . Bill to location should not be null and should be a valid one.
    . Ship to location should not be null and should be a valid one at both header and line level.
    . Charge account should be a valid one.

Interface Tables.
    PO_HEADERS_INTERFACE.
    PO_LINES_INTERFACE.
    PO_DISTRIBUTIONS_INTERFACE.

Base Tables.
    PO_HEADERS_ALL
    PO_LINES_ALL

    PO_DISTRIBUTIONS_ALL

Import Program Name.
    Purchase Order Open Interface.

UTL File Read a file, UTL Write a file , UTL Move the file, UTL Remove the file in Oracle Applications

CREATE OR REPLACE PROCEDURE apps.xx_file_copy_test (
   errbuf    OUT   VARCHAR2
  ,retcode   OUT   NUMBER
)
AS
   msg                 VARCHAR2 (4000)    := NULL;
   ufilehndl           UTL_FILE.file_type;
   ufilehnd2           UTL_FILE.file_type;
   sfilename           VARCHAR2 (30)      := 'FND_USER.txt';
   l_source_dir        VARCHAR2 (500);
   l_destination_dir   VARCHAR2 (500);
   i                   NUMBER             := 0;
   desfilename         VARCHAR2 (500)
                              := TO_CHAR (SYSDATE, 'MMDDYYHH24MISS')
                                 || '.txt';
BEGIN
   desfilename := 'UTLFILE' || TO_CHAR (SYSDATE, 'MMDDYYHH24MISS') || '.txt';
   ufilehndl := UTL_FILE.fopen ('/u005/data/XXX/appl/temp', sfilename, 'r');
   ufilehnd2 :=
            UTL_FILE.fopen ('/u004/XXXX/XXX/appl/data/in', desfilename, 'w');

   LOOP
      BEGIN
         i := i + 1;
         UTL_FILE.get_line (ufilehndl, msg);
         fnd_file.put_line (fnd_file.LOG, 'Line no ' || i || ' ' || msg);
         UTL_FILE.put_line (ufilehnd2, 'Line no ' || i || ' ' || msg);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;
   END LOOP;

   fnd_file.put_line (fnd_file.LOG, 'After done all lines');
   fnd_file.put_line (fnd_file.LOG
                     ,'************************************************'
                     );
-- Close the output file
   UTL_FILE.fclose (ufilehndl);
   UTL_FILE.fclose (ufilehnd2);
   retcode := 0;
   sfilename := 'UNIXCommands.pdf';
   desfilename := 'FileCopy' || TO_CHAR (SYSDATE, 'MMDDYYHH24MISS') || '.txt';
   fnd_file.put_line (fnd_file.LOG, 'Started');
   --DBMS_FILE_TRANSFER.copy_file
   UTL_FILE.fcopy ('/u005/data/XXXX/appl/temp', sfilename, '/u004/XXXX/XXX/appl/data/in', TO_CHAR (SYSDATE, 'MMDDYYHH24MISS') ||sfilename);
   fnd_file.put_line (fnd_file.LOG, 'file moved');
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      UTL_FILE.fclose (ufilehndl);
      fnd_file.put_line (fnd_file.LOG, 'Error ' || SQLERRM);
      errbuf := 'Error ' || SQLERRM;
      retcode := 2;
END;

Supplier One time insert

Text Data

insert into AP_SUPPlIERS_INT(VENDOR_INTERFACE_ID,VENDOR_NAME) values (ap_suppliers_int_s.NEXTVAL,'ICICI222')

insert into ap_supplier_sites_int(vendor_interface_id,vendor_site_interface_id,VENDOR_SITE_CODE,COUNTRY,ADDRESS_LINE1,org_id)

values(ap_suppliers_int_s.currval,ap_supplier_sites_int_s.NEXTVAL,'Hyderabad','IN','SRNAGAR',204)

select status from ap_suppliers_int where vendor_name = 'ICICI222' --and status is null

select * from ap_supplier_sites_int where vendor_interface_id = 13006 --vendor_site_code = 'Hyderabad'

select * from fnd_user where user_name = 'OPERATIONS'

select * from fnd_responsibility where RESPONSIBILITY_KEY = 'PAYABLES_OPERATION'

select * from ap_suppliers_int

delete from ap_suppliers_int

select * from ap_supplier_sites_int

delete from ap_supplier_sites_int

 concurrent program -- supplier open interface import

 short name-----APXSUIMP

 resp id --50554

 resp_app_id--200

 user_id--1318

 

commit

select * from ap_suppliers where vendor_name='ICICI999'

select * from ap_supplier_sites_all where vendor_id =47189

Submit Request for supplier

=======================================

declare

v_request_id number;

begin

FND_GLOBAL.APPS_INITIALIZE(1318,50554,200);

v_request_id := FND_REQUEST.SUBMIT_REQUEST

                ('SQLAP',

                 'APXSUIMP',

                 null,

                 sysdate,

                 TRUE,

                  'ALL',

                 1000,

                 'N',

                 'N',

                 'N');

if v_request_id > 0 then

dbms_output.put_line('Request submitted successfully');

dbms_output.put_line(v_request_id);

else

dbms_output.put_line('Request submission failure');

dbms_output.put_line('errmsg:-'||sqlerrm);

dbms_output.put_line('errcode:-'||sqlcode);

end if;

commit;

end;

2) submit request for supplier sites

===============================================

declare

v_request_id number;

begin

FND_GLOBAL.APPS_INITIALIZE(1318,50554,200);

v_request_id := FND_REQUEST.SUBMIT_REQUEST

                ('SQLAP',

                 'APXSSIMP',

                 null,

                 sysdate,

                 TRUE,

                  'ALL',

                 1000,

                 'N',

                 'N',

                 'N');

if v_request_id > 0 then

dbms_output.put_line('Request submitted successfully');

dbms_output.put_line(v_request_id);

else

dbms_output.put_line('Request submission failure');

dbms_output.put_line('errmsg:-'||sqlerrm);

dbms_output.put_line('errcode:-'||sqlcode);

end if;

commit;end;

insert into AP_SUP_SITE_CONTACT_INT(LAST_NAME,ORG_ID,VENDOR_CONTACT_INTERFACE_ID,VENDOR_INTERFACE_ID,PHONE,vendor_id,vendor_site_id)                                     

values('nalam',204,AP_SUP_SITE_CONTACT_INT_S.NEXTVAL,ap_suppliers_int_s.currval,9959666537,47192,) 

3) Submit Request for contact Information

===================================================

declare

v_request_id number;

begin

FND_GLOBAL.APPS_INITIALIZE(1318,50554,200);

v_request_id := FND_REQUEST.SUBMIT_REQUEST

                ('SQLAP',

                 'APXSCIMP',

                 null,

                 sysdate,

                 TRUE,

                  'ALL',

                 1000,

                 'N',

                 'N',

                 'N');

if v_request_id > 0 then

dbms_output.put_line('Request submitted successfully');

dbms_output.put_line(v_request_id);

else

dbms_output.put_line('Request submission failure');

dbms_output.put_line('errmsg:-'||sqlerrm);

dbms_output.put_line('errcode:-'||sqlcode);

end if;

commit;end;

supplier Api

===================================================

ap_vendor_pub_pkg

POS_SUPP_CONTACT_PKG

fnd_api

select * from all_tables where table_name like 'FND%REQUEST%'

SELECT * FROM FND_CONCURRENT_REQUESTS WHERE TRUNC(SYSDATE)=TRUNC(REQUEST_DATE)

AND CONCURRENT_PROGRAM_ID = 47176

ORDER BY 1 DESC