Oracle EBS Developer-Blog: January 2021

Friday, January 29, 2021

Query to Find Concurrent Program Responsibility

SELECT frt.responsibility_name, frg.request_group_name,

frgu.request_unit_type,frgu.request_unit_id,
fcpt.user_concurrent_program_name
  FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
fnd_request_groups frg, fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt
  WHERE frt.responsibility_id = fr.responsibility_id
  AND frg.request_group_id = fr.request_group_id
  AND frgu.request_group_id = frg.request_group_id
  AND fcpt.concurrent_program_id = frgu.request_unit_id
  AND frt.LANGUAGE = USERENV('LANG')
  AND fcpt.LANGUAGE = USERENV('LANG')
  AND fcpt.user_concurrent_program_name = :conc_prg_name
  ORDER BY 1,2,3,4

Sunday, January 24, 2021

Po Action History Insert Query - po_action_history

DECLARE

cursor l_data IS

select poah.object_id

, po_header_id

, poah.object_type_code

, poah.object_sub_type_code

, poah.sequence_num

, poah.last_update_date

, poah.last_updated_by

, poah.creation_date

, poah.created_by

, poah.action_code

, poah.action_date

, poah.employee_id

, NVL((select new_person_id from XX.XX_PERSON_CREATE_TMP where person_id= poah.employee_id),1490) new_person_id

, poah.note

, poah.object_revision_num

, poah.last_update_login

, poah.request_id

, poah.program_application_id

, poah.program_id

, poah.program_update_date

, poah.approval_path_id from xxcustom_stage_table poah, po_headers_all poh

where poah.object_id=poh.GLOBAL_ATTRIBUTE1 ;

BEGIN

FOR n_data IN l_data LOOP

INSERT INTO PO_ACTION_HISTORY

( object_id

, object_type_code

, object_sub_type_code

, sequence_num

, last_update_date

, last_updated_by

, creation_date

, created_by

, action_code

, action_date

, employee_id

, note

, object_revision_num

, last_update_login

, request_id

, program_application_id

, program_id

, program_update_date

, approval_path_id

)

VALUES

( n_data.po_header_id

, n_data.object_type_code

, n_data.object_sub_type_code

, n_data.sequence_num

, n_data.last_update_date

, -1

, n_data.creation_date

, -1

, n_data.action_code

, n_data.action_date

, n_data.NEW_PERSON_ID

, n_data.note

, n_Data.OBJECT_REVISION_NUM

, -1

, 0

, ''

, NULL

);

END LOOP;

END;

Fnd Attachment Create Api - fnd_documents_pkg.insert_row and fnd_attached_documents_pkg.insert_row

DECLARE

l_rowid ROWID;

l_attached_document_id NUMBER;

l_document_id NUMBER;

l_media_id NUMBER;

l_user_id NUMBER;

l_category_id NUMBER;

l_pk1_value fnd_attached_documents.pk1_value%TYPE;

l_description fnd_documents_tl.description%TYPE;

l_seq_num NUMBER;

lv_short_text VARCHAR2(1000);

lv_bill_address1 VARCHAR2(1000);

lv_bill_address2 VARCHAR2(1000);

lv_bill_city VARCHAR2(1000);

lv_bill_state VARCHAR2(1000);

lv_bill_countryid VARCHAR2(1000);

lv_bill_postalcode VARCHAR2(1000);

l_title VARCHAR2(1000);

l_security_id NUMBER;

l_storage_type VARCHAR2(1000);

l_image_type VARCHAR2(1000);

l_start_date_active DATE;

l_end_date_active DATE;

l_usage_type VARCHAR2(1000);

lv_notes_cnt NUMBER;

lv_bill_company VARCHAR2(1000);

v_long_text CLOB;

v_bfile BFILE;

v_clob CLOB;

l_blob BLOB;

CURSOR l_data IS

SELECT xfad.ATTACHED_DOCUMENT_ID,

xfad.document_id,

xfad.seq_num,

xfad.entity_name,

xfad.pk1_value,

xfad.pk2_value,

xfad.AUTOMATICALLY_ADDED_FLAG,

xfad.PROGRAM_UPDATE_DATE,

xfad.category_id,

(SELECT category_id

FROM FND_DOCUMENT_CATEGORIES_vl fdc

WHERE fdc.user_name = xfd.category_name)

new_category_id,

xfd.category_name,

xfd.datatype_id,

(SELECT datatype_id

FROM FND_DOCUMENT_DATATYPES fdd

WHERE fdd.name = xfd.datatype_name AND LANGUAGE = 'TR')

new_datatype_id,

xfd.security_type,

xfd.security_id,

xfd.publish_flag,

xfd.USAGE_TYPE,

xfd.START_DATE_ACTIVE,

xfd.media_id,

xfd.file_name,

(select po_header_id from po_headers_all where GLOBAL_ATTRIBUTE1=xfad.pk1_value and rownum=1) new_po_header_id,

xfad.status ,

poh.type_lookup_code,

poh.segment1,

poh.creation_date,

po_header_id,

GLOBAL_ATTRIBUTE1

FROM XX_FND_ATTACHED_DOCUMENTS xfad, XX.XX_FND_DOCUMENTS xfd ,po_headers_all poh

WHERE xfd.document_id = xfad.document_id and pk1_value = poh.GLOBAL_ATTRIBUTE1

and xfad.status is null and xfd.status is null ;

BEGIN

FOR n_data IN l_data LOOP

l_media_id :=NULL;

l_document_id := NULL;

l_attached_document_id := NULL;

BEGIN

SELECT fnd_documents_s.NEXTVAL INTO l_document_id FROM DUAL;

EXCEPTION

WHEN OTHERS THEN

l_document_id := 1;

END;

BEGIN

SELECT fnd_attached_documents_s.NEXTVAL

INTO l_attached_document_id

