R12 Banks Queries
SELECT aps.vendor_name "VERDOR NAME",
apss.vendor_site_code "VENDOR SITE CODE",
ieb.bank_name "BANK NAME",
iebb.bank_branch_name "BANK BRANCH NAME",
iebb.branch_number "BRANCH NUMBER",
ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM ap.ap_suppliers aps,
ap.ap_supplier_sites_all apss,
apps.iby_ext_bank_accounts ieba,
apps.iby_account_owners iao,
apps.iby_ext_banks_v ieb,
apps.iby_ext_bank_branches_v iebb
WHERE aps.vendor_id = apss.vendor_id
and iao.account_owner_party_id = aps.party_id
and ieba.ext_bank_account_id = iao.ext_bank_account_id
and ieb.bank_party_id = iebb.bank_party_id
and ieba.branch_id = iebb.branch_party_id
and ieba.bank_id = ieb.bank_party_id;
SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
, ass.vendor_site_code;
Wednesday, December 29, 2010
Delete Duplicate Records in Oracle
Delete Duplicate Records in Oracle
There are times when duplicate rows somehow creep into a table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully. Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:
CREATE TABLE dup_test (
Emp_Id VARCHAR2(5),
Name VARCHAR2(15),
Phone NUMBER);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('101','Dave',561982);
SELECT * FROM dup_test;
Use subquery to delete duplicate rows:
Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:
DELETE FROM
dup_test A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
dup_test B
WHERE
A.Emp_Id = B.Emp_Id
AND
A.Name = B.Name
AND
A.Phone = B.Phone
);
Use analytics to delete duplicate rows:
You can also detect and delete duplicate rows using Oracle analytic functions:
DELETE FROM dup_test
WHERE ROWID IN
(SELECT ROWID FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY Emp_Id) rnk FROM dup_test)
WHERE rnk>1);
Use another table to delete duplicate rows:
This is the simplest method to remove duplicity.
CREATE TABLE dup_test_1 as select distinct * from dup_test;
DROP TABLE dup_test;
RENAME dup_test_1 to dup_test;
Use RANK to delete duplicate rows:
This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:
DELETE FROM dup_test where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by Emp_Id order by rowid) rank_n, rowid as "rowid"
from dup_test
)
)
where rank_n > 1
);
The above methods are only standard methods. You can also use your own techniques to remove duplicate records.
There are times when duplicate rows somehow creep into a table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully. Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:
CREATE TABLE dup_test (
Emp_Id VARCHAR2(5),
Name VARCHAR2(15),
Phone NUMBER);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('101','Dave',561982);
SELECT * FROM dup_test;
Use subquery to delete duplicate rows:
Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:
DELETE FROM
dup_test A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
dup_test B
WHERE
A.Emp_Id = B.Emp_Id
AND
A.Name = B.Name
AND
A.Phone = B.Phone
);
Use analytics to delete duplicate rows:
You can also detect and delete duplicate rows using Oracle analytic functions:
DELETE FROM dup_test
WHERE ROWID IN
(SELECT ROWID FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY Emp_Id) rnk FROM dup_test)
WHERE rnk>1);
Use another table to delete duplicate rows:
This is the simplest method to remove duplicity.
CREATE TABLE dup_test_1 as select distinct * from dup_test;
DROP TABLE dup_test;
RENAME dup_test_1 to dup_test;
Use RANK to delete duplicate rows:
This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:
DELETE FROM dup_test where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by Emp_Id order by rowid) rank_n, rowid as "rowid"
from dup_test
)
)
where rank_n > 1
);
The above methods are only standard methods. You can also use your own techniques to remove duplicate records.
Supplier Conversion in R12
Supplier Conversion in R12
Vendor conversion program will load the Supplier Master, Sites and Contacts data from data files to the staging tables, validate the data and then load the data into Interface tables, finally Validated data will import into Oracle Supplier Standard Tables by using Oracle Standard Supplier Import Programs.
Pre-requisites setup’s are: Payment terms, Pay Groups, CCID, Supplier classifications, Bank Accounts , Employees (if employees have to set up as vendors).
The Interface Tables are:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AP_SUPPLIERS_INT:
This is the open interface table for AP Suppliers. It holds Supplier information which is loaded by the user for import. The columns in the table map to corresponding columns in the PO_VENDORS table. The Oracle Payables application uses this information to create a new Supplier record when the Supplier Open Interface Import program is submitted. Each row in the table will be identified by a unique identifier, the VENDOR_INTERFACE_ID.
Mandatory Columns:
VENDOR_INTERFACE_ID (ap_suppliers_int_s.NEXTVAL)- Supplier interface record unique identifier
VENDOR_NAME – Supplier name
Other important columns:
SEGMENT1 – Supplier Number
VENDOR_TYPE_LOOKUP_CODE – Supplier type
SHIP_TO_LOCATION_CODE – Default ship-to-location name
BILL_TO_LOCATION_CODE – Default bill-to-location name
TERMS_NAME – Payment terms name
TAX_VERIFICATION_DATE – Tax verification date(1099)
VAT_REGISTRATION_NUM – Tax registration number
ATTRIBUTE1 -15 – Descriptive Flexfield Segments
PAY_GROUP_LOOKUP_CODE – Payment group type
INVOICE_CURRENCY_CODE – Default currency unique identifier
PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
NUM_1099 – Tax identification number (1099)
VAT_CODE – Default invoice tax code
HOLD_FLAG – Indicates whether or not the supplier is on purchasing hold
SUMMARY_FLAG – Key flexfield summary flag
ENABLED_FLAG – Key flexfield enable flag
EMPLOYEE_ID – Employee unique identifier if supplier is an employee
AP_SUPPLIER_SITES_INT:
This is the open interface table for AP Supplier Sites. It holds Supplier Site information which is loaded by the user for import. The columns in the table map to corresponding columns in PO_VENDOR_SITES_ALL table. The Oracle Payables application uses this information to create a new Supplier Site record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier using the VENDOR_ID column.
Mandatory Columns:
VENDOR_SITE_INTERFACE_ID (ap_supplier_sites_int_s.NEXTVAL) – Supplier Site interface record unique identifier
VENDOR_SITE_CODE – Supplier Site name
Other important columns:
ADDRESS_LINE1 – First line of supplier address
ADDRESS_LINE2 – Second line of supplier address
ADDRESS_LINE3 – Third line of supplier address
CITY – City name
STATE – State name or abbreviation
ZIP – Postal code
COUNTRY – Country name
PHONE – Phone number
FAX – Supplier site facsimile number
SHIP_TO_LOCATION_CODE – Default ship-to-location name
BILL_TO_LOCATION_CODE – Default bill-to-location name
PAYMENT_METHOD_LOOKUP_CODE – Default payment method type
VAT_CODE – Invoice default tax code
PAY_GROUP_LOOKUP_CODE – Payment group type
TERMS_NAME – Payment terms name
INVOICE_CURRENCY_CODE – Default currency unique identifier
PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
EMAIL_ADDRESS – E-mail address of the supplier contact
PURCHASING_SITE_FLAG – Indicates whether purchasing is allowed from this site
AUTO_TAX_CALC_FLAG – Level of automatic tax calculation for supplier
HOLD_ALL_PAYMENTS_FLAG – Indicates if Oracle Payables should place payments for this supplier on hold
AP_SUP_SITE_CONTACT_INT:
This is the open interface table for AP Supplier Site Contacts. It holds Supplier contact data. The columns in the table map to corresponding columns in PO_VENDOR_CONTACTS table. The Oracle Payables application uses this information to create a new Supplier Contact record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier Site using the VENDOR_SITE_CODE and ORG_ID.
Mandatory Columns:
VENDOR_INTERFACE_ID – Supplier interface record unique identifier
VENDOR_CONTACT_INTERFACE_ID (AP_SUP_SITE_CONTACT_INT_S.NEXTVAL) – Vendor Contact Interface Identifier
VENDOR_SITE_CODE – Supplier Site name
Other important columns:
FIRST_NAME – Contact First name
LAST_NAME – Contact last name
AREA_CODE – Area code of contact phone number
PHONE – Contact phone number
FIRST_NAME_ALT – Alternate Supplier contact first name
LAST_NAME_ALT – Alternate Supplier contact last name
EMAIL_ADDRESS – Email address for the Supplier Site contact
FAX – Facsimile number for the Supplier Site contact
VENDOR_ID – Supplier unique identifier
Validations:
Vendor Number (Check for duplicate records in ap_suppliers table)
Vendor Name (Check for duplicate records in staging as well as in ap_suppliers table)
Terms Name (Check for proper record in ap_terms_tl table)
Pay Group (Check for proper record in fnd_lookup_values_vl table where lookup_type = ‘PAY GROUP’)
Employee Id (Check for proper employee record in per_all_people_f table)
Vendor Type (Check for proper record in po_lookup_codes table where lookup_type = ‘VENDOR TYPE’)
Vendor Site Code (Check for duplicate records in ap_supplier_sites_all table)
Country Code ( Check for proper country code in fnd_territories_vl table)
Payment Method (Check for proper payment method in iby_payment_methods_vl table)
Interface programs:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import
The data inserted via these interfaces are automatically populated into TCA tables.
Note: AP_SUPPLIER_INT_REJECTIONS table contains suppliers, sites, contacts rejections information.
Vendor conversion program will load the Supplier Master, Sites and Contacts data from data files to the staging tables, validate the data and then load the data into Interface tables, finally Validated data will import into Oracle Supplier Standard Tables by using Oracle Standard Supplier Import Programs.
Pre-requisites setup’s are: Payment terms, Pay Groups, CCID, Supplier classifications, Bank Accounts , Employees (if employees have to set up as vendors).
The Interface Tables are:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AP_SUPPLIERS_INT:
This is the open interface table for AP Suppliers. It holds Supplier information which is loaded by the user for import. The columns in the table map to corresponding columns in the PO_VENDORS table. The Oracle Payables application uses this information to create a new Supplier record when the Supplier Open Interface Import program is submitted. Each row in the table will be identified by a unique identifier, the VENDOR_INTERFACE_ID.
Mandatory Columns:
VENDOR_INTERFACE_ID (ap_suppliers_int_s.NEXTVAL)- Supplier interface record unique identifier
VENDOR_NAME – Supplier name
Other important columns:
SEGMENT1 – Supplier Number
VENDOR_TYPE_LOOKUP_CODE – Supplier type
SHIP_TO_LOCATION_CODE – Default ship-to-location name
BILL_TO_LOCATION_CODE – Default bill-to-location name
TERMS_NAME – Payment terms name
TAX_VERIFICATION_DATE – Tax verification date(1099)
VAT_REGISTRATION_NUM – Tax registration number
ATTRIBUTE1 -15 – Descriptive Flexfield Segments
PAY_GROUP_LOOKUP_CODE – Payment group type
INVOICE_CURRENCY_CODE – Default currency unique identifier
PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
NUM_1099 – Tax identification number (1099)
VAT_CODE – Default invoice tax code
HOLD_FLAG – Indicates whether or not the supplier is on purchasing hold
SUMMARY_FLAG – Key flexfield summary flag
ENABLED_FLAG – Key flexfield enable flag
EMPLOYEE_ID – Employee unique identifier if supplier is an employee
AP_SUPPLIER_SITES_INT:
This is the open interface table for AP Supplier Sites. It holds Supplier Site information which is loaded by the user for import. The columns in the table map to corresponding columns in PO_VENDOR_SITES_ALL table. The Oracle Payables application uses this information to create a new Supplier Site record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier using the VENDOR_ID column.
Mandatory Columns:
VENDOR_SITE_INTERFACE_ID (ap_supplier_sites_int_s.NEXTVAL) – Supplier Site interface record unique identifier
VENDOR_SITE_CODE – Supplier Site name
Other important columns:
ADDRESS_LINE1 – First line of supplier address
ADDRESS_LINE2 – Second line of supplier address
ADDRESS_LINE3 – Third line of supplier address
CITY – City name
STATE – State name or abbreviation
ZIP – Postal code
COUNTRY – Country name
PHONE – Phone number
FAX – Supplier site facsimile number
SHIP_TO_LOCATION_CODE – Default ship-to-location name
BILL_TO_LOCATION_CODE – Default bill-to-location name
PAYMENT_METHOD_LOOKUP_CODE – Default payment method type
VAT_CODE – Invoice default tax code
PAY_GROUP_LOOKUP_CODE – Payment group type
TERMS_NAME – Payment terms name
INVOICE_CURRENCY_CODE – Default currency unique identifier
PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
EMAIL_ADDRESS – E-mail address of the supplier contact
PURCHASING_SITE_FLAG – Indicates whether purchasing is allowed from this site
AUTO_TAX_CALC_FLAG – Level of automatic tax calculation for supplier
HOLD_ALL_PAYMENTS_FLAG – Indicates if Oracle Payables should place payments for this supplier on hold
AP_SUP_SITE_CONTACT_INT:
This is the open interface table for AP Supplier Site Contacts. It holds Supplier contact data. The columns in the table map to corresponding columns in PO_VENDOR_CONTACTS table. The Oracle Payables application uses this information to create a new Supplier Contact record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier Site using the VENDOR_SITE_CODE and ORG_ID.
Mandatory Columns:
VENDOR_INTERFACE_ID – Supplier interface record unique identifier
VENDOR_CONTACT_INTERFACE_ID (AP_SUP_SITE_CONTACT_INT_S.NEXTVAL) – Vendor Contact Interface Identifier
VENDOR_SITE_CODE – Supplier Site name
Other important columns:
FIRST_NAME – Contact First name
LAST_NAME – Contact last name
AREA_CODE – Area code of contact phone number
PHONE – Contact phone number
FIRST_NAME_ALT – Alternate Supplier contact first name
LAST_NAME_ALT – Alternate Supplier contact last name
EMAIL_ADDRESS – Email address for the Supplier Site contact
FAX – Facsimile number for the Supplier Site contact
VENDOR_ID – Supplier unique identifier
Validations:
Vendor Number (Check for duplicate records in ap_suppliers table)
Vendor Name (Check for duplicate records in staging as well as in ap_suppliers table)
Terms Name (Check for proper record in ap_terms_tl table)
Pay Group (Check for proper record in fnd_lookup_values_vl table where lookup_type = ‘PAY GROUP’)
Employee Id (Check for proper employee record in per_all_people_f table)
Vendor Type (Check for proper record in po_lookup_codes table where lookup_type = ‘VENDOR TYPE’)
Vendor Site Code (Check for duplicate records in ap_supplier_sites_all table)
Country Code ( Check for proper country code in fnd_territories_vl table)
Payment Method (Check for proper payment method in iby_payment_methods_vl table)
Interface programs:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import
The data inserted via these interfaces are automatically populated into TCA tables.
Note: AP_SUPPLIER_INT_REJECTIONS table contains suppliers, sites, contacts rejections information.
Thursday, December 16, 2010
Oracle R12 Banks Accounts Query
Oracle R12 Bank Accounts Query
SELECT owners.account_owner_party_id, asp.segment1 vendor_num,
asp.vendor_name,
(SELECT NAME
FROM hr_all_organization_units
WHERE TYPE = 'OU' AND organization_id = asa.org_id) ou_name,
asa.vendor_site_code, ieb.country_code
--, (select bank_name from ce_banks_v cbv where bank_party_id =ieb.bank_id) bank_name
--, (select bank_number from ce_banks_v cbv where bank_party_id =ieb.bank_id) bank_number
--, ieb.branch_id
, cbbv.bank_name, cbbv.bank_number, cbbv.bank_branch_name,
cbbv.branch_number, cbbv.bank_branch_type, cbbv.eft_swift_code,
ieb.bank_account_num, ieb.currency_code, ieb.iban,
ieb.foreign_payment_use_flag,
ieb.bank_account_name_alt bankacct_fromdata
FROM iby_pmt_instr_uses_all instrument,
iby_account_owners owners,
iby_external_payees_all payees,
iby_ext_bank_accounts ieb,
ap_supplier_sites_all asa,
ap_suppliers asp,
ce_bank_branches_v cbbv
WHERE owners.primary_flag = 'Y'
AND owners.ext_bank_account_id = ieb.ext_bank_account_id
AND owners.ext_bank_account_id = instrument.instrument_id
AND payees.ext_payee_id = instrument.ext_pmt_party_id
AND payees.payee_party_id = owners.account_owner_party_id
AND payees.supplier_site_id = asa.vendor_site_id
AND asa.vendor_id = asp.vendor_id
AND cbbv.branch_party_id(+) = ieb.branch_id
--and asa.org_id in (125)
AND asp.created_by = 1451
ORDER BY 4, 1, 3
SELECT owners.account_owner_party_id, asp.segment1 vendor_num,
asp.vendor_name,
(SELECT NAME
FROM hr_all_organization_units
WHERE TYPE = 'OU' AND organization_id = asa.org_id) ou_name,
asa.vendor_site_code, ieb.country_code
--, (select bank_name from ce_banks_v cbv where bank_party_id =ieb.bank_id) bank_name
--, (select bank_number from ce_banks_v cbv where bank_party_id =ieb.bank_id) bank_number
--, ieb.branch_id
, cbbv.bank_name, cbbv.bank_number, cbbv.bank_branch_name,
cbbv.branch_number, cbbv.bank_branch_type, cbbv.eft_swift_code,
ieb.bank_account_num, ieb.currency_code, ieb.iban,
ieb.foreign_payment_use_flag,
ieb.bank_account_name_alt bankacct_fromdata
FROM iby_pmt_instr_uses_all instrument,
iby_account_owners owners,
iby_external_payees_all payees,
iby_ext_bank_accounts ieb,
ap_supplier_sites_all asa,
ap_suppliers asp,
ce_bank_branches_v cbbv
WHERE owners.primary_flag = 'Y'
AND owners.ext_bank_account_id = ieb.ext_bank_account_id
AND owners.ext_bank_account_id = instrument.instrument_id
AND payees.ext_payee_id = instrument.ext_pmt_party_id
AND payees.payee_party_id = owners.account_owner_party_id
AND payees.supplier_site_id = asa.vendor_site_id
AND asa.vendor_id = asp.vendor_id
AND cbbv.branch_party_id(+) = ieb.branch_id
--and asa.org_id in (125)
AND asp.created_by = 1451
ORDER BY 4, 1, 3
Subscribe to:
Posts (Atom)