Home » Applications » Oracle Fusion Apps & E-Business Suite » Oracle Payables (Oracle apps R12)
Oracle Payables [message #582832] Mon, 22 April 2013 23:58 Go to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
This query is giving me all the invoices having invoice type look up code='standard'
and i want the exchange rate of the invoices having the invoice type look up code='prepayment'

My standard invoice have the prepayment invoice.how can i pick the exchange rate of the prepayment invoice.

ap_invoices_all have the exchange rate

hey guys please tell me the relationship or any condition which i should add to get the exchange rate of the prepayment invoice..

Else other column are coming right..



   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' ) 
) ) 


-------------------///without prepayment------------------------




This query is giving me all the invoices having invoice type look up code='standard'
and i want the exchange rate of the invoices having the invoice type look up code='prepayment'

My standard invoice have the prepayment invoice.how can i pick the exchange rate of the prepayment invoice.

ap_invoices_all have the exchange rate

hey guys please tell me the relationship or any condition which i should add to get the exchange rate of the prepayment invoice..

Else other column are coming right..


Thanks in advance
Re: Oracle Payables [message #583600 is a reply to message #582832] Thu, 02 May 2013 03:18 Go to previous messageGo to next message
a_kamalraj
Messages: 18
Registered: May 2013
Location: Bangalore
Junior Member

Hi

Let me know is my understanding from your message is correct.

You can able to get from the query written ie.. information with exchange rate for STANDARD invoice and you are expecting same information with exchange rate for PREPAYMENT Invoice... am i right?

Cheers
Kamal
(kamal.love@gmail.com)
Re: Oracle Payables [message #583601 is a reply to message #583600] Thu, 02 May 2013 03:39 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
yes you are right.i want the same thing
Re: Oracle Payables [message #583602 is a reply to message #583601] Thu, 02 May 2013 03:42 Go to previous messageGo to next message
a_kamalraj
Messages: 18
Registered: May 2013
Location: Bangalore
Junior Member

Hi,

Have you tried Changing the Condition with invoice_type_lookup_code ='PREPAYMENT'

Cheers
Kamal
(kamal.love@gmail.com)
Re: Oracle Payables [message #583603 is a reply to message #583602] Thu, 02 May 2013 03:44 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
yes i tried it.but not able to get the write data.

for exchange rate i tried this one.
as the prepayment exchange rate data was coming from this query.

(select   api.invoice_type_lookup_code,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 

Re: Oracle Payables [message #583604 is a reply to message #583603] Thu, 02 May 2013 03:52 Go to previous messageGo to next message
a_kamalraj
Messages: 18
Registered: May 2013
Location: Bangalore
Junior Member

Hi,

Should be some mistake in the Join of the table.
Please check out the query again for 1 record and try.

Just execute the query for a particular prepayment invoice and then check it out.
if single record works fine then try for some 4 to 5 invoices and you can find out the issue.

Cheers
Kamal
(kamal.love@gmail.com)
Re: Oracle Payables [message #583612 is a reply to message #583603] Thu, 02 May 2013 05:47 Go to previous message
a_kamalraj
Messages: 18
Registered: May 2013
Location: Bangalore
Junior Member

Hi,

Did you get chance to rectify that query from your end...

Cheers
Kamal
(kamal.love@gmail.com)
Previous Topic: purchase order
Next Topic: Oracle payables invoice status
Goto Forum:
  


Current Time: Mon Jul 28 06:08:58 CDT 2014

Total time taken to generate the page: 0.04852 seconds