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. I am Sridevi Koduru, Senior Oracle Apps Trainer at Oracleappstechnical.com With 8 Yrs Exp on Oracle Apps and 13 Yrs IT Exp Providing Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.

    Contact for (One to One Personal Online Training) on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K at training@oracleappstechnical.com | sridevikoduru@oracleappstechnical.com | +91-9581017828 | http://www.oracleappstechnical.com

    Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/

    ReplyDelete
  3. 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
  4. 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
  5. 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
  6. 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
  7. we have almost all country students as our subscribers for online course.We have 10+ years of experience we can serve various ascent people. oracle fusion Cloud HCM online training at erptree.com is worlds best online training center. we have excelent knowledge sharing Platform we have user friendly website where you
    will be provided with all the required details and Self-paced DEMO videos. we have our branches in pune, gurgaon, noida, india, usa, uk, uae, oracle fusion hcm training, fusion Procurement training, fusion hcm, scm training

    ReplyDelete
  8. 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
  9. 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