Home » Applications » Oracle Fusion Apps & E-Business Suite » How to find out the primary key of oracle apps tables (Oracle apps 10g database)
| How to find out the primary key of oracle apps tables [message #583021] |
Wed, 24 April 2013 22:52  |
 |
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
|
Member |
|
|
How to find out the primary key of oracle apps tables
i have question
how ap_invoices_all and ap_invoices_lines_all are related to each other.as there is not primary key and foreign key constraint in their scripts??How to check it on toad.and how we related them like.
ap_invoices_all.invoice_id=ap_invoice_line_all.invoice_id
Thanks
Bhawna
[Updated on: Wed, 24 April 2013 23:04] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: How to find out the primary key of oracle apps tables [message #583039 is a reply to message #583033] |
Thu, 25 April 2013 01:19  |
 |
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
|
Senior Member |
|
|
Hi Bhawna,
Thinking behind that to not having many database constraints in Oracle Applications is performance.
Also as it is Oracle product, the developers take care of the constraints in the coding.
Anyway if issues / bugs, MOS is already there for support.
For your needy information, please check etrm.oracle.com
This is mentioned in the third point of Apps Forum Guide. Please recheck.
It will ask for user and password. You can give the same of MOS. Pick your version and choose FND Data.
By
Vamsi
[Updated on: Thu, 25 April 2013 01:20] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How to find out the primary key of oracle apps tables [message #583053 is a reply to message #583049] |
Thu, 25 April 2013 03:41 |
|
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
|
Senior Member |
|
|
May be I should rephrase my statement.
Instead of Primary keys on the database level, it seems there are unique indexes and not null constraints separately.
But for the above tables I've not seen any FK reference in database.
It depends on the team which is developing it internally in Oracle.
Anyway as Eric pointed out, there is a foundation module (FND / AOL), where they control the rest.
FND_TABLES
FND_COLUMNS
FND_VIEWS
FND_VIEW_COLUMNS
FND_PRIMARY_KEYS
FND_PRIMARY_KEY_COLUMNS
FND_FOREIGN_KEYS
FND_FOREIGN_KEY_COLUMNS
FND_INDEXES
FND_INDEX_COLUMNS
etc
ETRM may pick the data from these tables and show in a detailed manner.
I could see some OTN thread on the similar lines where there is a debate between DB experts and APPS guys. Actually that is closed without any conclusion.
Also I found a crispy answer in this link.
By
Vamsi
[Updated on: Thu, 25 April 2013 04:11] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How to find out the primary key of oracle apps tables [message #583066 is a reply to message #583064] |
Thu, 25 April 2013 05:30 |
|
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
|
Member |
|
|
SELECT povs.vendor_site_code,
api.vendor_id,
api.vendor_site_id,
PoVS.address_line1,
PoVS.address_line2,
PoVS.address_line3,
api.invoice_type_lookup_code,
api.invoice_num,
API.accts_pay_code_combination_id,
( GCC.segment1
|| '.'
|| gCC.segment2
|| '.'
|| GCC.segment3
|| '.'
|| GCC.segment4
|| '.'
|| GCC.segment5
|| '.'
|| GCC.segment6
|| '.'
|| GCC.segment7 )
account_code,
api.invoice_date,
api.doc_sequence_value
Vou_num,
api.creation_date
Vou_date,
apd.description
description,
apd.dist_code_combination_id
ccid,
api.invoice_currency_code,
Nvl(api.exchange_rate, 1)
exchange_rate,
Decode(api.invoice_type_lookup_code, 'CREDIT', Abs(z.amt_val) -
Abs(
Nvl(api.discount_amount_taken, 0)),
0)
dr_val,
Decode(api.invoice_type_lookup_code, 'CREDIT', Abs(z.amt_val) -
Abs(
Nvl(api.discount_amount_taken, 0)),
0) * Nvl(api.exchange_rate, 1)
dr_exchange_val,
Decode(api.invoice_type_lookup_code, 'CREDIT', 0,
z.amt_val -
Nvl(api.discount_amount_taken, 0))
cr_val,
Decode(api.invoice_type_lookup_code, 'CREDIT', 0,
z.amt_val -
Nvl(api.discount_amount_taken, 0))
*
Nvl(
api.exchange_rate, 1)
cr_exchange_val,
To_char(api.doc_sequence_value)
payment_num,
To_char(apd.accounting_date, 'dd-MON-yyyy')
pay_accounting_date,
Nvl(ap_invoice_lines_utility_pkg.Get_posting_status(api.invoice_id,
apil.line_number), 'N')
acc_status,
Nvl(ap_invoice_lines_utility_pkg.Get_approval_status(api.invoice_id,
apil.line_number), 'NEVER APPROVED')
VALIDATION_STATUS,
NULL
check_number,
pov.segment1,
pov.vendor_name,
pov.vendor_type_lookup_code,
apd.po_distribution_id,
api.exchange_rate_type,
api.org_id,
api.batch_id,
api.exchange_date,
api.invoice_id,
apd.accounting_date,
0
dr_val_opening,
0
cr_val_opening
FROM ap_invoices_all api,
ap_invoice_lines_all apil,
ap_invoice_distributions_all apd,
po_vendors pov,
po_vendor_sites_all povs,
gl_code_combinations GCC,
(SELECT Nvl(Sum(apd.amount), 0) amt_val,
api.invoice_id
FROM ap_invoices_all api,
ap_invoice_lines_all apil,
ap_invoice_distributions_all apd,
po_vendors pov,
po_vendor_sites_all povs
WHERE api.invoice_id = apd.invoice_id
AND apil.invoice_id = api.invoice_id
AND apil.line_number = apd.invoice_line_number
AND api.vendor_id = pov.vendor_id
AND api.vendor_id = Nvl(:p_vendor_id, api.vendor_id)
AND pov.segment1 = Nvl(:p_vendor_no, pov.segment1)
AND Nvl(pov.vendor_type_lookup_code, 'NULL') =
Nvl(:P_Vendor_Type_Lookup_Code,
Nvl(Pov.vendor_type_lookup_code,
'NULL'))
AND api.invoice_type_lookup_code <> 'PREPAYMENT'
AND ( api.org_id = :p_org_id
OR api.org_id IS NULL )
AND api.vendor_site_id = povs.vendor_site_id
AND api.vendor_site_id = Nvl(:p_vendor_site_id,
api.vendor_site_id)
AND apd.accounting_date BETWEEN :p_from_date AND :p_to_date
AND apd.match_status_flag = 'A'
AND apil.line_type_lookup_code <> 'PREPAY'
GROUP BY api.invoice_id) z
WHERE api.invoice_id = z.invoice_id
AND api.invoice_id = apd.invoice_id
AND apil.invoice_id = api.invoice_id
AND GCC.code_combination_id = API.accts_pay_code_combination_id
AND apil.line_number = apd.invoice_line_number
AND apd.rowid = (SELECT rowid
FROM ap_invoice_distributions_all
WHERE rownum = 1
AND invoice_id = apd.invoice_id
AND accounting_date BETWEEN
:p_from_date AND :p_to_date
AND match_status_flag = 'A')
AND api.vendor_id = pov.vendor_id
AND api.vendor_id = Nvl(:p_vendor_id, api.vendor_id)
AND pov.segment1 = Nvl(:p_vendor_no, pov.segment1)
AND Nvl(pov.vendor_type_lookup_code, 'NULL') =
Nvl(:P_Vendor_Type_Lookup_Code, Nvl(Pov.vendor_type_lookup_code,
'NULL'))
AND api.invoice_type_lookup_code <> 'PREPAYMENT'
AND apd.match_status_flag = 'A'
AND ( api.org_id = :p_org_id
OR api.org_id IS NULL )
AND api.vendor_site_id = povs.vendor_site_id
AND api.invoice_type_lookup_code LIKE
Nvl(:p_invoice_type_lookup_code, api.invoice_type_lookup_code)
AND api.vendor_site_id = Nvl(:p_vendor_site_id, api.vendor_site_id)
AND ( ( api.invoice_type_lookup_code <> 'DEBIT' )
OR (( api.invoice_type_lookup_code = 'DEBIT' )
) )
that query is giving me the data of the standard invoice look up code.
but i want one field exchange rate to get from the invoices where invoice type look up code is prepayment.
for exchange rate i wrote this query
(select nvl(apc.exchange_rate,1)--,apc.exchange_Date
FROM ap_invoices_all api,
ap_invoice_lines_all apil,
ap_invoice_distributions_all apd,
po_vendors pov,
ap_invoice_payments_all app,
ap_checks_all apc,
po_vendor_sites_all povs,
gl_code_combinations GCC
WHERE api.invoice_id = apd.invoice_id
AND apil.invoice_id = api.invoice_id
AND apil.line_number = apd.invoice_line_number
AND GCC.code_combination_id = API.accts_pay_code_combination_id
AND apd.rowid = (SELECT rowid
FROM ap_invoice_distributions_all
WHERE rownum = 1
AND invoice_id = apd.invoice_id
AND match_status_flag = 'A')
AND api.vendor_id = pov.vendor_id
AND app.invoice_id = api.invoice_id
AND app.check_id = apc.check_id
AND api.vendor_id = Nvl(:p_vendor_id, api.vendor_id)
AND pov.segment1 = Nvl(:p_vendor_no, pov.segment1)
AND app.accounting_date BETWEEN :p_from_date AND :p_to_date
AND ( api.org_id = :p_org_id
OR api.org_id IS NULL )
AND apc.status_lookup_code IN ( 'CLEARED', 'NEGOTIABLE', 'VOIDED',
'RECONCILED UNACCOUNTED',
'RECONCILED', 'CLEARED BUT UNACCOUNTED' )
AND apd.match_status_flag = 'A'
AND api.vendor_site_id = povs.vendor_site_id
and api.invoice_currency_code in 'USD'
AND api.invoice_type_lookup_code LIKE
Nvl(:p_invoice_type_lookup_code, api.invoice_type_lookup_code)
AND api.vendor_site_id = Nvl(:p_vendor_site_id, api.vendor_site_id) )--exc_rate
when i am joing into my standard query then it is giving me the error of multiple values retrieviing by exchange rate query.
How can i make it happen to relate so invoice id from the ap_invoices_all and prepay_invoice_id from ap_invoice_distributions_all so that i can get the invoice whose exchage rate from the prepay invoice type look up code.
please please please guys help me out in this.i m doing head hunting from last 5 days.
but not able to get any output,
may be i m not making the right subquery.
i need to merge these two queries on the basic of some conditions.
Thanks
Bhawna
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 00:15:03 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
