Supplier API Using Cursor

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_rec           apps.ap_vendor_pub_pkg.r_vendor_rec_type;
       x_vendor_id            NUMBER;
       x_party_id               NUMBER;
       V_MSG_INDEX_OUT      NUMBER;
       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;
       p_vendor_contact_rec   apps.ap_vendor_pub_pkg.r_vendor_contact_rec_type;
       x_vendor_contact_id       NUMBER;
       x_per_party_id                NUMBER;
       x_rel_party_id                 NUMBER;
       x_rel_id                           NUMBER;
       x_org_contact_id            NUMBER;
  
       CURSOR PO_HD IS SELECT * FROM  xx_sup_cus;
       CURSOR PO_SUP IS SELECT * FROM xx_sup_sites;
       CURSOR PO_CON IS SELECT * FROM xx_sup_cont;
  
BEGIN
    FOR I IN PO_HD
    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_rec.vendor_name := I.VENDOR_NAME;--'A.V.K';
           p_vendor_rec.vendor_type_lookup_code  := I.VENDOR_TYPE_LOOKUP_CODE;--'Internal';
           p_vendor_rec.women_owned_flag := I.WOMEN_OWNED_FLAG;--'N';
           p_vendor_rec.small_business_flag := I.SMALL_BUSINESS_FLAG;--'Y'; 
           p_vendor_rec.segment1 :=I.SEGMENT1;--'8650'; --(insert non duplicate number)
           x_vendor_id := NULL;
           x_party_id := NULL;
           apps.ap_vendor_pub_pkg.create_vendor (p_api_version,
                                         p_init_msg_list,
                                         p_commit,
                                         p_validation_level,
                                         x_return_status,
                                         x_msg_count,
                                         x_msg_data,
                                         p_vendor_rec,
                                         x_vendor_id,
                                         x_party_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_ID = ' || TO_CHAR (x_vendor_id));
           DBMS_OUTPUT.put_line ('X_PARTY_ID = ' || TO_CHAR (x_party_id));
           DBMS_OUTPUT.put_line ('');
           COMMIT;
       END LOOP;

      IF x_return_status = 'S' THEN
        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, 200);
              DBMS_OUTPUT.put_line (x_msg_data);
    
        END LOOP;
      END IF;
 
BEGIN
    FOR J IN PO_SUP
    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 := J.AREA_CODE;--'';
           P_VENDOR_SITE_REC.PHONE := J.PHONE;--'';
           P_VENDOR_SITE_REC.FAX_AREA_CODE :=J.FAX_AREA_CODE;-- '';
           P_VENDOR_SITE_REC.FAX :=J.FAX;-- '';
           P_VENDOR_SITE_REC.VENDOR_ID :=X_VENDOR_ID; --89182;
           P_VENDOR_SITE_REC.VENDOR_SITE_CODE := J.VENDOR_SITE_CODE;--'HOME';
           P_VENDOR_SITE_REC.ORG_ID := J.ORG_ID;--204;
           P_VENDOR_SITE_REC.address_line1 :=J.ADDRESS_LINE1;-- 'Ammam Kovil Street';
           P_VENDOR_SITE_REC.address_line2 :=J.ADDRESS_LINE2;--'Vadapalani';
           P_VENDOR_SITE_REC.address_line3 :=J.ADDRESS_LINE3;-- 'Chennai';     
           P_VENDOR_SITE_REC.org_name := J.ORG_NAME;--'Vision Operations';
           P_VENDOR_SITE_REC.country := J.COUNTRY;--'US';       
           p_vendor_site_rec.pay_group_lookup_code := j.pay_group_lookup_code;
           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;
       END LOOP;
 
 
      IF x_return_status = 'S' THEN
        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, 200);
              DBMS_OUTPUT.put_line (x_msg_data);
        END LOOP;
      END IF;
EXCEPTION
WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('SQL Error' || SQLERRM);
END;
 
