Wednesday, March 12, 2014

API to add the responsibility to the user



bEGIN
fnd_user_pkg.addresp(username => 'VINOTH'
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE - 1
,end_date => SYSDATE + 1000);
COMMIT;
END;

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;