Saturday, February 1, 2025

Query to get the Concurrent Program along with Value Set details

SELECT fcpl1.user_concurrent_program_name
      ,fdfcuv.end_user_column_name
      ,ffvs.flex_value_set_name value_set_name
      ,ffvt.application_table_name
  ,ffvt.value_column_name
      ,ffvt.additional_where_clause
  FROM apps.fnd_concurrent_programs fcp1
      ,apps.fnd_descr_flex_col_usage_vl fdfcuv
      ,apps.fnd_flex_value_sets ffvs
      ,apps.fnd_flex_validation_tables ffvt
      ,apps.fnd_concurrent_programs_tl fcpl1
 WHERE 1 = 1     
   AND fcp1.concurrent_program_id IN (SELECT DISTINCT fcp.concurrent_program_id
                                        FROM apps.fnd_concurrent_programs fcp
                                            ,apps.fnd_concurrent_programs_tl fcpl
                                            ,apps.fnd_concurrent_requests fcr
                                       WHERE 1 = 1
                                         AND fcp.concurrent_program_id = fcpl.concurrent_program_id
                                         AND fcpl.language = 'US'
                                         AND fcr.concurrent_program_id = fcpl.concurrent_program_id
                                         AND trunc(fcr.request_date) BETWEEN :p_from_date AND :p_to_date
                                         AND fcp.concurrent_program_name LIKE '%XXCPNAME%'
)
   AND fdfcuv.descriptive_flexfield_name = '$SRS$.' ||fcp1.concurrent_program_name
   AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
   AND ffvt.flex_value_set_id = ffvs.flex_value_set_id
   AND fcp1.concurrent_program_id = fcpl1.concurrent_program_id
ORDER BY fcpl1.user_concurrent_program_name 

Monday, January 27, 2025

Query to get Unposted Journals in Oracle APPS R12

SELECT gjh.accrual_rev_flag accrual_rev_flag
      ,gjh.accrual_rev_period_name accrual_rev_period_name
  ,gjh.creation_date journal_creation_date
  ,gjh.currency_code currency_code
  ,gjh.doc_sequence_value doc_sequence_value
  ,fu.email_address email_address
  ,gjh.je_category je_category
  ,gjh.last_update_date last_update_date
  ,gjh.name name journal_header_name
  ,gsobv.name ledger_name
  ,gjh.period_name period_name
  ,gjh.posted_date posted_date
  ,gjh.reversed_je_header_id reversed_je_header_id
  ,gjh.running_total_accounted_dr running_total_accounted_dr
  ,gjh.status status
  ,fu.user_name user_name
  ,gjsv.user_je_source_name user_je_source_name
  ,fu1.user_name user_name
  ,gjbv.name journal_batch_name
  ,gjbv.posted_by_name posted_by_name
  ,ppx.email_address email_address
  ,period.period_set
  ,CASE WHEN ( ( MIN(fad.attached_document_id) ) IS NOT NULL OR ( MIN(fad1.attached_document_id) ) IS NOT NULL ) 
        THEN 'Y' 
ELSE 'N' 
END document_exists
  FROM applsys.fnd_user fu
      ,gl.gl_je_headers gjh
  ,apps.gl_sets_of_books_v gsobv
  ,applsys.fnd_user fu1
  ,apps.gl_je_sources_v gjsv
  ,apps.gl_je_batches_v gjbv
  ,apps.per_people_x ppx
  ,applsys.fnd_attached_documents fad
  ,applsys.fnd_attached_documents fad1
  ,(SELECT gpv.period_name AS period_name
          ,MAX((gpv.period_set_name||gpv.period_year||LPAD(gpv.period_num,2,0))) AS period_set 
  FROM apps.gl_periods_v gpv 
WHERE (gpv.period_year >= 2024) 
GROUP BY gpv.period_name
) period
 WHERE ( (gsobv.set_of_books_id = gjh.ledger_id)
   AND (fu1.employee_id = ppx.person_id(+))
   AND (gjh.last_updated_by = fu.user_id(+))
   AND (gjh.created_by = fu1.user_id(+))
   AND (gjh.je_batch_id = gjbv.je_batch_id(+))
   AND ((TO_CHAR(gjbv.je_batch_id)) = fad.pk1_value(+))
   AND ((TO_CHAR(gjh.je_header_id)) = fad1.pk2_value(+))
   AND (gjh.je_source = gjsv.je_source_name)
   AND (gjh.period_name = period.period_name(+)))
   AND (fad.entity_name(+) = 'GL_JE_BATCHES')
   AND (fad1.entity_name(+) = 'GL_JE_HEADERS')
   AND (gjh.posted_date >= :p_posted_date)
   AND (gjsv.user_je_source_name IN (:p_source))
   AND (gjh.status IN ('U'))
   AND (gjh.period_name = :p_period_name)
GROUP BY period.period_set
        ,gjh.accrual_rev_flag
,gjh.accrual_rev_period_name
,gjh.creation_date
,gjh.currency_code
,gjh.doc_sequence_value
,fu.email_address,gjh.je_category
,gjh.last_update_date
,gjh.name
,gsobv.name
,gjh.period_name
,gjh.posted_date
,gjh.reversed_je_header_id
,gjh.running_total_accounted_dr
,gjh.running_total_dr
,gjh.status
,fu.user_name
,gjsv.user_je_source_name
,fu1.user_name
,gjbv.name
,gjbv.posted_by_name
,ppx.email_address
ORDER BY gsobv.name ASC 
        ,period.period_set ASC 
,gjsv.user_je_source_name ASC 
,gjh.doc_sequence_value ASC 

Sunday, January 26, 2025

Query to get Employee Responsibility details in Oracle Fusion

SELECT papf.person_number
      ,ppnfv.display_name person_display_name
  ,paam.assignment_name
  ,hl.meaning AS representative_type
  ,par.asg_responsibility_id
  ,par.responsibility_name
  ,par.business_unit_id
  ,par.work_contacts_flag
  FROM per_asg_responsibilities par
      ,per_all_people_f papf
  ,per_all_assignments_m paam
  ,per_person_names_f_v ppnfv
  ,hcm_lookups hl
 WHERE par.person_id = papf.person_id
   AND par.assignment_id = paam.assignment_id
   AND par.person_id = ppnfv.person_id
   AND par.responsibility_type = hl.lookup_code
   AND hl.lookup_type = 'PER_RESPONSIBILITY_TYPES'
   AND par.status = 'Active'
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppnfv.effective_start_date AND ppnfv.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN par.start_date AND NVL(par.end_date,TO_DATE('31-12-4712','DD-MM-YYYY'))
ORDER BY papf.person_number

Employee assigned Responsibilities in Oracle Fusion

