Friday, February 28, 2014

OM to AR and SC to AR Linking queries

OM to AR Table linkage
==================
SELECT ooha.order_number
     , oola.line_number so_line_number
     , oola.ordered_item
     , oola.ordered_quantity * oola.unit_selling_price so_extended_price
     , rcta.trx_number invoice_number
     , rcta.trx_date
     , rctla.line_number inv_line_number
     , rctla.unit_selling_price inv_unit_selling_price
FROM   oe_order_headers_all ooha
     , oe_order_lines_all oola
     , ra_customer_trx_all rcta
     , ra_customer_trx_lines_all rctla
WHERE  ooha.header_id = oola.header_id
AND    rcta.customer_trx_id = rctla.customer_trx_id
AND    rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND    RCTLA.INTERFACE_LINE_ATTRIBUTE1 = TO_CHAR (OOHA.ORDER_NUMBER)
AND    order_number =  :p_order_number;

SC to AR
=======
SELECT * FROM APPS.RA_INTERFACE_LINES_ALL WHERE BATCH_SOURCE_NAME = 'OKS_CONTRACTS'
AND INTERFACE_LINE_ATTRIBUTE1 = :p_contract_number;

SC Billing details
==============
SELECT hdr.contract_number "Contract"
, hdr.contract_number_modifier "Modifier"
, hdr.id
, TO_CHAR( cont.creation_date, 'DD-MON-YYYY HH24:MI') "Creation Date"
, bill_action
, cont.btn_id "Billing Transaction ID"
, amount
, TO_CHAR( DATE_BILLED_FROM, 'DD-MON-YYYY' ) "Date Billed From"
, TO_CHAR( DATE_BILLED_TO, 'DD-MON-YYYY' ) "Date Billed To"
, obt.trx_date
, obt.trx_amount
, obt.trx_number
, obt.trx_amount
FROM apps.oks_bill_cont_lines cont
, apps.okc_k_lines_b line
, apps.okc_k_headers_b hdr
, apps.oks_bill_transactions obt
, APPS.oks_bill_txn_lines obtl
WHERE hdr.id = line.dnz_chr_id
AND cont.cle_id = line.id
AND HDR.CONTRACT_number =:p_contract_number
AND CONT.BTN_ID = OBT.ID (+)
AND OBTL.BTN_ID (+) = CONT.BTN_ID
ORDER BY CONT.CREATION_DATE;

9 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Its impressive to know something about your note on Oracle apps Course. Please do share your articles like this your articles for our awareness. Mostly we do also provide Online Training on Cub training oracle apps course.

    ReplyDelete
  3. Very interesting notes on ebusiness Suite
    Please do share your articles like this.This link also useful to Find info On Oracle apps-jobs,interview tips,Resume preparation,functional issuesOracle eBusiness Suite

    ReplyDelete
  4. www.oracleonlinetraining.com is providing Oracle APPS Functional and Technical

    training contact us:+91-9533456356, by real time experts in USA,UK,Canada,

    Australia.Oracle apps finance online training,oracle Apps SCM online

    training,Oracle apps technical online training,oracle Apps 11g online

    training,Oracle apps manufacturing training,Oracle Apps Supply Chain Management training

    full course details please visit our website http://oracleonlinetraining.com/



    ReplyDelete
  5. Hi,
    Thanks for sharing the information with us,i am new to this site but i am impressed by your posts and the way you doing it.i have some information oracle R12 in below link.
    Thank you.

    oracle R12 training

    ReplyDelete
  6. we are the leading oracle fusion financials training institutes search engine. we have huge database regarding oracle institutions present in india. we can provide
    complete institute details as we don't have money charge policy for the information provided


    Oracle fusion Financials Training in hyderabad

    Oracle Fusion Financials online Training in hyderabad

    ReplyDelete
  7. It was an excellent article to hear from you which is very useful.
    Oracle Financials Training in Hyderabad

    ReplyDelete
  8. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    oracle Fusion Technical online training

    ReplyDelete