Yadul Oracle Duniya: 2020

Thursday, March 12, 2020

SQL Query to find Customer, Customer Account and Customer Sites Information

/******************************************************************************
 *PURPOSE: Query to Customer, Customer Account and Customer Sites Information *
 *AUTHOR: Abul Mohsin                                                        *
 ******************************************************************************/
SELECT
----------------------------------------------------------
-- Party Information
----------------------------------------------------------
         hp.party_number "Registry ID", hp.party_name "Party Name",
         hp.party_type "Party Type",
         DECODE (hp.status,
                 'A', 'Active',
                 'I', 'Inactive',
                 hp.status
                ) "Party Status",
 
----------------------------------------------------------
-- Account Information
----------------------------------------------------------
         hca.account_number "Account Number",
         DECODE (hca.status,
                 'A', 'Active',
                 'I', 'Inactive',
                 hca.status
                ) "Account Status",
         hca.account_name "Account Description",
         hca.customer_class_code "Classification",
         DECODE (hca.customer_type,
                 'R', 'External',
                 'I', 'Internal',
                 hca.customer_type
                ) "Account Type",
 
----------------------------------------------------------
-- Site Information
----------------------------------------------------------
         hps.party_site_number "Customer Site Number",
         DECODE (hcas.status, 'A', 'Active', 'Inactive') "Site Status",
         DECODE (hcas.bill_to_flag,
                 'P', 'Primary',
                 'Y', 'Yes',
                 hcas.bill_to_flag
                ) "Bill To Flag",
         DECODE (hcas.ship_to_flag,
                 'P', 'Primary',
                 'Y', 'Yes',
                 hcas.ship_to_flag
                ) "Ship To Flag",
         hcas.cust_acct_site_id "Customer Acct Site ID",
 
----------------------------------------------------------
-- Address Information
----------------------------------------------------------
         hl.address1 "Address1", hl.address2 "Address2",
         hl.address3 "Address3", hl.address4 "Address4", hl.city "City",
         hl.state "State", hl.postal_code "Zip Code", ter.NAME "Territory",
 
----------------------------------------------------------
-- DFF Information (specific to client)
----------------------------------------------------------
         hcas.attribute4 "SMG Key", hcas.attribute8 "GLN Key",
         hca.attribute3 "Credit Approval Date",
         hca.attribute7 "Credit Approved By",
         hca.attribute4 "Acct Opened Date",
         hca.attribute5 "Credit Collection Status",
         hca.attribute1 "BPCS Last Trx Date",
         hca.attribute2 "BPCS Avg Pay Days",
         hca.attribute6 "BPCS RCM Reference",
----------------------------------------------------------
-- Collector Information
----------------------------------------------------------
                                             col.NAME "Collector Name",
 
----------------------------------------------------------
-- Account Profile Information
----------------------------------------------------------
         hcp.credit_checking "Credit Check Flag",
         hcp.credit_hold "Credit Hold Flag",
         hcpa.auto_rec_min_receipt_amount "Min Receipt Amount",
         hcpa.overall_credit_limit "Credit Limit",
         hcpa.trx_credit_limit "Order Credit Limit",
 
--------------------------------------------------------
-- Attachment Flag
----------------------------------------------------------
         NVL ((SELECT 'Y'
                 FROM fnd_documents_vl doc,
                      fnd_lobs blo,
                      fnd_attached_documents att
                WHERE doc.media_id = blo.file_id
                  AND doc.document_id = att.document_id
                  AND att.entity_name = 'AR_CUSTOMERS'
                  AND att.pk1_value = hca.cust_account_id
                  AND ROWNUM = 1),
              'N'
             ) "Attachment Flag",
 
----------------------------------------------------------
-- Party Relationship Flag
----------------------------------------------------------
         NVL
            ((SELECT 'Y'
                FROM hz_cust_acct_relate_all hzcar
               WHERE hzcar.cust_account_id = hca.cust_account_id
                 AND hzcar.relationship_type = 'ALL'
                 AND ROWNUM = 1),
             'N'
            ) "Party Relationship Flag",
 