BEGIN
    FOR K IN PO_CON
    LOOP
           p_api_version := 1.0;
           p_init_msg_list := 'T';
           p_commit := 'T';
           p_validation_level := FND_API.G_VALID_LEVEL_FULL;
           x_return_status := NULL;
           x_msg_count := NULL;
           x_msg_data := NULL;
           p_vendor_contact_rec.vendor_contact_id := po_vendor_contacts_s.NEXTVAL;
           DBMS_OUTPUT.put_line ('po_vendor_contacts_s.NEXTVAL = ' ||                                            po_vendor_contacts_s.NEXTVAL);
            P_VENDOR_CONTACT_REC.vendor_site_id :=x_vendor_site_id;--7952;
               P_VENDOR_CONTACT_REC.PERSON_FIRST_NAME := K.PERSON_FIRST_NAME;                                                             P_VENDOR_CONTACT_REC.PERSON_LAST_NAME := K.PERSON_LAST_NAME;                                                         P_VENDOR_CONTACT_REC.PHONE := K.PHONE;                        
               P_VENDOR_CONTACT_REC.EMAIL_ADDRESS := K.EMAIL_ADDRESS;           
               P_VENDOR_CONTACT_REC.URL :=K.URL;     
               P_VENDOR_CONTACT_REC.org_id :=K.ORG_ID;-- 204;
           p_vendor_contact_rec.party_site_id  := x_party_site_id;
           p_vendor_contact_rec.org_party_site_id := x_party_site_id;
           p_vendor_contact_rec.VENDOR_ID := x_vendor_id;          
           x_vendor_contact_id := NULL;
           x_per_party_id := NULL;
           x_rel_party_id :=NULL;
           x_rel_id := NULL;
           x_org_contact_id := NULL;
           x_party_site_id := NULL;
           apps.ap_vendor_pub_pkg.create_vendor_contact (p_api_version,
                                                 p_init_msg_list,
                                                 p_commit,
                                                 p_validation_level,
                                                 x_return_status,
                                                 x_msg_count,
                                                 x_msg_data,
                                                 p_vendor_contact_rec,
                                                 x_vendor_contact_id,
                                                 x_per_party_id,
                                                 x_rel_party_id,
                                                 x_rel_id,
                                                 x_org_contact_id,
                                                 x_party_site_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_CONTACT_ID = ' || TO_CHAR           (x_vendor_contact_id));
           DBMS_OUTPUT.put_line ('X_PER_PARTY_ID = ' || TO_CHAR (x_per_party_id));
           DBMS_OUTPUT.put_line ('X_REL_PARTY_ID = ' || TO_CHAR (x_rel_party_id));
           DBMS_OUTPUT.put_line ('X_REL_ID = ' || TO_CHAR (x_rel_id));
           DBMS_OUTPUT.put_line ('X_ORG_CONTACT_ID = ' || TO_CHAR (x_org_contact_id));
           DBMS_OUTPUT.put_line ('X_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_id));
           DBMS_OUTPUT.put_line ('');
           COMMIT;
       END LOOP;

        IF x_return_status = 'S' THEN
        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, 200);
              DBMS_OUTPUT.put_line (x_msg_data);
        END LOOP;
  END IF;
END;
END;

Oracle Apps Shortcut Keys

http://3.bp.blogspot.com/-O5-3eN76-wI/TVqCze_3YnI/AAAAAAAAAds/7t31QgW_Cb4
/s1600/Oracle+Apps+Short+cut+Keys.JPG

Sales Order using API

1.Create a Stagging Table.

2.Create a Data File and Control File.

3.Move the Data and Control File into the Following Top
  '/oracle2/VIS1211/apps/apps_st/appl/ap/12.0.0/bin/so.txt'


4.In Apps
  Switch Responsibility->Application Developer->Concurrent->Executable

  Executable                     :so

  Short Name                   :so

  Application                   :payables

  Execution File Name    :so

  save it.


  Switch Responsibility->Application Developer->Concurrent->Program

  Program             :so

  Short Name        :so

  Application        :payables

  Name                 :so


 Switch Responsibility->System Administrator->Security->Responsibility->Request

 Application        :payables

 Name                :so

 save it.


 Switch Responsibility->payables,vision operations(USA)

 View->Request->Submit a New Request

 Name            :so

 and click Find Then the Control File will Completed Normal.


After Finishing the Stagging Table Run the Procedure Program

CREATE OR REPLACE
PROCEDURE APPS.vimal_sales
AS
  l_api_version_number NUMBER := 1;
  l_return_status      VARCHAR2(2000);
  l_msg_count          NUMBER;
  l_msg_data           VARCHAR2(2000);
  l_debug_level        NUMBER := 1;     -- OM DEBUG LEVEL (MAX 5)
  l_org                NUMBER := 204;   -- OPERATING UNIT
  l_user               NUMBER :=1318 ;  -- USER
  l_resp               NUMBER := 21623; -- RESPONSIBLILTY
  l_appl               NUMBER := 660;   -- ORDER MANAGEMENT
  l_header_rec oe_order_pub.header_rec_type;
  l_line_tbl oe_order_pub.line_tbl_type;
  l_action_request_tbl oe_order_pub.Request_Tbl_Type;
  l_header_rec_out oe_order_pub.header_rec_type;
  l_header_val_rec_out oe_order_pub.header_val_rec_type;
  l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
  l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
  l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
  l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
  l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
  l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
  l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
  l_line_tbl_out oe_order_pub.line_tbl_type;
  l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
  l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
  l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
  l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
  l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
  l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
  l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
  l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
  l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
  l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
  l_action_request_tbl_out oe_order_pub.request_tbl_type;
  l_msg_index     NUMBER;
  l_data          VARCHAR2(2000);
  l_loop_count    NUMBER;
  l_debug_file    VARCHAR2(200);
  b_return_status VARCHAR2(200);
  b_msg_count     NUMBER;
  b_msg_data      VARCHAR2(2000);
  org             NUMBER;
  ord_id          NUMBER;
  CURSOR c1
  IS
     SELECT * FROM sales_vimal865;
BEGIN
  dbms_application_info.set_client_info(l_org);
  mo_global.init('ONT');
  IF (l_debug_level > 0) THEN
    l_debug_file   := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
    oe_debug_pub.initialize;
    oe_debug_pub.setdebuglevel(l_debug_level);
    oe_Msg_Pub.initialize;
  END IF;
  fnd_global.apps_initialize(1318 ,21623 ,660);
  FOR i IN c1
  LOOP
    IF i.flow_status_code IS NOT NULL THEN
      IF i.org_id         IS NOT NULL THEN
        BEGIN
           SELECT organization_id
             INTO org
             FROM org_organization_definitions
            WHERE organization_id = i.org_id;
        EXCEPTION
        WHEN no_data_found THEN
          -- val := 'org is  null';
          dbms_output.put_line('org is null'||SQLERRM);
        WHEN OTHERS THEN
          -- vals := 'org is invalid';
          dbms_output.put_line('org is invalid'||SQLERRM);
        END;
      END IF;
     
     
      IF i.order_type_id IS NOT NULL THEN
        BEGIN
           SELECT oet.transaction_type_id
             INTO ord_id
             FROM oe_order_headers_all oeh ,
            oe_transaction_types_tl oet
            WHERE oeh.order_type_id = oet.transaction_type_id
          AND oeh.order_type_id      = i.order_type_id;
         /* EXCEPTION
        WHEN no_data_found THEN
          dbms_ouput.put_line('order is invalid'||SQLERRM);
        WHEN OTHERS THEN
          dbms_output.put_line('order is invalidasdfsadfasdf'||SQLERRM);*/
        END;
      END IF;
     
     
      l_header_rec                         := OE_ORDER_PUB.G_MISS_HEADER_REC;
      l_header_rec.operation               := OE_GLOBALS.G_OPR_CREATE;
      l_header_rec.order_type_id           := i.order_type_id;
      l_header_rec.sold_to_org_id          := i.sold_to_org_id;
      l_header_rec.ship_to_org_id          := i.ship_to_org_id;
      l_header_rec.sold_from_org_id        := i.sold_from_org_id;
      l_header_rec.ship_from_org_id        := i.ship_from_org_id;
      l_header_rec.org_id                  := i.org_id;
      l_action_request_tbl(1)              := OE_ORDER_PUB.G_MISS_REQUEST_REC;
      l_action_request_tbl(1).request_type := oe_globals.g_book_order;
      l_action_request_tbl(1).entity_code  := oe_globals.g_entity_header;
      l_line_tbl(1)                        := OE_ORDER_PUB.G_MISS_LINE_REC;
      l_line_tbl(1).operation              := OE_GLOBALS.G_OPR_CREATE;
      dbms_output.put_line('Calling API');
      OE_Order_PUB.Process_Order( p_api_version_number => l_api_version_number, p_header_rec => l_header_rec, p_line_tbl => l_line_tbl, p_action_request_tbl => l_action_request_tbl,
      --OUT variables
      x_header_rec => l_header_rec_out, x_header_val_rec => l_header_val_rec_out, x_header_adj_tbl => l_header_adj_tbl_out, x_header_adj_val_tbl => l_header_adj_val_tbl_out, x_header_price_att_tbl => l_header_price_att_tbl_out, x_header_adj_att_tbl => l_header_adj_att_tbl_out, x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out, x_header_scredit_tbl => l_header_scredit_tbl_out, x_header_scredit_val_tbl => l_header_scredit_val_tbl_out, x_line_tbl => l_line_tbl_out, x_line_val_tbl => l_line_val_tbl_out, x_line_adj_tbl => l_line_adj_tbl_out, x_line_adj_val_tbl => l_line_adj_val_tbl_out, x_line_price_att_tbl => l_line_price_att_tbl_out, x_line_adj_att_tbl => l_line_adj_att_tbl_out, x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out, x_line_scredit_tbl => l_line_scredit_tbl_out, x_line_scredit_val_tbl => l_line_scredit_val_tbl_out, x_lot_serial_tbl => l_lot_serial_tbl_out, x_lot_serial_val_tbl => l_lot_serial_val_tbl_out, x_action_request_tbl => l_action_request_tbl_out, x_return_status =>
      l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data);
      COMMIT;
    END IF;
  END LOOP;
  IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
    dbms_output.put_line('Return status is success ');
    dbms_output.put_line('debug level '||l_debug_level);
    IF (l_debug_level > 0) THEN
      dbms_output.put_line('success');
    END IF;
    COMMIT;
  ELSE
    dbms_output.put_line('Return status failure ');
    IF (l_debug_level > 0) THEN
      dbms_output.put_line('failure');
    END IF;
    ROLLBACK;
  END IF;
  IF (l_debug_level > 0) THEN
    DBMS_OUTPUT.PUT_LINE('process ORDER ret status IS: ' || l_return_status);
    DBMS_OUTPUT.PUT_LINE('process ORDER msg data IS: ' || l_msg_data);
    DBMS_OUTPUT.PUT_LINE('process ORDER msg COUNT IS: ' || l_msg_count);
    DBMS_OUTPUT.PUT_LINE('header.order_number IS: ' || TO_CHAR(l_header_rec_out.order_number));
    DBMS_OUTPUT.PUT_LINE('header.return_status IS: ' || l_header_rec_out.return_status);
    DBMS_OUTPUT.PUT_LINE('header.booked_flag IS: ' || l_header_rec_out.booked_flag);
    DBMS_OUTPUT.PUT_LINE('header.header_id IS: ' || l_header_rec_out.header_id);
    DBMS_OUTPUT.PUT_LINE('header.order_source_id IS: ' || l_header_rec_out.order_source_id);
    DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: ' || l_header_rec_out.flow_status_code);
  END IF;
  IF (l_debug_level > 0) THEN
    FOR i          IN 1 .. l_msg_count
    LOOP
      oe_Msg_Pub.get(p_msg_index => i,p_encoded => Fnd_Api.G_FALSE,p_data => l_data, p_msg_index_out => l_msg_index);
      DBMS_OUTPUT.PUT_LINE('message is: ' || l_data);
      DBMS_OUTPUT.PUT_LINE('message index is: ' || l_msg_index);
    END LOOP;
  END IF;
  IF (l_debug_level > 0) THEN
    DBMS_OUTPUT.PUT_LINE('Debug = ' || OE_DEBUG_PUB.G_DEBUG);
    DBMS_OUTPUT.PUT_LINE('Debug Level = ' || TO_CHAR(OE_DEBUG_PUB.G_DEBUG_LEVEL));
    DBMS_OUTPUT.PUT_LINE('Debug File = ' || OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);
    OE_DEBUG_PUB.DEBUG_OFF;
  END IF;
END;


select * from oe_order_headers_all order by creation_date desc

 

Item Interface

1.Create a Stagging Tables

2.Create a Data File.

DATA FILE:

AVK,204,VIMAL,CREATE,1,1001,@Finished Good,FG,V1

BVK,204,VINOT,CREATE,1,1001,@Finished Good,FG,V1

MRB,204,RAJES,CREATE,1,1001,@Finished Good,FG,V1

RAK,204,ARAVI,CREATE,1,1001,@Finished Good,FG,V1

BVP,204,VIVIN,CREATE,1,1001,@Finished Good,FG,V1

ARK,204,RAJES,CREATE,1,1001,@Finished Good,FG,V1

MBM,204,BALAS,CREATE,1,1001,@Finished Good,FG,V1

MMS,204,SAMIS,CREATE,1,1001,@Finished Good,FG,V1

SJM,204,JOTHI,CREATE,1,1001,@Finished Good,FG,V1

RKN,204,KANNA,CREATE,1,1001,@Finished Good,FG,V1

3.Create a Control File

CONTROL FILE:

LOAD DATA
INFILE '/oracle2/VIS1211/apps/apps_st/appl/inv/12.0.0/bin/oit.txt'
INSERT INTO TABLE iteminterface
FIELDS TERMINATED BY ','
(
SEGMENT1 ,
ORGANIZATION_ID,
DESCRIPTION,
TRANSACTION_TYPE,
PROCESS_FLAG,
SET_PROCESS_ID,
TEMPLATE_NAME,
ITEM_TYPE,
ORGANIZATION_CODE
)

save it.

4.Move Data&Control File in gftp in This directory
    /oracle2/VIS1211/apps/apps_st/appl/inv/12.0.0/bin

5.In Apps

Switch Responsibility->Application Developer->Concurrent->Executable

Executable                     :oit865
Short Name                   :oit865   
Application                    :Inventory
Execution Method         :SQL Loader
Execution File Name     :oit865

Switch Responsibility->Application Developer->Concurrent->Program

Program             :oit865
Short Name        :oit865   
Application        :Inventory
Name                 :oit865



Switch Responsibility->System Administrator

Security->Responsibility->Request

Group             :All Inclusive GUI
Application     :Inventory

Give the Control File Name and save it.


Switch Responsibility->inventory,vision operations(USA)

View->Request->submit a new request


Name        :oit865

click submit

Then the Program Will Compile and Run Normal.

Then the Data's are loaded into stagging table

Table Name:iteminterface

After Finishing the Stagging table move the table to interface table using PL/SQL Procedure.

Before that run the Procedure

 Procedure Program:

CREATE OR REPLACE PROCEDURE APPS.vimal_interface
(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2) IS
    LN_REC_CNT      NUMBER := 0;
      v_template_name NUMBER;
      v_organization  NUMBER;
      l_error_details VARCHAR2 (4000) := NULL;
      l_error_flag    VARCHAR2 (2000) := NULL;
      l_failure_count NUMBER          := 0;
      l_success_count NUMBER          := 0;
      l_user_id       NUMBER          := fnd_global.user_id;
      l_resp_id       NUMBER          := fnd_global.resp_id;
      l_appl_id       NUMBER          := fnd_global.resp_appl_id;
      CURSOR DATA_LOAD
      IS
         SELECT * FROM iteminterface;
BEGIN
      fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
      FOR I IN DATA_LOAD
      LOOP
            l_error_flag    := NULL;
            l_error_details := NULL;
            LN_REC_CNT      := LN_REC_CNT + 1;
            BEGIN
               SELECT COUNT (*)
             INTO v_organization
             FROM org_organization_definitions
            WHERE organization_code = i.organization_code;
              IF v_organization         = 0 THEN
            l_error_flag           := 'Y';
            l_error_details        := l_error_details || '\' || 'Organization Error' || '\' ||             SUBSTR (SQLERRM, 1, 100);
      END IF;
END;
BEGIN
    SELECT COUNT (*)
        INTO v_template_name
        FROM mtl_item_templates
        WHERE template_name = i.template_name;
          IF v_template_name    = 0 THEN
        l_error_flag       := 'Y';
        l_error_details    := l_error_details || '\' || 'Template_Name Error' || '\' || SUBSTR          (SQLERRM, 1, 100);
      END IF;
END;
        IF (l_error_flag   = 'Y') THEN
          l_failure_count := l_failure_count + 1;
        ELSE
           INSERT
         INTO MTL_SYSTEM_ITEMS_INTERFACE
         (
              SEGMENT1        ,
              ORGANIZATION_ID ,
              DESCRIPTION     ,
              TRANSACTION_TYPE,
              PROCESS_FLAG    ,
              SET_PROCESS_ID  ,
              TEMPLATE_NAME   ,
               ITEM_TYPE       ,
              ORGANIZATION_CODE
    )
        VALUES
        (
              i.SEGMENT1        ,
              i.ORGANIZATION_ID ,
              i.DESCRIPTION     ,
              i.TRANSACTION_TYPE,
              i.PROCESS_FLAG    ,
              i.SET_PROCESS_ID  ,
              i.TEMPLATE_NAME   ,
              i.ITEM_TYPE       ,
              i.ORGANIZATION_CODE
    );
    
          l_success_count := l_success_count + 1;
        END IF;
      END LOOP;
      COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      FND_FILE.PUT_LINE
      (
            FND_FILE.LOG,'Records not loaded into the interface table'
      );
END;
Switch Responsibility->Application Developer->Concurrent->Executable

Executable                    :vimal_interface
Short Name                  :vimal_interface
Application                   :Inventory
Execution Method        :PL/SQL Stored Procedure
Execution File Name    :vimal_interface


Switch Responsibility->Application Developer->Concurrent->Program

Program             :vimal_interface
Short Name        :vimal_interface
Application        :Inventory
Name                 :vimal_interface


Switch Responsibility->System Administrator

Security->Responsibility->Request

Group            :All Inclusive GUI
Application    :Inventory

Give the interface table name and save it.

Switch Responsibility->inventory,vision operations(USA)

view->request->submit a new request


Name        :vimal_interface

click submit

Then the Program Will Compile and Run Normal.

Then the Data's are moved from stagging table to interface table.

Table Name:mtl_system_items_interface

Then we want to move from interface table to base table

Inventory Vision Operations->View->Request


Name        :Import Items

click submit

Then the Program Will Compile and Run Normal.

Then the Data's are moved from  interface table to base table.

Table Name:select * from mtl_system_items_b order by creation_date desc;

Error Table:mtl_interface_errors 

SYNTAX SQL

Select Statement

SELECT "column_name" FROM "table_name"

Distinct

SELECT DISTINCT "column_name"
FROM "table_name"

Where

SELECT "column_name"
FROM "table_name"
WHERE "condition"

And/Or

SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}