SELECT (SELECT DISTINCT person_number
          FROM per_all_people_f per
         WHERE per.person_id = aor.person_id
       ) person_number
  ,(SELECT DISTINCT full_name
          FROM per_person_names_f per
         WHERE per.person_id = aor.person_id
   AND name_type = 'GLOBAL'
    ) person_name
  ,aor.responsibility_name
  ,TO_CHAR(aor.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') start_date
  ,TO_CHAR(aor.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') end_date
  ,aor.responsibility_type
  ,aor.status
  ,(SELECT hou.name
          FROM hr_all_organization_units hou
         WHERE hou.organization_id = aor.business_unit_id
       ) business_unit_name
  ,(SELECT hou.name
          FROM hr_all_organization_units hou
         WHERE hou.organization_id = aor.legal_entity_id
        ) legal_entity
  ,(SELECT hou.name
          FROM hr_all_organization_units hou
         WHERE hou.organization_id = aor.organization_id
        ) department_name
      ,(SELECT location_name
          FROM hr_locations hl
         WHERE hl.location_id = aor.location_id
        ) location_name
  ,(SELECT DISTINCT name
          FROM hr_all_positions_f_vl pp
         WHERE pp.position_id = aor.position_id
        ) position_name
  ,(SELECT DISTINCT name
          FROM per_jobs_f_tl pj
         WHERE pj.job_id = aor.job_id
        ) job_name
  ,(SELECT DISTINCT name
          FROM per_grades_f_tl pg
         WHERE pg.grade_id = aor.grade_id
        ) grade_name
  ,aor.assignment_category
  ,(SELECT pay.payroll_name
          FROM pay_all_payrolls_f pay
         WHERE pay.payroll_id = aor.payroll_id
           AND TRUNC(SYSDATE) BETWEEN pay.effective_start_date AND pay.effective_end_date
        ) payroll_name
  ,(SELECT name
          FROM per_legislative_data_groups_vl pld
         WHERE pld.legislative_data_group_id = aor.legislative_data_group_id
            ) legislative_data_group
  FROM per_asg_responsibilities aor

Thursday, January 23, 2025

Business Classification in Oracle Supplier R12 - POS_BUSINESS_CLASSIFICATIONS

Query:
SELECT * 
  FROM fnd_lookup_values_vl flv
 WHERE lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'
   AND flv.enabled_flag='Y' 
   --AND lookup_code = '8010'
   AND flv.start_date_active < sysdate 
   AND (flv.end_date_active > sysdate or flv.end_date_active is null) 


Application Developer->Application->Lookups->Application Object Library 

Query Type with POS_BUSINESS_CLASSIFICATIONS

SQL Query To Get Invoice Validation And Accounting Status For The Given Check Number (Doc ID 2975480.1)

SELECT aca.check_number
      ,aia.invoice_id
  ,aia.invoice_num
  ,aia.invoice_amount
  ,distribution_line_number
  ,aida.accrual_posted_flag
  ,decode(aida.match_status_flag,
              'A', 'Validated',
              'N', 'Never Validated',
              'T', 'Needs Revalidation',
              'S', 'Stopped') "Invoice Validation Status"
  ,decode(ap_invoices_pkg.get_posting_status(aia.invoice_id),
              'S', 'Selected for Accounting',
              'P', 'Partial',
              'N', 'Unaccounted',
              'Y', 'Accounted',
              'Unaccounted') "Invoice Accounting Status",
      aida.invoice_distribution_id,
      aida.accounting_event_id,
      apps.ap_invoices_pkg.get_approval_status(aia.invoice_id,
                                               aia.invoice_amount,
                                               aia.payment_status_flag,
                                               aia.invoice_type_lookup_code)"Invoice Approval Status",
      decode(aida.posted_flag,
              'Y', 'Posted',
              'N', 'Not Posted',
              'Not Posted') "Invoice Posting Status"   
FROM ap_checks_all aca
    ,ap_invoice_payments_all aipa
,ap_invoices_all aia
,ap_suppliers asa
,ap_invoice_distributions_all aida
WHERE aca.check_id = aipa.check_id
AND aipa.invoice_id = aia.invoice_id
AND aia.vendor_id = asa.vendor_id
AND aida.invoice_id = aia.invoice_id
AND aca.check_number = <pass check number>

Friday, January 17, 2025

How To Populate the Phone Number Field in Oracle Loans? (Doc ID 2990762.1)

SELECT party.party_name Borrower_Name
      ,org.name operating_unit
  ,loan.loan_number
  ,contact_person.party_name contact_person_name
  ,con_phone.raw_phone_number contact_phone_number
  FROM hz_parties party
      ,lns_loan_headers_all loan
  ,hr_all_organization_units_tl org
  ,hz_parties contact_person
  ,hz_contact_points con_phone
 WHERE party.party_id = loan.primary_borrower_id
   AND org.organization_id = loan.org_id
   AND loan.contact_pers_party_id = contact_person.party_id(+)
   AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
   AND con_phone.owner_table_id(+) = loan.contact_rel_party_id
   AND con_phone.primary_flag(+) = 'Y'
   AND con_phone.status(+) = 'A'
   AND con_phone.contact_point_type(+) = 'PHONE'
   AND loan.loan_id = :p_loan_id

Thursday, January 16, 2025

Query for Outstanding Card Transactions Report (Doc ID 2755945.1) in Fusion Expenses

SELECT TRUNC(SYSDATE - e.start_date) "No. of Days"
      ,papf.person_number employee_number
  ,hzp.person_first_name emp_first_name
  ,hzp.person_last_name emp_last_name
  ,e.assignment_id
  ,ecp.card_program_name AS card_program_name
  ,cc.card_program_id AS card_prog_id
  ,e.org_id AS org_id
  ,e.emp_default_cost_center AS cost_ctr
  ,cc.company_account_id AS company_acct_id
  ,e.merchant_name
  ,ec.card_reference_id
  ,icc.masked_cc_number credit_card_num
  ,cc.description description
  ,e.emp_default_cost_center AS cost_center
  ,eed.segment1
  ,eed.segment2
  ,eed.segment3
  ,eed.segment4
  ,eed.segment5
  ,cc.billed_date
  ,cc.billed_amount
  ,cc.posted_date
  ,cc.posted_amount
  ,cc.transaction_date
  ,cc.transaction_amount
  ,e.orig_reimbursable_amount
  ,DECODE(er.expense_status_code, NULL, 'EMPLOYEE',
       'PEND_MGR_APPROVAL', 'APPROVER',
       'SUBMITTED', 'APPROVER',
       'PEND_IND_APPROVAL', 'EMPLOYEE',
       'MGR_REJECTED', 'EMPLOYEE',
       'IND_REJECTED', 'EMPLOYEE',
       'SAVED', 'EMPLOYEE',
       'WITHDRAWN', 'EMPLOYEE',
       'REQUEST_INFO', 'EMPLOYEE',
       'EMPLOYEE') AS pending_action
  ,ca.payment_currency_code AS currency_code
  FROM fusion.exm_expenses e
      ,fusion.exm_expense_Reports er
      ,fusion.exm_credit_card_trxns cc
      ,fusion.exm_cc_company_accounts ca
      ,fusion.exm_cards ec
      ,fusion.per_all_people_f papf
      ,fusion.IBY_CREDITCARD ICC
      ,fusion.hz_parties hzp
      ,fusion.PER_USERS pu
      ,fusion.EXM_EXPENSE_DISTS eed
      ,fusion.EXM_CARD_PROGRAMS ecp
 WHERE (e.expense_source = 'CREDIT_CARD' or e.expense_source = 'BUSINESS_TRAVEL')
   AND e.expense_report_id = er.expense_report_id(+)
   AND er.expense_status_code(+) NOT IN ('PAID', 'PARTIAL_PAID', 'APPROVAL_COMPLETE', 'INVOICED', 'INVOICE_CANCELED')
   AND (e.expense_report_id IS NULL OR er.expense_report_id IS NOT NULL)
   AND (e.itemization_parent_expense_id IS NULL OR e.itemization_parent_expense_id = -1)
   AND e.credit_card_trxn_id = cc.credit_card_trxn_id
   AND cc.company_account_id = ca.cc_company_account_id(+)
   AND ec.card_id(+) = cc.card_id
   AND NVL(ec.account_type_code,'EMPLOYEE') <> 'COMPANY'
   AND papf.person_id = e.person_id
   AND pu.person_id = e.person_id
   AND ec.card_reference_id = icc.instrid
   AND pu.user_guid = hzp.user_guid(+)
   AND e.expense_id = eed.expense_id
   -- and papf.person_number in ( 1984678,1984999,1978237,1992954,1986569, 1984999 )
   AND ecp.card_program_id = cc.card_program_id
   AND expense_report_num like 'EXP%6386164%'
GROUP BY TRUNC(SYSDATE - cc.billed_date)
        ,TRUNC(SYSDATE - e.start_date)
,papf.person_number
,hzp.person_first_name 
,hzp.person_last_name 
,e.assignment_id
,ecp.card_program_name
,cc.card_program_id
,e.org_id
,e.emp_default_cost_center
,cc.company_account_id
,e.merchant_name
,ec.card_reference_id
,icc.masked_cc_number
,cc.description
,e.emp_default_cost_center
,eed.segment1
,eed.segment2
,eed.segment3
,eed.segment4
,eed.segment5
,cc.billed_date
,cc.billed_amount
,cc.posted_date
,cc.posted_amount
,cc.transaction_date
,cc.transaction_amount
,e.orig_reimbursable_amount
,DECODE(er.expense_status_code, NULL, 'EMPLOYEE',
         'PEND_MGR_APPROVAL', 'APPROVER',
         'SUBMITTED', 'APPROVER',
         'PEND_IND_APPROVAL', 'EMPLOYEE',
         'MGR_REJECTED', 'EMPLOYEE',
         'IND_REJECTED', 'EMPLOYEE',
         'SAVED', 'EMPLOYEE',
         'WITHDRAWN', 'EMPLOYEE',
         'REQUEST_INFO', 'EMPLOYEE',
         'EMPLOYEE')
,ca.payment_currency_code
ORDER BY 1 DESC

Query to get the Approver Details for Expense Report (Doc ID 2279634.1) in Oracle Fusion

SELECT er.expense_report_id
      ,er.expense_report_num
      ,er.expense_report_total
      ,er.reimbursement_currency_code
      ,er.person_id
      ,er.expense_report_date
      ,er.expense_status_code
      ,approval.event
      ,approval.event_performer_id
      ,approval.event_date
      ,approval.approval_level
      ,approval.expense_status_code approval_status
      ,approval.audit_code
      ,approval.audit_return_reason_code
      ,approval.export_reject_code
  FROM exm_expense_reports er
      ,exm_exp_rep_processing approval
 WHERE er.expense_report_id= approval.expense_report_id
   AND er.expense_report_num='EXP00000123456'
ORDER BY approval.approval_level

Wednesday, January 8, 2025

Query to get Parent and Child Accounts in Oracle APPS R12

SELECT ffv1.flex_value parent_account
      ,ffvt1.description parent_account_desc
  ,ffv2.flex_value child_account
  ,ffvt2.description child_account_desc
  FROM fnd_flex_values_tl ffvt2
      ,fnd_flex_values ffv2
      ,fnd_flex_value_hierarchies ffvh
      ,fnd_flex_values_tl ffvt1
      ,fnd_flex_values ffv1
      ,fnd_id_flex_segments fifs
 WHERE fifs.application_id = 101
   AND fifs.id_flex_code = 'GL#'
   AND fifs.id_flex_num = 101
   AND fifs.application_column_name = 'SEGMENT4'
   AND fifs.enabled_flag = 'Y'
   AND ffv1.enabled_flag = 'Y'
   AND ffv1.summary_flag = 'Y'
   AND ffv1.flex_value != 'T'
   AND ffv1.flex_value_set_id = fifs.flex_value_set_id + 0
   AND NVL (TO_CHAR (ffv1.start_date_active, 'YYYY/MM/DD'), '0000/00/00') <= TO_CHAR (SYSDATE, 'YYYY/MM/DD')
   AND NVL (TO_CHAR (ffv1.end_date_active, 'YYYY/MM/DD'), '9999/99/99') >= TO_CHAR (SYSDATE, 'YYYY/MM/DD')
   AND ffvt1.flex_value_id = ffv1.flex_value_id + 0
   AND ffvt1.language = USERENV ('LANG')
   AND ffvh.flex_value_set_id = fifs.flex_value_set_id + 0
   AND ffvh.parent_flex_value = ffv1.flex_value
   AND ffv2.summary_flag = 'N'
   AND NVL (TO_CHAR (ffv2.start_date_active, 'YYYY/MM/DD'), '0000/00/00') <= TO_CHAR (SYSDATE, 'YYYY/MM/DD')
   AND NVL (TO_CHAR (ffv2.end_date_active, 'YYYY/MM/DD'), '9999/99/99') >= TO_CHAR (SYSDATE, 'YYYY/MM/DD')
   AND ffv2.flex_value_set_id + 0 = fifs.flex_value_set_id + 0
   AND ffv2.flex_value BETWEEN ffvh.child_flex_value_low AND ffvh.child_flex_value_high
   AND ffv2.enabled_flag = 'Y'
   AND ffvt2.flex_value_id = ffv2.flex_value_id + 0
   AND ffvt2.language = USERENV ('LANG')
ORDER BY ffv1.flex_value, ffv2.flex_value

Friday, January 3, 2025

Query to get Employee Assignment Status in Oracle Fusion

SELECT papf.person_number
      ,pastt.user_status assignment_status
  FROM per_all_people_f papf
      ,per_all_assignments_m paam
  ,per_assignment_status_types past
  ,per_assignment_status_types_tl pastt
 WHERE papf.person_id = paam.person_id
   AND paam.assignment_status_type_id = past.assignment_status_type_id
   AND past.assignment_status_type_id = pastt.assignment_status_type_id
   AND pastt.source_lang = USERENV('LANG')
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND paam.primary_assignment_flag = 'Y'
   AND paam.assignment_type = 'E'
   and paam.effective_latest_change = 'Y'
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN past.start_date AND NVL(past.end_date,SYSDATE)
   AND papf.person_number = nvl(:p_person_number,papf.person_number)
ORDER BY papf.person_number asc
        ,pastt.user_status 

Query to get Element Entry details in Oracle Fusion HCM

SELECT peef.* 
  FROM per_all_people_f papf
      ,pay_element_entries_f peef
  ,pay_element_types_f pet
 WHERE peef.element_type_id = pet.element_type_id
   AND papf.person_id = peef.person_id
   AND papf.person_number = 'E-12345'
   AND peef.creator_type = 'BEN'
   AND pet.base_element_name= 'XX_ELEMENT_NAME'  

Mandatory Input value for HCM Elements in Oracle Fusion

SELECT petf.element_type_id
      ,pettl.element_name
      ,petf.processing_type Recurring_NonRecurring
      ,petf.effective_start_date
      ,petf.effective_end_date
      ,petf.multiple_entries_allowed_flag
      ,pivf.base_name
      ,pivf.mandatory_flag
  FROM pay_element_types_f petf
      ,pay_element_types_tl pettl
      ,pay_input_values_f pivf
 WHERE petf.element_type_id = pettl.element_type_id
   AND pivf.element_type_id=petf.element_type_id
   AND pettl.element_name = 'XX_ELEMENT_NAME'
   AND pettl.language=USERENV('LANG')
   AND pivf.user_enterable_flag='Y'

Query to get Grade Rate details in Oracle ERP

SELECT prft.name "ratename"
      ,pg.name "gradename"
      ,prvf.minimum
      ,prvf.maximum
      ,prvf.mid_value
      ,prft.effective_start_date "ab"
      ,prvf.effective_start_date "cd"
      ,pg.effective_start_date "ef"
  FROM per_rates_f_tl    prft
      ,per_rate_values_f prvf
      ,per_grades        pg
 WHERE prft.rate_id        = prvf.rate_id
   AND prvf.rate_object_id = pg.grade_id
   AND prvf.rate_object_type = 'GRADE'
   AND prft.name IN ('XX Rate Name')
   AND PRFT.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')
   AND TRUNC(SYSDATE) BETWEEN prft.effective_start_date AND prft.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN prvf.effective_start_date AND prvf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN pg.effective_start_date AND pg.effective_end_date
ORDER BY prft.name
       , pg.name

Exclude Freight from Discount(Supplier) in Oracle APPS R12

SELECT aps.vendor_name
      ,aps.exclude_freight_from_discount vend_excd
  ,apss.vendor_site_code
  ,apss.exclude_freight_from_discount site_excd
  FROM ap_suppliers aps
      ,ap_supplier_sites_all apss
 WHERE aps.vendor_id = apss.vendor_id
   AND apss.exclude_freight_from_discount IS NULL
   AND aps.exclude_freight_from_discount IS NULL
   --AND aps.vendor_id NOT IN (1001)
   

SQL query to get list of Jurisdictions for which Tax Rates has been defined in Oracle APPS R12

SELECT zj.tax
      ,zj.tax_jurisdiction_code
  ,hzg.geography_element2_code state_code
  ,hzg.geography_element3_code county_code
  ,hzg.geography_element4_code city_code
  FROM zx_jurisdictions_b zj
      ,hz_geographies hzg
 WHERE zj.tax_regime_codE='XX_SALE_TAX'
   AND SYSDATE BETWEEN zj.effective_from AND NVL(zj.effective_to,'31-DEC-4999')
   AND SYSDATE BETWEEN hzg.start_date AND hzg.end_date
   AND zj.zone_geography_id=hzg.geography_id
   AND zj.tax=hzg.geography_type
   AND NOT EXISTS (SELECT 1 
                     FROM zx_rates_b zr
                    WHERE zr.tax_regime_code='XX_SALE_TAX'
                      AND zr.tax_jurisdiction_code=zj.tax_jurisdiction_code
   )
ORDER BY zj.tax
        ,zj.tax_jurisdiction_code
    ,hzg.geography_element2_code
,hzg.geography_element3_code
,hzg.geography_element4_code

Query to get Customer Address that doesn't have Geography Reference in Oracle APPS R12

SELECT hca.account_number
      ,hca.account_name
      ,hcs_ship.site_use_code
      ,hl_ship.address1
      ,hl_ship.state
      ,hl_ship.county
      ,hl_ship.city
      ,hl_ship.postal_code
  FROM hz_cust_site_uses_all hcs_ship
      ,hz_cust_acct_sites_all hca_ship
      ,hz_cust_accounts hca
      ,hz_party_sites hps_ship
      ,hz_locations hl_ship
 WHERE hca.cust_account_id=hca_ship.cust_account_id(+)
   AND hcs_ship.cust_acct_site_id(+) = hca_ship.cust_acct_site_id
   AND hca_ship.party_site_id = hps_ship.party_site_id
   AND hps_ship.location_id = hl_ship.location_id
   AND hca.status='A'
   AND hcs_ship.status='A'
   AND hca_ship.status='A'
   AND hl_ship.country='US'
   --AND hca.account_number='1234567890'
   AND NOT EXISTS (SELECT 1 
                     FROM hz_geographies hg
                    WHERE hg.geography_element2_code=hl_ship.state
                      AND UPPER(hl_ship.county)=UPPER(hg.geography_element3_code)
                      AND UPPER(hl_ship.city)=UPPER(hg.geography_element4_code)
                      AND SYSDATE BETWEEN hg.start_date AND hg.end_date
  )

Thursday, January 2, 2025

Query to get Errored or Warning Concurrent Program Request details in R12

SELECT fu.user_name
      ,fcpt.user_concurrent_program_name
  ,fcpt.description
  ,fcp.concurrent_program_name
  ,fcr.request_id
  ,((fcr.actual_completion_date
         -DECODE (TRUNC(fcr.request_date)
         ,fcr.requested_start_date
,fcr.request_date
,fcr.requested_start_date
))*60*24) wait_time_min
      ,DECODE(fcr.phase_code,'C','Completed'
                        ,'R','Running'
,fcr.phase_code
) phase_code
  ,DECODE(fcr.status_code,'C','Completed'
                         ,'R','Running'
,'W','Paused'
,'E','Error'
,'G','Warning'
,fcr.status_code
) status_code
  ,TO_CHAR(fcr.request_date,'DD/MM/YYYY HH24:MI:SS') request_date
  ,TO_CHAR(fcr.requested_start_date,'DD/MM/YYYY HH24:MI:SS') start_time
  ,TO_CHAR(fcr.actual_completion_date,'DD/MM/YYYY HH24:MI:SS') complete_time
  FROM fnd_concurrent_requests fcr
      ,fnd_concurrent_programs fcp
  ,fnd_concurrent_programs_tl fcpt
  ,fnd_user fu
 WHERE 1=1
   AND fcp.concurrent_program_id=fcr.concurrent_program_id
   AND fcpt.concurrent_program_id=fcp.concurrent_program_id
   AND fcr.requested_by = fu.user_id
   AND TRUNC(fcr.request_date) BETWEEN SYSDATE - 5 AND SYSDATE
   AND fcr.status_code IN ('E','G')
ORDER BY fcr.status_code
        ,fcp.concurrent_program_name
,fcr.request_id

fnd_concurrent_requests.status_code in Oracle APPS R12(CP_STATUS_CODE)

SELECT lookup_code
      ,meaning 
  FROM fnd_lookups 
 WHERE lookup_type = 'CP_STATUS_CODE'
ORDER BY lookup_code

SQL Query to find Request Set details in R12

SELECT frt.responsibility_name
      ,frg.request_group_name
  ,frgu.request_unit_type
  ,frgu.request_unit_id
  ,fcpt.user_request_set_name
 FROM apps.fnd_Responsibility fr
     ,apps.fnd_responsibility_tl frt
,apps.fnd_request_groups frg
,apps.fnd_request_group_units frgu
,apps.fnd_request_Sets_tl fcpt
 WHERE frt.responsibility_id = fr.responsibility_id
   AND frg.request_group_id = fr.request_group_id
   AND frgu.request_group_id = frg.request_group_id
   AND fcpt.request_set_id = frgu.request_unit_id
   AND frt.language = USERENV('LANG')
   AND fcpt.language = USERENV('LANG')
   AND fcpt.user_request_set_name = :p_request_set_name
order by frt.responsibility_name
      ,frg.request_group_name
  ,frgu.request_unit_type

SQL Query to check Concurrent Program details in R12

SELECT frt.responsibility_name
      ,frg.request_group_name
  ,frgu.request_unit_type
  ,frgu.request_unit_id
  ,fcpt.user_concurrent_program_name
  FROM fnd_Responsibility fr
      ,fnd_responsibility_tl frt
  ,fnd_request_groups frg
  ,fnd_request_group_units frgu
  ,fnd_concurrent_programs_tl fcpt
 WHERE frt.responsibility_id = fr.responsibility_id
   AND frg.request_group_id = fr.request_group_id
   AND frgu.request_group_id = frg.request_group_id
   AND fcpt.concurrent_program_id = frgu.request_unit_id
   AND frt.language = USERENV('LANG')
   AND fcpt.language = USERENV('LANG')
   AND fcpt.user_concurrent_program_name = :p_conc_program_name
ORDER BY frt.responsibility_name
      ,frg.request_group_name
  ,frgu.request_unit_type

SQL Query to check Concurrent Requests SID & OS Process Details for given Request ID in R12

SELECT fcr.request_id
      ,fcr.argument_text
      ,fcr.phase_code
  ,fcr.status_code
  ,fcr.oracle_process_id
  ,fcr.os_process_id
  ,f.user_name
  FROM fnd_concurrent_requests fcr
      ,fnd_user f 
WHERE fcr.request_id = :p_request_id
  AND fcr.requested_by=f.user_id

SQL Query to Check Currently Running Concurrent Requests in Oracle APPS R12

SELECT fcr.request_id
      ,fcr.oracle_process_id
  ,fcr.os_process_id
  ,fcp.user_concurrent_program_name
  ,fu.user_name
  ,fcr.status_code
  ,fcr.phase_code
  ,vsp.username
  ,vsp.sid
  ,vsp.serial#
  ,vsp.program
  ,vsw.event
  ,TO_CHAR(fcr.actual_start_date,'MON-DD-HH-MI-SS') actual_start_date
  ,TO_CHAR(fcr.actual_completion_date,'MON-DD-HH-MI-SS') actual_completion_date
  FROM fnd_concurrent_requests fcr
      ,(SELECT c.username
          ,c.sid
  ,c.serial#
  ,c.program
  ,d.spid 
  FROM v$session c
      ,v$process d
         WHERE c.paddr=d.addr
) vsp
  ,fnd_concurrent_programs_tl fcp
  ,fnd_user fu
  ,v$session_wait vsw
WHERE fcr.oracle_process_id=vsp.spid
  AND fcr.concurrent_program_id=fcp.concurrent_program_id
  AND fcp.language=USERENV('LANG')
  AND fcr.requested_by=fu.user_id
  AND vsp.sid=vsw.sid
  AND fcr.status_code='R'
  AND fcr.phase_code='R'

Wednesday, January 1, 2025

Query to get History of Concurrent Requests which are errored out in Oracle APPS R12

SELECT a.request_id request_id
      ,a.phase_code
  ,a.status_code
      ,actual_start_date
      ,actual_completion_date
      ,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name concurrent_program_name
  FROM fnd_Concurrent_requests a
      ,fnd_concurrent_processes b
      ,fnd_concurrent_queues q
      ,fnd_concurrent_programs c
      ,fnd_concurrent_programs_tl ctl
 WHERE a.controlling_manager = b.concurrent_process_id
   AND a.concurrent_program_id = c.concurrent_program_id
   AND a.program_application_id = c.application_id
   AND a.status_code = 'E'
   AND a.phase_code = 'C'
   AND actual_start_date > SYSDATE - 10
   AND b.queue_application_id = q.application_id
   AND b.concurrent_queue_id = q.concurrent_queue_id
   AND ctl.concurrent_program_id = c.concurrent_program_id
   AND ctl.LANGUAGE = USERENV('LANG')
ORDER BY actual_completion_date

Query to get the Running History of Concurrent Programs in Oracle APPS R12

SELECT fcr.oracle_session_id o_sid
      ,ROUND (( ( NVL (fcr.actual_completion_date, SYSDATE)- fcr.actual_start_date)* 60* 24),2) runtime_min
      ,fcr.request_id request_id
      ,fcr.requested_by requested_by
      ,fu.user_name user_name
      ,fr.responsibility_name
      ,fcr.concurrent_program_id concurrent_program_id
      ,fcp.user_concurrent_program_name concurrent_program_name
      ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') start_time
      ,DECODE (fcr.status_code ,'C', 'C:Completed','G', 'G:Warning','E', 'E:Error','Q','Q:Queued',fcr.status_code) status
      ,TO_CHAR (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') end_time
      ,ROUND (( ( NVL (fcr.actual_completion_date, SYSDATE)- fcr.actual_start_date)* 60* 60* 24),2) run_time_in_sec
      ,fcr.oracle_process_id "oracle_pid/SPID"
      ,fcr.os_process_id os_pid
      ,fcr.argument_text
      ,fcr.outfile_name
      ,fcr.logfile_name
      ,fcr.enable_trace
  FROM fnd_concurrent_requests fcr
      ,fnd_user fu
      ,fnd_responsibility_tl fr
      ,fnd_concurrent_programs_tl fcp
 WHERE fcp.user_concurrent_program_name LIKE 'XX Concurrent Program Name%'
    AND fr.responsibility_id = fcr.responsibility_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND fu.user_id = fcr.requested_by
   --AND fcr.request_id = 1234567890
ORDER BY fcr.concurrent_program_id
        ,request_id DESC

Query to get Long Running Concurrent Programs in R12

SELECT fcr.oracle_session_id
      ,fcr.request_id rqst_id
      ,fcr.requested_by rqst_by
      ,fu.user_name
      ,fr.responsibility_name
      ,fcr.concurrent_program_id cp_id
      ,fcp.user_concurrent_program_name cp_name
      ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') act_start_datetime
      ,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status
      ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_in_min
      ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 60 * 24), 2)runtime_in_sec
      ,fcr.oracle_process_id "oracle_pid/SPID"
      ,fcr.os_process_id os_pid
      ,fcr.argument_text
      ,fcr.outfile_name
      ,fcr.logfile_name
      ,fcr.enable_trace
  FROM fnd_concurrent_requests fcr
      ,fnd_user fu
      ,fnd_responsibility_tl fr
      ,fnd_concurrent_programs_tl fcp
 WHERE fcr.status_code = 'R'
   AND fu.user_id = fcr.requested_by
   AND fr.responsibility_id = fcr.responsibility_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) > 60
   ORDER BY fcr.concurrent_program_id
           ,request_id DESC

