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;