----------------------------------------------------------
-- Account Relationship Flag
----------------------------------------------------------
         NVL
            ((SELECT 'Y'
                FROM hz_cust_acct_relate_all hzcar
               WHERE hzcar.cust_account_id = hca.cust_account_id
                     AND ROWNUM = 1),
             'N'
            ) "Account Relationship Flag",
 
----------------------------------------------------------
-- Party Contact Flag
----------------------------------------------------------
         NVL
            ((SELECT 'Y'
                FROM hz_parties hp2
               WHERE 1 = 1
                 AND hp2.party_id = hp.party_id
                 AND (   hp2.url IS NOT NULL
                      OR
                         -- LENGTH(TRIM(hp.email_address)) > 5
                         INSTR (hp2.email_address, '@') > 0
                      OR hp2.primary_phone_purpose IS NOT NULL
                     )),
             'N'
            ) "Party Contact Flag",
 
----------------------------------------------------------
-- Account Contact Flag
----------------------------------------------------------
         NVL
            ((SELECT 'Y'
                FROM hz_contact_points
               WHERE STATUS = 'A'
                 AND owner_table_id =
                        (SELECT hcar.party_id
                           FROM hz_cust_account_roles hcar, ar_contacts_v acv
                          WHERE hcar.cust_account_id = hca.cust_account_id
                            AND hcar.cust_account_role_id = acv.contact_id
                            AND hcar.cust_acct_site_id IS NULL
                            -- look for account level only
                            AND ROWNUM = 1
                                          -- add this row to show inactive sites (i.e. with no site id)
                        )
                 AND ROWNUM = 1),
             'N'
            ) "Account Contact Flag",
 
----------------------------------------------------------
-- Site Contact Flag
----------------------------------------------------------
         NVL
            ((SELECT 'Y'
                FROM hz_contact_points
               WHERE STATUS = 'A'
                 AND owner_table_id =
                        (SELECT hcar.party_id
                           FROM hz_cust_account_roles hcar, ar_contacts_v acv
                          WHERE hcar.cust_acct_site_id =
                                                        hcas.cust_acct_site_id
                            AND hcar.cust_account_role_id = acv.contact_id
                            AND ROWNUM = 1
                                          -- add this row to show inactive sites (i.e. with no site id)
                        )
                 AND ROWNUM = 1),
             'N'
            -- any contact (email, phone, fax) would suffice this condition
            ) "Site Contact Flag"
    FROM hz_parties hp,
         hz_party_sites hps,
         hz_cust_accounts_all hca,
         hz_cust_acct_sites_all hcas,
         hz_customer_profiles hcp,
         hz_cust_profile_amts hcpa,
         hz_locations hl,
         ra_territories ter,
         ar_collectors col
   WHERE 1 = 1
     AND hp.party_id = hca.party_id
     AND hca.cust_account_id = hcas.cust_account_id(+)
     AND hps.party_site_id(+) = hcas.party_site_id
     AND hp.party_id = hcp.party_id
     AND hca.cust_account_id = hcp.cust_account_id
     AND hps.location_id = hl.location_id(+)
     AND col.collector_id = hcp.collector_id
     AND hcas.territory_id = ter.territory_id(+)
     AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id(+)
     ----
     AND hp.party_type = 'ORGANIZATION'       -- only ORGANIZATION Party types
     AND hp.status = 'A'
                                 -- only Active Parties/Customers
----
-- following conditions are for testing purpose only
-- comment/uncomment as needed
----
-- AND hp.party_number        = &party_number
-- AND hca.account_number     = &account_number
ORDER BY TO_NUMBER (hp.party_number), hp.party_name, hca.account_number;

Tuesday, February 11, 2020

Supplier Interface with APIs (Custom Interface)

create or replace package XX_SS_WEBADI_PKG is

procedure XX_SUPPLIER_SITE_CREATION(p_vendor_site_code varchar2,
p_address_line1 varchar2,
p_address_line2 varchar2,
p_address_line3 varchar2,
p_address_line4 varchar2,
p_country varchar2,
p_organization varchar2,
p_city varchar2,
p_state varchar2,
p_province varchar2,
p_zip varchar2,
p_pay_site_flag varchar2,
p_purchasing_site_flag varchar2,
p_area_code varchar2,
p_site_phone varchar2,
p_fax_area_code varchar2,
p_fax varchar2,
p_site_email_add varchar2,
p_allow_awt_flag varchar2,
p_vat_registration_num number,
p_vat_code varchar2,
p_hold_all_payments_flag varchar2,
p_hold_unmatch_inv_flag varchar2,
p_hold_reason varchar2,
p_pay_group_lookup_code varchar2,
p_chque_type varchar2,
p_vendor_name varchar2,
x_vendor_site_id out nocopy number);