FROM DUAL;

EXCEPTION

WHEN OTHERS THEN

l_attached_document_id := 1;

END;

DBMS_OUTPUT.PUT_LINE('document_id='||l_document_id);

DBMS_OUTPUT.PUT_LINE('attached_document_id='||l_attached_document_id);

fnd_documents_pkg.insert_row(x_rowid => l_rowid,

x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => -1,

x_last_update_date => SYSDATE,

x_last_updated_by => -1,

x_last_update_login => -1,

X_datatype_id => n_data.new_datatype_id,

x_category_id => n_data.new_category_id,

x_security_type => n_data.security_type,

x_security_id => n_data.security_id,

x_publish_flag => n_data.publish_flag,

x_usage_type => n_data.USAGE_TYPE,

X_start_date_active =>n_data.START_DATE_ACTIVE,

x_language => 'TR',

-- x_description => l_description,

x_file_name => n_data.file_name,

--x_title => 'Line level Notes',

x_media_id => l_media_id);

fnd_documents_pkg.insert_tl_row(x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => -1,

x_last_update_date => SYSDATE,

x_last_updated_by => -1,

x_last_update_login => -1,

x_language => 'US'

-- x_description => l_description

);

fnd_attached_documents_pkg.insert_row(x_rowid => l_rowid,

x_attached_document_id => l_attached_document_id,

x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => -1,

x_last_update_date => SYSDATE,

x_last_updated_by => -1,

x_last_update_login => -1,

x_seq_num => n_data.seq_num,

x_entity_name => n_data.entity_name,

x_column1 => NULL,

x_pk1_value => n_data.po_header_id ,

x_pk2_value => n_data.pk2_value,

x_pk3_value => NULL,

x_pk4_value => NULL,

x_pk5_value => NULL,

X_program_update_date => n_data.program_update_date,

x_automatically_added_flag => n_data.AUTOMATICALLY_ADDED_FLAG,

x_datatype_id => n_data.new_datatype_id,

x_category_id => NVL(n_data.new_category_id,1),

x_security_type => n_data.security_type,

x_publish_flag => n_data.publish_flag,

x_language => 'TR',

-- x_description => l_description,

x_media_id => l_media_id);

DBMS_OUTPUT.PUT_LINE ('Media id '||l_media_id);

-- lv_short_text := 'Test for short text attachment INSERT';

IF n_data.NEW_DATATYPE_ID =2 AND l_media_id is not null THEN

BEGIN

FOR r IN (SELECT *

FROM fnd_documents_long_text@DB_LINK

WHERE media_id = n_data.media_id)

LOOP

INSERT INTO fnd_documents_long_text

VALUES (l_media_id, NULL, r.long_text);

END LOOP;

UPDATE xx.XX_FND_ATTACHED_DOCUMENTS

SET status = 'UPDATE'

WHERE ATTACHED_DOCUMENT_ID = n_data.ATTACHED_DOCUMENT_ID;

UPDATE XX.XX_FND_DOCUMENTS

SET status = 'UPDATE'

WHERE document_id = n_data.document_id;

COMMIT;

END;

ELSIF n_data.NEW_DATATYPE_ID =6 AND l_media_id is not null THEN

INSERT INTO FND_LOBS (file_id,

file_name,

file_content_type,

file_data,

upload_date,

program_name,

language,

oracle_charset,

file_format)

SELECT l_media_id,

file_name,

file_content_type,

file_data,

upload_date,

program_name,

language,

oracle_charset,

file_format

FROM FND_LOBS@DB_LINK

WHERE file_id = n_data.media_id;

UPDATE xx.XX_FND_ATTACHED_DOCUMENTS

SET status = 'UPDATE'

WHERE ATTACHED_DOCUMENT_ID = n_data.ATTACHED_DOCUMENT_ID;

UPDATE XX.XX_FND_DOCUMENTS

SET status = 'UPDATE'

WHERE document_id = n_data.document_id;

COMMIT;

END IF;

COMMIT;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(sqlerrm);

END;

Ap_Supplier_bank Create Ap Supplier Bank - iby_ext_bankacct_pub.create_ext_bank_acct

Ap supplier bank is completing with 2 steps, you can follow below steps

1- iby_ext_bankacct_pub.create_ext_bank_acct

2-iby_disbursement_setup_pub.set_payee_instr_assignment

DECLARE

p_api_version NUMBER;

p_init_msg_list VARCHAR2(200);

p_ext_bank_acct_rec apps.iby_ext_bankacct_pub.extbankacct_rec_type;

x_acct_id NUMBER;

x_return_status VARCHAR2(200);

x_msg_count NUMBER;

x_msg_data VARCHAR2(200);

x_response apps.iby_fndcpt_common_pub.result_rec_type;

lv_bank_name VARCHAR2(100);

lv_bank_branch VARCHAR2(100);

lv_bank_acct_num VARCHAR2(100);

lv_bank_acct_name VARCHAR2(100);

lv_vendor_name VARCHAR2(100);

ln_bank_id NUMBER;

ln_branch_id NUMBER;

lv_bank_home_country_code VARCHAR2(100);

ln_acct_owner_party_id NUMBER;

l_msg VARCHAR2(200);

l_count NUMBER;

CURSOR l_data IS

select vendor_name,

vendor_id vendor_id,

vendor_type_lookup_code,

ext_bank_account_id,

xiba.BANK_PARTY_ID,

xiba.COUNTRY_CODE,

xiba.BANK_NAME,

xiba.BANK_NUMBER,

xiba.BRANCH_PARTY_ID,

xiba.BANK_BRANCH_NAME,

xiba.BRANCH_NUMBER,

xiba.BRANCH_TYPE,

xiba.BANK_BRANCH_ADDRESS_ID,

xiba.BANK_ACCOUNT_ID,

xiba.BANK_ACCOUNT_NAME,