In

SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)

Between

SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'

Like

SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN}

Order By

SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC]

Count

SELECT COUNT("column_name")
FROM "table_name"

Group By

SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"

Having

SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithematic function condition)

Create Table Statement

CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )

Drop Table Statement

DROP TABLE "table_name"

Truncate Table Statement

TRUNCATE TABLE "table_name"

Insert Into Statement

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

Update Statement

UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition}

Delete From Statement
DELETE FROM "table_name"
WHERE {condition}

SQL

1. Difference between DCL and DML  commands?

    DCL  commands  will   commit  implicitly   where  in  DML  commands  we  have   to commit explicitly.

2. Table, Rowid, Rownum

    Table is a database object, which is having more than one column associated with its data          type.
    Rowid is the unique binary address of the row in a table
    Rownum it  is a temporary  number  in a memory  and  was  assigned  to each  row selected         by the statement.

3. Pseudo-Columns

    Columns  that are not created explicitly by  the user and can be used  explicitly in queries.  The pseudo-Columns  are rowid,  rownum, currval,  nextval,  sysdate, and level

4. What is a View?

    View is Virtual Table, which hold the data at runtime

5. Difference between ordinary view and complex view?
    Simple  views  can be  modified  easily  and these  cant hold  the data where   as complex views  cant be modified  directly.
    complex view can  hold  the  data  to modify a complex view we have to use INSTEAD OF TRIGGERS.

