SELECT DISTINCT iop.organization_code warehouse_code
, haotl.name warehouse_name
, rt.subinventory
, rah.ra_document_number AS rma_number
, dla.display_line_number line_number
, dla.display_line_number
|| '.'
|| dfla.fulfill_line_number rma_line_number
, rsh.receipt_num rma_receipt_number
, esi.item_number sku
, nvl2(dlsn.item_serial_number_from, 1, rsl.quantity_received) quantity_received
, dlsn.item_serial_number_from
, dlsn.item_serial_number_to
, rt.transaction_id
, rt.transaction_type
, rt.quantity
, rt.uom_code
, ral.receipt_advice_line_number AS doo_fulfil_line_id
, dfld.rma_receipt_date AS rma_receipt_date
, rt.creation_date
, rt.transaction_date
, dha.order_type_code
, DECODE(dha.order_type_code, 'B2BRMA', NVL(substr(dfla.source_line_number,0,instr(dfla.source_line_number,'.')-1),dfla.source_line_number),1) source_line_number
FROM rcv_shipment_headers rah
, rcv_shipment_lines ral
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, rcv_transactions rt
, inv_org_parameters iop
, fusion.hr_organization_units_f_tl haotl
, fusion.hr_org_unit_classifications_f houc
, doo_fulfill_lines_all dfla
, doo_fulfill_line_details dfld
, doo_lot_serial_numbers dlsn
, egp_system_items_b esi
, doo_lines_all dla
, doo_headers_all dha
WHERE 1 = 1
-- AND rah.ra_document_number = '1595254717339306'
AND rt.transaction_type = 'DELIVER'
AND rt.source_document_code = 'RMA'
AND rt.shipment_header_id = rsh.shipment_header_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rt.receipt_advice_header_id = rah.shipment_header_id
AND rt.receipt_advice_line_id = ral.shipment_line_id
AND iop.organization_id = rsl.to_organization_id
AND haotl.organization_id = rsl.to_organization_id
AND haotl.language = userenv('LANG')
AND houc.organization_id = haotl.organization_id
AND houc.classification_code = 'INV'
AND rt.transaction_id = dfld.rma_receipt_transaction_id
AND dfla.fulfill_line_id = dfld.fulfill_line_id
AND upper(dfld.task_type) = 'RETURN'
AND dfla.fulfill_line_id = dlsn.fulfill_line_id (+)
AND dfla.inventory_item_id = esi.inventory_item_id
AND dfla.inventory_organization_id = esi.organization_id
AND dfla.line_id = dla.line_id
AND dla.header_id = dha.header_id
ORDER BY 4
, 5
, 6
Friday, February 26, 2021
SQL to get the RMA Details In Oracle Fusion (Inventory/Order Management)
SQL to get Material Transaction Details (Oracle Fusion Inventory Management)
SQL to get Material Transaction Details
SELECT iop.organization_code warehouse_code
, ittv.transaction_type_name
, flva.meaning transaction_action
, itst.transaction_source_type_name
, esi.item_number
, imt.subinventory_code
, imt.transaction_quantity
, imt.transaction_uom
, imt.primary_quantity
, imt.transaction_date
, imt.transaction_source_id
, imt.transaction_source_name
, imt.rcv_transaction_id
, imt.distribution_account_id
, imt.trx_source_line_id
, imt.trx_source_delivery_id
, imt.rma_line_id
, imt.transfer_transaction_id
, imt.transaction_Set_id
, imt.source_code
, imt.source_line_id
, imt.transfer_organization_id
, imt.transfer_subinventory
, imt.shipment_number
FROM inv_material_txns imt
, inv_org_parameters iop
, egp_system_items_b esi
, inv_transaction_types_vl ittv
, fnd_lookup_values_vl flva
, inv_txn_source_types_vl itst
WHERE imt.organization_id = iop.organization_id
AND imt.organization_id = esi.organization_id
AND imt.inventory_item_id = esi.inventory_item_id
AND imt.transaction_type_id = ittv.transaction_type_id
AND ittv.transaction_action_id = flva.lookup_code
AND flva.lookup_type = 'INV_TRANSACTION_ACTION'
AND flva.enabled_flag = 'Y'
AND ittv.transaction_source_type_id = itst.transaction_source_type_id
AND ittv.transaction_type_name = 'RMA Receipt';
/
Sales Order Issue Materail Transaction Report.
SELECT iop.organization_code warehouse_code
, ittv.transaction_type_name
, flva.meaning transaction_action
, itst.transaction_source_type_name
, esi.item_number
, imt.subinventory_code
, imt.transaction_quantity
, imt.transaction_uom
, imt.primary_quantity
, imt.transaction_date
, imt.transaction_source_id
, imt.transaction_source_name
, imt.rcv_transaction_id
, imt.distribution_account_id
, imt.trx_source_line_id
, imt.trx_source_delivery_id
, imt.rma_line_id
, imt.transfer_transaction_id
, imt.transaction_set_id
, imt.source_code
, imt.source_line_id
, imt.transfer_organization_id
, imt.transfer_subinventory
, imt.shipment_number
, dha.source_order_number
, dha.order_number
, dfla.status_code
, dfla.source_line_number
FROM inv_material_txns imt
, inv_org_parameters iop
, egp_system_items_b esi
, inv_transaction_types_vl ittv
, fnd_lookup_values_vl flva
, inv_txn_source_types_vl itst
, fusion.doo_headers_all dha
, fusion.doo_fulfill_lines_all dfla
, fusion.wsh_delivery_assignments wda
, fusion.wsh_new_deliveries wnd
, fusion.wsh_delivery_details wdd
WHERE imt.organization_id = iop.organization_id
AND imt.organization_id = esi.organization_id
AND imt.inventory_item_id = esi.inventory_item_id
AND imt.transaction_type_id = ittv.transaction_type_id
AND ittv.transaction_action_id = flva.lookup_code
AND flva.lookup_type = 'INV_TRANSACTION_ACTION'
AND flva.enabled_flag = 'Y'
AND ittv.transaction_source_type_id = itst.transaction_source_type_id
AND ittv.transaction_type_name LIKE 'Sales Order Issue'
AND dha.header_id = dfla.header_id
AND dfla.fulfill_line_id = wdd.source_shipment_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id (+)
AND wda.delivery_detail_id = imt.trx_source_line_id (+)
AND dha.submitted_flag = 'Y'
/
Monday, December 14, 2020
Oracle Fusion: Anonymous blocks (Procedural Call) data model in Oracle BI Publisher (Oracle Fusion)
BI Publisher supports executing PL/SQL anonymous blocks. You can perform calculations in the PL/SQL block and return the result set. BI Publisher uses callable statements to execute anonymous blocks.
The requirements are:
- The PL/SQL block must return a result set of type REF cursor
- You must declare the out variable with the name, xdo_cursor;. This needs to be the first parameter in position in Data model.
- If you do not declare the name properly, the first bind variable is treated as an out variable type and binds with REF cursor.
- Declare the data model parameter with name xdo_cursor. This name is reserved for out variable type for procedure/anonymous blocks.
Sample Script:
Wednesday, November 18, 2020
How to Enable Audit Trail On Tables
a. Responsibility: System Administrator
Navigation: Profile > System
Query Profile: 'AuditTrail:Activate'. Click FIND
Set it to 'Yes' at Site level.
b) Enable Audit Installations (AR)
Navigation: System Admin > Security > Audit Trail >Install
Enable Audit Installation for AR
c) Define Audit tables and desired columns.
Navigation: System Admin > Security > Audit Trail > Tables
Query for user table name 'HZ_PARTIES' and add columns on which you want to enable trail
do the same for table HZ_CUST_ACCOUNTS
d) Define an Audit Group and associated tables
Navigation: System Admin > Security > Audit Trail >Groups
Create Audit group for table defined in 'C'
e) Run Concurrent program 'AuditTrail Report for Audit Group Validation' with parameter as your
Audit Group.
f) Define an Industry Template contain Audit Group
Navigation: System Admin >Security > Audit Trail Reporting >Audit Industry Template
g) Run concurrent program "AuditTrail Update Tables".
h) Confirm existence of Audit tables (_A).
i) Run Audit report from SysAdmin menus.
Navigation: Security > AuditTrai > Audit Trail Reporting > Audit Report
Above request will fire a concurrent request whose output can be used for Audit reporting.
Oracle Metalink Ref ID : 848039.1
Oracle Receivables - Version 11.5.10.0 and later
Oracle Application Object Library - Version 12.1.3 and later
Wednesday, March 18, 2020
Wednesday, February 6, 2019
Script to Get Application user password from backend
CREATE OR REPLACE PACKAGE get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_pwd;
/
--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/
--Query to execute
SELECT usr.user_name,
get_pwd.decrypt
((SELECT (SELECT get_pwd.decrypt
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name =
(SELECT SUBSTR
(fnd_web_sec.get_guest_username_pwd,
1,
INSTR
(fnd_web_sec.get_guest_username_pwd,
'/'
)
- 1
)
FROM DUAL)),
usr.encrypted_user_password
) PASSWORD
FROM fnd_user usr
WHERE usr.user_name = :p_user_name;
Wednesday, May 9, 2018
How to Use an API to Delete An Order Level Modifier From a Sales Order
How to Use an API to Delete An Order Level Modifier From a Sales Order (Doc ID 2191611.1)
SET SERVEROUTPUT ON;
DECLARE
/* Initialize the proper Context */
l_org_id NUMBER := 204; --ENTER VALUE
l_application_id NUMBER := 660;
l_responsibility_id NUMBER := 21623; --ENTER VALUE
l_user_id NUMBER := 1013419; --ENTER VALUE
/* Initialize the record to G_MISS to enable defaulting */
l_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
l_old_header_rec oe_order_pub.header_rec_type;
l_header_adj_tbl oe_order_pub.header_adj_tbl_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_old_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.request_tbl_type;
x_header_rec oe_order_pub.header_rec_type;
x_header_val_rec oe_order_pub.header_val_rec_type;
x_header_adj_tbl oe_order_pub.header_adj_tbl_type;
x_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;
x_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
x_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;
x_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;
x_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
x_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type;
x_line_tbl oe_order_pub.line_tbl_type;
x_line_val_tbl oe_order_pub.line_val_tbl_type;
x_line_adj_tbl oe_order_pub.line_adj_tbl_type;
x_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type;
x_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
x_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;
x_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;
x_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
x_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type;
x_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;
x_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type;
x_action_request_tbl oe_order_pub.request_tbl_type;
l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_line_cnt NUMBER := 0;
l_top_model_line_index NUMBER;
l_link_to_line_index NUMBER;
x_debug_file VARCHAR2(100);
BEGIN
/* Turn on DBMS Output */
DBMS_OUTPUT.enable(1000000);
DBMS_OUTPUT.put_line('Executing Process Order API for R12');
mo_global.init('ONT');
mo_global.set_policy_context('S'
, l_org_id);
fnd_global.apps_initialize(l_user_id
, l_responsibility_id
, l_application_id
, NULL);
oe_msg_pub.initialize;
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(5);
x_debug_file := oe_debug_pub.set_debug_mode('FILE');
DBMS_OUTPUT.put_line('START OF NEW DEBUG');
l_header_rec := oe_order_pub.g_miss_header_rec;
l_header_rec.header_id := 431776; --ENTER VALUE
l_header_rec.operation := oe_globals.g_opr_update;
l_header_adj_tbl(1) := oe_order_pub.g_miss_header_adj_rec;
l_header_adj_tbl(1).operation := oe_globals.g_opr_delete;
l_header_adj_tbl(1).header_id := 431776; --ENTER VALUE
l_header_adj_tbl(1).price_adjustment_id := 3994447; --ENTER VALUE
/* Call the Process Order API with Header Rec and Line Tbl */
oe_order_pub.process_order(p_api_version_number => 1
, p_org_id => l_org_id
, -- For R12
p_init_msg_list => fnd_api.g_true
, p_return_values => fnd_api.g_true
, p_action_commit => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_action_request_tbl => l_action_request_tbl
, p_header_rec => l_header_rec
, p_old_header_rec => l_old_header_rec
, p_header_adj_tbl => l_header_adj_tbl
, p_line_tbl => l_line_tbl
, p_old_line_tbl => l_old_line_tbl
, x_header_rec => x_header_rec
, x_header_val_rec => x_header_val_rec
, x_header_adj_tbl => x_header_adj_tbl
, x_header_adj_val_tbl => x_header_adj_val_tbl
, x_header_price_att_tbl => x_header_price_att_tbl
, x_header_adj_att_tbl => x_header_adj_att_tbl
, x_header_adj_assoc_tbl => x_header_adj_assoc_tbl
, x_header_scredit_tbl => x_header_scredit_tbl
, x_header_scredit_val_tbl => x_header_scredit_val_tbl
, x_line_tbl => x_line_tbl
, x_line_val_tbl => x_line_val_tbl
, x_line_adj_tbl => x_line_adj_tbl
, x_line_adj_val_tbl => x_line_adj_val_tbl
, x_line_price_att_tbl => x_line_price_att_tbl
, x_line_adj_att_tbl => x_line_adj_att_tbl
, x_line_adj_assoc_tbl => x_line_adj_assoc_tbl
, x_line_scredit_tbl => x_line_scredit_tbl
, x_line_scredit_val_tbl => x_line_scredit_val_tbl
, x_lot_serial_tbl => x_lot_serial_tbl
, x_lot_serial_val_tbl => x_lot_serial_val_tbl
, x_action_request_tbl => x_action_request_tbl);
DBMS_OUTPUT.put_line( 'OM Debug file:'
|| oe_debug_pub.g_dir
|| '/'
|| oe_debug_pub.g_file);
IF l_return_status = 'S'
THEN
DBMS_OUTPUT.put_line('CONGRATULATIONS! !');
DBMS_OUTPUT.put_line('Commit Issued ... !');
COMMIT;
ELSE
DBMS_OUTPUT.put_line('Hard Luck! Error(s) while attempting to delete Order Level Modifier');
END IF;
FOR i IN 1 .. oe_msg_pub.g_msg_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line(apps.oe_msg_pub.get(i
, 'F'));
END LOOP;
oe_msg_pub.g_msg_tbl.delete;
/* Turn off OM Debug */
oe_debug_pub.debug_off;
END;
/