xiba.BANK_ACCOUNT_NUMBER,

xiba.CURRENCY_CODE,

xiba.DESCRIPTION,

ALTERNATE_ACCOUNT_NAME,

SHORT_ACCT_NAME,

-- xiba.PRIMARY_ACCT_OWNER_PARTY_ID,

xiba.PRIMARY_ACCT_OWNER_NAME,

(SELECT party_id FROM ap_suppliers WHERE vendor_name = xiba.vendor_name) PRIMARY_ACCT_OWNER_id,

xiba.ACCOUNT_CLASSIFICATION,

iban_number,

xiba.START_DATE,

xiba.END_DATE,

BANK_CODE, --account_type

vat_registration_num, --vergi_no

sube_swift,

BANKA_SWIFT,

vendor_creation,

site_creation ,

new_ext_bank_account_id,

xiba.status ,

ORDER_OF_PREFERENCE

from XX.XX_IBY_BANK_ACCOUNTS xiba

where 1=1 and status is null ;

BEGIN

-- Initialize apps session

fnd_global.apps_initialize (user_id => 1110,

resp_id => 50779,

resp_appl_id => 200);

mo_global.init ('SQLAP');

-- fnd_client_info.set_org_context (101);

-- Nullify temp variables

ln_bank_id := NULL;

ln_branch_id := NULL;

lv_bank_home_country_code := NULL;

-- ln_acct_owner_party_id := NULL;

-- Get bank details

FOR n_data IN l_data LOOP

BEGIN

SELECT count(1)

INTO l_count

FROM ce_bank_branches_v

WHERE bank_number = n_data.bank_number

AND BRANCH_NUMBER = n_data.BRANCH_NUMBER;

DBMS_OUTPUT.PUT_LINE('1- count= '|| l_count );

IF l_count > 0 THEN

SELECT bank_home_country, bank_party_id, pk_id

INTO lv_bank_home_country_code, ln_bank_id, ln_branch_id

FROM ce_bank_branches_v

WHERE bank_number = n_data.bank_number

AND BRANCH_NUMBER = n_data.BRANCH_NUMBER

AND ROWNUM=1;

DBMS_OUTPUT.PUT_LINE('2- bank_id= '|| ln_bank_id || ' branch_id= ' || ln_branch_id );

ELSE

SELECT bank_home_country, bank_party_id, pk_id

INTO lv_bank_home_country_code, ln_bank_id, ln_branch_id

FROM ce_bank_branches_v

WHERE bank_number = n_data.bank_number

AND BRANCH_NUMBER = '0';

DBMS_OUTPUT.PUT_LINE('3- bank_id= '|| ln_bank_id || ' branch_id= ' || ln_branch_id );

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

BEGIN

SELECT bank_home_country, bank_party_id, pk_id

INTO lv_bank_home_country_code, ln_bank_id, ln_branch_id

FROM ce_bank_branches_v

WHERE BANK_NAME = n_data.bank_name

AND BANK_BRANCH_NAME = n_data.bank_branch_name;

DBMS_OUTPUT.PUT_LINE (

'4- bank_id= '

|| ln_bank_id

|| ' branch_id= '

|| ln_branch_id);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

DBMS_OUTPUT.put_line (

'Unable to derive Bank and Branch details-----'

|| SQLERRM

|| ' ----->'

|| n_data.bank_name

|| '-'

|| n_data.BANK_BRANCH_NAME);

END;

-- Assign API parameters

p_api_version := 1.0;

p_init_msg_list := fnd_api.g_true;

p_ext_bank_acct_rec.country_code := n_data.country_code;

p_ext_bank_acct_rec.bank_id := ln_bank_id; -- bank_id

p_ext_bank_acct_rec.branch_id := ln_branch_id; -- branch_id

p_ext_bank_acct_rec.acct_owner_party_id := n_data.PRIMARY_ACCT_OWNER_id;

-- p_ext_bank_acct_rec.bank_account_id := n_data.bank_account_id;

p_ext_bank_acct_rec.iban := n_data.IBAN_NUMBER;

p_ext_bank_acct_rec.bank_account_name := n_data.bank_account_name;

p_ext_bank_acct_rec.bank_account_num := n_data.BANK_ACCOUNT_NUMBER;

p_ext_bank_acct_rec.currency := n_data.currency_code; -- bank_acct_currency

p_ext_bank_acct_rec.start_date := n_data.start_date;

p_ext_bank_acct_rec.foreign_payment_use_flag := 'Y';

p_ext_bank_acct_rec.object_version_number :=1;

iby_ext_bankacct_pub.create_ext_bank_acct(p_api_version => p_api_version,

p_init_msg_list => p_init_msg_list,

p_ext_bank_acct_rec => p_ext_bank_acct_rec,

x_acct_id => x_acct_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

x_response => x_response);

IF x_return_status ='S' THEN

update XX.XX_IBY_BANK_ACCOUNTS set new_ext_bank_account_id = x_acct_id ,

status ='UPDATE' where EXT_BANK_ACCOUNT_ID= n_data.EXT_BANK_ACCOUNT_ID;

COMMIT;

DBMS_OUTPUT.put_line('X_ACCT_ID = ' || x_acct_id);

DBMS_OUTPUT.put_line('X_RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.put_line('X_MSG_COUNT = ' || x_msg_count);

DBMS_OUTPUT.put_line('X_MSG_DATA = ' || x_msg_data);

DBMS_OUTPUT.put_line('X_RESPONSE.Result_Code = ' ||

x_response.result_code);

DBMS_OUTPUT.put_line('X_RESPONSE.Result_Category = ' ||

x_response.result_category);

DBMS_OUTPUT.put_line('X_RESPONSE.Result_Message = ' ||

x_response.result_message);

END IF;

IF (x_return_status <> fnd_api.g_ret_sts_success) THEN

FOR i IN 1 .. fnd_msg_pub.count_msg LOOP

l_msg := fnd_msg_pub.get(p_msg_index => i,

p_encoded => fnd_api.g_false);

DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg || 'Vendor Name: ' || n_data.vendor_name || ' Account No: ' || n_data.BANK_ACCOUNT_NUMBER);

