UTL_FILE Example Using Cursor

CREATE OR REPLACE PROCEDURE xxaj_utl_file

v_file_handle    UTL_FILE.FILE_TYPE;

CURSOR c_supplier IS

       select a.vendor_name,

       a.segment1 Vendror#,

       site.vendor_site_code,

       site.address_line1,

       site.ADDRESS_LINE2,

       CITY,

       STATE,

       country,

       zip

FROM ap_suppliers a,

     ap_supplier_sites_all site

WHERE a.vendor_id = site.vendor_id

AND site.org_id =204

ORDER BY 1,3;

     

BEGIN

v_file_handle := UTL_FILE.FOPEN('/usr/tmp','MY_UTL_SUPP.csv', 'W');

UTL_FILE.PUT_LINE(v_file_handle,'VendorNmae'||'|'||

                                'Vendor#'||'|'||

                            'vendor_site_code'||'|'||

                            'Address1'||'|'||

                            'Address2'||'|'||

                            'City'||'|'||

                            'State'||'|'||

                            'County'||'|'||

                            'ZIP'

                            );

FOR c_rec IN c_supplier LOOP

  UTL_FILE.PUT_LINE(v_file_handle,c_rec.vendor_name||'|'||

                                  c_rec.Vendror#||'|'||

                              c_rec.vendor_site_code||'|'||

                              c_rec.address_line1||'|'||

                              c_rec.address_line2||'|'||

                              c_rec.CITY||'|'||

                              c_rec.STATE||'|'||

                              c_rec.country||'|'||

                              c_rec.zip

                                  );

END LOOP;

UTL_FILE.PUT_LINE(v_file_handle,'END OF Extraction');

UTL_FILE.FCLOSE(v_file_handle);

COMMIT;

/*  Handle UTL_FILE exceptions */

        EXCEPTION

        WHEN UTL_FILE.INVALID_OPERATION THEN

            utl_file.fclose_all;

            dbms_output.put_line(' invalid operation');

         WHEN UTL_FILE.INVALID_FILEHANDLE THEN

            utl_file.fclose_all;

            dbms_output.put_line(' invalid filehandle');

        WHEN UTL_FILE.INVALID_PATH THEN

            utl_file.fclose_all;

            dbms_output.put_line(' invalid path');

         WHEN UTL_FILE.INTERNAL_ERROR THEN

            utl_file.fclose_all;

            dbms_output.put_line(' internal error');

         WHEN UTL_FILE.INVALID_MODE THEN

            utl_file.fclose_all;

            dbms_output.put_line(' invalid mode');

         WHEN UTL_FILE.WRITE_ERROR THEN

            utl_file.fclose_all;

            dbms_output.put_line(' write error');

         WHEN UTL_FILE.READ_ERROR THEN

            utl_file.fclose_all;

            dbms_output.put_line(' read error');

         WHEN OTHERS THEN

            utl_file.fclose_all;

            dbms_output.put_line('other exception');

END xxaj_utl_file;

Ur's

AmarAlam

Pseudo Code For PO Interface

CREATE OR REPLACE PROCEDURE xxaj_po_interface
l_interface_header_id number;
l_interface_line_id   NUMBER;
BEGIN
SELECT po_headers_interface_s.NEXTVAL
INTO l_interface_header_id
FROM dual;
dbms_output.put_line('Header SEQ:'||l_interface_header_id);
INSERT INTO po_headers_interface (batch_id
,interface_header_id
, interface_source_code
, action
, org_id
, document_type_code
, agent_id
, vendor_id
, vendor_site_id
, approval_status
,comments
, last_update_date
, last_updated_by
, creation_date
, created_by
)
VALUES   ( 1001                      --Batch ID
, l_interface_header_id       --Header Sequence
, 'CONVERSION'                         --Source Code
, 'ORIGINAL'                           --Action
, 204                        --Org ID
, 'STANDARD'                           --PO/Document Type
, 25                      --Agent ID
, 6                     --Vendor ID
, 7                --Vendor Site ID
, 'APPROVED'                           --PO status
, 'This is my Interface PO'                       --Comments
, SYSDATE                            
, 1318
, SYSDATE
, 1318
);
dbms_output.put_line('Header Details Inserted...');
BEGIN
SELECT po_lines_interface_s.NEXTVAL
INTO l_interface_line_id
FROM dual;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line( 'Error In Getting lines Seuence Number po_lines_interface_s.NEXTVAL..');
END;
INSERT INTO po_lines_interface (interface_line_id
, interface_header_id
,action
, line_num
, shipment_num
, line_type
, item
, uom_code
, quantity
, unit_price
,ship_to_organization_code
, ship_to_location
, need_by_date
, promised_date
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by )
VALUES   ( l_interface_line_id     --Interface Line ID
, l_interface_header_id               --Interface Header Id
,'ORIGINAL'                           --Action
, 1                          --Line Num
, 1                                   --Shipment Num
, 'Goods'                             --Line Type
, 'AS54888'                    --Item Name
, 'Ea'                           --Uom Code
, 10                     --Quantity
, 3                        --Price
, 'V1'                    --Ship to Organization Code
, 'V1- New York City'        -- 'One Time Address'
, sysdate+1                --Need by Date
, sysdate+1                --Promise Date
, SYSDATE                             --Last Update Date
, 1318
, 1318
, SYSDATE
, 1318);
---======================================
---Inserting PO Distributions
INSERT INTO po_distributions_interface (
    interface_header_id
  , interface_line_id
  , interface_distribution_id
  , quantity_ordered
  , charge_account_id
  , last_update_date
  , last_updated_by
  , last_update_login
  , creation_date
  , created_by
)
VALUES ( l_interface_header_id       --Interface Header Id
, l_interface_line_id              --Interface Line ID
, po_distributions_interface_s.NEXTVAL       --Interface Distribution id
, 10                            --Quantity
, 13401
, SYSDATE
, 1318
, 1318
, SYSDATE
, 1318);
END xxaj_po_interface;