6. Forced view

    Creating a view on a table, which is not there  in the database.

7. Inline view

    Inline view is basically  a subquery  with  an alias  that  you  can  use  like a  view inside a SQL statement.

8. What is an INDEX and what are the types?

    INDEX is a database object used in oracle to provide quick access to  rows.

9. Synonym

    Synonym  is an  alias  name for any database  object like tables,  views,  functions, procedures.

10. SELECT statement syntax?

SELECT    From
Where
Group by 
Having
Connect prior

11. What is Constraint? Different Constraints?

    Constraints   are  representators  of   the  columns   to  enforce  data  entity   and consistency. UNIQUE, NOT NULL, Primary key, foreign key, Check.

12. Difference between Primary key and Unique + Not Null constraints?

    Unique + Not Null is a combination  of  two constraints and we can use more  than one Unique + Not Null in any table. Primary Key is a single constraint we can use only one time for a table. It can be a referential key for any column in  any table.

13. What is NULL?

    Default Value.

14. Dual Table

    It is a one row, one column table with value X.

15. Difference between Truncate and Delete?

    Truncate  will  delete  all the  rows  from  the  table  with  out any condition.  It  will commit automatically  when it  fires  Where delete  will  delete  all or  specified  rows based upon the condition here we have to commit explicitly.