END LOOP;

ELSE

DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');

END IF;

END LOOP;

END;

/* Formatted on 12/7/2020 2:20:29 PM (QP5 v5.277) */

DECLARE

-- IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment

p_instrument iby_fndcpt_setup_pub.pmtinstrument_rec_type;

p_assignment_attribs iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;

x_response iby_fndcpt_common_pub.result_rec_type;

x_assign_id NUMBER;

x_return_status VARCHAR2 (2000);

x_msg_count NUMBER (5);

x_msg_data VARCHAR2 (100);

p_payee iby_disbursement_setup_pub.payeecontext_rec_type;

l_msg VARCHAR2 (200);

l_vendor_site_id NUMBER;

l_org_id NUMBER;

CURSOR l_data

SELECT ieb.ext_bank_account_id,

iep.payee_party_id,

ieb.start_date,

EXT_PAYEE_ID,

xiba.ORDER_OF_PREFERENCE

FROM iby_external_payees_all iep,

IBY_EXT_BANK_ACCOUNTS_V ieb,

XX.XX_IBY_BANK_ACCOUNTS xiba

WHERE iep.payee_party_id = ieb.PRIMARY_ACCT_OWNER_PARTY_ID

AND xiba.NEW_EXT_BANK_ACCOUNT_ID=ieb.EXT_BANK_ACCOUNT_ID

AND ieb.end_date IS NULL

AND iep.inactive_date IS NULL

AND iep.ORG_TYPE is null

and payee_party_id!=38466;

BEGIN

FOR n_data IN l_data

LOOP

p_payee.party_id := n_data.payee_party_id; --acct_owner_party_id or payee_party_id

p_payee.payment_function := 'PAYABLES_DISB';

p_payee.org_type := 'OPERATING_UNIT';

p_instrument.instrument_id := n_data.ext_bank_account_id; -- ext_bank_account_id

p_instrument.instrument_type := 'BANKACCOUNT';

p_assignment_attribs.priority := n_data.ORDER_OF_PREFERENCE;

p_assignment_attribs.start_date := n_data.start_date;

p_assignment_attribs.instrument := p_instrument;

iby_disbursement_setup_pub.set_payee_instr_assignment (

1.0,

fnd_api.g_true,

fnd_api.g_false, -- p_commit

x_return_status,

x_msg_count,

x_msg_data,

p_payee,

p_assignment_attribs,

x_assign_id,

x_response);

IF (x_return_status <> fnd_api.g_ret_sts_success)

THEN

FOR i IN 1 .. fnd_msg_pub.count_msg

LOOP

l_msg :=

fnd_msg_pub.get (p_msg_index => i, p_encoded => fnd_api.g_false);

DBMS_OUTPUT.put_line ('The API call failed with error ' || l_msg);

END LOOP;

ELSE

DBMS_OUTPUT.put_line ('The API call ended with SUCESSS status');

DBMS_OUTPUT.put_line ('x_assign_id = ' || x_assign_id);

DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);

DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

DBMS_OUTPUT.put_line ('X_RESPONSE.Result_Code = ' || x_response.result_code);

DBMS_OUTPUT.put_line ('X_RESPONSE.Result_Category = ' || x_response.result_category);

DBMS_OUTPUT.put_line ('X_RESPONSE.Result_Message = ' || x_response.result_message);

END IF;

END LOOP;

END;

Ap_supplier_sites_all Create Ap Supplier Sites Api - AP_VENDOR_PUB_PKG.Create_Vendor_Site

/* Formatted on 10/12/2020 7:56:18 PM (QP5 v5.277) */

DECLARE

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;

l_return_status VARCHAR2 (10);

l_msg_count NUMBER;

l_msg_data VARCHAR2 (1000);

l_vendor_site_id NUMBER;

l_party_id NUMBER;

p_count NUMBER;

v_api_error VARCHAR2 (500);

v_error_msg VARCHAR2 (500) := NULL;

l_msg VARCHAR2 (200);

x_party_site_id NUMBER;

l_party_site_id NUMBER;

x_location_id NUMBER;

l_location_id NUMBER;

p_code_combination_id NUMBER;

p_code_combination_id1 NUMBER;

p_segments APPS.FND_FLEX_EXT.SEGMENTARRAY;

p_segments1 APPS.FND_FLEX_EXT.SEGMENTARRAY;

bool boolean;

p_api_version NUMBER;

p_init_msg_list VARCHAR2(200);

p_commit VARCHAR2(200);

p_validation_level NUMBER;

z_segment3 varchar2(500);

z_segment10 varchar2(500);

l_segment2 varchar2(500):=NULL;

l_segment9 varchar2(500) :=NULL;

L_SHIP_TO_LOCATION_ID NUMBER;

l_BILL_TO_LOCATION_ID number;

l_hz_location_id number;

l_ccid number;

l_data_Set number:=null;

CURSOR l_data

SELECT xass.vendor_site_id,

xass.vendor_id,

xas.new_vendor_id,

xass.vendor_site_code,

xas.vendor_type_lookup_code,

xass.address_line1,

xass.address_line2,

xass.address_line3 ,--

xass.ADDRESS_STYLE ,--

xass.zip ,--

xass.province ,--

xass.city,

xass.country,

xass.area_code,

xass.phone,

xass.fax,

xass.fax_area_code,

xass.term_date_basis,

xas.vendor_name,

xass.accts_pay_code_combination_id,

xss.segment1,

xss.segment2,

xss.segment3,

xss.segment4,

