Posts by Brahmaiah kolla

Query to get Shipping and Inventory Material Transactions in fusion.

SELECT dha.source_order_number, dha.order_number, dfla.status_code, dfla.source_line_number   FROM  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, fusion.inv_material_txns imt  WHERE 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(+)…

Read More

Query to get Inventory on hand quantity details in fusion.

SELECT esi.inventory_item_id, esi.item_number, esi.organization_id, inv.organization_code, esi.enabled_flag, esi.end_date_active, ohq.transaction_quantity onhand_qty   FROM fusion.egp_system_items_b esi, fusion.inv_org_parameters inv, fusion.inv_onhand_quantities_detail ohq  WHERE  inv.organization_id=esi.organization_id    AND inv.organization_id=ohq.organization_id    AND esi.inventory_item_id=ohq.inventory_item_id

Read More

Query to get the subledger transfer to GL details by period wise

SELECT gjjlv.period_name “Period Name”, gjb.name “Batch Name”, gjjlv.header_name “Journal Entry”, gjjlv.je_source “Source”, glcc.concatenated_segments “Accounts”, gjjlv.line_entered_dr “Entered Debit”, gjjlv.line_entered_cr “Entered Credit”, gjjlv.line_accounted_dr “Accounted Debit”, gjjlv.line_accounted_cr “Accounted Credit”, gjjlv.currency_code “Currency”, arm.name “Payment…

Read More

Query to get the accounting entries details by receipt.

SELECT amount_dr, amount_cr, acctd_amount_dr, acct_amount_cr, gcc.segment1 ||’.’ ||gcc.segment2 ||’.’ ||gcc.segment3 ||’.’ ||gcc.segment4 ||’.’ ||gcc.segment5 FROM ar.ar_distributions_all ad, gl.gl_code_combinations gcc WHERE 1=1 and source_table=’CRH’ and ad.code_combination_id=gcc.code_combination_id and EXISTS( SELECT ‘T’ FROM…

Read More

Query to get FA Mass Additions Details

SELECT DISTINCT fma.book_type_code, fma.posting_status, fma.description, fcb.segmnet1 major_category, fcb.segment2 minor_category, fma.fixed_assets_units, fma.fixed_assets_cost, (select segment3   from fa_locations where location_id=fma.location_id) location, fma.date_placed_in_service, fma.asset_number, fma.asset_key_segment1, fma.asset_key_segment2, invoice_number, po_number, vendor_number, queue_name, invoice_date, payables_cost, depreciate_flag,…

Read More

Query to get Asset Remaining Life

SELECT DISTINCT fb.asset_number, fab.book_type_code, (SELECT   CASE WHEN decode(faab.conversion_date, NULL, faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.prorate_date)), faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.deprn_start_date)))<=0 then 0 ELSE decode(faab.conversion_date, NULL, faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.prorate_date)), faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.deprn_start_date))) END FROM apps.fa_books faab, apps.fa_deprn_periods fdpp, apps.fa_additions_bfbb WHERE faab.asset_id=fbb.asset_id   AND…

Read More

Query to get the tax type and tax accounts details.

SELECT hou.name Entity_name, tax_type_id, tax_type_code, tax_type_name, (SELECT concatenated_segments FROMapps.gl_code_combinations_kfv WHEREcode_combination_id=interim_recovery_ccid) interim_recovery_ccid, (SELECT concatenated_segments FROMapps.gl_code_combinations_kfv WHEREcode_combination_id=recovery_ccid) recovery_ccid, (SELECT concatenated_segments FROMapps.gl_code_combinations_kfv WHEREcode_combination_id=interim_liability_ccid) interim_liability_ccid, (SELECT concatenated_segments FROMapps.gl_code_combinations_kfv WHEREcode_combination_id=liability_ccid) liability_ccid FROM apps.jai_tax_types_v tv, apps.jai_tax_accounts…

Read More

Query to get ship to and Dist location mismatch invoices details.

select distinct hou.name,         a.org_id, a.invoice_id, a.invoice_num, a.invoice_date, a.attribute11 ship_to d.segment4 dist_loc, aps.vendor_name, apsa.vendor_site_code from apps.ap_invoices_all a, apps.ap_invoice_lines_all b, apps.ap_invoice_distributions_all c, apps.gl_code_combinations d, apps.hr_operating_units hou, apps.ap_suppliers aps,…

Read More

Query to get Accounting issue invoice details

select org_id, customer_trx_id, sum(tax) tax, sum(rev) rev, sum(rec) rec, sum(tax)+sum(rev) acutal_rec from ( select org_id, customer_trx_id, case when account_class=’TAX’ then sum(amount) else 0 end tax, case when account_class=’REV’ then sum(amount)…

Read More

Query to get the AR Netting details

select * from (select fnar.batch_id, fnar.customer_trx_id, rct.bill_to_customer_id cust_acct_id, rct.bill_to_site_use_id cust_site_use_id, fnar.ar_txn_rank, nvl(fnar.netted_amt,0) as netted_amt, fnb.batch_currency as reckoning_currency, rct.trx_number, rct.invoice_currency_code as transaction_currency, rctt.description as transaction_type, hp.party_name as cust_acct_name, hca.account_number as cust_acct_number,…

Read More