end XX_SS_WEBADI_PKG;

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

create or replace package body XX_SS_WEBADI_PKG is

Procedure XX_SUPPLIER_SITE_CREATION(p_vendor_site_code varchar2,
p_address_line1 varchar2,
p_address_line2 varchar2,
p_address_line3 varchar2,
p_address_line4 varchar2,
p_country varchar2,
p_organization varchar2,
p_city varchar2,
p_state varchar2,
p_province varchar2,
p_zip varchar2,
p_pay_site_flag varchar2,
p_purchasing_site_flag varchar2,
p_area_code varchar2,
p_site_phone varchar2,
p_fax_area_code varchar2,
p_fax varchar2,
p_site_email_add varchar2,
p_allow_awt_flag varchar2,
p_vat_registration_num number,
p_vat_code varchar2,
p_hold_all_payments_flag varchar2,
p_hold_unmatch_inv_flag varchar2,
p_hold_reason varchar2,
p_pay_group_lookup_code varchar2,
p_chque_type varchar2,
p_vendor_name varchar2,
x_vendor_site_id out nocopy number) as

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
lv_validation_exc EXCEPTION;
lv_error_message VARCHAR2(200);
l_return_status VARCHAR2(100);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_party_site_id NUMBER;
l_location_id NUMBER;
l_msg_index_out NUMBER;
p_api_version NUMBER;
p_init_msg_list VARCHAR2(200);
p_commit VARCHAR2(200);
p_validation_level NUMBER;
l_org_id NUMBER;
l_vendor_id NUMBER;

BEGIN

BEGIN
SELECT hou.organization_id
INTO l_org_id
FROM apps.hr_operating_units hou
WHERE UPPER(name) = UPPER(p_organization) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
XXEG_DEBUG_PKG.putLog(11, 'NO data Found for provided Organization ' || p_organization);
lv_error_message := 'NO data Found for provided Organization ' || p_organization ;
RAISE lv_validation_exc ;
WHEN OTHERS THEN
XXEG_DEBUG_PKG.putLog(11, 'Unhandled Exception for provided Organization ' || p_organization);
lv_error_message := 'Unhandled Exception for provided Organization ' || p_organization ;
RAISE lv_validation_exc ;
END;
BEGIN
SELECT aps.vendor_id
INTO l_vendor_id
FROM apps.ap_suppliers aps
WHERE UPPER(vendor_name) = UPPER(p_vendor_name) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
XXEG_DEBUG_PKG.putLog(11, 'NO data Found for provided Vendor ' || p_vendor_name);
lv_error_message := 'NO data Found for provided Vendor ' || p_vendor_name ;
RAISE lv_validation_exc ;
WHEN OTHERS THEN
XXEG_DEBUG_PKG.putLog(11, 'Unhandled Exception for provided Organization ' || p_vendor_name);
lv_error_message := 'Unhandled Exception for provided Organization ' || p_vendor_name ;
RAISE lv_validation_exc ;
END;

--Required
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_vendor_site_id := null;
l_vendor_site_rec.vendor_id := l_vendor_id;
l_vendor_site_rec.vendor_site_code := p_vendor_site_code;
l_vendor_site_rec.address_line1 := p_address_line1;