xss.segment5,

xss.segment6,

xss.segment7,

xss.segment8, --'I0000000'

xss.segment9,

xss.segment10,

xss.segment11,

xss.segment12,

xass.prepay_code_combination_id,

xss.segment13,

xss.segment14,

xss.segment15,

xss.segment16,

xss.segment17,

xss.segment18,

xss.segment19,

xss.segment20 ,--'I0000000' segment20,

xss.segment21,

xss.segment22,

xss.segment23,

xss.segment24,

xass.pay_group_lookup_code,

xass.payment_priority,

(SELECT DISTINCT term_id

FROM ap_terms_tl

WHERE name = xass.terms_name)

terms_id,

xass.terms_name,

xass.pay_date_basis_lookup_code,

xass.invoice_currency_code,

xass.attribute9,

xass.attribute10,

xass.attribute11,

xass.attribute12,

xass.attribute13,

xass.attribute14,

xass.attribute15,

xass.validation_number,

xass.org_id org_id_old,

(select organization_id from hr_organization_units where name=xass.org_name) org_id,

xass.org_name,

xass.language,

xass.location_id,

--(select location_id from hz_locations where address1= xass.location_name and rownum=1) new_location_id,

xass.location_name,

xass.party_site_id,

xass.tca_sync_city,

xass.tca_sync_country,

xass.new_vendor_site_id,

xass.status,

xass.ALLOW_AWT_FLAG ,

xass.PURCHASING_SITE_FLAG ,

xass.RFQ_ONLY_SITE_FLAG ,

xass.PAY_SITE_FLAG ,

xass.primary_pay_site_flag,

xass.SHIP_TO_LOCATION_ID ,

xass.SHIP_TO_LOCATION_NAME,

xass.BILL_TO_LOCATION_ID

FROM XX.XX_AP_SUPPLIER_SITES_ALL xass, XX.XX_AP_SUPPLIERS xas, xx.xx_supplier_sites_detail xss

WHERE xass.vendor_id =xas.vendor_id and xas.new_vendor_id is not null

AND xss.vendor_id=xas.vendor_id

AND xss.VENDOR_SITE_ID=xass.VENDOR_SITE_ID

AND xass.status is null;

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;

fnd_global.apps_initialize (user_id => 1110,

resp_id => 50779,

resp_appl_id => 200);

mo_global.init ('SQLAP');

--fnd_client_info.set_org_context (101);

FOR n_data IN l_data

LOOP

-- --------------

-- Required

-- --------------

IF n_data.vendor_type_lookup_code ='EMPLOYEE' and n_data.org_id_old = 81 THEN

l_segment2 :='33502';

l_segment9 :='19501';

SELECT LOCATION_ID INTO l_location_id FROM HR_LOCATIONS

WHERE LOCATION_CODE= -- default location_code

L_SHIP_TO_LOCATION_ID :=l_location_id;

l_BILL_TO_LOCATION_ID :=l_location_id;

ELSIF n_data.vendor_type_lookup_code ='EMPLOYEE' and n_data.org_id_old = 141 THEN

l_segment2 :='33502001';

l_segment9 :='19501001';

SELECT location_id INTO l_location_id FROM HR_LOCATIONS

WHERE LOCATION_CODE= -- default location_code

L_SHIP_TO_LOCATION_ID :=l_location_id;

l_BILL_TO_LOCATION_ID :=l_location_id;

ELSE

/* Formatted on 12/7/2020 12:39:03 AM (QP5 v5.277) */

BEGIN

SELECT location_id

INTO l_location_id

FROM HR_LOCATIONS

WHERE LOCATION_CODE = -- default location_code

L_SHIP_TO_LOCATION_ID := l_location_id;

l_BILL_TO_LOCATION_ID := l_location_id;

DBMS_OUTPUT.PUT_lINE ('1-location_id=' || l_location_id);

EXCEPTION

WHEN OTHERS

THEN

L_SHIP_TO_LOCATION_ID := NULL;

l_BILL_TO_LOCATION_ID := NULL;

DBMS_OUTPUT.PUT_lINE ('2-location_id=' || L_SHIP_TO_LOCATION_ID);

END;

END IF;

-- z_segment3 := xx_get_cc_segment3_po (n_data.segment3);

p_segments (1) := n_data.segment1;

p_segments (2) := n_data.segment2;--NVL(l_segment2,n_data.segment2); --l_segment2

p_segments (3) := n_data.segment3;--NVL(z_segment3,n_data.segment3);

p_segments (4) := n_data.segment4;

p_segments (5) := n_data.segment5;

p_segments (6) := n_data.segment6;

p_segments (7) := n_data.segment7;

p_segments (8) := n_data.segment8;

p_segments (9) := n_data.segment9;

p_segments (10) := n_data.segment10;

p_segments (11) := n_data.segment11;

p_segments (12) := n_data.segment12;

p_code_combination_id :=

xx_get_ccid (50388,

SYSDATE,

12,

p_segments);

dbms_output.put_line('ccid1 : ' || p_code_combination_id || ' - ' || fnd_flex_ext.get_message || 'vendor_site_id='|| n_data.vendor_site_id);

--z_segment10 := xx_get_cc_segment3_po (n_data.segment10);

p_segments1 (1) := n_data.segment13;

p_segments1 (2) := n_data.segment14;--NVL(l_segment9,n_data.segment9); --l_segment9

p_segments1 (3) := n_data.segment15;-- NVL(z_segment10,n_data.segment10);

p_segments1 (4) := n_data.segment16;

p_segments1 (5) := n_data.segment17;

p_segments1 (6) := n_data.segment18;

p_segments1 (7) := n_data.segment19;

p_segments1 (8) := n_data.segment20;

p_segments1 (9) := n_data.segment21;

p_segments1 (10) := n_data.segment22;

