SQL> desc emp;
Name Null? Type
------------------------------- -------- ----
EMP_ID NUMBER(3)
EMP_NAME VARCHAR2(15)
EMP_SAL NUMBER(6)
SQL> create table emp_bkp as select * from emp;
Table created.
SQL> drop table emp;
Table dropped.
SQL> alter table emp_bkp add (emp_dept number(30));
Table altered.
SQL> desc emp_bkp;
Name Null? Type
------------------------------- -------- ----
EMP_ID NUMBER(3)
EMP_NAME VARCHAR2(15)
EMP_SAL NUMBER(6)
EMP_DEPT NUMBER(30)
SQL> create table emp as select
2 emp_id,
3 emp_name,
4 emp_dept,
5 emp_sal from emp_bkp;
Table created.
SQL> desc emp;
Name Null? Type
------------------------------- -------- ----
EMP_ID NUMBER(3)
EMP_NAME VARCHAR2(15)
EMP_DEPT NUMBER(30)
EMP_SAL NUMBER(6)
Friday 22 May 2015
API to update po_vendor_sites_all (ap_vendor_sites_pkg.update_row)
/* Formatted on 05-05-2015 19:35:48 (QP5 v5.114.809.3010) */
DECLARE
CURSOR cur_suppsites_r11 (
p_org_id NUMBER
)
IS
SELECT pvss.LIABILITY_ACCOUNT,
pvss.PREPAYMENT_ACCOUNT,
pv.segment1 SUPPLIER_NUMBER,
pv.VENDOR_NAME,
pvsa.*,
pvsa.ROWID
FROM po_vendors pv,
po_vendor_sites_all pvsa,
po_vendor_sites_stg pvss
WHERE 1 = 1
AND pv.vendor_id = pvsa.vendor_id
AND pvsa.VENDOR_SITE_CODE = pvss.VENDOR_SITE_CODE
AND pvsa.org_id = pvss.org_id
AND pv.segment1 = pvss.SUPPLIER_NUMBER
-- AND pv.VENDOR_NAME = pvss.VENDOR_NAME
-- and pvss.VENDOR_SITE_CODE ='LOUISVILLE104'
AND process_flag ='P'
AND pvss.org_id = p_org_id;
v_date DATE;
v_itemkey VARCHAR2 (100);
v_vendor_site_id NUMBER;
v_user_id NUMBER;
v_resp_id NUMBER;
v_org_id number;
V_LIABILITY_ACCOUNT number;
V_PREPAYMENT_ACCOUNT number;
l_cnt number :=0;
BEGIN
DBMS_OUTPUT.put_line ('program start');
/* v_user_id := 7240; --fnd_profile.VALUE ('USER_ID');
v_resp_id := 51683; --fnd_profile.VALUE ('RESP_ID');
v_vendor_site_id := 37425;
fnd_global.apps_initialize (user_id => v_user_id,
resp_id => v_resp_id,
resp_appl_id => 200);*/
v_org_id := 103;
DBMS_OUTPUT.put_line ('v_org_id' || v_org_id);
BEGIN
DBMS_APPLICATION_INFO.set_client_info (v_org_id);
END;
FOR v_site IN cur_suppsites_r11 (v_org_id)
LOOP
l_cnt := l_cnt+1;
DBMS_OUTPUT.put_line ('loop start' ||l_cnt);
DBMS_OUTPUT.put_line ('cur_suppsites_r11.SUPPLIER_NUMBER' ||v_site.SUPPLIER_NUMBER ||'-'||v_site.VENDOR_SITE_CODE);
DBMS_OUTPUT.put_line ('v_site.LIABILITY_ACCOUNT' ||v_site.LIABILITY_ACCOUNT);
IF v_site.LIABILITY_ACCOUNT IS NOT NULL
THEN
BEGIN
SELECT gcc.CODE_COMBINATION_ID --,gcc.*
INTO V_LIABILITY_ACCOUNT
FROM gl_code_combinations gcc,
gl_sets_of_books sob,
hr_operating_units hou
WHERE gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8 = v_site.LIABILITY_ACCOUNT
AND gcc.CHART_OF_ACCOUNTS_ID = sob.CHART_OF_ACCOUNTS_ID
AND sob.SET_OF_BOOKS_ID = hou.SET_OF_BOOKS_ID
AND hou.ORGANIZATION_ID = v_org_id;
EXCEPTION
WHEN OTHERS
THEN
V_LIABILITY_ACCOUNT := NULL;
END;
ELSE
V_LIABILITY_ACCOUNT := NULL;
END IF;
DBMS_OUTPUT.put_line ('V_LIABILITY_ACCOUNT' || V_LIABILITY_ACCOUNT);
DBMS_OUTPUT.put_line ('v_site.PREPAYMENT_ACCOUNT' ||v_site.PREPAYMENT_ACCOUNT);
IF v_site.PREPAYMENT_ACCOUNT IS NOT NULL
THEN
BEGIN
SELECT gcc.CODE_COMBINATION_ID --,gcc.*
INTO V_PREPAYMENT_ACCOUNT
FROM gl_code_combinations gcc,
gl_sets_of_books sob,
hr_operating_units hou
WHERE gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8 = v_site.PREPAYMENT_ACCOUNT
AND gcc.CHART_OF_ACCOUNTS_ID = sob.CHART_OF_ACCOUNTS_ID
AND sob.SET_OF_BOOKS_ID = hou.SET_OF_BOOKS_ID
AND hou.ORGANIZATION_ID = v_org_id;
EXCEPTION
WHEN OTHERS
THEN
V_PREPAYMENT_ACCOUNT := NULL;
END;
ELSE
V_PREPAYMENT_ACCOUNT := NULL;
END IF;
DBMS_OUTPUT.put_line ('V_PREPAYMENT_ACCOUNT' || V_PREPAYMENT_ACCOUNT);
DBMS_OUTPUT.put_line ('API Start');
BEGIN
ap_vendor_sites_pkg.update_row (
x_rowid => v_site.ROWID,
x_vendor_site_id => v_site.vendor_site_id,
x_last_update_date => v_site.last_update_date,
x_last_updated_by => v_site.last_updated_by,
x_vendor_id => v_site.vendor_id,
x_vendor_site_code => v_site.vendor_site_code,
x_last_update_login => v_site.last_update_login,
x_creation_date => v_site.creation_date,
x_created_by => v_site.created_by,
x_purchasing_site_flag => v_site.purchasing_site_flag,
x_rfq_only_site_flag => v_site.rfq_only_site_flag,
x_pay_site_flag => v_site.pay_site_flag,
x_attention_ar_flag => v_site.attention_ar_flag,
x_address_line1 => v_site.address_line1,
x_address_line2 => v_site.address_line2,
x_address_line3 => v_site.address_line3,
x_city => v_site.city,
x_state => v_site.state,
x_zip => v_site.zip,
x_province => v_site.province,
x_country => v_site.country,
x_area_code => v_site.area_code,
x_phone => v_site.phone,
x_customer_num => v_site.customer_num,
x_ship_to_location_id => v_site.ship_to_location_id,
x_bill_to_location_id => v_site.bill_to_location_id,
x_ship_via_lookup_code => v_site.ship_via_lookup_code,
x_freight_terms_lookup_code => v_site.freight_terms_lookup_code,
x_fob_lookup_code => v_site.fob_lookup_code,
x_inactive_date => v_date,
x_fax => v_site.fax,
x_fax_area_code => v_site.fax_area_code,
x_telex => v_site.telex,
x_payment_method_lookup_code => v_site.payment_method_lookup_code,
x_bank_account_name => v_site.bank_account_name,
x_bank_account_num => v_site.bank_account_num,
x_bank_num => v_site.bank_num,
x_bank_account_type => v_site.bank_account_type,
x_terms_date_basis => v_site.terms_date_basis,
x_current_catalog_num => v_site.current_catalog_num,
x_vat_code => v_site.vat_code,
x_distribution_set_id => v_site.distribution_set_id,
x_accts_pay_ccid => V_LIABILITY_ACCOUNT,
--v_site.accts_pay_code_combination_id,
x_future_dated_payment_ccid => v_site.future_dated_payment_ccid,
x_prepay_code_combination_id => V_PREPAYMENT_ACCOUNT,
--v_site.prepay_code_combination_id,
x_pay_group_lookup_code => v_site.pay_group_lookup_code,
x_payment_priority => v_site.payment_priority,
x_terms_id => v_site.terms_id,
x_invoice_amount_limit => v_site.invoice_amount_limit,
x_pay_date_basis_lookup_code => v_site.pay_date_basis_lookup_code,
x_always_take_disc_flag => v_site.always_take_disc_flag,
x_invoice_currency_code => v_site.invoice_currency_code,
x_payment_currency_code => v_site.payment_currency_code,
x_hold_all_payments_flag => v_site.hold_all_payments_flag,
x_hold_future_payments_flag => v_site.hold_future_payments_flag,
x_hold_reason => v_site.hold_reason,
x_hold_unmatched_invoices_flag => v_site.hold_unmatched_invoices_flag,
x_match_option => v_site.match_option,
x_create_debit_memo_flag => v_site.create_debit_memo_flag,
x_exclusive_payment_flag => v_site.exclusive_payment_flag,
x_tax_reporting_site_flag => v_site.tax_reporting_site_flag,
x_attribute_category => v_site.attribute_category,
x_attribute1 => v_site.attribute1,
x_attribute2 => v_site.attribute2,
x_attribute3 => v_site.attribute3,
x_attribute4 => v_site.attribute4,
x_attribute5 => v_site.attribute5,
x_attribute6 => v_site.attribute6,
x_attribute7 => v_site.attribute7,
x_attribute8 => v_site.attribute8,
x_attribute9 => v_site.attribute9,
x_attribute10 => v_site.attribute10,
x_attribute11 => v_site.attribute11,
x_attribute12 => v_site.attribute12,
x_attribute13 => v_site.attribute13,
x_attribute14 => v_site.attribute14,
x_attribute15 => v_site.attribute15,
x_validation_number => v_site.validation_number,
x_exclude_freight_from_disc => v_site.exclude_freight_from_discount,
x_vat_registration_num => v_site.vat_registration_num,
x_offset_tax_flag => v_site.offset_tax_flag,
x_check_digits => v_site.check_digits,
x_bank_number => v_site.bank_number,
x_address_line4 => v_site.address_line4,
x_county => v_site.county,
x_address_style => v_site.address_style,
x_language => v_site.LANGUAGE,
x_allow_awt_flag => v_site.allow_awt_flag,
x_awt_group_id => v_site.awt_group_id,
x_pay_on_code => v_site.pay_on_code,
x_default_pay_site_id => v_site.default_pay_site_id,
x_pay_on_receipt_summary_code => v_site.pay_on_receipt_summary_code,
x_bank_branch_type => v_site.bank_branch_type,
x_edi_id_number => v_site.edi_id_number,
x_edi_payment_method => v_site.edi_payment_method,
x_edi_payment_format => v_site.edi_payment_format,
x_edi_remittance_method => v_site.edi_remittance_method,
x_edi_remittance_instruction => v_site.edi_remittance_instruction,
x_edi_transaction_handling => v_site.edi_transaction_handling,
x_auto_tax_calc_flag => v_site.auto_tax_calc_flag,
x_auto_tax_calc_override => v_site.auto_tax_calc_override,
x_amount_includes_tax_flag => v_site.amount_includes_tax_flag,
x_ap_tax_rounding_rule => v_site.ap_tax_rounding_rule,
x_vendor_site_code_alt => v_site.vendor_site_code_alt,
x_address_lines_alt => v_site.address_lines_alt,
x_global_attribute_category => v_site.global_attribute_category,
x_global_attribute1 => v_site.global_attribute1,
x_global_attribute2 => v_site.global_attribute2,
x_global_attribute3 => v_site.global_attribute3,
x_global_attribute4 => v_site.global_attribute4,
x_global_attribute5 => v_site.global_attribute5,
x_global_attribute6 => v_site.global_attribute6,
x_global_attribute7 => v_site.global_attribute7,
x_global_attribute8 => v_site.global_attribute8,
x_global_attribute9 => v_site.global_attribute9,
x_global_attribute10 => v_site.global_attribute10,
x_global_attribute11 => v_site.global_attribute11,
x_global_attribute12 => v_site.global_attribute12,
x_global_attribute13 => v_site.global_attribute13,
x_global_attribute14 => v_site.global_attribute14,
x_global_attribute15 => v_site.global_attribute15,
x_global_attribute16 => v_site.global_attribute16,
x_global_attribute17 => v_site.global_attribute17,
x_global_attribute18 => v_site.global_attribute18,
x_global_attribute19 => v_site.global_attribute19,
x_global_attribute20 => v_site.global_attribute20,
x_bank_charge_bearer => v_site.bank_charge_bearer,
x_ece_tp_location_code => v_site.ece_tp_location_code,
x_pcard_site_flag => v_site.pcard_site_flag,
x_country_of_origin_code => v_site.country_of_origin_code,
x_calling_sequence => NULL,
x_shipping_location_id => NULL,
x_supplier_notif_method => v_site.supplier_notif_method,
x_email_address => v_site.email_address,
x_remittance_email => v_site.remittance_email,
x_primary_pay_site_flag => v_site.primary_pay_site_flag,
x_shipping_control => v_site.shipping_control,
x_gapless_inv_num_flag => v_site.gapless_inv_num_flag,
x_selling_company_identifier => v_site.selling_company_identifier,
x_duns_number => v_site.duns_number,
x_tolerance_id => v_site.tolerance_id
);
DBMS_OUTPUT.put_line ('API End');
UPDATE po_vendor_sites_stg
SET process_flag = 'S'
WHERE SUPPLIER_NUMBER = v_site.SUPPLIER_NUMBER
-- AND VENDOR_NAME = v_site.VENDOR_NAME
AND VENDOR_SITE_CODE = v_site.VENDOR_SITE_CODE
AND org_id = v_site.org_id;
EXCEPTION
WHEN OTHERS
THEN
UPDATE po_vendor_sites_stg
SET process_flag = 'E'
-- ERROR_MESSAGE =SQLERRM
WHERE SUPPLIER_NUMBER = v_site.SUPPLIER_NUMBER
-- AND VENDOR_NAME = v_site.VENDOR_NAME
AND VENDOR_SITE_CODE = v_site.VENDOR_SITE_CODE
AND org_id = v_site.org_id;
END;
COMMIT;
DBMS_OUTPUT.put_line ('After commit');
END LOOP;
DBMS_OUTPUT.put_line ('Loop End');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('other exception' || SQLERRM);
END;
/
DECLARE
CURSOR cur_suppsites_r11 (
p_org_id NUMBER
)
IS
SELECT pvss.LIABILITY_ACCOUNT,
pvss.PREPAYMENT_ACCOUNT,
pv.segment1 SUPPLIER_NUMBER,
pv.VENDOR_NAME,
pvsa.*,
pvsa.ROWID
FROM po_vendors pv,
po_vendor_sites_all pvsa,
po_vendor_sites_stg pvss
WHERE 1 = 1
AND pv.vendor_id = pvsa.vendor_id
AND pvsa.VENDOR_SITE_CODE = pvss.VENDOR_SITE_CODE
AND pvsa.org_id = pvss.org_id
AND pv.segment1 = pvss.SUPPLIER_NUMBER
-- AND pv.VENDOR_NAME = pvss.VENDOR_NAME
-- and pvss.VENDOR_SITE_CODE ='LOUISVILLE104'
AND process_flag ='P'
AND pvss.org_id = p_org_id;
v_date DATE;
v_itemkey VARCHAR2 (100);
v_vendor_site_id NUMBER;
v_user_id NUMBER;
v_resp_id NUMBER;
v_org_id number;
V_LIABILITY_ACCOUNT number;
V_PREPAYMENT_ACCOUNT number;
l_cnt number :=0;
BEGIN
DBMS_OUTPUT.put_line ('program start');
/* v_user_id := 7240; --fnd_profile.VALUE ('USER_ID');
v_resp_id := 51683; --fnd_profile.VALUE ('RESP_ID');
v_vendor_site_id := 37425;
fnd_global.apps_initialize (user_id => v_user_id,
resp_id => v_resp_id,
resp_appl_id => 200);*/
v_org_id := 103;
DBMS_OUTPUT.put_line ('v_org_id' || v_org_id);
BEGIN
DBMS_APPLICATION_INFO.set_client_info (v_org_id);
END;
FOR v_site IN cur_suppsites_r11 (v_org_id)
LOOP
l_cnt := l_cnt+1;
DBMS_OUTPUT.put_line ('loop start' ||l_cnt);
DBMS_OUTPUT.put_line ('cur_suppsites_r11.SUPPLIER_NUMBER' ||v_site.SUPPLIER_NUMBER ||'-'||v_site.VENDOR_SITE_CODE);
DBMS_OUTPUT.put_line ('v_site.LIABILITY_ACCOUNT' ||v_site.LIABILITY_ACCOUNT);
IF v_site.LIABILITY_ACCOUNT IS NOT NULL
THEN
BEGIN
SELECT gcc.CODE_COMBINATION_ID --,gcc.*
INTO V_LIABILITY_ACCOUNT
FROM gl_code_combinations gcc,
gl_sets_of_books sob,
hr_operating_units hou
WHERE gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8 = v_site.LIABILITY_ACCOUNT
AND gcc.CHART_OF_ACCOUNTS_ID = sob.CHART_OF_ACCOUNTS_ID
AND sob.SET_OF_BOOKS_ID = hou.SET_OF_BOOKS_ID
AND hou.ORGANIZATION_ID = v_org_id;
EXCEPTION
WHEN OTHERS
THEN
V_LIABILITY_ACCOUNT := NULL;
END;
ELSE
V_LIABILITY_ACCOUNT := NULL;
END IF;
DBMS_OUTPUT.put_line ('V_LIABILITY_ACCOUNT' || V_LIABILITY_ACCOUNT);
DBMS_OUTPUT.put_line ('v_site.PREPAYMENT_ACCOUNT' ||v_site.PREPAYMENT_ACCOUNT);
IF v_site.PREPAYMENT_ACCOUNT IS NOT NULL
THEN
BEGIN
SELECT gcc.CODE_COMBINATION_ID --,gcc.*
INTO V_PREPAYMENT_ACCOUNT
FROM gl_code_combinations gcc,
gl_sets_of_books sob,
hr_operating_units hou
WHERE gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8 = v_site.PREPAYMENT_ACCOUNT
AND gcc.CHART_OF_ACCOUNTS_ID = sob.CHART_OF_ACCOUNTS_ID
AND sob.SET_OF_BOOKS_ID = hou.SET_OF_BOOKS_ID
AND hou.ORGANIZATION_ID = v_org_id;
EXCEPTION
WHEN OTHERS
THEN
V_PREPAYMENT_ACCOUNT := NULL;
END;
ELSE
V_PREPAYMENT_ACCOUNT := NULL;
END IF;
DBMS_OUTPUT.put_line ('V_PREPAYMENT_ACCOUNT' || V_PREPAYMENT_ACCOUNT);
DBMS_OUTPUT.put_line ('API Start');
BEGIN
ap_vendor_sites_pkg.update_row (
x_rowid => v_site.ROWID,
x_vendor_site_id => v_site.vendor_site_id,
x_last_update_date => v_site.last_update_date,
x_last_updated_by => v_site.last_updated_by,
x_vendor_id => v_site.vendor_id,
x_vendor_site_code => v_site.vendor_site_code,
x_last_update_login => v_site.last_update_login,
x_creation_date => v_site.creation_date,
x_created_by => v_site.created_by,
x_purchasing_site_flag => v_site.purchasing_site_flag,
x_rfq_only_site_flag => v_site.rfq_only_site_flag,
x_pay_site_flag => v_site.pay_site_flag,
x_attention_ar_flag => v_site.attention_ar_flag,
x_address_line1 => v_site.address_line1,
x_address_line2 => v_site.address_line2,
x_address_line3 => v_site.address_line3,
x_city => v_site.city,
x_state => v_site.state,
x_zip => v_site.zip,
x_province => v_site.province,
x_country => v_site.country,
x_area_code => v_site.area_code,
x_phone => v_site.phone,
x_customer_num => v_site.customer_num,
x_ship_to_location_id => v_site.ship_to_location_id,
x_bill_to_location_id => v_site.bill_to_location_id,
x_ship_via_lookup_code => v_site.ship_via_lookup_code,
x_freight_terms_lookup_code => v_site.freight_terms_lookup_code,
x_fob_lookup_code => v_site.fob_lookup_code,
x_inactive_date => v_date,
x_fax => v_site.fax,
x_fax_area_code => v_site.fax_area_code,
x_telex => v_site.telex,
x_payment_method_lookup_code => v_site.payment_method_lookup_code,
x_bank_account_name => v_site.bank_account_name,
x_bank_account_num => v_site.bank_account_num,
x_bank_num => v_site.bank_num,
x_bank_account_type => v_site.bank_account_type,
x_terms_date_basis => v_site.terms_date_basis,
x_current_catalog_num => v_site.current_catalog_num,
x_vat_code => v_site.vat_code,
x_distribution_set_id => v_site.distribution_set_id,
x_accts_pay_ccid => V_LIABILITY_ACCOUNT,
--v_site.accts_pay_code_combination_id,
x_future_dated_payment_ccid => v_site.future_dated_payment_ccid,
x_prepay_code_combination_id => V_PREPAYMENT_ACCOUNT,
--v_site.prepay_code_combination_id,
x_pay_group_lookup_code => v_site.pay_group_lookup_code,
x_payment_priority => v_site.payment_priority,
x_terms_id => v_site.terms_id,
x_invoice_amount_limit => v_site.invoice_amount_limit,
x_pay_date_basis_lookup_code => v_site.pay_date_basis_lookup_code,
x_always_take_disc_flag => v_site.always_take_disc_flag,
x_invoice_currency_code => v_site.invoice_currency_code,
x_payment_currency_code => v_site.payment_currency_code,
x_hold_all_payments_flag => v_site.hold_all_payments_flag,
x_hold_future_payments_flag => v_site.hold_future_payments_flag,
x_hold_reason => v_site.hold_reason,
x_hold_unmatched_invoices_flag => v_site.hold_unmatched_invoices_flag,
x_match_option => v_site.match_option,
x_create_debit_memo_flag => v_site.create_debit_memo_flag,
x_exclusive_payment_flag => v_site.exclusive_payment_flag,
x_tax_reporting_site_flag => v_site.tax_reporting_site_flag,
x_attribute_category => v_site.attribute_category,
x_attribute1 => v_site.attribute1,
x_attribute2 => v_site.attribute2,
x_attribute3 => v_site.attribute3,
x_attribute4 => v_site.attribute4,
x_attribute5 => v_site.attribute5,
x_attribute6 => v_site.attribute6,
x_attribute7 => v_site.attribute7,
x_attribute8 => v_site.attribute8,
x_attribute9 => v_site.attribute9,
x_attribute10 => v_site.attribute10,
x_attribute11 => v_site.attribute11,
x_attribute12 => v_site.attribute12,
x_attribute13 => v_site.attribute13,
x_attribute14 => v_site.attribute14,
x_attribute15 => v_site.attribute15,
x_validation_number => v_site.validation_number,
x_exclude_freight_from_disc => v_site.exclude_freight_from_discount,
x_vat_registration_num => v_site.vat_registration_num,
x_offset_tax_flag => v_site.offset_tax_flag,
x_check_digits => v_site.check_digits,
x_bank_number => v_site.bank_number,
x_address_line4 => v_site.address_line4,
x_county => v_site.county,
x_address_style => v_site.address_style,
x_language => v_site.LANGUAGE,
x_allow_awt_flag => v_site.allow_awt_flag,
x_awt_group_id => v_site.awt_group_id,
x_pay_on_code => v_site.pay_on_code,
x_default_pay_site_id => v_site.default_pay_site_id,
x_pay_on_receipt_summary_code => v_site.pay_on_receipt_summary_code,
x_bank_branch_type => v_site.bank_branch_type,
x_edi_id_number => v_site.edi_id_number,
x_edi_payment_method => v_site.edi_payment_method,
x_edi_payment_format => v_site.edi_payment_format,
x_edi_remittance_method => v_site.edi_remittance_method,
x_edi_remittance_instruction => v_site.edi_remittance_instruction,
x_edi_transaction_handling => v_site.edi_transaction_handling,
x_auto_tax_calc_flag => v_site.auto_tax_calc_flag,
x_auto_tax_calc_override => v_site.auto_tax_calc_override,
x_amount_includes_tax_flag => v_site.amount_includes_tax_flag,
x_ap_tax_rounding_rule => v_site.ap_tax_rounding_rule,
x_vendor_site_code_alt => v_site.vendor_site_code_alt,
x_address_lines_alt => v_site.address_lines_alt,
x_global_attribute_category => v_site.global_attribute_category,
x_global_attribute1 => v_site.global_attribute1,
x_global_attribute2 => v_site.global_attribute2,
x_global_attribute3 => v_site.global_attribute3,
x_global_attribute4 => v_site.global_attribute4,
x_global_attribute5 => v_site.global_attribute5,
x_global_attribute6 => v_site.global_attribute6,
x_global_attribute7 => v_site.global_attribute7,
x_global_attribute8 => v_site.global_attribute8,
x_global_attribute9 => v_site.global_attribute9,
x_global_attribute10 => v_site.global_attribute10,
x_global_attribute11 => v_site.global_attribute11,
x_global_attribute12 => v_site.global_attribute12,
x_global_attribute13 => v_site.global_attribute13,
x_global_attribute14 => v_site.global_attribute14,
x_global_attribute15 => v_site.global_attribute15,
x_global_attribute16 => v_site.global_attribute16,
x_global_attribute17 => v_site.global_attribute17,
x_global_attribute18 => v_site.global_attribute18,
x_global_attribute19 => v_site.global_attribute19,
x_global_attribute20 => v_site.global_attribute20,
x_bank_charge_bearer => v_site.bank_charge_bearer,
x_ece_tp_location_code => v_site.ece_tp_location_code,
x_pcard_site_flag => v_site.pcard_site_flag,
x_country_of_origin_code => v_site.country_of_origin_code,
x_calling_sequence => NULL,
x_shipping_location_id => NULL,
x_supplier_notif_method => v_site.supplier_notif_method,
x_email_address => v_site.email_address,
x_remittance_email => v_site.remittance_email,
x_primary_pay_site_flag => v_site.primary_pay_site_flag,
x_shipping_control => v_site.shipping_control,
x_gapless_inv_num_flag => v_site.gapless_inv_num_flag,
x_selling_company_identifier => v_site.selling_company_identifier,
x_duns_number => v_site.duns_number,
x_tolerance_id => v_site.tolerance_id
);
DBMS_OUTPUT.put_line ('API End');
UPDATE po_vendor_sites_stg
SET process_flag = 'S'
WHERE SUPPLIER_NUMBER = v_site.SUPPLIER_NUMBER
-- AND VENDOR_NAME = v_site.VENDOR_NAME
AND VENDOR_SITE_CODE = v_site.VENDOR_SITE_CODE
AND org_id = v_site.org_id;
EXCEPTION
WHEN OTHERS
THEN
UPDATE po_vendor_sites_stg
SET process_flag = 'E'
-- ERROR_MESSAGE =SQLERRM
WHERE SUPPLIER_NUMBER = v_site.SUPPLIER_NUMBER
-- AND VENDOR_NAME = v_site.VENDOR_NAME
AND VENDOR_SITE_CODE = v_site.VENDOR_SITE_CODE
AND org_id = v_site.org_id;
END;
COMMIT;
DBMS_OUTPUT.put_line ('After commit');
END LOOP;
DBMS_OUTPUT.put_line ('Loop End');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('other exception' || SQLERRM);
END;
/
Query to get the Liability & Prepayment Account details for Vendor
select
pvs.org_id ,
hou.name,
pv.segment1 SUPPLIER_NUMBER,
pv.VENDOR_NAME,
pvs.VENDOR_SITE_CODE,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3
CITY,
STATE,
ZIP,
PROVINCE,
COUNTRY ,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
"LIABILITY_ACCOUNT",
gcc_p.segment1
|| '.'
|| gcc_p.segment2
|| '.'
|| gcc_p.segment3
|| '.'
|| gcc_p.segment4
|| '.'
|| gcc_p.segment5
|| '.'
|| gcc_p.segment6
|| '.'
|| gcc_p.segment7
|| '.'
|| gcc_p.segment8
"PREPAYMENT_ACCOUNT",
pv.START_DATE_ACTIVE,
pv.end_DATE_ACTIVE
--pvs.ACCTS_PAY_CODE_COMBINATION_ID,
--pvs.PREPAY_CODE_COMBINATION_ID
--INACTIVE_DATE
from
po_vendors pv,
po_vendor_sites_all pvs,
gl_code_combinations gcc,
gl_code_combinations gcc_p,
hr_operating_units hou
where pv.vendor_id = pvs.vendor_id
and pvs.org_id = hou.ORGANIZATION_ID
AND pvs.ACCTS_PAY_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID(+)
AND pvs.PREPAY_CODE_COMBINATION_ID = gcc_p.CODE_COMBINATION_ID(+)
and pvs.org_id =111
and pv.VENDOR_NAME ='SSS SERVICES LTD'
pvs.org_id ,
hou.name,
pv.segment1 SUPPLIER_NUMBER,
pv.VENDOR_NAME,
pvs.VENDOR_SITE_CODE,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3
CITY,
STATE,
ZIP,
PROVINCE,
COUNTRY ,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
"LIABILITY_ACCOUNT",
gcc_p.segment1
|| '.'
|| gcc_p.segment2
|| '.'
|| gcc_p.segment3
|| '.'
|| gcc_p.segment4
|| '.'
|| gcc_p.segment5
|| '.'
|| gcc_p.segment6
|| '.'
|| gcc_p.segment7
|| '.'
|| gcc_p.segment8
"PREPAYMENT_ACCOUNT",
pv.START_DATE_ACTIVE,
pv.end_DATE_ACTIVE
--pvs.ACCTS_PAY_CODE_COMBINATION_ID,
--pvs.PREPAY_CODE_COMBINATION_ID
--INACTIVE_DATE
from
po_vendors pv,
po_vendor_sites_all pvs,
gl_code_combinations gcc,
gl_code_combinations gcc_p,
hr_operating_units hou
where pv.vendor_id = pvs.vendor_id
and pvs.org_id = hou.ORGANIZATION_ID
AND pvs.ACCTS_PAY_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID(+)
AND pvs.PREPAY_CODE_COMBINATION_ID = gcc_p.CODE_COMBINATION_ID(+)
and pvs.org_id =111
and pv.VENDOR_NAME ='SSS SERVICES LTD'
Subscribe to:
Posts (Atom)