--Optional
l_vendor_site_rec.address_line2 := p_address_line2;
l_vendor_site_rec.address_line3 := p_address_line3;
l_vendor_site_rec.address_line4 := p_address_line4;
l_vendor_site_rec.country := p_country;
l_vendor_site_rec.org_id := l_org_id;
l_vendor_site_rec.city := p_city;
l_vendor_site_rec.state := p_state;
l_vendor_site_rec.province := p_province;
l_vendor_site_rec.zip := p_zip;
l_vendor_site_rec.purchasing_site_flag := p_purchasing_site_flag;
l_vendor_site_rec.pay_site_flag := p_pay_site_flag;
l_vendor_site_rec.rfq_only_site_flag := 'N';
l_vendor_site_rec.area_code := p_area_code;
l_vendor_site_rec.phone := p_site_phone;
l_vendor_site_rec.fax_area_code := p_fax_area_code;
l_vendor_site_rec.fax := p_fax;
l_vendor_site_rec.email_address := p_site_email_add;
l_vendor_site_rec.allow_awt_flag := p_allow_awt_flag;
l_vendor_site_rec.vat_registration_num := p_vat_registration_num;
l_vendor_site_rec.vat_code := p_vat_code;
l_vendor_site_rec.hold_all_payments_flag := p_hold_all_payments_flag;
l_vendor_site_rec.hold_unmatched_invoices_flag := p_hold_unmatch_inv_flag;
l_vendor_site_rec.hold_reason := p_hold_reason;
l_vendor_site_rec.pay_group_lookup_code := p_pay_group_lookup_code;
l_vendor_site_rec.attribute2 := p_chque_type;

ap_vendor_pub_pkg.create_vendor_site(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
-- p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
x_vendor_site_id => x_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id);
--commit;
XXEG_DEBUG_PKG.putLog(11, 'return_status ' || l_return_status);
XXEG_DEBUG_PKG.putLog(11, 'msg_data ' || l_msg_data);
XXEG_DEBUG_PKG.putLog(11, 'msg_count ' || TO_CHAR(l_msg_count));
XXEG_DEBUG_PKG.putLog(11, 'vendor_site_id ' || x_vendor_site_id);
XXEG_DEBUG_PKG.putLog(11, 'party_site_id ' || l_party_site_id);
XXEG_DEBUG_PKG.putLog(11, 'location_id ' || l_location_id);
XXEG_DEBUG_PKG.putLog(11, '');

IF l_msg_count > 0 THEN
FOR v_index IN 1 .. l_msg_count LOOP
fnd_msg_pub.get(p_msg_index => v_index,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out);
l_msg_data := SUBSTR(l_msg_data, 1, 200);
DBMS_OUTPUT.put_line(l_msg_data);
END LOOP;
END IF;

EXCEPTION
WHEN lv_validation_exc THEN
fnd_message.set_name('BNE', 'WEBADI_ERROR');
fnd_message.set_token('ERROR_MESSAGE', lv_error_message);
raise_application_error(-20228, lv_error_message);
WHEN OTHERS THEN
lv_error_message := 'Oracle ErrMsg: ' || sqlerrm;
raise_application_error(-20228, lv_error_message);
END XX_SUPPLIER_SITE_CREATION;

end XX_SS_WEBADI_PKG;

Thursday, January 16, 2020

Adding a Concurrent Program to Request Group from backend

DECLARE
l_program_short_name VARCHAR2 (200);
l_program_application VARCHAR2 (200);
l_request_group VARCHAR2 (200);
l_group_application VARCHAR2 (200);
l_check VARCHAR2 (2);
--
BEGIN
--
l_program_short_name := 'XX_CON_PRG_SHORT_NAME';
l_program_application := 'CONC PRG APPLI APPLION NAME';
l_request_group := 'XX REQUEST GROUP NAME';
l_group_application := 'ABOVE REQUEST GROUP APPLICATION';
--Calling API to assign concurrent program to a reqest group
apps.fnd_program.add_to_group (program_short_name => l_program_short_name,
program_application => l_program_application,
request_group => l_request_group,
group_application => l_group_application
);
--
COMMIT;
--
BEGIN
--To check whether a paramter is assigned to a Concurrent Program or not
SELECT 'Y'
INTO l_check
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs fcp
WHERE frg.request_group_id = frgu.request_group_id
AND frg.application_id = frgu.application_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.unit_application_id = fcp.application_id
AND fcp.concurrent_program_name = 'XX_CON_PRG_SHORT_NAME';
--
dbms_output.put_line ('Adding Concurrent Program to Request Group Succeeded');
--
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line ('Adding Concurrent Program to Request Group Failed');
END;
END;

PL/SQL Script to Submit a Concurrent Program from backend

