Important Oracle Database related SQL Query
select INSTANCE_NAME from v$instance;
select USER_ID from ALL_USERS Order by 1
1) Run the below SQL Query.
SELECT /* ORACLE SQL */ * FROM dual;
2) Run below query to get above ran SQL ID.
SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
FROM v$sql
WHERE sql_text like 'SELECT /* ORACLE SQL */%'
------Here is the Output---------------
SQL_ID SQL_TEXT
------------- ----------------------------------------
0xzpkrs5gktjs SELECT /* ORACLE SQL */ * FROM dual
APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2
)
Preparing a Nested Query to read XML FILE from Appl Server.
select Hdrxml.TRX_ID, Hdrxml.BILL_TO_NAME, Hdrxml.TRX_NUMBER,Hdrxml.G_LINE,linexml.*
from
(SELECT XMLTYPE(bfilename('XX_AR_INV_OUT', 'o87864385Copy.out'), nls_charset_id('UTF8')) xml_data
FROM dual) a, xmltable('/XX_AR_INV_XML_GEN/LIST_G_INV/G_INV' passing a.xml_data
columns TRX_ID NUMBER path 'TRX_ID',
BILL_TO_NAME VARCHAR2(1000) path 'BILL_TO_NAME',
TRX_NUMBER VARCHAR2(1000) path 'TRX_NUMBER' ,
G_LINE XMLTYPE PATH 'LIST_G_LINE/G_LINE'
) Hdrxml,
xmltable('/G_LINE' passing Hdrxml.G_LINE
columns LINE_NUMBER NUMBER path 'LINE_NUMBER',
ITEM_NUMBER VARCHAR2(1000) path 'ITEM_NUMBER',
QUANTITY_SHIPPED VARCHAR2(1000) path 'QUANTITY_SHIPPED',
UNIT_PRICE VARCHAR2(1000) path 'UNIT_PRICE',
UOM_CODE VARCHAR2(1000) path 'UOM_CODE'
) linexml
;
select PAYMENT_PROCESS_REQUEST_NAME,COUNT(PAYMENT_CURRENCY_CODE)
from (
select PAYMENT_PROCESS_REQUEST_NAME, PAYMENT_CURRENCY_CODE
from apps.iby_paymentS_all
--where PAYMENT_PROCESS_REQUEST_NAME='FAITRAD'
group by PAYMENT_PROCESS_REQUEST_NAME, PAYMENT_CURRENCY_CODE
) xx
group by PAYMENT_PROCESS_REQUEST_NAME--, PAYMENT_CURRENCY_CODE
having count(PAYMENT_PROCESS_REQUEST_NAME) > 1
order by 1
select PAYMENT_METHOD_CODE,PAYMENT_PROCESS_PROFILE, CURRENCY_CODE, BANK_NAME,Bank_Account_Num,
BANK_ACCOUNT_NAME,BANK_BRANCH_NAME,OU_NAME,PAYEE_COUNTRY
from (
Select aba.Bank_Account_Num Bank_Account_Num, aba.BANK_ACCOUNT_NAME Bank_account_name,aba.bank_account_id bank_account_id,
abb.BANK_BRANCH_NAME,
(select bank_name from ce_banks_v where PK_ID =aba.bank_id) bank_name,
(select name from hr_operating_units where organization_id = ac.org_id ) ou_name,
(select NLS_TERRITORY from fnd_territories where TERRITORY_CODE = ac.COUNTRY) payee_country,
ac.org_id,ac.PAYMENT_METHOD_CODE, ac.CURRENCY_CODE,ac.COUNTRY,
(select ispp.SYSTEM_PROFILE_NAME from iby_acct_pmt_profiles_b ibyac,IBY_SYS_PMT_PROFILES_VL ispp
where PAYMENT_PROFILE_ID =ac.PAYMENT_PROFILE_ID
and ibyac.SYSTEM_PROFILE_CODE=ispp.SYSTEM_PROFILE_CODE) payment_process_profile,ac.check_number
From AP_CHECKS_ALL ac,
CE_PAYMENT_DOCUMENTS acs,
CE_BANK_ACCOUNTS aba,
CE_BANK_BRANCHES_V abb
WHERE 1 = 1
--and ac.PAYMENT_METHOD_CODE = 'CHECK'
and ac.STATUS_LOOKUP_CODE = 'NEGOTIABLE'
AND ac.PAYMENT_DOCUMENT_ID = acs.PAYMENT_DOCUMENT_ID
AND acs.INTERNAL_BANK_ACCOUNT_ID = aba.bank_account_id
And abb.BRANCH_PARTY_ID = aba.Bank_Branch_Id
And upper(ac.Status_Lookup_Code) = upper('NEGOTIABLE')
and ac.BANK_ACCOUNT_NAME ='BOA USD ALL'
and trunc(ac.creation_Date) >= '31-AUG-2014'
) xx
group by PAYMENT_METHOD_CODE,PAYMENT_PROCESS_PROFILE, CURRENCY_CODE, BANK_NAME,Bank_Account_Num,
BANK_ACCOUNT_NAME,BANK_BRANCH_NAME,OU_NAME,PAYEE_COUNTRY
GL
select name,currency_code,CHART_OF_ACCOUNTS_ID,
gl_flexfields_pkg.get_coa_name(CHART_OF_ACCOUNTS_ID) COA
from gl_ledgers where name in ('XXG18_500_USD');
SELECT '1REQ' TYPE, prh.requisition_header_id header_id,
prh.segment1 num, prd.budget_account_id ccid,
TO_CHAR (prd.last_update_date, 'YYYYMMDDHH24MISS'),
prd.last_update_date last_update_date,
SUM (NVL (prd.encumbered_amount, 0)) amount,prh.authorization_status status
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prh.requisition_header_id =prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND prh.authorization_status in ('APPROVED','IN PROCESS')
AND NVL (prh.cancel_flag, 'N') = 'N'
---- AND prd.budget_account_id = v_code_combination_id
AND prd.budget_account_id IN (SELECT detail_code_combination_id
FROM gl_account_hierarchies
WHERE summary_code_combination_id = v_code_combination_id
AND ledger_id = 2041
UNION
SELECT v_code_combination_id FROM DUAL)
AND prd.org_id = v_org_id
GROUP BY prh.requisition_header_id,
prh.segment1,
prd.budget_account_id,
TO_CHAR (prd.last_update_date, 'YYYYMMDDHH24MISS'),
prd.last_update_date,prh.authorization_status
UNION ALL
SELECT '2PO' TYPE, poh.po_header_id header_id,
poh.segment1 num, pda.budget_account_id,
TO_CHAR (pda.last_update_date, 'YYYYMMDDHH24MISS'),
pda.last_update_date last_update_date,
SUM (NVL (pda.encumbered_amount, 0)) amount,poh.authorization_status status
FROM po_headers_all poh, po_distributions_all pda
WHERE poh.po_header_id = pda.po_header_id
AND pda.org_id = v_org_id
AND poh.authorization_status in ('APPROVED','IN PROCESS')
AND NVL (poh.cancel_flag, 'N') = 'N'
----- AND pda.budget_account_id = v_code_combination_id
AND pda.budget_account_id IN (SELECT detail_code_combination_id
FROM gl_account_hierarchies
WHERE summary_code_combination_id = v_code_combination_id
AND ledger_id = 2041
UNION
SELECT v_code_combination_id FROM DUAL)
AND NOT EXISTS ( --added to exclude the PO match Inovice --
SELECT 1
FROM ap_invoices_all aia, ap_invoice_lines_all apia
WHERE aia.invoice_id = apia.invoice_id
AND aia.org_id = apia.org_id
AND apia.po_header_id = pda.po_header_id
AND apia.org_id = pda.org_id
AND ap_invoices_utility_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) = 'APPROVED'
union --added on 31mar17 to exclude po prepay invoice --
SELECT 1
FROM ap_invoices_all aia
where aia.quick_po_header_id=pda.po_header_id
and aia.org_id=pda.org_id
)
GROUP BY poh.po_header_id,
poh.segment1,
pda.budget_account_id,
TO_CHAR (pda.last_update_date, 'YYYYMMDDHH24MISS'),
pda.last_update_date,poh.authorization_status
UNION ALL
SELECT '3INV' TYPE, aia.invoice_id header_id,
aia.invoice_num num,
aid.dist_code_combination_id ccid,
TO_CHAR (aid.last_update_date, 'YYYYMMDDHH24MISS'),
aid.last_update_date last_update_date,
round(SUM(NVL ((aid.amount*nvl(aia.EXCHANGE_RATE,1)), 0)),2) amount,
'APPROVED' status
FROM ap_invoices_all aia,
ap_invoice_distributions_all aid
WHERE aia.invoice_id = aid.invoice_id
AND aia.org_id = aid.org_id
---AND aid.dist_code_combination_id = v_code_combination_id
AND aid.dist_code_combination_id IN (SELECT detail_code_combination_id
FROM gl_account_hierarchies
WHERE summary_code_combination_id = v_code_combination_id
AND ledger_id = 2041
UNION
SELECT v_code_combination_id FROM DUAL)
AND aia.org_id = v_org_id
AND aia.cancelled_date IS NULL
and aid.invoice_id not in ( 504841)------added on 29AUG16. For Special Case Journal has been made against invoice---
AND apps.ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) IN ('APPROVED', 'FULL', 'UNPAID','AVAILABLE')
GROUP BY aia.invoice_id,
aia.invoice_num,
aid.dist_code_combination_id,
TO_CHAR (aid.last_update_date, 'YYYYMMDDHH24MISS'),
aid.last_update_date,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code) main
WHERE 1 = 1
--AND main.header_id NOT IN v_header_id
AND TO_CHAR (last_update_date, 'YYYYMMDDHH24MISS') <=v_last_date
group by main.status
---Employee Tab
select * from per_all_people_f where EMPLOYEE_NUMBER ='12'
---Employee Assignment Tab
select * from per_assignments_f where person_id=35347
---Payroll Entries Tab
select * from pay_element_entries_f where ASSIGNMENT_ID= 3539 and ELEMENT_TYPE_ID=6523
select nvl(pet1.retro_summ_ele_id, pet1.element_type_id) from pay_element_types_f pet1 where pet1.element_type_id = 647;
pay_process_events;
PAY_RETRO_ASSIGNMENTS
PAY_RETRO_ENTRIES
pay_in_tax_utils.get_balance_value
PAY_RETRO_PKG
/********* Internal Sales Order & Sale Order Query***/
SELECT TO_CHAR (ooha.order_number) order_num,ml.meaning supply_demand_type,d.requirement_date required_date, oola.line_id, oola.request_date, wda.delivery_id,
NVL ( -1 * ( d.primary_uom_quantity - d.total_reservation_quantity - d.completed_quantity ), 0 ) qty,
FROM mtl_parameters p,
mtl_system_items_b i,
bom_calendar_dates c,
mrp_demand_om_reservations_v d,
oe_order_headers_all ooha,
oe_order_lines_all oola,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mfg_lookups ml,
(SELECT DECODE (demand_source_type,
2, DECODE (reservation_type, 1, 2, 3, 23, 9),
8, DECODE (reservation_type, 1, 21, 22),
demand_source_type
) supply_demand_source_type,
demand_id
FROM mrp_demand_om_reservations_v) dx
WHERE d.open_flag = 'Y'
AND ml.lookup_type = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'
AND ml.lookup_code = dx.supply_demand_source_type
AND d.demand_id = dx.demand_id
AND ooha.header_id = oola.header_id
AND oola.line_id = d.demand_id
AND wdd.source_line_id(+) = oola.line_id
AND wdd.source_header_id(+) = oola.header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
AND d.reservation_type != 2
AND d.organization_id = :p_org_id
AND d.primary_uom_quantity >
(d.total_reservation_quantity + d.completed_quantity
)
AND d.inventory_item_id = :p_inventory_item_id
AND ( d.visible_demand_flag = 'Y'
OR ( NVL (d.visible_demand_flag, 'N') = 'N'
AND d.ato_line_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM oe_order_lines_all ool, mtl_demand md
WHERE TO_CHAR (ool.line_id) = md.demand_source_line
AND ool.ato_line_id = d.ato_line_id
AND ool.item_type_code = 'CONFIG'
AND md.reservation_type IN (2, 3))
)
)
AND d.reservation_type != -1
AND d.reservation_type != -1
AND d.demand_source_type != -1
AND d.demand_source_type != -1
AND TRUNC (d.requirement_date) <= :p_to_date
AND ( d.subinventory IS NULL
OR d.subinventory IN (
SELECT s.secondary_inventory_name
FROM mtl_secondary_inventories s
WHERE s.organization_id = d.organization_id
AND s.inventory_atp_code = 1
AND s.attribute1 = 'FG')
)
AND i.organization_id = d.organization_id
AND i.inventory_item_id = d.inventory_item_id
AND p.organization_id = d.organization_id
AND p.calendar_code = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND c.calendar_date = TRUNC (d.requirement_date)
AND d.inventory_item_id =
DECODE (d.reservation_type,
1, DECODE (d.parent_demand_id,
NULL, d.inventory_item_id,
-1
),
2, d.inventory_item_id,
3, d.inventory_item_id,
-1
)