Ur's
AmarAlam

HZ Table Flow Diagram

-- Search Supplier Contact
SELECT DISTINCT asu.party_id, asu.segment1 Supp_Num
,asu.vendor_name
,hpc.party_name Contact_Name
,hpr.primary_phone_country_code cnt_cntry
,hpr.primary_phone_area_code cnt_area
,hpr.primary_phone_number cnt_phone
,assa.vendor_site_code
,assa.vendor_site_id
,asco.vendor_contact_id
FROM
 hz_relationships hr
,ap_suppliers asu
,ap_supplier_sites_all assa
,ap_supplier_contacts asco
,hz_org_contacts hoc
,hz_parties hpc
,hz_parties hpr
,hz_contact_points hpcp
WHERE hoc.party_relationship_id = hr.relationship_id
AND hr.subject_id = asu.party_id
AND hr.relationship_code = 'CONTACT'
AND hr.object_table_name = 'HZ_PARTIES'
AND asu.vendor_id = assa.vendor_id
AND hr.object_id = hpc.party_id
AND hr.party_id = hpr.party_id
AND asco.relationship_id  = hoc.party_relationship_id
AND assa.party_site_id = asco.org_party_site_id
AND hpr.party_type='PARTY_RELATIONSHIP'
AND hpr.party_id = hpcp.owner_table_id
AND hpcp.owner_table_name = 'HZ_PARTIES';


Ur's
AmarAlam

Supplier Contact Creation Using API

CREATE OR REPLACE PROCEDURE xxaj_supplier_contact_creation
l_vendor_contact_rec   ap_vendor_pub_pkg.r_vendor_contact_rec_type;
l_return_status     VARCHAR2(10);
l_msg_count     NUMBER;
l_msg_data  VARCHAR2(1000);
l_vendor_id NUMBER;
l_party_id  NUMBER;
l_vendor_contact_id NUMBER;
l_per_party_id      NUMBER;
l_rel_party_id      NUMBER;
l_rel_id            NUMBER;
l_org_contact_id    NUMBER;
l_party_site_id     NUMBER;

BEGIN
fnd_global.apps_initialize(user_id => fnd_global.USER_ID,resp_id => fnd_global.RESP_ID,resp_appl_id => fnd_global.RESP_APPL_ID);
fnd_global.apps_initialize(0,20707,200);
--Query to get Vendor Id
    SELECT vendor_id
    INTO l_vendor_contact_rec.vendor_id
    FROM pos_po_vendors_v
    WHERE vendor_name = 'SIVA L ERP';  --Vendor Name
    dbms_output.put_line('Vendor id is :'||l_vendor_contact_rec.vendor_id);
--Query to get Party Site Id
    SELECT party_site_id
    INTO   l_vendor_contact_rec.org_party_site_id
    FROM ap_supplier_sites_all
    WHERE vendor_site_code = 'ERP_SITE'
    AND vendor_id = l_vendor_contact_rec.vendor_id ;
    dbms_output.put_line('Party site id is :'||l_vendor_contact_rec.org_party_site_id);
--l_vendor_contact_rec.org_party_site_id := 175402;     --Party Site ID
l_vendor_contact_rec.org_id := 204;                --Org Id
l_vendor_contact_rec.PERSON_FIRST_NAME:= 'Amar';  --First Name
l_vendor_contact_rec.PERSON_LAST_NAME := 'Alam';   --Last Name

l_vendor_contact_rec.phone := 9966410696;                 --Phone Number
l_vendor_contact_rec.email_address := 'test@gmail.com' ;         --Email Address