DECLARE
l_responsibility_id NUMBER;
l_application_id NUMBER;
l_user_id NUMBER;
l_request_id NUMBER;
BEGIN
--
SELECT DISTINCT fr.responsibility_id, frx.application_id
INTO l_responsibility_id,l_application_id
FROM apps.fnd_responsibility frx,apps.fnd_responsibility_tl fr
WHERE fr.responsibility_id = frx.responsibility_id
AND UPPER (fr.responsibility_name) LIKE UPPER('XX RESP NAME');
--
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'XXUSER_NAME';
--
--To set environment context.
--
apps.fnd_global.apps_initialize (l_user_id,l_responsibility_id,l_application_id);
--
--Submitting Concurrent Request
--
l_request_id := fnd_request.submit_request (
application => 'XXCUST',
program => 'XXEMP',
description => 'XXTest Employee Details',
start_time => sysdate,
sub_request => FALSE,
argument1 => NULL
);
--
COMMIT;
--
IF l_request_id = 0
THEN
dbms_output.put_line ('Concurrent request failed to submit');
ELSE
dbms_output.put_line('Successfully Submitted the Concurrent Request');
END IF;
--
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error While Submitting Concurrent Request '||SQLCODE||'-'||sqlerrm);
END;
/

Tuesday, January 14, 2020

Oracle Supplier Custom Interface with API

create or replace package XX_SUP_DTL_CRT_PKG is

procedure XX_SUPPLIER_CREATION(p_vendor_name varchar2,
p_type_lookup_code varchar2,
p_vendor_name_alt varchar2,
x_vendor_id out nocopy number);

procedure XX_SUPPLIER_SITE_CREATION(p_vendor_site_code varchar2,
p_address_line1 varchar2,
p_address_line2 varchar2,
p_address_line3 varchar2,
p_address_line4 varchar2,
p_country varchar2,
p_org_id number,
p_city varchar2,
p_state varchar2,
p_province varchar2,
p_zip varchar2,
p_pay_site_flag varchar2,
p_purchasing_site_flag varchar2,
p_area_code varchar2,
p_site_phone varchar2,
p_fax_area_code varchar2,
p_fax varchar2,
p_site_email_add varchar2,
p_allow_awt_flag varchar2,
p_vat_registration_num number,
p_vat_code varchar2,
p_hold_all_payments_flag varchar2,
p_hold_unmatch_inv_flag varchar2,
p_hold_reason varchar2,
p_pay_group_lookup_code varchar2,
p_chque_type varchar2,
p_vendor_id number,
x_vendor_site_id out nocopy number);

Procedure XX_SUPPLIER_CONTACT_CREATION(p_org_id number,
p_prefix varchar2,
p_person_title varchar2,
p_person_first_name varchar2,
p_person_middle_name varchar2,
p_person_last_name varchar2,
p_phone varchar2,
p_email_address varchar2,
p_department varchar2,
p_fax_phone varchar2,
p_alt_phone varchar2,
p_vendor_id number,
p_vendor_site_id number,
x_vendor_contact_id out nocopy number);

procedure UPLOAD_MAIN( -- header details
p_vendor_name varchar2,
p_type_lookup_code varchar2,
p_vendor_name_alt varchar2,
--site details
p_vendor_site_code varchar2,
p_address_line1 varchar2,
p_address_line2 varchar2,
p_address_line3 varchar2,
p_address_line4 varchar2,
p_country varchar2,
p_org_id number,
p_purchasing_site_flag varchar2,
p_area_code varchar2,
p_site_phone varchar2,
p_fax_area_code varchar2,
p_fax varchar2,
p_site_email_add varchar2,
p_allow_awt_flag varchar2,
p_vat_registration_num number,
p_vat_code varchar2,
p_hold_all_payments_flag varchar2,
p_hold_unmatch_inv_flag varchar2,
p_hold_reason varchar2,
p_pay_group_lookup_code varchar2,
p_city varchar2,
p_state varchar2,
p_province varchar2,
p_zip varchar2,
p_pay_site_flag varchar2,
p_chque_type varchar2,
-- contact details
p_prefix varchar2,
p_person_title varchar2,
p_person_first_name varchar2,
p_person_middle_name varchar2,
p_person_last_name varchar2,
p_phone varchar2,
p_email_address varchar2,
p_department varchar2,
p_fax_phone varchar2,
p_alt_phone varchar2,
x_vendor_id out nocopy number,
x_vendor_site_id out nocopy number,
x_vendor_contact_id out nocopy number);