16.Difference between Char and Varchar2?

    Varchar2  is similar to char but can store available number of characters and while querying  the table  varchar2 will  trims  the extra spaces and fetches  the rows that exactly match the criteria.

17.Difference between LOB  and LONG data types?

    The maximum size of  an LOB is 4GB. It will support random access to data where in LONG maximum size is 2GB. It will support sequential  access to data.

18.Single Row functions:It will work on single row and give result for all the rows.
             Ex: to_char, to_date etc.

19.Group Functions: It will work on group of  rows in a table  and gives  a single row result.
                Ex: Sum(), Avg(), min(), max().. Etc.


20.String Handling Functions?

    Instr   –  It   returns the  position   of   the string  where  it   occur  according                     to   the parameters.
    Instrb  –  instr and instrb returns same but in the form of bytes.
    Substr  –  It returns the portion  of  a string depending on the parameters from and to.
    Substrb –  Substr and Substrb returns the same thing  but Substrb returns in  the form                 of bytes.


21.Sign: Sign is a function it will take numbers, as inputs and it will give

 i.  1 for positive integer
 ii. -1 for negative integer
iii.  0 for ZERO
SQL> Select sign(-1234) from dual;            

O/P: -1


22.Differences between UNION and UNION ALL?

    Union:  The values  of  the first  query are returned with  the values  of  the  second               query eliminating the duplicates.
    Union All: The values of the first query are returned with the values of the second query                including the duplicates.


23.Difference between NVL and NVL2 functions?

    NVL  is used  to  fill  a NULL  value  to known value.  NVL2  will  identify  the  NULL values  and Filled values  it  returns exp3 if it  is null otherwise it  returns  exp2. We have to pass 3 parameters for NVL2 and 2 parameters for NVL.

24.How can we compare range of values with out using the CASE?

    By using Decode with in Decode.

25.Can we Decode with in a Decode?

    YES

26.Decode and Case Difference?

    Case compares a Range of  values and Decode will work as if else statement.

27.Difference between Replace and Translate?

    Replace  is  used  to  replace   the whole  string   and  we can  pass  null  values  in      replace. 
    Translate  is used  to  translate  character-by-character here we  have to pass the three       parameters.