p_segments1 (11) := n_data.segment23;

p_segments1 (12) := n_data.segment24;

p_code_combination_id1 :=

xx_get_ccid (50388,

SYSDATE,

12,

p_segments1);

dbms_output.put_line('ccid2 : ' || p_code_combination_id1 || ' - ' || fnd_flex_ext.get_message|| 'vendor_site_id='|| n_data.vendor_site_id);

IF p_code_combination_id IS NOT NULL OR p_code_combination_id1 IS NOT NULL THEN

l_vendor_site_rec.VENDOR_SITE_CODE := n_data.VENDOR_SITE_CODE;

l_vendor_site_rec.VENDOR_ID := n_data.new_vendor_id;

l_vendor_site_rec.ADDRESS_LINE1 := n_data.ADDRESS_LINE1;

l_vendor_site_rec.ADDRESS_LINE2 := n_data.ADDRESS_LINE2;

l_vendor_site_rec.address_line3 := n_data.address_line3;

l_vendor_site_rec.ADDRESS_STYLE := n_data.ADDRESS_STYLE;

l_vendor_site_rec.zip := n_data.zip;

l_vendor_site_rec.province := n_data.province;

l_vendor_site_rec.CITY := n_data.CITY;

l_vendor_site_rec.country := n_data.COUNTRY;

l_vendor_site_rec.AREA_CODE := n_data.AREA_CODE;

l_vendor_site_rec.PHONE := n_data.PHONE;

l_vendor_site_rec.FAX := n_data.FAX;

l_vendor_site_rec.FAX_AREA_CODE := n_data.FAX_AREA_CODE;

l_vendor_site_rec.TERMS_DATE_BASIS := n_data.TERM_DATE_BASIS;

l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID := p_code_combination_id ;

l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID := p_code_combination_id1;

l_vendor_site_rec.PAY_GROUP_LOOKUP_CODE := n_data.PAY_GROUP_LOOKUP_CODE;

l_vendor_site_rec.PAYMENT_PRIORITY := n_data.PAYMENT_PRIORITY;

l_vendor_site_rec.TERMS_ID := n_data.TERMS_ID;

l_vendor_site_rec.PAY_DATE_BASIS_LOOKUP_CODE := n_data.PAY_DATE_BASIS_LOOKUP_CODE;

l_vendor_site_rec.INVOICE_CURRENCY_CODE := n_data.INVOICE_CURRENCY_CODE;

l_vendor_site_rec.ATTRIBUTE9 := n_data.ATTRIBUTE9;

l_vendor_site_rec.ATTRIBUTE10 := n_data.ATTRIBUTE10;

l_vendor_site_rec.ATTRIBUTE11 := n_data.ATTRIBUTE11;

l_vendor_site_rec.ATTRIBUTE12 := n_data.ATTRIBUTE12;

l_vendor_site_rec.ATTRIBUTE13 := n_data.ATTRIBUTE13;

l_vendor_site_rec.ATTRIBUTE14 := n_data.ATTRIBUTE14;

l_vendor_site_rec.ATTRIBUTE15 := n_data.ATTRIBUTE15;

l_vendor_site_rec.VALIDATION_NUMBER := n_data.VALIDATION_NUMBER;

l_vendor_site_rec.ORG_ID := n_data.ORG_ID;

-- l_vendor_site_rec.org_name := n_data.org_name;

l_vendor_site_rec.LANGUAGE := n_data.LANGUAGE;

-- l_vendor_site_rec.LOCATION_ID := n_data.new_LOCATION_ID;

l_vendor_site_rec.PURCHASING_SITE_FLAG := n_data.PURCHASING_SITE_FLAG;

l_vendor_site_rec.PAY_SITE_FLAG := n_data.PAY_SITE_FLAG;

l_vendor_site_rec.RFQ_ONLY_SITE_FLAG := n_data.RFQ_ONLY_SITE_FLAG;

l_vendor_site_rec.ALLOW_AWT_FLAG := n_data.ALLOW_AWT_FLAG;

l_vendor_site_rec.primary_pay_site_flag := n_data.primary_pay_site_flag;

l_vendor_site_rec.SHIP_TO_LOCATION_ID := L_SHIP_TO_LOCATION_ID;

l_vendor_site_rec.BILL_TO_LOCATION_ID := L_BILL_TO_LOCATION_ID;

l_vendor_site_rec.ext_payee_rec.default_pmt_method := 'EFT';

-- -------------

-- Optional

-- --------------

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 => l_vendor_site_id,

x_party_site_id => l_party_site_id,

x_location_id => l_hz_location_id);

IF l_return_status IN ('S') THEN

UPDATE XX.XX_AP_SUPPLIER_SITES_ALL

SET new_vendor_site_id = l_vendor_site_id,new_party_site_id = l_party_site_id , new_location_id=l_hz_location_id, status = 'UPDATE'

WHERE vendor_site_id = n_data.vendor_site_id;

COMMIT;

-- fnd_msg_pub.initialize;

DBMS_OUTPUT.put_line ('l_vendor_site_id = ' || l_vendor_site_id);

DBMS_OUTPUT.put_line ('l_party_site_id = ' || l_party_site_id);

DBMS_OUTPUT.put_line ('l_hz_location_id = ' || l_hz_location_id);

DBMS_OUTPUT.put_line ('l_return_status = ' || l_return_status);

DBMS_OUTPUT.put_line ('l_msg_count = ' || l_msg_count);

DBMS_OUTPUT.put_line ('l_msg_data = ' || l_msg_data);

END IF;

IF (l_return_status != 'S')

THEN

DBMS_OUTPUT.put_line (' Error in Creating Employee Type Supplier..');

IF (l_msg_count = 1)

THEN

DBMS_OUTPUT.put_line ('x_msg_data ' || l_msg_data);

ELSIF (l_msg_count > 1)

