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'
Thursday, 30 April 2015
Link between PA tables and AP Invoice
SELECT peia.expenditure_item_id trans_id
, ppa.segment1 project
, pt.task_number task
, pea.expenditure_group batch
, pet.expenditure_category expend_type
, pet.expenditure_type exp_type
, peia.transaction_source tx_src
, peia.expenditure_item_date exp_date
, peia.creation_date
, peia.raw_cost
, peia.quantity
, peia.request_id
, aia.invoice_num
, pv.vendor_name supplier
, peia.request_id
, pcdla.system_reference1 vendor_id
, pcdla.system_reference2 inv_id
, pcdla.system_reference3 inv_distr_line_num
FROM pa.pa_expenditure_items_all peia
, pa.pa_cost_distribution_lines_all pcdla
, pa.pa_expenditures_all pea
, pa.pa_projects_all ppa
, pa.pa_tasks pt
, pa.pa_expenditure_types pet
, pa.pa_expenditure_comments pec
, applsys.fnd_user fu
, ap.ap_invoices_all aia
, po.po_vendors pv
, ap.ap_invoice_distributions_all aida
WHERE peia.expenditure_item_id = pcdla.expenditure_item_id
AND pcdla.system_reference2 = aia.invoice_id(+)
AND aia.invoice_id = aida.invoice_id
AND pcdla.system_reference3 = aida.distribution_line_number
AND pcdla.system_reference1 = pv.vendor_id
AND peia.expenditure_id = pea.expenditure_id(+)
AND peia.project_id = ppa.project_id(+)
AND peia.task_id = pt.task_id(+)
AND peia.expenditure_type = pet.expenditure_type(+)
AND peia.expenditure_item_id = pec.expenditure_item_id(+)
AND ppa.project_id = pt.project_id
AND peia.created_by = fu.user_id(+)
-- and aia.invoice_id = 123456
AND ppa.segment1 = '100003234'
ORDER BY peia.transaction_source
, peia.creation_date DESC
, pt.task_number DESC;
, ppa.segment1 project
, pt.task_number task
, pea.expenditure_group batch
, pet.expenditure_category expend_type
, pet.expenditure_type exp_type
, peia.transaction_source tx_src
, peia.expenditure_item_date exp_date
, peia.creation_date
, peia.raw_cost
, peia.quantity
, peia.request_id
, aia.invoice_num
, pv.vendor_name supplier
, peia.request_id
, pcdla.system_reference1 vendor_id
, pcdla.system_reference2 inv_id
, pcdla.system_reference3 inv_distr_line_num
FROM pa.pa_expenditure_items_all peia
, pa.pa_cost_distribution_lines_all pcdla
, pa.pa_expenditures_all pea
, pa.pa_projects_all ppa
, pa.pa_tasks pt
, pa.pa_expenditure_types pet
, pa.pa_expenditure_comments pec
, applsys.fnd_user fu
, ap.ap_invoices_all aia
, po.po_vendors pv
, ap.ap_invoice_distributions_all aida
WHERE peia.expenditure_item_id = pcdla.expenditure_item_id
AND pcdla.system_reference2 = aia.invoice_id(+)
AND aia.invoice_id = aida.invoice_id
AND pcdla.system_reference3 = aida.distribution_line_number
AND pcdla.system_reference1 = pv.vendor_id
AND peia.expenditure_id = pea.expenditure_id(+)
AND peia.project_id = ppa.project_id(+)
AND peia.task_id = pt.task_id(+)
AND peia.expenditure_type = pet.expenditure_type(+)
AND peia.expenditure_item_id = pec.expenditure_item_id(+)
AND ppa.project_id = pt.project_id
AND peia.created_by = fu.user_id(+)
-- and aia.invoice_id = 123456
AND ppa.segment1 = '100003234'
ORDER BY peia.transaction_source
, peia.creation_date DESC
, pt.task_number DESC;
Wednesday, 29 April 2015
API to update ap_invoices_all table (AP_INVOICES_PKG.Update_Row)
DECLARE
current_calling_sequence varchar2 (2000);
BEGIN
current_calling_sequence := 'AP_INVOICES_PKG.UPDATE_ROW -->';
DBMS_OUTPUT.put_line ('start1');
FOR rec IN (SELECT l.ROWID, l.*
FROM ap_invoices_all l
WHERE invoice_num = '700033994')
LOOP
DBMS_OUTPUT.put_line ('loop');
AP_INVOICES_PKG.Update_Row (
X_Rowid => rec.ROWID,
X_Invoice_Id => rec.invoice_id,
X_Last_Update_Date => SYSDATE, --rec.last_update_date,
X_Last_Updated_By => 4060, --rec.last_updated_by,
X_Vendor_Id => rec.vendor_id,
X_Invoice_Num => rec.invoice_num,
X_Invoice_Amount => rec.invoice_amount,
X_Vendor_Site_Id => rec.vendor_site_id,
X_Amount_Paid => rec.amount_paid,
X_Discount_Amount_Taken => rec.discount_amount_taken,
X_Invoice_Date => rec.Invoice_Date,
X_Source => rec.source,
X_Invoice_Type_Lookup_Code => rec.invoice_type_lookup_code,
X_Description => rec.description,
X_Batch_Id => rec.batch_id,
X_Amt_Applicable_To_Discount => rec.amount_applicable_to_discount,
X_Tax_Amount => rec.Tax_Amount,
X_Terms_Id => rec.terms_id,
X_Terms_Date => '09-JAN-2015', --SYSDATE,
X_Payment_Method_Lookup_Code => rec.Payment_Method_Lookup_Code,
X_Goods_Received_Date => rec.goods_received_date,
X_Invoice_Received_Date => rec.invoice_received_date,
X_Voucher_Num => rec.voucher_num,
X_Approved_Amount => rec.approved_amount,
X_Approval_Status => rec.approval_status,
X_Approval_Description => rec.approval_description,
X_Pay_Group_Lookup_Code => rec.pay_group_lookup_code,
X_Set_Of_Books_Id => rec.set_of_books_id,
X_Accts_Pay_CCId => rec.accts_pay_code_combination_id,
X_Recurring_Payment_Id => rec.recurring_payment_id,
X_Invoice_Currency_Code => rec.invoice_currency_code,
X_Payment_Currency_Code => rec.payment_currency_code,
X_Exchange_Rate => rec.exchange_rate,
X_Invoice_Distribution_Total => rec.Invoice_Distribution_Total,
X_Payment_Amount_Total => rec.payment_amount_total,
X_Payment_Status_Flag => rec.payment_status_flag,
X_Posting_Status => rec.posting_status,
X_Authorized_By => rec.authorized_by,
X_Attribute_Category => rec.attribute_category,
X_Attribute1 => rec.attribute1,
X_Attribute2 => rec.attribute2,
X_Attribute3 => rec.attribute3,
X_Attribute4 => rec.attribute4,
X_Attribute5 => rec.attribute5,
X_Vendor_Prepay_Amount => rec.vendor_prepay_amount,
X_Prepay_Flag => rec.Prepay_Flag,
X_Base_Amount => rec.base_amount,
X_Exchange_Rate_Type => rec.exchange_rate_type,
X_Exchange_Date => rec.exchange_date,
X_Payment_Cross_Rate => rec.payment_cross_rate,
X_Payment_Cross_Rate_Type => rec.payment_cross_rate_type,
X_Payment_Cross_Rate_Date => rec.payment_cross_rate_date,
X_Pay_Curr_Invoice_Amount => rec.pay_curr_invoice_amount,
X_Vat_Code => rec.Vat_Code,
X_Last_Update_Login => rec.last_update_login,
X_Original_Prepayment_Amount => rec.original_prepayment_amount,
X_Earliest_Settlement_Date => rec.earliest_settlement_date,
X_Attribute11 => TO_CHAR (SYSDATE,
'DD-MON-RRRR HH24:MI:SS'), --rec.attribute11,
X_Attribute12 => rec.attribute12,
X_Attribute13 => rec.attribute13,
X_Attribute14 => rec.attribute14,
X_Attribute6 => rec.attribute6,
X_Attribute7 => rec.attribute7,
X_Attribute8 => rec.attribute8,
X_Attribute9 => rec.attribute9,
X_Attribute10 => rec.attribute10,
X_Attribute15 => rec.attribute15,
X_Cancelled_Date => rec.cancelled_date,
X_Cancelled_By => rec.cancelled_by,
X_Cancelled_Amount => rec.cancelled_amount,
X_Temp_Cancelled_Amount => rec.temp_cancelled_amount,
X_Exclusive_Payment_Flag => rec.exclusive_payment_flag,
X_Po_Header_Id => rec.po_header_id,
X_Ussgl_Transaction_Code => rec.Ussgl_Transaction_Code,
X_Ussgl_Trx_Code_Context => rec.Ussgl_Trx_Code_Context,
X_Doc_Sequence_Id => rec.doc_sequence_id,
X_Doc_Sequence_Value => rec.doc_sequence_value,
X_Doc_Category_Code => rec.doc_category_code,
X_Freight_Amount => rec.Freight_Amount,
X_Expenditure_Item_Date => rec.expenditure_item_date,
X_Expenditure_Organization_Id => rec.expenditure_organization_id,
X_Expenditure_Type => rec.expenditure_type,
X_Pa_Default_Dist_Ccid => rec.pa_default_dist_ccid,
X_Pa_Quantity => rec.pa_quantity,
X_Project_Id => rec.project_id,
X_Project_Accounting_Context => rec.Project_Accounting_Context,
X_Task_Id => rec.task_id,
X_Awt_Flag => rec.awt_flag,
X_Awt_Group_Id => rec.awt_group_id,
-- X_Pay_Awt_Group_Id => NULL,
X_Reference_1 => rec.reference_1,
X_Reference_2 => rec.reference_2,
X_Auto_Tax_Calc_Flag => rec.Auto_Tax_Calc_Flag,
X_Org_Id => rec.org_id,
X_global_attribute_category => rec.global_attribute_category,
X_global_attribute1 => rec.global_attribute1,
X_global_attribute2 => rec.global_attribute2,
X_global_attribute3 => rec.global_attribute3,
X_global_attribute4 => rec.global_attribute4,
X_global_attribute5 => rec.global_attribute5,
X_global_attribute6 => rec.global_attribute6,
X_global_attribute7 => rec.global_attribute7,
X_global_attribute8 => rec.global_attribute8,
X_global_attribute9 => rec.global_attribute9,
X_global_attribute10 => rec.global_attribute10,
X_global_attribute11 => rec.global_attribute11,
X_global_attribute12 => rec.global_attribute12,
X_global_attribute13 => rec.global_attribute13,
X_global_attribute14 => rec.global_attribute14,
X_global_attribute15 => rec.global_attribute15,
X_global_attribute16 => rec.global_attribute16,
X_global_attribute17 => rec.global_attribute17,
X_global_attribute18 => rec.global_attribute18,
X_global_attribute19 => rec.global_attribute19,
X_global_attribute20 => rec.global_attribute20,
X_calling_sequence => current_calling_sequence,
X_gl_date => rec.gl_date,
X_award_Id => rec.award_id,
X_approval_iteration => rec.approval_iteration,
X_approval_ready_flag => rec.approval_ready_flag,
X_wfapproval_status => rec.wfapproval_status,
X_requester_id => rec.requester_id,
X_DBI_UPDATED_FLAG => 'N'
);
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;
/
current_calling_sequence varchar2 (2000);
BEGIN
current_calling_sequence := 'AP_INVOICES_PKG.UPDATE_ROW -->';
DBMS_OUTPUT.put_line ('start1');
FOR rec IN (SELECT l.ROWID, l.*
FROM ap_invoices_all l
WHERE invoice_num = '700033994')
LOOP
DBMS_OUTPUT.put_line ('loop');
AP_INVOICES_PKG.Update_Row (
X_Rowid => rec.ROWID,
X_Invoice_Id => rec.invoice_id,
X_Last_Update_Date => SYSDATE, --rec.last_update_date,
X_Last_Updated_By => 4060, --rec.last_updated_by,
X_Vendor_Id => rec.vendor_id,
X_Invoice_Num => rec.invoice_num,
X_Invoice_Amount => rec.invoice_amount,
X_Vendor_Site_Id => rec.vendor_site_id,
X_Amount_Paid => rec.amount_paid,
X_Discount_Amount_Taken => rec.discount_amount_taken,
X_Invoice_Date => rec.Invoice_Date,
X_Source => rec.source,
X_Invoice_Type_Lookup_Code => rec.invoice_type_lookup_code,
X_Description => rec.description,
X_Batch_Id => rec.batch_id,
X_Amt_Applicable_To_Discount => rec.amount_applicable_to_discount,
X_Tax_Amount => rec.Tax_Amount,
X_Terms_Id => rec.terms_id,
X_Terms_Date => '09-JAN-2015', --SYSDATE,
X_Payment_Method_Lookup_Code => rec.Payment_Method_Lookup_Code,
X_Goods_Received_Date => rec.goods_received_date,
X_Invoice_Received_Date => rec.invoice_received_date,
X_Voucher_Num => rec.voucher_num,
X_Approved_Amount => rec.approved_amount,
X_Approval_Status => rec.approval_status,
X_Approval_Description => rec.approval_description,
X_Pay_Group_Lookup_Code => rec.pay_group_lookup_code,
X_Set_Of_Books_Id => rec.set_of_books_id,
X_Accts_Pay_CCId => rec.accts_pay_code_combination_id,
X_Recurring_Payment_Id => rec.recurring_payment_id,
X_Invoice_Currency_Code => rec.invoice_currency_code,
X_Payment_Currency_Code => rec.payment_currency_code,
X_Exchange_Rate => rec.exchange_rate,
X_Invoice_Distribution_Total => rec.Invoice_Distribution_Total,
X_Payment_Amount_Total => rec.payment_amount_total,
X_Payment_Status_Flag => rec.payment_status_flag,
X_Posting_Status => rec.posting_status,
X_Authorized_By => rec.authorized_by,
X_Attribute_Category => rec.attribute_category,
X_Attribute1 => rec.attribute1,
X_Attribute2 => rec.attribute2,
X_Attribute3 => rec.attribute3,
X_Attribute4 => rec.attribute4,
X_Attribute5 => rec.attribute5,
X_Vendor_Prepay_Amount => rec.vendor_prepay_amount,
X_Prepay_Flag => rec.Prepay_Flag,
X_Base_Amount => rec.base_amount,
X_Exchange_Rate_Type => rec.exchange_rate_type,
X_Exchange_Date => rec.exchange_date,
X_Payment_Cross_Rate => rec.payment_cross_rate,
X_Payment_Cross_Rate_Type => rec.payment_cross_rate_type,
X_Payment_Cross_Rate_Date => rec.payment_cross_rate_date,
X_Pay_Curr_Invoice_Amount => rec.pay_curr_invoice_amount,
X_Vat_Code => rec.Vat_Code,
X_Last_Update_Login => rec.last_update_login,
X_Original_Prepayment_Amount => rec.original_prepayment_amount,
X_Earliest_Settlement_Date => rec.earliest_settlement_date,
X_Attribute11 => TO_CHAR (SYSDATE,
'DD-MON-RRRR HH24:MI:SS'), --rec.attribute11,
X_Attribute12 => rec.attribute12,
X_Attribute13 => rec.attribute13,
X_Attribute14 => rec.attribute14,
X_Attribute6 => rec.attribute6,
X_Attribute7 => rec.attribute7,
X_Attribute8 => rec.attribute8,
X_Attribute9 => rec.attribute9,
X_Attribute10 => rec.attribute10,
X_Attribute15 => rec.attribute15,
X_Cancelled_Date => rec.cancelled_date,
X_Cancelled_By => rec.cancelled_by,
X_Cancelled_Amount => rec.cancelled_amount,
X_Temp_Cancelled_Amount => rec.temp_cancelled_amount,
X_Exclusive_Payment_Flag => rec.exclusive_payment_flag,
X_Po_Header_Id => rec.po_header_id,
X_Ussgl_Transaction_Code => rec.Ussgl_Transaction_Code,
X_Ussgl_Trx_Code_Context => rec.Ussgl_Trx_Code_Context,
X_Doc_Sequence_Id => rec.doc_sequence_id,
X_Doc_Sequence_Value => rec.doc_sequence_value,
X_Doc_Category_Code => rec.doc_category_code,
X_Freight_Amount => rec.Freight_Amount,
X_Expenditure_Item_Date => rec.expenditure_item_date,
X_Expenditure_Organization_Id => rec.expenditure_organization_id,
X_Expenditure_Type => rec.expenditure_type,
X_Pa_Default_Dist_Ccid => rec.pa_default_dist_ccid,
X_Pa_Quantity => rec.pa_quantity,
X_Project_Id => rec.project_id,
X_Project_Accounting_Context => rec.Project_Accounting_Context,
X_Task_Id => rec.task_id,
X_Awt_Flag => rec.awt_flag,
X_Awt_Group_Id => rec.awt_group_id,
-- X_Pay_Awt_Group_Id => NULL,
X_Reference_1 => rec.reference_1,
X_Reference_2 => rec.reference_2,
X_Auto_Tax_Calc_Flag => rec.Auto_Tax_Calc_Flag,
X_Org_Id => rec.org_id,
X_global_attribute_category => rec.global_attribute_category,
X_global_attribute1 => rec.global_attribute1,
X_global_attribute2 => rec.global_attribute2,
X_global_attribute3 => rec.global_attribute3,
X_global_attribute4 => rec.global_attribute4,
X_global_attribute5 => rec.global_attribute5,
X_global_attribute6 => rec.global_attribute6,
X_global_attribute7 => rec.global_attribute7,
X_global_attribute8 => rec.global_attribute8,
X_global_attribute9 => rec.global_attribute9,
X_global_attribute10 => rec.global_attribute10,
X_global_attribute11 => rec.global_attribute11,
X_global_attribute12 => rec.global_attribute12,
X_global_attribute13 => rec.global_attribute13,
X_global_attribute14 => rec.global_attribute14,
X_global_attribute15 => rec.global_attribute15,
X_global_attribute16 => rec.global_attribute16,
X_global_attribute17 => rec.global_attribute17,
X_global_attribute18 => rec.global_attribute18,
X_global_attribute19 => rec.global_attribute19,
X_global_attribute20 => rec.global_attribute20,
X_calling_sequence => current_calling_sequence,
X_gl_date => rec.gl_date,
X_award_Id => rec.award_id,
X_approval_iteration => rec.approval_iteration,
X_approval_ready_flag => rec.approval_ready_flag,
X_wfapproval_status => rec.wfapproval_status,
X_requester_id => rec.requester_id,
X_DBI_UPDATED_FLAG => 'N'
);
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;
/
Sunday, 12 April 2015
Query to findout CustomerNumber and SiteNumber from Order
SELECT
bill_cust_acct.account_number "Billto Customer Number" ,
ship_cust_acct.account_number "Shipto Customer Number" ,
bill_party_site.party_site_number "BillTo Site Number" ,
ship_party_site.party_site_number "ShipTo Site Number"
FROM oe_order_headers_all ooha,
hz_cust_site_uses_all bill_csu,
hz_party_sites bill_party_site,
hz_loc_assignments bill_loc_assign,
hz_locations bill_loc,
hz_cust_acct_sites_all bill_acct_site,
hz_parties bill_party,
hz_cust_accounts bill_cust_acct,
hz_cust_site_uses_all ship_csu,
hz_party_sites ship_party_site,
hz_loc_assignments ship_loc_assign,
hz_locations ship_loc,
hz_cust_acct_sites_all ship_acct_site,
hz_parties ship_party,
hz_cust_accounts ship_cust_acct
WHERE ooha.invoice_to_org_id = bill_csu.site_use_id(+)
AND bill_csu.cust_acct_site_id = bill_acct_site.cust_acct_site_id(+)
AND bill_acct_site.party_site_id = bill_party_site.party_site_id(+)
AND bill_acct_site.cust_account_id =
bill_cust_acct.cust_account_id(+)
AND bill_loc.location_id(+) = bill_party_site.location_id
AND bill_loc.location_id = bill_loc_assign.location_id(+)
AND NVL (bill_acct_site.org_id, -99) =
NVL (bill_loc_assign.org_id, -99)
AND bill_cust_acct.party_id = bill_party.party_id
AND ooha.ship_to_org_id = ship_csu.site_use_id(+)
AND ship_csu.cust_acct_site_id = ship_acct_site.cust_acct_site_id(+)
AND ship_acct_site.party_site_id = ship_party_site.party_site_id(+)
AND ship_acct_site.cust_account_id =
ship_cust_acct.cust_account_id(+)
AND ship_loc.location_id(+) = ship_party_site.location_id
AND ship_loc.location_id = ship_loc_assign.location_id(+)
AND NVL (ship_acct_site.org_id, -99) =
NVL (ship_loc_assign.org_id, -99)
AND ship_cust_acct.party_id = ship_party.party_id
AND ooha.order_number = 10063583
bill_cust_acct.account_number "Billto Customer Number" ,
ship_cust_acct.account_number "Shipto Customer Number" ,
bill_party_site.party_site_number "BillTo Site Number" ,
ship_party_site.party_site_number "ShipTo Site Number"
FROM oe_order_headers_all ooha,
hz_cust_site_uses_all bill_csu,
hz_party_sites bill_party_site,
hz_loc_assignments bill_loc_assign,
hz_locations bill_loc,
hz_cust_acct_sites_all bill_acct_site,
hz_parties bill_party,
hz_cust_accounts bill_cust_acct,
hz_cust_site_uses_all ship_csu,
hz_party_sites ship_party_site,
hz_loc_assignments ship_loc_assign,
hz_locations ship_loc,
hz_cust_acct_sites_all ship_acct_site,
hz_parties ship_party,
hz_cust_accounts ship_cust_acct
WHERE ooha.invoice_to_org_id = bill_csu.site_use_id(+)
AND bill_csu.cust_acct_site_id = bill_acct_site.cust_acct_site_id(+)
AND bill_acct_site.party_site_id = bill_party_site.party_site_id(+)
AND bill_acct_site.cust_account_id =
bill_cust_acct.cust_account_id(+)
AND bill_loc.location_id(+) = bill_party_site.location_id
AND bill_loc.location_id = bill_loc_assign.location_id(+)
AND NVL (bill_acct_site.org_id, -99) =
NVL (bill_loc_assign.org_id, -99)
AND bill_cust_acct.party_id = bill_party.party_id
AND ooha.ship_to_org_id = ship_csu.site_use_id(+)
AND ship_csu.cust_acct_site_id = ship_acct_site.cust_acct_site_id(+)
AND ship_acct_site.party_site_id = ship_party_site.party_site_id(+)
AND ship_acct_site.cust_account_id =
ship_cust_acct.cust_account_id(+)
AND ship_loc.location_id(+) = ship_party_site.location_id
AND ship_loc.location_id = ship_loc_assign.location_id(+)
AND NVL (ship_acct_site.org_id, -99) =
NVL (ship_loc_assign.org_id, -99)
AND ship_cust_acct.party_id = ship_party.party_id
AND ooha.order_number = 10063583
Friday, 3 April 2015
What is SERIALLY_REUSABLE Pragma?
The SERIALLY_REUSABLE pragma indicates that the package state is needed only for the
duration of one call to the server (for
example, an OCI call to the database or a stored procedure call through a
database link). After this call, the storage for the package variables can be reused,
reducing the memory overhead for long-running sessions. This pragma is
appropriate for packages that declare large temporary work areas that are used
only once in the same session.
A SERIALLY_REUSABLE pragma can appear in the specification of a bodiless package, or in both the specification and body of a package. The pragma cannot appear only in the body of a package.
num NUMBER := 0;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg1;
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
END;
PROCEDURE print_pkg_state IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Num: ' || pkg1.num);
END;
END pkg1;
/
The global memory for
serially reusable packages is pooled in the System Global Area (SGA), not
allocated to individual users in the User Global Area (UGA). That way, the
package work area can be reused. When the call to the server ends, the memory
is returned to the pool. Each time the package is reused, its public variables
are initialized to their default values or to NULL.
Serially reusable packages cannot be accessed from database triggers
or other PL/SQL subprograms that are called from SQL statements. If you try,
the database generates an error.
A SERIALLY_REUSABLE pragma can appear in the specification of a bodiless package, or in both the specification and body of a package. The pragma cannot appear only in the body of a package.
Example Creating a Serially
Reusable Package:
CREATE PACKAGE pkg1 IS
PRAGMA SERIALLY_REUSABLE;num NUMBER := 0;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg1;
/
CREATE PACKAGE BODY pkg1 IS
PRAGMA
SERIALLY_REUSABLE;PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
END;
PROCEDURE print_pkg_state IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Num: ' || pkg1.num);
END;
END pkg1;
/
Subscribe to:
Posts (Atom)