end XX_SUP_DTL_CRT_PKG ;

========================== X X ================================

create or replace package body XX_SUP_DTL_CRT_PKG is

procedure XX_SUPPLIER_CREATION(p_vendor_name varchar2,
p_type_lookup_code varchar2,
p_vendor_name_alt varchar2,
x_vendor_id out nocopy number) as

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);
l_vendor_rec apps.ap_vendor_pub_pkg.r_vendor_rec_type;
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;
l_vendor_rec.vendor_name := p_vendor_name;
l_vendor_rec.vendor_type_lookup_code := p_type_lookup_code;
l_vendor_rec.vendor_name_alt := p_vendor_name_alt;
l_vendor_rec.women_owned_flag := 'N';
l_vendor_rec.small_business_flag := 'Y';
x_vendor_id := NULL;
x_party_id := NULL;

-- Attribute Details
/*l_vendor_rec.attribute_category := p_attribute_category;
l_vendor_rec.attribute1 := p_attribute1;
l_vendor_rec.attribute2 := p_attribute2;
l_vendor_rec.attribute3 := p_attribute3;
l_vendor_rec.attribute4 := p_attribute4;
l_vendor_rec.attribute5 := p_attribute5;
l_vendor_rec.attribute6 := p_attribute6;
l_vendor_rec.attribute7 := p_attribute7;
l_vendor_rec.attribute8 := p_attribute8;
l_vendor_rec.attribute9 := p_attribute9;
l_vendor_rec.attribute10 := p_attribute10;
l_vendor_rec.attribute11 := p_attribute11;
l_vendor_rec.attribute12 := p_attribute12;
l_vendor_rec.attribute13 := p_attribute13;
l_vendor_rec.attribute14 := p_attribute14;
l_vendor_rec.attribute15 := p_attribute15;*/

-- Other values

ap_vendor_pub_pkg.create_vendor(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
-- p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_rec => l_vendor_rec,
x_vendor_id => x_vendor_id,
x_party_id => x_party_id);
--commit;
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('');

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 XX_SUPPLIER_CREATION;

Procedure XX_SUPPLIER_SITE_CREATION(p_vendor_site_code varchar2,
p_address_line1 varchar2,
p_address_line2 varchar2,
p_address_line3 varchar2,
p_address_line4 varchar2,
p_country varchar2,
p_org_id number,
p_city varchar2,
p_state varchar2,
p_province varchar2,
p_zip varchar2,
p_pay_site_flag varchar2,
p_purchasing_site_flag varchar2,
p_area_code varchar2,
p_site_phone varchar2,
p_fax_area_code varchar2,
p_fax varchar2,
p_site_email_add varchar2,
p_allow_awt_flag varchar2,
p_vat_registration_num number,
p_vat_code varchar2,
p_hold_all_payments_flag varchar2,
p_hold_unmatch_inv_flag varchar2,
p_hold_reason varchar2,
p_pay_group_lookup_code varchar2,
p_chque_type varchar2,
p_vendor_id number,
x_vendor_site_id out nocopy number) as

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status VARCHAR2(100);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_party_site_id NUMBER;
l_location_id NUMBER;
l_msg_index_out NUMBER;
p_api_version NUMBER;
p_init_msg_list VARCHAR2(200);
p_commit VARCHAR2(200);
p_validation_level NUMBER;

BEGIN

--Required
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_vendor_site_id := null;
l_vendor_site_rec.vendor_id := p_vendor_id; -- '10013890';
l_vendor_site_rec.vendor_site_code := p_vendor_site_code;
l_vendor_site_rec.address_line1 := p_address_line1;