28.Difference between where and having clause?

    Where used  to specify  condition  and used to restrict  the data. Having  used  to specify the condition on grouped results and used to filter the data.


29.Difference between IN and EXISTS clause?

    EXISTS gives the status of  the  inner query.  If the  inner  query  is success  then  it returns true other wise it returns false and IN will compare the list of values.


30.Difference between subquery and correlated subquery?
   
    Query with  in a query  is subquery.  Inner query will  executes first  and based  on the result  the outer query will  be displayed.  Correlated  subquery outer  query will executes first and then inner query will be executed.

Order to Cash(O2C) Query

select * from oe_order_headers_all where order_number=66782;

select flow_status_code from oe_order_headers_all where order_number='66782';

select * from oe_order_lines_all where header_id=195990;

select * from wsh_new_deliveries where delivery_id=3835372;

select * from wsh_delivery_assignments where delivery_detail_id=4034469;

select * from wsh_delivery_details where source_header_id=192874;

select * from ra_customer_trx_all where ct_reference='66782';

select * from ra_customer_trx_lines_all where customer_trx_id=762706;

select * from ar_receivable_applications_all where applied_customer_trx_id=762706;

select * from ar_cash_Receipts_All where cash_receipt_id=110271;

select event_id from ar_cash_receipt_history_all where cash_receipt_id=110271;

select * from xla_ae_headers where event_id=3381895;

select gl_sl_link_id from xla_ae_lines where ae_header_id=5020977;

select * from xla_events where event_id=3381895;

select * from xla_distribution_links where ae_header_id=5020977;

gl_sl_link_id=6926056;

select je_batch_id from gl_je_headers where je_header_id=4762158;

select * from gl_je_lines where je_header_id=4762158;

select * from gl_je_batches where je_batch_id=3008187;

select * from gl_import_references where gl_sl_link_id=6926056;

Procure to Pay(P2P) Query

Requisition:

select * from po_requisition_headers_all where segment1='14895';

select authorization_status from   po_requisition_headers_all where segment1='14895';

go the program

select * from po_requisition_lines_all where requisition_header_id=184097

select * from po_req_distributions_all where requisition_line_id=211718

select authorization_status from   po_requisition_headers_all where segment1='14895';


Purchase Order:Auto Create

select * from po_headers_all where segment1='6850';

select authorization_status from po_headers_all where segment1='6850';

go the program

select * from po_lines_all where po_header_id=113543

select * from po_line_locations_all where po_line_id=176377

select * from po_distributions_all where line_location_id=267947

select authorization_status from po_headers_all where segment1='6850';

Receiving:Receipts


select * from rcv_shipment_headers where receipt_num='8785' and trunc(creation_date)=trunc(sysdate)

select * from rcv_shipment_lines where shipment_header_id=4941927

select * from rcv_transactions where shipment_line_id=4937487

Switch Responsibility:Payables Vision Operations:Oracle Payables:Entry:Invoices

select * from ap_invoices_all where invoice_num='ERS-8785-166226';

select * from ap_invoice_lines_all where invoice_id=214137

select * from ap_invoice_distributions_all where invoice_id='214137'

select accounting_event_id from ap_invoice_distributions_all where invoice_id='214137'

select * from ap_invoice_payments_all where invoice_id=214137

select * from ap_payment_schedules_all where invoice_id=214137

select * from ap_checks_all order by creation_date desc

select * from ap_checks_all where check_number=1017

General Ledger:

select * from gl_je_headers where name='Jun-11 Payments USD' and description='Journal Import

5834645:';

select * from gl_je_headers where description like '%5834645%' and ledger_id=1;

select * from gl_je_lines where je_header_id=4802230;

select * from gl_import_references where je_header_id=4802230

select * from gl_je_batches where je_batch_id=3048259

Sub Ledger:

select * from xla_events where entity_id=3340714

select * from xla_ae_headers where event_id=3408207

select * from xla_ae_lines where ae_header_id=5064034

Supplier API

SUPPLIER CREATION

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_rec         apps.ap_vendor_pub_pkg.r_vendor_rec_type;
   x_vendor_id          NUMBER;
   x_party_id           NUMBER;
   V_MSG_INDEX_OUT      NUMBER;
BEGIN
   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_rec.vendor_name := 'A.V.K';
   p_vendor_rec.vendor_type_lookup_code  := 'Internal';
   p_vendor_rec.women_owned_flag := 'N';
   p_vendor_rec.small_business_flag := 'Y'; 
   p_vendor_rec.segment1 :='865'; --(insert non duplicate number)
   x_vendor_id := NULL;
   x_party_id := NULL;
   apps.ap_vendor_pub_pkg.create_vendor (p_api_version,
                                         p_init_msg_list,
                                         p_commit,
                                         p_validation_level,
                                         x_return_status,
                                         x_msg_count,
                                         x_msg_data,
                                         p_vendor_rec,
                                         x_vendor_id,
                                         x_party_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_ID = ' || TO_CHAR (x_vendor_id));
   DBMS_OUTPUT.put_line ('X_PARTY_ID = ' || TO_CHAR (x_party_id));
   DBMS_OUTPUT.put_line ('');
   COMMIT;

  IF x_return_status = 'S' THEN
    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, 200);
      DBMS_OUTPUT.put_line (x_msg_data);
    
    END LOOP;
  END IF;
END;