Query to get Trace file Including SID in Oracle APPS R12

SELECT 'Request id: '||request_id 
      ,'Trace id: '||oracle_Process_id
  ,'Trace Flag: '||req.enable_trace
  ,'Trace Name: '||dest.value||'/'||LOWER(dbnm.value)
                 ||'_ora_'||oracle_process_id||'.trc', 'Prog. Name: '
                     ||prog.user_concurrent_program_name
  ,'File Name: '||execname.execution_file_name|| execname.subroutine_name 
  ,'Status : '||DECODE(phase_code,'R','Running') ||'-'|| DECODE(status_code,'R','Normal')
  ,'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module 
  FROM fnd_concurrent_requests req
      ,v$session ses
  ,v$process proc
  ,v$parameter dest
  ,v$parameter dbnm
  ,fnd_concurrent_programs_vl prog
  ,fnd_executables execname 
 WHERE req.request_id = :p_request_id
   AND req.oracle_process_id=proc.spid(+)
   AND proc.addr = ses.paddr(+) 
   AND dest.name='user_dump_dest' 
   AND dbnm.name='db_name'
   AND req.concurrent_program_id = prog.concurrent_program_id 
   AND req.program_application_id = prog.application_id
   AND prog.application_id = execname.application_id 
   AND prog.executable_id=execname.executable_id