--Optional
l_vendor_site_rec.address_line2 := p_address_line2;
l_vendor_site_rec.address_line3 := p_address_line3;
l_vendor_site_rec.address_line4 := p_address_line4;
l_vendor_site_rec.country := p_country;
l_vendor_site_rec.org_id := p_org_id;
l_vendor_site_rec.city := p_city;
l_vendor_site_rec.state := p_state;
l_vendor_site_rec.province := p_province;
l_vendor_site_rec.zip := p_zip;
l_vendor_site_rec.purchasing_site_flag := p_purchasing_site_flag; --'Y';
l_vendor_site_rec.pay_site_flag := p_pay_site_flag; --'Y';
l_vendor_site_rec.rfq_only_site_flag := 'N';
l_vendor_site_rec.area_code := p_area_code;
l_vendor_site_rec.phone := p_site_phone; --'0567072954';
l_vendor_site_rec.fax_area_code := p_fax_area_code;
l_vendor_site_rec.fax := p_fax;
l_vendor_site_rec.email_address := p_site_email_add;
l_vendor_site_rec.allow_awt_flag := p_allow_awt_flag;
l_vendor_site_rec.vat_registration_num := p_vat_registration_num;
l_vendor_site_rec.vat_code := p_vat_code;
l_vendor_site_rec.hold_all_payments_flag := p_hold_all_payments_flag; --
l_vendor_site_rec.hold_unmatched_invoices_flag := p_hold_unmatch_inv_flag;
l_vendor_site_rec.hold_reason := p_hold_reason;
l_vendor_site_rec.pay_group_lookup_code := p_pay_group_lookup_code;
l_vendor_site_rec.attribute2 := p_chque_type;

ap_vendor_pub_pkg.create_vendor_site(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
-- p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
x_vendor_site_id => x_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id);
--commit;
dbms_output.put_line('return_status: ' || l_return_status);
dbms_output.put_line('msg_data: ' || l_msg_data);
dbms_output.put_line('msg_count = ' || TO_CHAR(l_msg_count));
dbms_output.put_line('vendor_site_id: ' || x_vendor_site_id);
dbms_output.put_line('party_site_id: ' || l_party_site_id);
dbms_output.put_line('location_id: ' || l_location_id);
dbms_output.put_line('');

IF l_msg_count > 0 THEN
FOR v_index IN 1 .. l_msg_count LOOP
fnd_msg_pub.get(p_msg_index => v_index,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out);
l_msg_data := SUBSTR(l_msg_data, 1, 200);
DBMS_OUTPUT.put_line(l_msg_data);
END LOOP;
END IF;

END XX_SUPPLIER_SITE_CREATION;

Procedure XX_SUPPLIER_CONTACT_CREATION(p_org_id number,
p_prefix varchar2,
p_person_title varchar2,
p_person_first_name varchar2,
p_person_middle_name varchar2,
p_person_last_name varchar2,
p_phone varchar2,
p_email_address varchar2,
p_department varchar2,
p_fax_phone varchar2,
p_alt_phone varchar2,
p_vendor_id number,
p_vendor_site_id number,
x_vendor_contact_id out nocopy number) as

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_per_party_id NUMBER;
l_rel_party_id NUMBER;
l_rel_id NUMBER;
l_org_contact_id NUMBER;
l_party_site_id NUMBER;
l_msg_index_out NUMBER;
p_api_version NUMBER;
p_init_msg_list VARCHAR2(200);
p_commit VARCHAR2(200);
p_validation_level NUMBER;

BEGIN

-- Required

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_vendor_contact_id := null;
l_vendor_contact_rec.vendor_site_id := p_vendor_site_id;
l_vendor_contact_rec.vendor_id := p_vendor_id;
l_vendor_contact_rec.org_id := p_org_id;
l_vendor_contact_rec.person_first_name := p_person_first_name;
l_vendor_contact_rec.person_last_name := p_person_last_name;

-- Optional
l_vendor_contact_rec.prefix := p_prefix;
l_vendor_contact_rec.person_title := p_person_title;
l_vendor_contact_rec.person_middle_name := p_person_middle_name;
l_vendor_contact_rec.email_address := p_email_address;
l_vendor_contact_rec.department := p_department;
l_vendor_contact_rec.fax_phone := p_fax_phone;
l_vendor_contact_rec.phone := p_phone;
l_vendor_contact_rec.alt_phone := p_alt_phone;

ap_vendor_pub_pkg.create_vendor_contact(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
-- p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_contact_rec => l_vendor_contact_rec,
x_vendor_contact_id => x_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);