SUPPLIER SITE CREATION

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;
BEGIN
   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 :=89183;
   P_VENDOR_SITE_REC.VENDOR_SITE_CODE := 'HOME';
   P_VENDOR_SITE_REC.ORG_ID := 204;
   P_VENDOR_SITE_REC.address_line1 := 'Ammam Kovil Street';
   P_VENDOR_SITE_REC.address_line2 :='Vadapalani';
   P_VENDOR_SITE_REC.address_line3 := 'Chennai';     
   P_VENDOR_SITE_REC.org_name := 'Vision Operations';
   P_VENDOR_SITE_REC.country := 'US';       
   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
    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, 200);
      DBMS_OUTPUT.put_line (x_msg_data);
     
    END LOOP;
  END IF;

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

SUPPLIER CONTACT CREATION

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_contact_rec   apps.ap_vendor_pub_pkg.r_vendor_contact_rec_type;
   x_vendor_contact_id    NUMBER;
   x_per_party_id         NUMBER;
   x_rel_party_id         NUMBER;
   x_rel_id               NUMBER;
   x_org_contact_id       NUMBER;
   x_party_site_id        NUMBER;
   v_msg_index_out          NUMBER;
BEGIN
   p_api_version := 1.0;
   p_init_msg_list := 'T';
   p_commit := 'T';
   p_validation_level := FND_API.G_VALID_LEVEL_FULL;
   x_return_status := NULL;
   x_msg_count := NULL;
   x_msg_data := NULL;
   p_vendor_contact_rec.vendor_contact_id := po_vendor_contacts_s.NEXTVAL;
   DBMS_OUTPUT.put_line ('po_vendor_contacts_s.NEXTVAL = ' || po_vendor_contacts_s.NEXTVAL);
   P_VENDOR_CONTACT_REC.vendor_site_id :=7953;
   P_VENDOR_CONTACT_REC.PERSON_FIRST_NAME := 'VIMAL';
   P_VENDOR_CONTACT_REC.PERSON_LAST_NAME := 'KUMAR';
   P_VENDOR_CONTACT_REC.PHONE           := '9840395136';
   P_VENDOR_CONTACT_REC.EMAIL_ADDRESS := 'vimalkumar.a@chain-sys.com';
   P_VENDOR_CONTACT_REC.URL := 'www.chain-sys.com';
   P_VENDOR_CONTACT_REC.org_id := 204;
   p_vendor_contact_rec.party_site_id  := 304651;
   p_vendor_contact_rec.org_party_site_id := 304651;
   p_vendor_contact_rec.VENDOR_ID := 89183;          
   x_vendor_contact_id := NULL;
   x_per_party_id := NULL;
   x_rel_party_id :=NULL;
   x_rel_id := NULL;
   x_org_contact_id := NULL;
   x_party_site_id := NULL;
   apps.ap_vendor_pub_pkg.create_vendor_contact (p_api_version,
                                                 p_init_msg_list,
                                                 p_commit,
                                                 p_validation_level,
                                                 x_return_status,
                                                 x_msg_count,
                                                 x_msg_data,
                                                 p_vendor_contact_rec,
                                                 x_vendor_contact_id,
                                                 x_per_party_id,
                                                 x_rel_party_id,
                                                 x_rel_id,
                                                 x_org_contact_id,
                                                 x_party_site_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_CONTACT_ID = ' || TO_CHAR (x_vendor_contact_id));
   DBMS_OUTPUT.put_line ('X_PER_PARTY_ID = ' || TO_CHAR (x_per_party_id));
   DBMS_OUTPUT.put_line ('X_REL_PARTY_ID = ' || TO_CHAR (x_rel_party_id));
   DBMS_OUTPUT.put_line ('X_REL_ID = ' || TO_CHAR (x_rel_id));
   DBMS_OUTPUT.put_line ('X_ORG_CONTACT_ID = ' || TO_CHAR (x_org_contact_id));
   DBMS_OUTPUT.put_line ('X_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_id));
   DBMS_OUTPUT.put_line ('');
   COMMIT;

    IF x_return_status = 'S' THEN
    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, 200);
      DBMS_OUTPUT.put_line (x_msg_data);
     
    END LOOP;
  END IF;
END;

TABLES:


1.select * from po_vendors where vendor_name = 'A.V.K';

2.select segment1 from po_vendors where segment1='865';

3.select * from po_vendor_sites_all where vendor_id =89183;

4.select vendor_site_id from po_vendor_sites_all where vendor_id =89183;

5.select party_site_id from po_vendor_sites_all  where vendor_id = 89183;

6.select vendor_site_id from po_vendor_sites_all where vendor_id = 89182;

7.select * from po_vendor_contacts WHERE party_site_id=304651;

PO Open Interface

INTERFACE TABLE:

    PO_HEADERS_INTERFACE
    PO_LINES_INTERFACE
   

BASE TABLE:

    PO_HEADERS_ALL
    PO_LINES_ALL
    PO_LINE_LOCATIONS_ALL

CONCURRENT PROGRAM NAME:

    IMPORT STANDARD PURCHASE ORDER

ERROR TABLE 

                             PO_INTERFACE_ERRORS


STAGGING TABLE:

HEADER TABLE:


 Create table V_PO_HEADER(
        INTERFACE_HEADER_ID       NUMBER,    
        ORG_ID                                       NUMBER,
        DOCUMENT_TYPE_CODE     VARCHAR2(25),
        SHIP_TO_LOCATION_ID         NUMBER,
        BILL_TO_LOCATION_ID        NUMBER,
        AGENT_NAME                         VARCHAR2(240),
        APPROVAL_STATUS              VARCHAR2(25),
        CURRENCY_CODE                  VARCHAR2(15),
        PROCESSING_ID                       NUMBER,
        BATCH_ID                                  NUMBER,
        ACTION                                      VARCHAR2(25),
        VENDOR_ID                              NUMBER,
        VENDOR_SITE_ID                    NUMBER);
   


LINE TABLE:


create table V_PO_LINES(
                INTERFACE_HEADER_ID         NUMBER,
                INTERFACE_LINE_ID                NUMBER,
                LINE_NUM                                   NUMBER,
                LINE_TYPE                                  VARCHAR2(25),
                ITEM                                              VARCHAR2(1000),
                CATEGORY                                 VARCHAR2(2000),
                ITEM_DESCRIPTION                  VARCHAR2(240),
                UOM_CODE                                  VARCHAR2(3),
                QUANTITY                                    NUMBER,
                UNIT_PRICE                                 NUMBER,
                PROCESSING_ID                         NUMBER,
                ACTION                                        VARCHAR2(25));
               

DATA FILE FOR HEADER:

910001,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910002,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910003,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910004,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910005,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910006,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910007,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910008,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910009,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910010,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910011,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910012,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910013,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910014,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910015,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910016,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910017,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910018,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910019,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910020,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414


CONTROL FILE FOR HEADER:


LOAD DATA 
INFILE '/oracle2/VIS1211/apps/apps_st/appl/po/12.0.0/bin/pur_heads.txt' 
INSERT INTO TABLE V_PO_HEADER
FIELDS TERMINATED BY "," 
   (INTERFACE_HEADER_ID,    
    ORG_ID,                   
    DOCUMENT_TYPE_CODE,       
    SHIP_TO_LOCATION_ID, 
    BILL_TO_LOCATION_ID,      
    AGENT_NAME,              
    APPROVAL_STATUS,        
    CURRENCY_CODE,
    PROCESSING_ID,
    BATCH_ID,
    ACTION,
    VENDOR_ID,
    VENDOR_SITE_ID)         


DATA FILE FOR LINE:


910001,55001,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910002,55002,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910003,55003,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910004,55004,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910005,55005,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910006,55006,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910007,55007,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910008,55008,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910009,55009,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910010,55010,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910011,55011,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910012,55012,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910013,55013,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910014,55014,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910015,55015,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910016,55016,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910017,55017,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910018,55018,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910019,55019,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910020,55020,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL


CONTROL FILE FOR LINE:


LOAD DATA 
INFILE '/oracle2/VIS1211/apps/apps_st/appl/po/12.0.0/bin/pur_line.txt' 
INSERT INTO TABLE V_PO_LINES
FIELDS TERMINATED BY ","
   (INTERFACE_HEADER_ID,
    INTERFACE_LINE_ID,
    LINE_NUM,
    LINE_TYPE, 
    ITEM,      
    CATEGORY,   
    ITEM_DESCRIPTION,   
    UOM_CODE,          
    QUANTITY,
    UNIT_PRICE,
    PROCESSING_ID,
    ACTION)


After Finishing this move the Header and Line file to gftp.

Run the Header and Line Control File in SQL Loader.


PROCEDURE PROGRAM


CREATE OR REPLACE PROCEDURE pur_proce (errbuf out varchar2,retcode out varchar2) is
  
   CURSOR c1 IS SELECT * FROM V_PO_HEADER;
   CURSOR c2 IS SELECT * FROM V_PO_LINES;

BEGIN
  FOR i IN c1 LOOP
  INSERT INTO po_headers_interface
                 (INTERFACE_HEADER_ID,    
                  ORG_ID,                   
                  DOCUMENT_TYPE_CODE,       
                  SHIP_TO_LOCATION_ID, 
                  BILL_TO_LOCATION_ID,      
                  AGENT_NAME,              
                  APPROVAL_STATUS,        
                  CURRENCY_CODE,
                  PROCESSING_ID,
                  BATCH_ID,
                  ACTION,
                  VENDOR_ID,
                  vendor_site_id)
           VALUES (i.INTERFACE_HEADER_ID,    
                    i.ORG_ID,                   
                    i.DOCUMENT_TYPE_CODE,       
                    i.SHIP_TO_LOCATION_ID, 
                    i.BILL_TO_LOCATION_ID,      
                    i.AGENT_NAME,              
                    i.APPROVAL_STATUS,        
                    i.CURRENCY_CODE,
                    i.PROCESSING_ID,
                    i.BATCH_ID,
                    i.ACTION,
                    i.VENDOR_ID,
                    i.VENDOR_SITE_ID);               
   END LOOP;

 FOR j IN c2 LOOP
 INSERT INTO po_lines_interface
                  (INTERFACE_HEADER_ID,
                    INTERFACE_LINE_ID,
                    LINE_NUM,
                    LINE_TYPE, 
                    ITEM,      
                    CATEGORY,   
                    ITEM_DESCRIPTION,   
                    UOM_CODE,          
                    QUANTITY,
                    UNIT_PRICE,
                    PROCESSING_ID,
                    ACTION )
                
           VALUES (j.INTERFACE_HEADER_ID,
                    j.INTERFACE_LINE_ID,
                    j.LINE_NUM,
                    j.LINE_TYPE, 
                    j.ITEM,      
                    j.CATEGORY,   
                    j.ITEM_DESCRIPTION,   
                    j.UOM_CODE,          
                    j.QUANTITY,
                    j.UNIT_PRICE,
                    j.PROCESSING_ID,
                    j.ACTION );
                
   END LOOP;
   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      fnd_file.put_line (fnd_file.LOG, SQLERRM);
     
END;



Run the Procedure using PL/SQL Stored Preocedure.


Finally Run the Concurrent Program Name:IMPORT STANDARD PURCHASE ORDER.


QUERY:


select * from  V_PO_HEADER

select * from V_PO_LINES


select * from po_headers_interface

select * from po_lines_interface

select * from po_interface_errors

select * from po_headers_all where request_id=5832984

select * from po_lines_all where request_id=5832984