SQL to find out the Raw Trace Name and Location for the Concurrent Program

SELECT req.request_id
      ,req.logfile_node_name node
      ,req.oracle_Process_id
      ,req.enable_trace
      ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
      ,prog.user_concurrent_program_name
      ,execname.execution_file_name
      ,execname.subroutine_name
      ,phase_code
      ,status_code
      ,ses.SID
      ,ses.serial#
      ,ses.module
      ,ses.machine
  FROM fnd_concurrent_requests req
      ,v$session ses
      ,v$process proc
      ,v$parameter dest
      ,v$parameter dbnm
      ,fnd_concurrent_programs_vl prog
      ,fnd_executables execname
 WHERE 1=1
   AND req.request_id = :p_request_id
   AND req.oracle_process_id = proc.spid(+)
   AND proc.addr = ses.paddr(+)
   AND dest.name='user_dump_dest'
   AND dbnm.name='db_name'
   AND req.concurrent_program_id = prog.concurrent_program_id
   AND req.program_application_id = prog.application_id
   AND prog.application_id = execname.application_id
   AND prog.executable_id=execname.executable_id

Query to get Concurrent Manager Status in R12

SELECT q.concurrent_queue_id
      ,q.concurrent_queue_name
  ,q.user_concurrent_queue_name
  ,q.target_node
  ,q.max_processes
  ,q.running_processes
  ,running.run running
  ,pending.pend
  ,DECODE(q.control_code, 'D', 'Deactivating'
, 'E', 'Deactivated'
, 'N', 'Node unavai'
, 'A', 'Activating'
, 'X', 'Terminated'
, 'T', 'Terminating'
, 'V', 'Verifying'
, 'O', 'Suspending'
, 'P', 'Suspended'
, 'Q', 'Resuming'
, 'R', 'Restarting') status
  FROM (SELECT concurrent_queue_name
              ,COUNT(phase_code) run
          FROM fnd_concurrent_worker_requests
         WHERE phase_code = 'R'
           AND hold_flag != 'Y'
           AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name
) running
   ,(SELECT concurrent_queue_name
           ,COUNT(phase_code) pend 
   FROM fnd_concurrent_worker_requests 
  WHERE phase_code = 'P' 
    AND hold_flag != 'Y' 
AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name
    ) pending
   ,apps.fnd_concurrent_queues_vl q
 WHERE q.concurrent_queue_name = running.concurrent_queue_name(+) 
   AND q.concurrent_queue_name = pending.concurrent_queue_name(+) 
   AND q.enabled_flag = 'Y' 
