Using the UTL_INADDR package, a PL/SQL subprogram can determine the host name of the local system or the IP address of a given host name.
E.g. Retrieve the local host name and IP address.
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME); -- get local host name
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS); -- get local IP addr
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS('www.oracle.com'));
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME('141.146.9.91'));
END;
Oracle Order to Cash Queries
select l.line_id, l.flow_status_code , l.open_flag,pr.interface_source_code,pr.interface_source_line_id,pr.note_to_buyer,
pr.note_to_receiver
from
oe_order_lines_all l,
po_requisitions_interface_all pr
where l.line_id = pr.interface_source_line_id
and pr.interface_source_code='CTO'
Query to Join OM and Purchase Order tables for Back 2 Back Order
select ph.segment1,a. supply_source_line_id, a.supply_source_header_id
from
mtl_reservations a,
oe_order_lines_all l,
po_headers_all ph
where demand_source_line_id = &Enter_Order_lineID
and l.line_id = a.demand_source_line_id
and a.supply_source_header_id = ph.po_header_id
Query to Join OM and PO Requisition table for Back 2 Back Order
select ph.segment1,a. supply_source_line_id, a.supply_source_header_id
from
mtl_reservations a,
oe_order_lines_all l,
po_requisition_headers_all pqh
where demand_source_line_id = &Enter_Order_lineID
and l.line_id = a.demand_source_line_id
and a.supply_source_header_id = pqh.requisition_header_id
Query to Join OM , WSH and AR table
SELECT h.order_number,l.line_id,l.ordered_quantity,l.shipped_quantity,l.invoiced_quantity,
wdd.delivery_detail_id,wnd.delivery_id,wdd.shipped_quantity,a.org_id,
a.creation_date ,a.trx_number,b.quantity_ordered , b.quantity_invoiced ,b.interface_line_attribute1,b.interface_line_attribute3,
b.interface_line_attribute6,interface_line_attribute12
from
ra_customer_trx_all a,
ra_customer_trx_lines_all b,
oe_order_headers_all h,
oe_order_lines_all l,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where a.customer_trx_id = b.customer_trx_id
and a.interface_header_context = 'ORDER ENTRY'
and b.interface_line_attribute1 = to_char(h.order_number)
and h.header_id = l.header_id
and to_char(l.line_id) = b.interface_line_attribute6
and l.line_id = wdd.source_line_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and to_char(wnd.delivery_id) = b.interface_line_attribute3
Mapping Between AR and OM (Transaction Flex field)
(RAL) - RA_CUSTOMER_TRX_LINES_ALL
RAL.INTERFACE_LINE_ATTRIBUTE1 Order_Num
RAL.INTERFACE_LINE_ATTRIBUTE2 Order_Type
RAL.INTERFACE_LINE_ATTRIBUTE3 Delivery ID
RAL.INTERFACE_LINE_ATTRIBUTE4 WayBill
RAL.INTERFACE_LINE_ATTRIBUTE6 Line_ID
RAL.INTERFACE_LINE_ATTRIBUTE8 Bill_Lading
RAL.INTERFACE_LINE_ATTRIBUTE10 WH_ID RAL.INTERFACE_LINE_ATTRIBUTE11 PA_ID
Releasing Order Holds
This is an example of removing order holds using OM API
CREATE OR REPLACE PROCEDURE skm_releaseHolds
( p_user_name VARCHAR2
, p_order_num_low NUMBER
, p_order_num_high NUMBER
) IS
l_order_tbl OE_HOLDS_PVT.order_tbl_type;
l_return_status VARCHAR2(5);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
i NUMBER;
l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
CURSOR c_orders_held IS
select /*+ index(hld OE_ORDER_HOLDS_ALL_N1) index(hsrc OE_HOLD_SOURCES_U1)*/
hdr.header_id
, hsrc.hold_source_id
, hsrc.hold_id
from oe_order_headers_all hdr
, oe_order_holds_all hld
, oe_hold_sources_all hsrc
, oe_hold_definitions hdef
where hdr.order_number between p_order_num_low and p_order_num_high
and hdr.header_id = hld.header_id
and hld.hold_source_id = hsrc.hold_source_id
and hsrc.hold_id = hdef.hold_id
;
PROCEDURE get_user_id ( p_user_name VARCHAR2
, p_user_id IN OUT NUMBER
, p_err_msg IN OUT VARCHAR2
) IS
BEGIN
p_err_msg := null;
SELECT user_id
INTO p_user_id
FROM fnd_user
WHERE user_name = p_user_name;
--
--
EXCEPTION
WHEN OTHERS THEN
p_user_id := -1;
p_err_msg := 'procedure Get_user_Id error:'||sqlerrm;
END;
PROCEDURE get_resp_id ( p_resp_key VARCHAR2
, p_resp_id IN OUT NUMBER
, p_resp_appl_id IN OUT NUMBER
, p_err_msg IN OUT VARCHAR2
) IS
BEGIN
SELECT responsibility_id
, application_id
INTO p_resp_id
, p_resp_appl_id
FROM fnd_responsibility
WHERE responsibility_key = p_resp_key;
--
--
EXCEPTION
WHEN OTHERS THEN
p_resp_id := -1;
p_resp_appl_id := -1;
p_err_msg := 'procedure Get_resp_Id error:'||sqlerrm;
END;
PROCEDURE init
( p_user_name VARCHAR2
, p_resp_key VARCHAR2
, px_err_msg IN OUT VARCHAR2
) Is
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
BEGIN
px_err_msg := null;
get_user_id(p_user_name, l_user_id, px_err_msg);
IF px_err_msg IS NOT NULL
THEN
RETURN;
END IF;
--
get_resp_id(p_resp_key, l_resp_id, l_appl_id, px_err_msg);
IF px_err_msg IS NOT NULL
THEN
RETURN;
END IF;
--
--
FND_GLOBAL.apps_initialize(l_user_id, l_resp_id, l_appl_id);
dbms_output.put_line(l_user_id || ','|| l_resp_id || ',' || l_appl_id);
END;
begin
l_return_status := 'S';
OE_MSG_PUB.initialize;
init(p_user_name, 'ORDER_MGMT_SUPER_USER', l_msg_data);
--
--
IF l_msg_data IS NOT NULL
THEN
dbms_output.put_line('init call failed with the following message');
dbms_output.put_line(l_msg_data);
return;
END IF;
--
-- Loop through each order and release hold
--
FOR l_orders_held in c_orders_held
LOOP
l_order_tbl(1).header_id := l_orders_held.header_id;
oe_holds_pub.Release_Holds
( p_api_version => 1.0
, p_order_tbl => l_order_tbl
, p_hold_id => l_orders_held.hold_id
, p_release_reason_code => 'EXPIRE'
, p_release_comment => 'Updated Through release hold API Call'
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
END LOOP;
dbms_output.put_line('Return Status = ' || l_return_status);
dbms_output.put_line('Message Count = ' || l_msg_count);
dbms_output.put_line('Message Data = :' || l_msg_data || ':');
--
-- Look for Any error messages
--
FOR j in 1..OE_MSG_PUB.count_msg
LOOP
OE_MSG_PUB.get
( p_msg_index => j
, p_encoded => 'F'
, p_data => l_msg_data
, p_msg_index_out => i
);
dbms_output.put_line('Error: ' || j || ':' || l_msg_data);
END LOOP;
end skm_releaseHolds ;===================================================================================
Below script will help you to Release Order level or Line Level hold in Oracle Order Management through API OE_HOLDS_PUB.RELEASE_HOLDS
This script was tested in R12.1.1
SET serveroutput ON;
DECLARE
v_return_status VARCHAR2(30);
v_msg_data VARCHAR2(4000);
v_msg_count NUMBER;
v_order_tbl OE_HOLDS_PVT.order_tbl_type;
v_hold_id NUMBER DEFAULT 50;
v_header_id NUMBER DEFAULT 1705;
v_context VARCHAR2 (2);
FUNCTION set_context( i_user_name IN VARCHAR2
,i_resp_name IN VARCHAR2
,i_org_id IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
NULL;
-- In order to reduce the content of the post I moved the implementation part of this function to another post and it is available here
END set_context;
BEGIN
-- Setting the context ----
v_context := set_context ('&user', '&responsibility', 2038);
IF v_context = 'F'
THEN
DBMS_OUTPUT.put_line ('Error while setting the context');
END IF;
--- context done ------------
BEGIN
v_order_tbl(1).header_id := v_header_id;
v_return_status := NULL;
v_msg_data := NULL;
v_msg_count := NULL;
dbms_output.put_line('Calling the API to Release hold' );
OE_HOLDS_PUB.RELEASE_HOLDS (
p_api_version => 1.0,
p_order_tbl => v_order_tbl,
p_hold_id => v_hold_id,
p_release_reason_code => 'AR_AUTOMATIC',
p_release_comment => 'TESTING',
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
IF v_return_status = FND_API.G_RET_STS_SUCCESS THEN
dbms_output.put_line('success:');
COMMIT;
ELSIF v_return_status IS NULL THEN
dbms_output.put_line('Status is null');
ELSE
dbms_output.put_line('Failed: '|| v_msg_data );
FOR i IN 1 .. oe_msg_pub.count_msg
LOOP
v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| v_msg_data);
END LOOP;
ROLLBACK;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error is '||SQLCODE||'---'||SQLERRM);
END; Oracle Apps Stuff in Net
http://georgenet.net/oracle/
http://oracleebusinesssuite.wordpress.com/
http://www.sap-img.com/oracle-database/oracle-application-hints-and-tips.htm
http://garethroberts.blogspot.com/2007/09/standard-report-to-csv-file-via-bi.html
http://oracle-hrms-11i.blogspot.com/
http://www.w3schools.com
http://www.ebusinesslab.cn
http://www.trutek.com/index.php?id=53
http://feeds.feedburner.com/OracleAppsFAQ
http://onlineappsdba.com/index.php/category/identity_manager/
http://www.richardbyrom.com/download.htm
http://www.appsworkshop.com
http://onlineappsdba.blogspot.com/
http://www.admin.ox.ac.uk/financials/helpsheets/
http://oracleqa2.blogspot.com
http://peopleapps.com/
http://aspen.ithaca.edu:7778/portal/page_pageid=133,77462&_dad=portal&_schema=PORTAL
http://www.frp.qut.edu.au/frptoolkit/frptraining/finance/purchasing/enter_req.jsp
http://www.orafaq.com/forum/i/0/
http://www.chain-sys.com/demo_ground.shtml
http://www.ebusinesslab.cn
http://www.sqlmanager.net/en/news/sql/1133
http://blogs.oracle.com/xmlpublisher/2007/08/07
http://advait.wordpress.com/oracle-apps-11i-profiles/
http://www.infocaptor.com/articles/sql.html
http://oracle-applications-rama.blogspot.com/2007_10_01_archive.html
http://www.more4apps.com/?gclid=CKXglPqfhI8CFQh6gwod9zR91w
http://www.appssys.com/
http://oraclecrp.com/
http://www.oracleappsblog.com/index.php/forum
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/ap/invoic13.htm
http://main.uab.edu/show.asp?durki=73096
http://main.uab.edu/show.asp?durki=66289
http://www.learndiscoverer.com/downloads/downloads.htm
https://internet-apps.com/iapps/ic/InvReports.html
https://app.smartturn.com/occam/help/help_en/inv_reports.html
http://www.oracleappshub.com/category/oracle-purchasing/
http://oracle-applications-rama.blogspot.com/
http://www.appsdba.com/
http://www.eas.gwu.edu/home/support/documentation/documentation.cfm
http://www.fin.gov.nl.ca/ComptrollerGeneral/oraclemanuals/purchasing
http://www.fin.gov.nl.ca/ComptrollerGeneral/oraclemanuals/accountspayable/default.htm
http://oraclea2z.blogspot.com
http://www.oraclefans.com/oraclefans/forum/erpfinan/
http://www.erpfans.com/
http://www.teachmeoracle.com/forum/
http://www.esnips.com/_t_/oracle+apps
http://docs.huihoo.com/oracle/docs/B25516_06/current/html/doclist.html
http://www.umec.com.tw/r11i/html/trmdoc.html
http://www.orafaq.com/forum/t/49413/0/
http://erpstuff.com/forum.asp?FORUM_ID=3
http://www.ysn.ru/docs/oracle/workflow.920/
http://www.praetoriate.com/shad_fin9.htm
http://www.praetoriate.com/oracle_tips.htm
http://www.tacticspartners.com
http://www.geocities.com/oracletricks/oraapps/
http://oracle-financials-11i.blogspot.com/
http://www.visualbuilder.com/showCode.php?id=81369&scd_id=2849
http://www.java2s.com/Code/Oracle/PL-SQL/CatalogPL-SQL.htm
http://www.idevelopment.info/data/Programming/plsql/PROGRAMMING_PLSQL_Home.cgi
http://oracleapplications-11i.blogspot.com
http://oracle-hrms-11i.blogspot.com
http://learn-oracle-apps.blogspot.com
http://oracle-aol-11i.blogspot.com
http://oraclehrmsapps.blogspot.com
http://computerebook.blogspot.com/
http://www.oracle.ask2ask.com
http://www.appsdbablog.com
http://blogs.oracle.com/schan/
http://www.filibeto.org/sun/lib/nonsun/oracle/10.2.0.1.0/B19306_01/workflow.102/b15853/T361836T361982.htm
http://www.workflowfaq.com/
http://www.miraclewisdom.com/oracle_applications.htm
http://oracleappstechnology.blogspot.com/2007/07/why-is-it-called-apps-and-not-oracle.html
http://appstechnical.blogspot.com/
http://garethroberts.blogspot.com/2007/08/audit-trail-must-do-bank-accounts.html
http://getappstraining.blogspot.com/2006/10/what-happens-when-you-login-to-apps.html
http://www.coaug.org/presentations.html
http://www.appworx.com/solutions/oracle.cfm
http://www.infocaptor.com/articles/2006/01/view-discoverer-reports-through-oracle.html
http://web.mit.edu/sapr3/windocs/bporb06a.htm
http://www.ctipc.com/Novaware/IC.htm
http://www.virginia.edu/integratedsystem/howdoi/HTML/NAV5537U.htm
http://www.bscaler.com/erm/next_generation_erp.htm
http://www.cougarmtn.com/accounting-software-reports/accounts-payable.asp
http://www.dbtips.com/
http://www.sucharitha.com/
http://www.fors.com/velpuri2/Applications/8.htm
http://bfa.sdsu.edu/~leap/appsupgrade.htm
http://bfa.sdsu.edu/~leap/documentation.htm
http://www.purchasing.upenn.edu/buyinfo/guide/
http://www.acsspr.com/po.html
http://www.frameware2000.com/reports.htm
http://www.virginia.edu/integratedsystem/howdoi/HTML/NAV5515U.htm#ZZZ_TUT_1
http://www.mainstreetasp.com/purchase.htm
http://scripts4oracle.blogspot.com/
http://orafact.com/index_files/Page1023.htm
http://www.erpstuff.com/topic.asp?TOPIC_ID=2353
http://www.eplanetlabs.com/oracle-1Z0-141-Certification.html
http://onlineappsdba.blogspot.com/
Procure To Pay base tables
po_requisition_headers_all
po_requisition_lines_all ( segment1 ========== requisition number)
po_req_distributions_all
Note: requisition_header_id is the link between po_requisition_headers_all and po_requisition_lines_all
requisition_line_id is the link between po_requisition_lines_all and po_req_distributions_all
Purchase order
po_headers_all
po_lines_all
po_distributions_all
po_line_locations_all
Note: po_header_id is the link between all these tables
Receipt
rcv_shipment_headers
rcv_shipment_lines
rcv_transactions
quality result stored in qa_results table
Note: shipment_header_id is the link between rcv_shipment_headers and rcv_shipment_lines
po_header_id is the link between rcv_shipment_headers and rcv_transaction
Invoices
ap_invoices_all
ap_invoice_distributions_all
Note: invoice_id is the link between ap_invoices_all and ap_invoice_distributions_all
Payments
ap_checks_all
ap_invoice_payments_all
ap_payment_schedules_all
Payment Reconcillation
ap_banks
ap_bank_branches
GL Transfer
gl_periods
gl_period_status
gl_set_of_books
glfv_charts_of_accounts
Subscribe to:
Posts (Atom)