THEN

LOOP

p_count := p_count + 1;

l_msg_data :=

fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF (l_msg_data IS NULL)

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line (

' Error Message' || p_count || ' —' || l_msg_data);

END LOOP;

END IF;

ELSE

DBMS_OUTPUT.put_line ('Vendor_site_id:' || l_vendor_site_id);

END IF;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

ROLLBACK;

v_api_error := SQLERRM;

v_error_msg := v_error_msg || ' ' || v_api_error;

DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

Ap_Suppliers Create Ap_Suppliers api - AP_VENDOR_PUB_PKG.Create_Vendor

/* Formatted on 10/12/2020 4:16:48 PM (QP5 v5.277) */

DECLARE

l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

l_return_status VARCHAR2 (10);

l_msg_count NUMBER;

l_msg_data VARCHAR2 (1000);

l_vendor_id NUMBER;

l_party_id NUMBER;

p_count NUMBER;

v_api_error VARCHAR2 (500);

v_error_msg VARCHAR2 (500) := NULL;

l_msg VARCHAR2 (200);

x_party_id NUMBER;

CURSOR l_data

SELECT vendor_id,

vendor_name,

segment1,

--new_person_id,

TRIM (vendor_type_lookup_code) vendor_type_lookup_code,

one_time_flag,

terms_id terms_id_old,

(SELECT DISTINCT term_id

FROM ap_terms_tl

WHERE name = aps.term_name)

terms_id,

term_name,

PAY_DATE_BASIS_LOOKUP_CODE,

PAY_GROUP_LOOKUP_CODE,

PAYMENT_PRIORITY,

INVOICE_CURRENCY_CODE,

PAYMENT_CURRENCY_CODE,

start_date_active,

TERMS_DATE_BASIS,

match_option,

new_vendor_id,

new_party_id,

status,

(select new_person_id from XX.XX_PERSON_CREATE_TMP where person_id=aps.employee_id) employee_id,

num_1099,

attribute1,

attribute2,

attribute7,

attribute8,

attribute9,

attribute10,

attribute11,

attribute12,

attribute13,

attribute14,

attribute15,

ALLOW_AWT_FLAG,

VAT_REGISTRATION_NUM

FROM xx.xx_ap_suppliers aps where status is null;

BEGIN

fnd_global.apps_initialize(

user_id=>1110,

resp_id=>50779,

resp_appl_id=>200);

mo_global.init ('SQLAP');

--- fnd_client_info.set_org_context (101);

FOR n_data IN l_data

LOOP

-- --------------

-- Required

-- --------------

l_vendor_rec.segment1 := n_data.segment1;

l_vendor_rec.vendor_name := n_data.vendor_name;

l_vendor_rec.vendor_type_lookup_code := n_data.vendor_type_lookup_code;

l_vendor_rec.ONE_TIME_FLAG := n_data.ONE_TIME_FLAG;

l_vendor_rec.TERMS_ID := n_data.TERMS_ID;

l_vendor_rec.PAY_DATE_BASIS_LOOKUP_CODE :=n_data.PAY_DATE_BASIS_LOOKUP_CODE;

l_vendor_rec.PAY_GROUP_LOOKUP_CODE := n_data.PAY_GROUP_LOOKUP_CODE;

l_vendor_rec.PAYMENT_PRIORITY := n_data.PAYMENT_PRIORITY;

l_vendor_rec.INVOICE_CURRENCY_CODE := n_data.INVOICE_CURRENCY_CODE;

l_vendor_rec.PAYMENT_CURRENCY_CODE := n_data.PAYMENT_CURRENCY_CODE;

l_vendor_rec.START_DATE_ACTIVE := n_data.START_DATE_ACTIVE;

l_vendor_rec.TERMS_DATE_BASIS := n_data.TERMS_DATE_BASIS;

l_vendor_rec.MATCH_OPTION := n_data.MATCH_OPTION;

l_vendor_rec.EMPLOYEE_ID := n_data.employee_id;

l_vendor_rec.attribute1 := n_data.attribute1;

l_vendor_rec.attribute2 := n_data.attribute2;

l_vendor_rec.attribute7 := n_data.attribute7;

l_vendor_rec.attribute8 := n_data.attribute8;

l_vendor_rec.attribute9 := n_data.attribute9;

l_vendor_rec.attribute10 := n_data.attribute10;

l_vendor_rec.attribute11 := n_data.attribute11;

l_vendor_rec.attribute12 := n_data.attribute12;

l_vendor_rec.attribute13 := n_data.attribute13;

l_vendor_rec.attribute14 := n_data.attribute14;

l_vendor_rec.attribute15 := n_data.attribute15;

l_vendor_rec.ALLOW_AWT_FLAG := n_data.ALLOW_AWT_FLAG;

l_vendor_rec.jgzz_fiscal_code := n_data.num_1099;

l_vendor_rec.VAT_REGISTRATION_NUM :=n_data.VAT_REGISTRATION_NUM;

l_vendor_rec.ext_payee_rec.default_pmt_method := 'EFT'; --SOR

-- -------------

-- Optional

AP_VENDOR_PUB_PKG.Create_Vendor (p_api_version => 1,

p_init_msg_list => FND_API.G_TRUE,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

p_vendor_rec => l_vendor_rec,

x_vendor_id => l_vendor_id,

x_party_id => l_party_id);

IF l_return_status ='S' THEN

UPDATE xx.xx_ap_suppliers

SET new_vendor_id = l_vendor_id,new_party_id=l_party_id, status = 'UPDATE'

WHERE vendor_id = n_data.vendor_id;

COMMIT;

DBMS_OUTPUT.put_line ('l_vendor_id = ' || l_vendor_id);

DBMS_OUTPUT.put_line ('l_return_status = ' || l_return_status);

DBMS_OUTPUT.put_line ('l_msg_count = ' || l_msg_count);