ORDER BY DECODE(q.application_id, 0, DECODE(q.concurrent_queue_id, 1, 1,4, 2))
     ,SIGN(q.max_processes) DESC
,q.concurrent_queue_name
,q.application_id

SQL Query to get the Responsibility/Request Group attached for a given Concurrent Program

SELECT DISTINCT frt.responsibility_name ,frg.request_group_name ,frgu.request_unit_type ,frgu.request_unit_id ,fcpt.user_concurrent_program_name FROM fnd_responsibility fr ,fnd_responsibility_tl frt ,fnd_request_groups frg ,fnd_request_group_units frgu ,fnd_concurrent_programs_tl fcpt WHERE frt.responsibility_id = fr.responsibility_id AND frg.request_group_id = fr.request_group_id AND frgu.request_group_id = frg.request_group_id AND fcpt.concurrent_program_id = frgu.request_unit_id AND UPPER(fcpt.user_concurrent_program_name) = UPPER(:p_concurrent_program) ORDER BY frt.responsibility_name

Monday, December 30, 2024

Query to get failed records in Invoices Interface in Oracle APPS R12

SELECT aif.invoice_id
              ,ailf.invoice_line_id
  ,aif.invoice_num
  ,air.reject_lookup_code
  ,aif.invoice_amount
  ,ailf.amount line_amount
  ,ailf.line_number
  ,aif.source
  ,ailf.accounting_date
  ,aif.gl_date
  ,aif.invoice_date
  ,aif.creation_date
  ,(SELECT vendor_name
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_name
  ,(SELECT end_date_active
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active
  ,(SELECT last_update_date
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date
  ,(SELECT segment1
                  FROM po_headers_all
                 WHERE po_header_id = ailf.po_header_id) po_number
  ,(SELECT name
                  FROM hr_operating_units
                 WHERE organization_id = aif.org_id) operating_unit
          FROM ap_invoices_interface aif
      ,ap_interface_rejections air
  ,ap_invoice_lines_interface ailf
         WHERE aif.invoice_id = ailf.invoice_id
           AND ailf.invoice_line_id = air.parent_id
           -- AND    aif.group_id        =v_group_id
           AND air.parent_table = 'AP_INVOICE_LINES_INTERFACE'
   --AND aif.source = 'ERS'
           AND aif.status = 'REJECTED'
        UNION
        SELECT aif.invoice_id
      ,ailf.invoice_line_id
  ,aif.invoice_num
  ,air.reject_lookup_code
  ,aif.invoice_amount
  ,ailf.amount line_amount
  ,ailf.line_number
  ,aif.source
  ,ailf.accounting_date
  ,aif.gl_date
  ,aif.invoice_date
  ,aif.creation_date
  ,(SELECT vendor_name
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_name
  ,(SELECT end_date_active
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active
  ,(SELECT last_update_date
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date
  ,(SELECT segment1
                  FROM po_headers_all
                 WHERE po_header_id = ailf.po_header_id) po_number
  ,(SELECT name
                  FROM hr_operating_units
                 WHERE organization_id = aif.org_id) operating_unit
          FROM ap_invoices_interface aif
      ,ap_interface_rejections air
  ,ap_invoice_lines_interface ailf
         WHERE aif.invoice_id = ailf.invoice_id
           AND aif.invoice_id = air.parent_id
           -- AND    aif.group_id        =v_group_id
           AND air.parent_table = 'AP_INVOICES_INTERFACE'
           --AND aif.source = 'ERS'
   AND aif.status = 'REJECTED'

Query to get GRN Details in Oracle APPS R12

SELECT    
       DISTINCT rsl.to_subinventory
           ,rts.invoice_status_code
   ,phs.org_id
   ,phs.segment1 po_number
   ,rts.transaction_date
           FROM po_vendor_sites_all pvss
               ,po_vendor_sites_all pvss2
               ,po_vendors pvds
               ,po_headers_all phs
               ,po_lines_all pls
               ,po_line_locations_all plls
               ,rcv_shipment_headers rsh
               ,rcv_shipment_lines rsl
               ,inl_ship_lines_all isl
               ,rcv_transactions rts
               ,mtl_system_items_b msi
          WHERE rts.shipment_header_id = rsh.shipment_header_id
            AND rts.po_header_id = phs.po_header_id
            AND rts.po_line_location_id = plls.line_location_id
            AND rts.po_line_id = pls.po_line_id
            AND rts.shipment_header_id = rsl.shipment_header_id
            AND rts.shipment_line_id = rsl.shipment_line_id
            AND rts.lcm_shipment_line_id = isl.ship_line_id(+)
            AND rts.lcm_adjustment_num = isl.adjustment_num(+)
            AND phs.vendor_id = pvds.vendor_id
            AND phs.vendor_site_id = pvss.vendor_site_id
            AND rsh.receipt_source_code = 'VENDOR'
            AND rts.source_document_code = 'PO' 
            AND phs.segment1 = '1234567890'           --- Purchase Order Number/LPO Number
            --AND NVL (rts.invoice_status_code, 'NA') IN ('PENDING', 'REJECTED')
            AND (   (rts.transaction_type = 'RECEIVE')
                 OR (    'N' = 'Y'
                     AND rts.transaction_type = 'CORRECT'
                     AND rts.timecard_id IS NOT NULL
                     AND EXISTS (
                            SELECT 1
                              FROM apps.rcv_transactions prt
                             WHERE prt.transaction_type = 'RECEIVE'
                               AND prt.source_document_code = 'PO'
                               AND prt.transaction_id =
                                                     rts.parent_transaction_id
                               AND prt.invoice_status_code = 'INVOICED'
                               AND prt.last_update_date <= rts.creation_date)
                    )
                )
            AND pvss.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')
            AND phs.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')
            AND NVL (plls.consigned_flag, 'N') <> 'Y'
            AND pvss2.vendor_site_id = NVL (pvss.default_pay_site_id, pvss.vendor_site_id)
            AND NVL (rsh.asn_type, ' ') <> 'ASBN'
            AND rts.po_release_id IS NULL
            AND rsl.item_id = msi.inventory_item_id
            AND rts.organization_id = msi.organization_id
            AND rts.transaction_date >= '01-JAN-2024'

Query to get POR is Enabled at Supplier Site and PO Level in Oracle APPS R12

SELECT DISTINCT apss.pay_on_code por_supp_site
               ,pha.pay_on_code por_po
           FROM ap_suppliers aps
       ,ap_supplier_sites_all apss
   ,po_headers_all pha
          WHERE aps.vendor_id = apss.vendor_id
            AND pha.vendor_id = apss.vendor_id
            AND pha.vendor_site_id = apss.vendor_site_id
            AND pha.segment1 = '1234567890'  --- Purchase Order Number/LPO Number

Query to get Inventory Item details in R12

SELECT MTL_SYSTEM_ITEMS_B.LAST_UPDATE_DATE
      ,MTL_SYSTEM_ITEMS_B.LAST_UPDATED_BY
  ,MTL_SYSTEM_ITEMS_B.CREATION_DATE
  ,MTL_SYSTEM_ITEMS_B.CREATED_BY
  ,MTL_SYSTEM_ITEMS_B.DESCRIPTION
  ,MTL_SYSTEM_ITEMS_B.SEGMENT1
  ,MTL_SYSTEM_ITEMS_B.SHELF_LIFE_CODE
  ,MTL_SYSTEM_ITEMS_B.UNIT_WEIGHT
  ,MTL_SYSTEM_ITEMS_B.WEIGHT_UOM_CODE
  ,MTL_SYSTEM_ITEMS_B.VOLUME_UOM_CODE
  ,MTL_SYSTEM_ITEMS_B.UNIT_VOLUME
  ,MTL_SYSTEM_ITEMS_B.PRIMARY_UOM_CODE
  ,MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID
  ,MTL_SYSTEM_ITEMS_B.ITEM_TYPE
  ,MTL_PARAMETERS.MASTER_ORGANIZATION_ID
  ,MTL_SYSTEM_ITEMS_B.HAZARD_CLASS_ID
  ,MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE
  ,MTL_SYSTEM_ITEMS_B.PLANNING_MAKE_BUY_CODE
  ,MTL_SYSTEM_ITEMS_B.BASE_ITEM_ID
  ,MTL_SYSTEM_ITEMS_B.START_DATE_ACTIVE
  ,MTL_SYSTEM_ITEMS_B.RETURNABLE_FLAG
  ,MTL_SYSTEM_ITEMS_B.PURCHASING_ENABLED_FLAG
  ,MTL_SYSTEM_ITEMS_B.SERVICEABLE_PRODUCT_FLAG
  ,MTL_SYSTEM_ITEMS_B.END_DATE_ACTIVE
  ,MTL_SYSTEM_ITEMS_B.FIXED_ORDER_QUANTITY
  ,MTL_SYSTEM_ITEMS_B.ORDER_COST
  ,MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_FLAG
  ,MTL_SYSTEM_ITEMS_B.CUSTOMER_ORDER_ENABLED_FLAG 
  FROM MTL_SYSTEM_ITEMS_B
      ,MTL_PARAMETERS 
 WHERE MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID 
   AND MTL_PARAMETERS.MASTER_ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID                             

Query to get GL Journal Details in R12

SELECT JEL.JE_HEADER_ID
      ,JEL.JE_LINE_NUM
  ,JEL.LAST_UPDATE_DATE
      ,JEL.LAST_UPDATED_BY
  ,JEL.LEDGER_ID
  ,JEL.CODE_COMBINATION_ID
      ,JEL.PERIOD_NAME
  ,JEL.STATUS
  ,JEL.CREATION_DATE
  ,JEL.CREATED_BY
  ,JEL.ENTERED_DR
  ,JEL.ENTERED_CR
  ,JEL.ACCOUNTED_DR
  ,JEL.ACCOUNTED_CR
  ,JEL.REFERENCE_1
  ,JEL.REFERENCE_2
  ,JEL.REFERENCE_3
  ,JEL.REFERENCE_4
  ,JEL.REFERENCE_5
  ,JEL.REFERENCE_6
  ,JEL.REFERENCE_7
  ,JEL.REFERENCE_8
  ,JEL.REFERENCE_9
  ,JEL.REFERENCE_10
  ,JEL.GL_SL_LINK_ID
  ,JEH.JE_CATEGORY
  ,JEH.JE_SOURCE
  ,JEH.NAME
  ,JEH.CURRENCY_CODE
  ,JEH.POSTED_DATE
  ,JEB.NAME
  ,PRDS.START_DATE
  ,PRDS.END_DATE
  ,GL.LEDGER_CATEGORY_CODE
  ,PRDS.ADJUSTMENT_PERIOD_FLAG 
  FROM GL_JE_LINES JEL
      ,GL_JE_HEADERS JEH
      ,GL_JE_BATCHES JEB
      ,GL_PERIOD_STATUSES PRDS
  ,GL_LEDGERS GL 
 WHERE JEL.JE_HEADER_ID = JEH.JE_HEADER_ID 
   AND JEH.ACTUAL_FLAG = 'A' 
   AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID (+) 
   AND JEL.PERIOD_NAME = PRDS.PERIOD_NAME 
   AND JEL.LEDGER_ID = PRDS.SET_OF_BOOKS_ID 
   AND JEL.LEDGER_ID = GL.LEDGER_ID 
   AND PRDS.APPLICATION_ID = 101 
   AND JEH.CURRENCY_CODE <> 'STAT' 
   AND JEB.STATUS = 'P' 

Query to get Sales Invoice(Receivables) Details in R12

SELECT RA_CUST_TRX_TYPES_ALL.TYPE,
       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID,
       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATE_DATE,
       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATED_BY,
       RA_CUSTOMER_TRX_LINES_ALL.CREATION_DATE,
       RA_CUSTOMER_TRX_LINES_ALL.CREATED_BY,
       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID,
       RA_CUSTOMER_TRX_LINES_ALL.LINE_NUMBER,
       RA_CUSTOMER_TRX_LINES_ALL.SET_OF_BOOKS_ID,
       RA_CUSTOMER_TRX_LINES_ALL.INVENTORY_ITEM_ID,
       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_INVOICED,
       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_CREDITED,
       RA_CUSTOMER_TRX_LINES_ALL.UNIT_STANDARD_PRICE,
       RA_CUSTOMER_TRX_LINES_ALL.UNIT_SELLING_PRICE,
       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER,
       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER_LINE,
       RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE,
       RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT,
       RA_CUSTOMER_TRX_LINES_ALL.TAX_RATE,
       RA_CUSTOMER_TRX_LINES_ALL.UOM_CODE,
       RA_CUSTOMER_TRX_LINES_ALL.ORG_ID,
       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT,
       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6,
       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE7,
       RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID,
       RA_CUSTOMER_TRX_LINES_ALL.WAREHOUSE_ID,
       RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID,
       RA_CUSTOMER_TRX_ALL.TRX_NUMBER,
       RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID,
       RA_CUSTOMER_TRX_ALL.TRX_DATE,
       RA_CUSTOMER_TRX_ALL.SOLD_TO_CUSTOMER_ID,
       OE_ORDER_HEADERS_ALL.SOLD_TO_CONTACT_ID,
       RA_CUSTOMER_TRX_ALL.SOLD_TO_SITE_USE_ID,
       RA_CUSTOMER_TRX_ALL.BILL_TO_CUSTOMER_ID,
       RA_CUSTOMER_TRX_ALL.BILL_TO_SITE_USE_ID,
       RA_CUSTOMER_TRX_ALL.SHIP_TO_CUSTOMER_ID,
       RA_CUSTOMER_TRX_ALL.SHIP_TO_SITE_USE_ID,
       RA_CUSTOMER_TRX_ALL.TERM_ID,
       RA_CUSTOMER_TRX_ALL.TERM_DUE_DATE,
       RA_CUSTOMER_TRX_ALL.PRIMARY_SALESREP_ID,
       RA_CUSTOMER_TRX_ALL.PRINTING_ORIGINAL_DATE,
       RA_CUSTOMER_TRX_ALL.PURCHASE_ORDER,
       RA_CUSTOMER_TRX_ALL.EXCHANGE_DATE,
       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE,
       RA_CUSTOMER_TRX_ALL.TERRITORY_ID,
       RA_CUSTOMER_TRX_ALL.INVOICE_CURRENCY_CODE,
       RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG,
       RA_CUSTOMER_TRX_ALL.PAYING_SITE_USE_ID,
       RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID,
       RA_CUSTOMER_TRX_LINES_ALL1.INVENTORY_ITEM_ID,
       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER,
       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER_LINE,
       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_CONTEXT,
       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE6,
       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE7,
       RA_CUSTOMER_TRX_LINES_ALL1.WAREHOUSE_ID,
       RA_CUSTOMER_TRX_ALL.STATUS_TRX,
       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE_TYPE,
       RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE,
       RA_CUST_TRX_TYPES_ALL.LAST_UPDATE_DATE,
       OE_ORDER_HEADERS_ALL.LAST_UPDATE_DATE,
       OE_ORDER_HEADERS_ALL.SALES_CHANNEL_CODE,
       OE_ORDER_HEADERS_ALL.ORDER_TYPE_ID,
       OE_ORDER_HEADERS_ALL.PAYMENT_TYPE_CODE,
       OE_ORDER_HEADERS_ALL.FREIGHT_TERMS_CODE,
       OE_ORDER_LINES_ALL.LINE_CATEGORY_CODE,
       OE_ORDER_LINES_ALL.ITEM_TYPE_CODE,
       OE_ORDER_LINES_ALL.SHIPMENT_NUMBER,
       OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID,
       OE_ORDER_LINES_ALL.PROJECT_ID,
       OE_ORDER_LINES_ALL.TASK_ID,
       OE_ORDER_LINES_ALL.OPTION_NUMBER,
       OE_ORDER_LINES_ALL.COMPONENT_NUMBER,
       OE_ORDER_LINES_ALL.SOURCE_TYPE_CODE,
       OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID,
       RA_CUSTOMER_TRX_ALL.LEGAL_ENTITY_ID,
       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE11
  FROM RA_CUSTOMER_TRX_LINES_ALL  RA_CUSTOMER_TRX_LINES_ALL1,
       RA_CUSTOMER_TRX_LINES_ALL,
       RA_CUSTOMER_TRX_ALL,
       RA_CUST_TRX_TYPES_ALL,
       OE_ORDER_HEADERS_ALL,
       OE_ORDER_LINES_ALL
 WHERE     RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID =
           RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID
       AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = 'Y'
       AND RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID =
           RA_CUST_TRX_TYPES_ALL.CUST_TRX_TYPE_ID
       AND RA_CUSTOMER_TRX_ALL.ORG_ID = RA_CUST_TRX_TYPES_ALL.ORG_ID
       AND RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID =
           RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID(+)
       AND RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT IN
               ('ORDER ENTRY', 'INTERCOMPANY')
       AND TO_NUMBER (RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6) =
           OE_ORDER_LINES_ALL.LINE_ID(+)
       AND OE_ORDER_LINES_ALL.HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID(+)
       AND RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE >=
           TO_DATE (:P_LAST_EXTRACT_DATE, 'MM/D D/YYYY HH24:MI:SS')

Query to get the Concurrent Program along with Value Set details

SELECT fcpl1.user_concurrent_program_name       ,fdfcuv.end_user_column_name       ,ffvs.flex_value_set_name value_set_name       ,ffvt.appl...