Query to get opening,purchasing,receive,sales and closing quantity of items
SELECT * FROM (SELECT b.item_code, b.item_description item_description, b.primary_uom, b.sub_inventory, b.LOCATOR, b.opening_qty opening_qty,…
Read MoreQuery for Approval groups
SELECT pcr.control_rule_id, pcga.control_group_name, pcr.object_code, pcr.rule_type_code, pcr.amount_limit, ( segment1_low || ‘.’ || segment2_low …
Read MoreQuery for Approval Assignments
SELECT hou.NAME operating_unit, pj.NAME job, pcf.control_function_name, pcga.control_group_name, TO_CHAR (ppca.start_date, ‘DD-MON-YY’) start_date, TO_CHAR (ppca.end_date, ‘DD-MON-YY’) end_date FROM apps.po_position_controls_all ppca, …
Read MoreQuery to find list of suppliers who has attachments
Often times you might have to find a list of suppliers who has a specific attachment. For example, if you need to find list of suppliers who has attached with…
Read MoreQuery to Link PO, AP, GL, ZX, CE, RCV & XLA
SELECT glimp.je_header_id je_header_id, glimp.je_line_num je_line_num, xal.accounted_cr accounted_cr, xal.accounted_dr accounted_dr, xal.entered_cr entered_cr, xal.entered_dr entered_dr, xah.accounting_date accounting_date, xal.accounting_class_code acct_line_type, xal.code_combination_id code_combination_id, gcc.segment1 || ‘.’…
Read MoreQuery to Link PO,AP,GL,TAX RATE & XLA
SELECT r.je_header_id je_header_id, r.je_line_num je_line_num, 200 application_id, aeh.ledger_id set_of_books_id, ‘INV’ trx_class, l8.displayed_field trx_class_name, l1.displayed_field trx_type_name, i.invoice_type_lookup_code trx_type_c, i.invoice_num invoice_number, i.invoice_num trx_number_displayed, ael.description comments, fd.NAME doc_sequence_name,…
Read MoreAP Pre Payments Query: AP XLA GL
SELECT glcc.segment1 COMPANY, glcc.segment2 LOCATION, glcc.segment3 COST_CENTER, glcc.segment4 ACCOUNT, glcc.segment5 PRODUCT_LINE, glcc.segment6 CHANNEL, glcc.segment7 PROJECT, (SELECT flex_value || ‘ ‘ || fvt.description FROM apps.ap_invoices_all ap1, apps.gl_code_combinations glc, apps.fnd_flex_values fv, apps.fnd_flex_values_tl…
Read MoreAP Distribution Query: AP XLA GL
SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cost_center, glcc.segment4 ACCOUNT, glcc.segment5 product_line, glcc.segment6 channel, glcc.segment7 project, (SELECT flex_value || ‘ ‘ || fvt.description FROM apps.ap_invoices_all ap1, apps.gl_code_combinations glc, apps.fnd_flex_values fv, apps.fnd_flex_values_tl…
Read MoreAR Adjustments Query: AR XLA GL
SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cost_center, glcc.segment4 ACCOUNT, glcc.segment5 product_line, glcc.segment6 channel, glcc.segment7 project, (SELECT flex_value || ‘ ‘ || fvt.description FROM apps.gl_code_combinations glc, apps.fnd_flex_values fv, apps.fnd_flex_values_tl fvt WHERE…
Read MoreAR Transactions Query: AR XLA GL
SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cost_center, glcc.segment4 ACCOUNT, glcc.segment5 product, glcc.segment6 channel, glcc.segment7 project, (SELECT flex_value || ‘ ‘ || fvt.description FROM apps.gl_code_combinations glc, apps.fnd_flex_values fv, apps.fnd_flex_values_tl fvt WHERE…
Read More