--commit;

dbms_output.put_line('return_status: ' || l_return_status);
dbms_output.put_line('msg_data: ' || l_msg_data);
dbms_output.put_line('vendor_contact_id: ' || x_vendor_contact_id);
dbms_output.put_line('party_site_id: ' || l_party_site_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('p_vendor_site_id: ' || p_vendor_site_id);

IF l_msg_count > 0 THEN
FOR v_index IN 1 .. l_msg_count LOOP
fnd_msg_pub.get(p_msg_index => v_index,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out);
l_msg_data := SUBSTR(l_msg_data, 1, 200);
DBMS_OUTPUT.put_line(l_msg_data);
END LOOP;
END IF;

END XX_SUPPLIER_CONTACT_CREATION;

procedure UPLOAD_MAIN( -- header details
p_vendor_name varchar2,
p_type_lookup_code varchar2,
p_vendor_name_alt varchar2,
--site details
p_vendor_site_code varchar2,
p_address_line1 varchar2,
p_address_line2 varchar2,
p_address_line3 varchar2,
p_address_line4 varchar2,
p_country varchar2,
p_org_id number,
p_purchasing_site_flag varchar2,
p_area_code varchar2,
p_site_phone varchar2,
p_fax_area_code varchar2,
p_fax varchar2,
p_site_email_add varchar2,
p_allow_awt_flag varchar2,
p_vat_registration_num number,
p_vat_code varchar2,
p_hold_all_payments_flag varchar2,
p_hold_unmatch_inv_flag varchar2,
p_hold_reason varchar2,
p_pay_group_lookup_code varchar2,
p_city varchar2,
p_state varchar2,
p_province varchar2,
p_zip varchar2,
p_pay_site_flag varchar2,
p_chque_type varchar2,
-- contact details
p_prefix varchar2,
p_person_title varchar2,
p_person_first_name varchar2,
p_person_middle_name varchar2,
p_person_last_name varchar2,
p_phone varchar2,
p_email_address varchar2,
p_department varchar2,
p_fax_phone varchar2,
p_alt_phone varchar2,
x_vendor_id out nocopy number,
x_vendor_site_id out nocopy number,
x_vendor_contact_id out nocopy number) as

BEGIN

XX_SUP_DTL_CRT_PKG.XX_SUPPLIER_CREATION(p_vendor_name,
p_type_lookup_code,
p_vendor_name_alt,
x_vendor_id);

XX_SUP_DTL_CRT_PKG.XX_SUPPLIER_SITE_CREATION(p_vendor_site_code,
p_address_line1,
p_address_line2,
p_address_line3,
p_address_line4,
p_country,
p_org_id,
p_city,
p_state,
p_province,
p_zip,
p_pay_site_flag,
p_purchasing_site_flag,
p_area_code,
p_site_phone,
p_fax_area_code,
p_fax,
p_site_email_add,
p_allow_awt_flag,
p_vat_registration_num,
p_vat_code,
p_hold_all_payments_flag,
p_hold_unmatch_inv_flag,
p_hold_reason,
p_pay_group_lookup_code,
p_chque_type,
x_vendor_id,
x_vendor_site_id);

XX_SUP_DTL_CRT_PKG.XX_SUPPLIER_CONTACT_CREATION(p_org_id,
p_prefix,
p_person_title,
p_person_first_name,
p_person_middle_name,
p_person_last_name,
p_phone,
p_email_address,
p_department,
p_fax_phone,
p_alt_phone,
x_vendor_id,
x_vendor_site_id,
x_vendor_contact_id);

END UPLOAD_MAIN;

end XX_SUP_DTL_CRT_PKG;

Yadul Oracle Duniya

Thursday, March 12, 2020

SQL Query to find Customer, Customer Account and Customer Sites Information

Tuesday, February 11, 2020

Supplier Interface with APIs (Custom Interface)

Thursday, January 16, 2020

Adding a Concurrent Program to Request Group from backend

PL/SQL Script to Submit a Concurrent Program from backend

Tuesday, January 14, 2020

Oracle Supplier Custom Interface with API

SQL Query to find Customer, Customer Account and Customer Sites Information

Labels

Report Abuse

Pages