pos_vendor_pub_pkg.create_vendor_contact
( p_vendor_contact_rec  => l_vendor_contact_rec,
  x_return_status       => l_return_status,
  x_msg_count           => l_msg_count,
  x_msg_data            => l_msg_data,
  x_vendor_contact_id   => l_vendor_contact_id,
  x_per_party_id        => l_per_party_id,
  x_rel_party_id        => l_rel_party_id,
  x_rel_id              => l_rel_id,
  x_org_contact_id      => l_org_contact_id,
  x_party_site_id       => l_party_site_id
 );
 IF x_msg_count > 1 THEN
    FOR i IN 1 .. x_msg_count LOOP
      DBMS_OUTPUT.put_line(SUBSTR (fnd_msg_pub.get(p_encoded => 'F'),1,255));
    END LOOP;
ELSE
 DBMS_OUTPUT.put_line('API ERROR :'||x_msg_data);
END IF;

 COMMIT;
  dbms_output.put_line('return_status: '||l_return_status);
  dbms_output.put_line('msg_data: '||l_msg_data);
  dbms_output.put_line('msg_data_count: '||l_msg_count);
  dbms_output.put_line('vendor Contact Id: '||l_vendor_contact_id);
  dbms_output.put_line('per_party_id: '||l_per_party_id);
  dbms_output.put_line('rel_party_id: '||l_rel_party_id);
  dbms_output.put_line('rel_id: '||l_rel_id);
  dbms_output.put_line('org_contact_id: '||l_org_contact_id);
  dbms_output.put_line('party_site_id: '||l_party_site_id);
End xxaj_supplier_contact_creation;


Exec xxaj_supplier_contact_creation;


Ur's
AmarAlam

Supplier Site Creation Using API

CREATE OR REPLACE PROCEDURE xxaj_supplier_site_creation
p_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
x_return_status    VARCHAR2(8);
x_msg_count        NUMBER;
x_msg_data         VARCHAR2(4000);
x_vendor_site_id   NUMBER;
x_party_site_id    NUMBER;
x_location_id      NUMBER;
BEGIN
p_vendor_site_rec.vendor_site_code := 'ERP_SITE';
p_vendor_site_rec.vendor_id        := 39173;
p_vendor_site_rec.address_line1    := 'ERP Bangalore';
p_vendor_site_rec.ADDRESS_LINE2    := '#C-21 2nd Cross';
p_vendor_site_rec.country          := 'US';
p_vendor_site_rec.county           := 'New York';
p_vendor_site_rec.city             := 'Brooklyn';
p_vendor_site_rec.state            := 'FL';
p_vendor_site_rec.zip              := 10292;
p_vendor_site_rec.org_id           := 204;

POS_VENDOR_PUB_PKG.Create_Vendor_Site
( p_vendor_site_rec,
  x_return_status,
  x_msg_count,
  x_msg_data,
  x_vendor_site_id,
  x_party_site_id,
  x_location_id
);
DBMS_OUTput.put_line('The Status is :'||x_return_status);
DBMS_OUTput.put_line('The x_msg_count is :'||x_msg_count);
DBMS_OUTput.put_line('The x_msg_data is :'||x_msg_data);
DBMS_OUTput.put_line('The x_vendor_site_id is :'||x_vendor_site_id);
DBMS_OUTput.put_line('The x_party_site_id is :'||x_party_site_id);
DBMS_OUTput.put_line('The x_location_id is :'||x_location_id);
commit;
END xxaj_supplier_site_creation;

Exec xxaj_supplier_site_creation;

Ur's
AmarAlam

Supplier Creation Using API

CREATE OR REPLACE PROCEDURE xxaj_supplier_creation
IS
v_vendor_rec     AP_VENDOR_PUB_PKG.r_vendor_rec_type;
x_return_status  VARCHAR2(10);
x_msg_count      NUMBER;
x_msg_data       VARCHAR2(4000);
x_vendor_id      NUMBER;
x_party_id       NUMBER;
v_error_message  VARCHAR2(4000);
v_valdation_flag VARCHAR2(5);

BEGIN

v_vendor_rec.vendor_name :='SIVA L ERP' ;
DBMS_OUTput.put_line(' Calling POS_VENDOR_PUB_PKG');
POS_VENDOR_PUB_PKG.Create_Vendor
( v_vendor_rec,
  x_return_status,
  x_msg_count,
  x_msg_data,
  x_vendor_id,
  x_party_id
);
IF x_return_status = 'S' then
  DBMS_OUTput.put_line('Success');
ELSE
 v_error_message :=x_msg_data;
 v_valdation_flag :='E';
END IF;
DBMS_OUTput.put_line('The Status is :'||x_return_status);
DBMS_OUTput.put_line('The x_msg_count is :'||x_msg_count);
DBMS_OUTput.put_line('The x_msg_data is :'||x_msg_data);
DBMS_OUTput.put_line('The x_vendor_id is :'||x_vendor_id);
DBMS_OUTput.put_line('The x_party_id is :'||x_party_id);
COMMIT;
END xxaj_supplier_creation;


Exec xxaj_supplier_creation;

Ur's
AmarAlam