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 Go to next message
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 #583024 is a reply to message #583021] Thu, 25 April 2013 00:08 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
TOAD? Select that table and click "Constraints" tab on the right side of the screen (where you review table's list of columns, data, triggers, constraints, ...).

Generally, you'd query USER_CONSTRAINTS and USER_CONS_COLUMNS which contain all constraints in user's schema.
Re: How to find out the primary key of oracle apps tables [message #583026 is a reply to message #583024] Thu, 25 April 2013 00:10 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
hey littlefoot


In constraint all constraint are check constraint.there is no primary or foreign constraint.so on what basis ,i can find out the reference between ap_invoices_all and ap_invoice_lines_all?


Thanks


Re: How to find out the primary key of oracle apps tables [message #583033 is a reply to message #583026] Thu, 25 April 2013 00:52 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If no referential integrity constraint exists, then you're in trouble. Consistency might be maintained through the application (instead of database) so, if you don't know the application (its code), you can't tell for sure. As I don't use Oracle Apps., I'm rather useless here. However, there are people here who use that software product and they will assist (at least, I hope so). Sorry for not being able to help.
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 Go to previous messageGo to next message
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 #583041 is a reply to message #583039] Thu, 25 April 2013 02:11 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
vamsi kasina wrote on Thu, 25 April 2013 08:19
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.


If that's true, I'd swear that this is wrong. How can an application outmatch database in enforcing primary key constraint (or referential integrity constraint. Any constraint)? /forum/fa/1580/0/
Re: How to find out the primary key of oracle apps tables [message #583049 is a reply to message #583021] Thu, 25 April 2013 03:23 Go to previous messageGo to next message
eric62
Messages: 24
Registered: January 2009
Junior Member
Hello.

You can use le responsibility Application Developer
Navigation: Application -> Database -> Table -> Foreign Keys

Queries on FND_TABLE, FND_FOREIGN_KEYS and FND_FOREIGN_KEY_COLUMNS

Regards,
Eric.
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 Go to previous messageGo to next message
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 #583058 is a reply to message #583053] Thu, 25 April 2013 04:22 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
Thanks alot eric.

It really helped me.to find the relationships between tables.
Re: How to find out the primary key of oracle apps tables [message #583064 is a reply to message #583058] Thu, 25 April 2013 05:16 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
hey guys i have another query i am not able to make the relationship in them or not able to make the subquery.i posted it many times.but dint get any solutions.will u help me here.if somebody is from oracle payable module query writer then help me out.
CAn i post it here?
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 Go to previous message
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
Previous Topic: upload_attachments_to_oracle_r12
Next Topic: oracle balance sheet report
Goto Forum:
  


Current Time: Thu Mar 28 12:17:21 CDT 2024