Windows File rename: How to rename bulk file through CMD mode
1) Open CMD and go to specific directory
2) type below command to display list of files
dir /b
3) Select all files and copy,
4) Paste the file in Excel and build the dos rename syntax as shown below
6) Multiple bulk files renamed
OPM : Query to get Item wise cost in OPM
SELECT ccdv.inventory_item_id, ccdv.organization_id, ccm.cmpnt_group,
SUM (NVL (cmpnt_cost, 0)) costs
--ccm.cost_cmpntcls_code,
FROM cm_cmpt_dtl_vw ccdv,
cm_cldr_mst_v ccmv,
cm_cmpt_mst ccm,
mtl_parameters mp,
org_organization_definitions ood,
gl_code_combinations gcc,
gmf_fiscal_policies gfp
WHERE 1 = 1
AND ccdv.period_id = ccmv.period_id
AND TRUNC (ccmv.start_date) >= :p_start_date
AND TRUNC (ccmv.end_date) <= :p_end_date
AND ccmv.calendar_code = :p_calendar_code
AND ccdv.cost_cmpntcls_id = ccm.cost_cmpntcls_id
AND ccmv.cost_type_id = gfp.cost_type_id
AND ccdv.organization_id = mp.organization_id
AND mp.organization_id = ood.organization_id
AND mp.material_account = gcc.code_combination_id
AND gcc.segment1 BETWEEN :p_child_low AND :p_child_high
AND (:p_business_unit IS NULL OR gcc.segment2 = :p_business_unit)
AND ood.legal_entity = gfp.legal_entity_id
GROUP BY ccdv.inventory_item_id, ccdv.organization_id, ccm.cmpnt_group
OAF : Migration Command
java oracle.jrad.tools.xml.
RTF Template Function and XML method TAG
1) How to use NVL function in RTF template (XML Publisher)
<?xdofx:nvl(RISF_VC_LY,0)-nvl(RISF_VC_AC,0)?>
How to upload GMD Specification validity for Customer
DECLARE
--l_customer_spec_vrs GMD_CUSTOMER_SPEC_VRS%ROWTYPE;
l_customer_spec_vrs_tbl GMD_SPEC_VRS_PUB.customer_spec_vrs_tbl;
--l_customer_spec_vrs_out GMD_CUSTOMER_SPEC_VRS%ROWTYPE;
x_customer_spec_vrs_tbl GMD_SPEC_VRS_PUB.customer_spec_vrs_tbl;
l_return_status CHAR(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
BEGIN
fnd_profile.initialize (2820);
fnd_global.apps_initialize(user_id =>1142,resp_id =>23805 ,resp_appl_id =>552 );
fnd_msg_pub.initialize;
gme_common_pvt.g_error_count := 0;
gme_common_pvt.set_timestamp;
gme_common_pvt.g_move_to_temp := fnd_api.g_false;
--l_customer_spec_vrs.spec_vr_id := NULL;
l_customer_spec_vrs_tbl(1).spec_id :=12416;--- 12420;
-- l_customer_spec_vrs.ORG_ID := 110;
l_customer_spec_vrs_tbl(1).cust_id := 10988;
l_customer_spec_vrs_tbl(1).ship_to_site_id := NULL;
l_customer_spec_vrs_tbl(1).spec_vr_status := 100;--700
l_customer_spec_vrs_tbl(1).delete_mark := 0;
l_customer_spec_vrs_tbl(1).start_date := SYSDATE;
-- l_customer_spec_vrner_id := q.USER_ID;
l_customer_spec_vrs_tbl(1).creation_date := SYSDATE;
l_customer_spec_vrs_tbl(1).created_by := 1133;
l_customer_spec_vrs_tbl(1).last_updated_by := 1133;
l_customer_spec_vrs_tbl(1).last_update_date := SYSDATE;
l_customer_spec_vrs_tbl(1).last_update_login := 1133;
apps.GMD_SPEC_VRS_PUB.CREATE_CUSTOMER_SPEC_VRS
( p_api_version => '1.0'
, p_init_msg_list => apps.FND_API.G_FALSE
, p_commit => apps.FND_API.G_TRUE
, p_validation_level => apps.fnd_api.g_valid_level_full
, p_customer_spec_vrs_tbl => l_customer_spec_vrs_tbl
, p_user_name => 'ALAM'
, x_customer_spec_vrs_tbl => x_customer_spec_vrs_tbl
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
COMMIT;
dbms_output.put_line( l_customer_spec_vrs_tbl(1).spec_id );
dbms_output.put_line('return_status:-----'||l_return_status);
dbms_output.put_line('Msg_Count:--------'|| l_msg_count||','||l_msg_data);
--dbms_output.put_line('Msg_Count:--------'|| l_msg_count);
--dbms_output.put_line('msg_data ---------'||x_msg_data);
--dbms_output.put_line('spec_id-----------'||x_spec);
--dbms_output.put_line('spec_test_id------'||x_spec_tests_tbl);
IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
THEN
FOR i IN 1..FND_MSG_PUB.COUNT_MSG LOOP
l_msg_data := FND_MSG_PUB.get( p_msg_index => i,
p_encoded => FND_API.G_FALSE
);
dbms_output.put_line('The API call failed with error '||l_msg_data);
END LOOP;
ELSE
dbms_output.put_line('The API call ended with SUCESSS status');
END IF;
END;
To Find DFF Flex field attribute
SELECT ffv.application_table_name, ffv.descriptive_flexfield_name,
ffv.context_column_name,
ffv.title, att.application_column_name,
att.end_user_column_name,
att.column_seq_num, att.enabled_flag,
att.required_flag,
att.security_enabled_flag, att.display_flag,
att.flex_value_set_id,
att.form_left_prompt, ffv.*
FROM fnd_descriptive_flexs_vl
ffv, fnd_descr_flex_col_usage_vl att
WHERE
ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
-- AND
ffv.descriptive_flexfield_name = 'PO_LINES'
AND (ffv.title) =
'Transaction Information'
How to find Operating Units in Oracle Apps
SELECT hr.organization_id org_id, hr.NAME operating_unit,po_moac_utils_pvt.get_ou_shortcode (organization_id) ou_short_code
FROM hr_operating_units hr
WHERE po_moac_utils_pvt.check_access (hr.organization_id) = 'Y'
ORDER BY 1
===========================================================================
#Oracle Apps #EBS #OU #Operating Units
SET
SERVEROUTPUT ON
DECLARE
-- Change the following two parameters
var_templateCode VARCHAR2 (100) :=
'Order_Details'; -- Template Code
boo_deleteDataDef BOOLEAN :=
TRUE; -- delete the associated Data Def.
BEGIN
FOR RS
IN (SELECT T1.APPLICATION_SHORT_NAME
TEMPLATE_APP_NAME,
T1.DATA_SOURCE_CODE,
T2.APPLICATION_SHORT_NAME DEF_APP_NAME
FROM
XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
WHERE
T1.TEMPLATE_CODE = var_templateCode
AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
LOOP
XDO_TEMPLATES_PKG.DELETE_ROW
(RS.TEMPLATE_APP_NAME, var_templateCode);
DELETE FROM XDO_LOBS
WHERE LOB_CODE = var_templateCode
AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');
DELETE FROM XDO_CONFIG_VALUES
WHERE APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND TEMPLATE_CODE = var_templateCode
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 50;
DBMS_OUTPUT.PUT_LINE ('Selected template has
been ' || var_templateCode || ' deleted.');
IF boo_deleteDataDef
THEN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
RS.DATA_SOURCE_CODE);
DELETE FROM XDO_LOBS
WHERE LOB_CODE = RS.DATA_SOURCE_CODE
AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND LOB_TYPE IN
('XML_SCHEMA',
'DATA_TEMPLATE',
'XML_SAMPLE',
'BURSTING_FILE');
DELETE FROM XDO_CONFIG_VALUES
WHERE APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 30;
DBMS_OUTPUT.PUT_LINE (
'Selected
Data Defintion has been ' || RS.DATA_SOURCE_CODE || ' deleted.');
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (
'Unable to delete XML Publisher
Template ' || var_templateCode);
DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;
select a.ITEM_DESCRIPTION,a.*
from ap_invoice_lines_all a
select * from mtl_system_items_b
where description like '%MILK%'
AND ORGANIZATION_ID=103
AND INVENTORY_ITEM_ID =2135
2135 -- MILK (COW)
---------------------PO---------------------------------------------------------
SELECT * FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID=2017
SELECT *FROM PO_LINES_ALL
WHERE ITEM_ID IN ( select INVENTORY_ITEM_ID from mtl_system_items_b
where description like '%MILK%'
AND ORGANIZATION_ID=103)
AND ITEM_ID=2135
AND PO_HEADER_ID=2017
-------------------Receipts-----------------------------------------------------
SELECT * FROM RCV_SHIPMENT_LINES R
WHERE 1=1--ORGANIZATION_ID=103
AND ITEM_ID=2135
AND PO_HEADER_ID=2017
AND PO_LINE_ID=2034
SELECT TRANSACTION_ID,a.* FROM RCV_TRANSACTIONS a
WHERE ORGANIZATION_ID=103
--AND ITEM_ID=2135
AND PO_HEADER_ID=2017
AND SHIPMENT_LINE_ID = 5046;
--------------------MTL ACCOUNTING----------------------------------------------
select TRANSACTION_ID,a.* from mtl_material_transactions a
where a.RCV_TRANSACTION_ID in (2060,2062,2063);
select * from mtl_transaction_accounts
where TRANSACTION_ID =89463
--------------AP INV------------------------------------------------------------
select * from ap_invoices_all
where invoice_id = 35029
select * from ap_invoice_distributions_all aid
where (invoice_id ,aid.INVOICE_LINE_NUMBER) in (
select invoice_id ,ail.LINE_NUMBER from ap_invoice_lines_all ail
where po_header_id=2017
AND PO_LINE_ID=2034)
--------------Sub Ledger Inv ---------------------------------------------------
select *from xla.XLA_TRANSACTION_ENTITIES xte
where 1=1
AND xte.entity_code = 'AP_INVOICES'
and SOURCE_ID_INT_1=35029 --invoice id--
select * from xla_ae_headers
where entity_id=60015
select * from xla_ae_lines
where ae_header_id=71836
-----------------------INV GL---------------------------------------------------
select * from gl_import_references
where gl_sl_link_id in (
select gl_sl_link_id from xla_ae_lines
where ae_header_id=71836 )
select (select gl_flexfields_pkg.get_description_sql (:char_of_account_id, 5, gcc.segment5) FROM gl_code_combinations gcc
where gcc.CODE_COMBINATION_ID =gil.CODE_COMBINATION_ID ) acc_desc
,gil.* from gl_je_lines gil
where je_header_id in (54034)
-------------------AP Pay------------------
select * from ap_invoice_payments_all
where invoice_id=35029
select * from ap_checks_all
where check_id = 33542
--------------Sub Ledger Pay ---------------------------------------------------
select *from xla.XLA_TRANSACTION_ENTITIES xte
where 1=1
AND xte.entity_code = 'AP_PAYMENTS'
and SOURCE_ID_INT_1=33542 --check id--
select * from xla_ae_headers
where entity_id=60079
select gl_sl_link_id,a.* from xla_ae_lines a
where ae_header_id=71837
-----------------------Payments GL---------------------------------------------------
select * from gl_import_references
where gl_sl_link_id in (
select gl_sl_link_id from xla_ae_lines
where ae_header_id in (71837 ,71836))
select (select gl_flexfields_pkg.get_description_sql (:char_of_account_id, 5, gcc.segment5) FROM gl_code_combinations gcc
where gcc.CODE_COMBINATION_ID =gil.CODE_COMBINATION_ID ) acc_desc
,gil.* from gl_je_lines gil
where je_header_id in (54036)
--------------------------------------------------------------------------------
select * from gl_code_combinations_kfv
where code_combination_id in (2012,3001)
select * from ap_suppliers
where vendor_id=2231
1)PO
2)Receipt
a) MTL transaction--->
Inventory Valuation dr
Receiving Inspection cr
3)AP INV DIST(standard Invoice)---
INVENTORY AP ACCRUAL
a) subledger accounting-->
1) Accural A/C Dr
To Liability A/C Cr
4) GL Lines --->
liability ac Dr
Cash Clearing Cr