DBMS_OUTPUT.put_line ('l_msg_data = ' || l_msg_data);

END IF;

-- fnd_msg_pub.initialize;

IF (l_return_status != 'S')

THEN

DBMS_OUTPUT.put_line (' Error in Creating Employee Type Supplier..');

IF (l_msg_count = 1)

THEN

DBMS_OUTPUT.put_line ('x_msg_data ' || l_msg_data);

ELSIF (l_msg_count > 1)

THEN

LOOP

p_count := p_count + 1;

l_msg_data :=

fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF (l_msg_data IS NULL)

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line (

' Error Message' || p_count || ' —' || l_msg_data);

END LOOP;

END IF;

ELSE

DBMS_OUTPUT.put_line ('Vendor_id:' || l_vendor_id);

END IF;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

ROLLBACK;

v_api_error := SQLERRM;

v_error_msg := v_error_msg || ' ' || v_api_error;

DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

CE_BANK_ACCT_USES_ALL Create Bank Account Uses Api - ce_bank_pub.create_bank_acct_use

/* Formatted on 10/22/2020 9:06:22 PM (QP5 v5.277) */

DECLARE

p_init_msg_list VARCHAR2 (200);

p_acct_use_rec apps.ce_bank_pub.bankacct_use_rec_type;

x_acct_use_id NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

l_msg VARCHAR2 (200);

lv_bank_account_id NUMBER;

lv_used_org_id NUMBER;

lv_org_type VARCHAR2 (10);

lv_asset_ccid NUMBER;

lv_ar_use_flag VARCHAR2 (10);

lv_ap_use_flag VARCHAR2 (10);

cursor l_data IS

SELECT xba.BANK_ACCT_USE_ID,

xba.bank_account_id,

new_bank_account_id,

xba.org_id,

(SELECT organization_id

FROM hr_organization_units

WHERE name = xba.org_name) new_org_id,

xba.org_party_id,

(SELECT party_id

FROM hz_parties

WHERE party_name = xba.org_party_name AND ROWNUM = 1)

new_org_party_id,

xba.AP_USE_ENABLE_FLAG AP_USE_ENABLE_FLAG,

xba.xtr_use_enable_flag xtr_use_enable_flag,

xba.legal_entity_id,

(SELECT legal_entity_id

FROM xle_entity_profiles

WHERE name = xba.legal_entity_name)

new_legal_entity_id,

xba.AP_DEFAULT_SETTLEMENT_FLAG AP_DEFAULT_SETTLEMENT_FLAG,

xba.XTR_DEFAULT_SETTLEMENT_FLAG XTR_DEFAULT_SETTLEMENT_FLAG,

xba.AUTHORIZED_FLAG AUTHORIZED_FLAG,

xba.DEFAULT_ACCOUNT_FLAG DEFAULT_ACCOUNT_FLAG,

cba.ASSET_CODE_COMBINATION_ID

FROM XX.XX_BANK_ACCT_USES_ALL xba,XX.XX_CE_BANK_ACCOUNTS_ALL xcba, ce_bank_accounts cba

where xcba.bank_account_id = xba.bank_account_id

and xcba.new_bank_account_id=cba.bank_account_id

and xba.status is null;-- and cba.XTR_USE_ALLOWED_FLAG ='Y';

BEGIN

fnd_global.apps_initialize(

user_id=>1110,

resp_id=>50779,

resp_appl_id=>200);

mo_global.init ('SQLAP');

FOR n_data IN l_data LOOP

-- required, valid values: LE, OU, BG

lv_org_type := 'LE'; --or OU

p_init_msg_list := fnd_api.g_true;

p_acct_use_rec.bank_account_id := n_data.new_bank_account_id;

p_acct_use_rec.org_id :=81;

p_acct_use_rec.org_party_id := n_data.new_org_party_id;

p_acct_use_rec.ap_use_enable_flag := n_data.AP_USE_ENABLE_FLAG;

p_acct_use_rec.xtr_use_enable_flag := n_data.xtr_use_enable_flag;

p_acct_use_rec.org_type :=lv_org_type;

p_acct_use_rec.asset_code_combination_id := n_Data.ASSET_CODE_COMBINATION_ID; -- CE_BANK_ACCOUNTS banka hesabindaki asset_code_combination_id al

p_acct_use_rec.authorized_flag :=n_data.AUTHORIZED_FLAG;

p_acct_use_rec.default_account_flag := n_Data.DEFAULT_ACCOUNT_FLAG;

p_acct_use_rec.ap_default_settlement_flag := n_data.AP_DEFAULT_SETTLEMENT_FLAG;

p_acct_use_rec.xtr_default_settlement_flag := n_data.xtr_default_settlement_flag;

ce_bank_pub.create_bank_acct_use (p_init_msg_list => p_init_msg_list,

p_acct_use_rec => p_acct_use_rec,

x_acct_use_id => x_acct_use_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data);

IF x_return_status ='S' then

update XX.XX_BANK_ACCT_USES_ALL set status ='UPDATE' , NEW_BANK_ACCT_USE_ID=x_acct_use_id where BANK_ACCOUNT_ID= n_data.BANK_ACCOUNT_ID;

COMMIT;

end if;

DBMS_OUTPUT.put_line ('X_ACCT_USE_ID = ' || x_acct_use_id);

DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);

DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

IF (x_return_status <> fnd_api.g_ret_sts_success)

THEN

FOR i IN 1 .. fnd_msg_pub.count_msg

LOOP

l_msg :=

fnd_msg_pub.get (p_msg_index => i, p_encoded => fnd_api.g_false);

DBMS_OUTPUT.put_line ('The API call failed with error ' || l_msg);

END LOOP;

ELSE

DBMS_OUTPUT.put_line ('The API call ended with SUCESSS status');

END IF;

END LOOP;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;