Query to get GL code combinations description

----------
1) Below given query can be used TO get concatenated Gl code combinations segment
AND its description USING API GL_FLEXFIELDS_PKG.get_concat_description
----------
SELECT
  gcc.CODE_COMBINATION_ID,
  gcc.CONCATENATED_SEGMENTS,
  APPS.GL_FLEXFIELDS_PKG.get_concat_description(gcc.chart_of_accounts_id,
  gcc.code_combination_id) acc_description
FROM
  gl_code_combinations_kfv gcc;
  
  
----------
2)Below given Query can be used
WHEN one dont have access TO GL_FLEXFIELDS_PKG.get_concat_description API.
  ----------
  SELECT
    gcc.CODE_COMBINATION_ID,
    gcc.CONCATENATED_SEGMENTS,
    acc_desc.acc_description
  FROM
    (
      SELECT
        gcc.code_combination_id,
        A1.DESCRIPTION
        ||'.'
        ||A2.DESCRIPTION
        ||'.'
        || A3.DESCRIPTION
        ||'.'
        || A4.DESCRIPTION
        ||'.'
        || A5.DESCRIPTION
        ||'.'
        || A6.DESCRIPTION
        ||'.'
        || A7.DESCRIPTION
        ||'.'
        || A8.DESCRIPTION acc_description
      FROM
        apps.fnd_flex_values_vl A1,
        apps.fnd_flex_values_vl A2,
        apps.fnd_flex_values_vl A3,
        apps.fnd_flex_values_vl A4,
        apps.fnd_flex_values_vl A5,
        apps.fnd_flex_values_vl A6,
        apps.fnd_flex_values_vl A7,
        apps.fnd_flex_values_vl A8,
        apps.gl_code_combinations gcc
      WHERE
        a1.flex_value           =gcc.segment1
      AND a1.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT1'
        )
      AND a2.flex_value         =gcc.segment2
      AND a2.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT2'
        )
      AND a3.flex_value         =gcc.segment3
      AND a3.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT3'
        )
      AND a4.flex_value         =gcc.segment4
      AND a4.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT4'
        )
      AND a5.flex_value         =gcc.segment5
      AND a5.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT5'
        )
      AND a6.flex_value         =gcc.segment6
      AND a6.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT6'
        )
      AND a7.flex_value         =gcc.segment7
      AND a7.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT7'
        )
      AND a8.flex_value         =gcc.segment8
      AND a8.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT8'
        )
    )
    acc_desc,
    gl_code_combinations_kfv gcc
  WHERE
    gcc.code_combination_id=acc_desc.code_combination_id;
    
    
  ----------
  3) Below given query can be used TO get EACH segment description seperately
  USING API gl_flexfields_pkg.get_description_sql
  ---------
  SELECT
    gcc.segment1,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    1,          ----- Position of segment
    gcc.segment1 ---- Segment value
    ) Segment1_desc,
    gcc.segment2,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    2,          ----- Position of segment
    gcc.segment2 ---- Segment value
    ) Segment2_desc,
    gcc.segment3,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    3,          ----- Position of segment
    gcc.segment3 ---- Segment value
    ) Segment3_desc,
    gcc.segment4,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    4,          ----- Position of segment
    gcc.segment4 ---- Segment value
    ) Segment4_desc,
    gcc.segment5,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    5,          ----- Position of segment
    gcc.segment5 ---- Segment value
    ) Segment5_desc,
    gcc.segment6,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    6,          ----- Position of segment
    gcc.segment6 ---- Segment value
    ) Segment6_desc,
    gcc.segment7,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    7,          ----- Position of segment
    gcc.segment7 ---- Segment value
    ) Segment7_desc,
    gcc.segment8,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    8,          ----- Position of segment
    gcc.segment8 ---- Segment value
    ) Segment8_desc
  FROM
    gl_code_combinations gcc;

Query to check concurrent program attached to responsibility and User

SELECT fu.USER_NAME,
  frt.responsibility_name,
  frg.request_group_name,
  frgu.request_unit_type,
  DECODE (frgu.request_unit_type,
  'A' ,'Application',
  'P' ,'Program',
  'S' ,'Request Set',
  'Unknown')Request_type,
  fcpt.user_concurrent_program_name
FROM apps.fnd_Responsibility fr,
  apps.fnd_responsibility_tl frt,
  apps.fnd_request_groups frg,
  apps.fnd_request_group_units frgu,
  apps.fnd_concurrent_programs_tl fcpt,
  apps.fnd_user_resp_groups_direct furg,
  apps.fnd_user fu
WHERE frt.responsibility_id= fr.responsibility_id
AND frg.request_group_id= fr.request_group_id
AND furg.RESPONSIBILITY_ID= fr.RESPONSIBILITY_ID
AND furg.USER_ID    = fu.USER_ID
AND frgu.request_group_id = frg.request_group_id
AND fcpt.concurrent_program_id        = frgu.request_unit_id
AND fcpt.user_concurrent_program_name = NVL(:p_conc_prog_name,fcpt.user_concurrent_program_name)
AND frt.responsibility_name = NVL(:p_resp_name,frt.responsibility_name)
AND fu.USER_NAME=nvl(:p_user_name , fu.USER_NAME)
ORDER BY 1,2,3;

Query for Oracle Period status for AP and AR applications

query to check Period status for AP and AR application

SELECT GLS.NAME LEDGER_NAME,
  FA.APPLICATION_SHORT_NAME,
  GPS.closing_status,
  DECODE(GPS.closing_status, 'C', 'Closed', 
         'O', 'Open', 
         'F', 'Future', 
         'W', 'Closed Pending', 
         'N', 'Never Opened', 
         'P', 'Permanently Closed') "PeriodStatus" ,
  GPS.*
FROM GL.GL_PERIOD_STATUSES GPS,
  GL.GL_LEDGERS GLS,
  APPLSYS.FND_APPLICATION FA
WHERE PERIOD_NAME     ='SEP-FY-17'
AND GPS.APPLICATION_ID IN (222,200)
AND FA.APPLICATION_ID =GPS.APPLICATION_ID
AND GLS.LEDGER_ID     =GPS.SET_OF_BOOKS_ID
ORDER BY 1; 

Query to get AP invoice Payment details

AP Invoice payments details query

AP_DOCUMENTS_PAYABLE

SELECT
  *
FROM
  AP_DOCUMENTS_PAYABLE
WHERE
  calling_app_id                = 200
AND calling_app_doc_unique_ref2 = :P_INVOICE_ID;

AP_INVOICE_PAYMENTS_ALL

SELECT DISTINCT
  aip.*,
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' ,
  aip.accts_pay_code_combination_id) "Accts Pay Account"
FROM
  AP_INVOICE_PAYMENTS_ALL aip,
  AP_INVOICE_PAYMENTS_ALL aip2
WHERE
  aip2.check_id     = aip.check_id
AND aip2.invoice_id = :P_INVOICE_ID
ORDER BY
  aip.check_id ASC,
  aip.invoice_payment_id ASC;
  
AP_CHECKS_ALL

SELECT
  *
FROM
  AP_CHECKS_ALL
WHERE
  check_id IN
  (
    SELECT
      check_id
    FROM
      AP_INVOICE_PAYMENTS_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );
  
  AP_PAYMENT_DISTRIBUTIONS_ALL
  
SELECT
  tab.*
FROM
  AP_INVOICE_PAYMENTS_ALL aip,
  AP_PAYMENT_DISTRIBUTIONS_ALL tab
WHERE
  aip.invoice_payment_id = tab.invoice_payment_id
AND aip.invoice_id       = :P_INVOICE_ID;

AP_PAYMENT_HISTORY_ALL
SELECT
  *
FROM
  AP_PAYMENT_HISTORY_ALL
WHERE
  check_id IN
  (
    SELECT DISTINCT
      check_id
    FROM
      AP_INVOICE_PAYMENTS_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  )
ORDER BY
  accounting_event_id ASC;
  
AP_PAYMENT_HIST_DISTS

SELECT DISTINCT
  aphd.*
FROM
  ap_payment_hist_dists aphd,
  AP_PAYMENT_HISTORY_ALL aph
WHERE
  aph.payment_history_id = aphd.payment_history_id
AND aph.check_id        IN
  (
    SELECT DISTINCT
      check_id
    FROM
      AP_INVOICE_PAYMENTS_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  )
ORDER BY
  aphd.payment_history_id ASC;
  
AP_INV_SELECTION_CRITERIA_ALL

SELECT
  *
FROM
  AP_INV_SELECTION_CRITERIA_ALL
WHERE
  checkrun_name IN
  (
    SELECT
      checkrun_name
    FROM
      AP_CHECKS_ALL
    WHERE
      check_id IN
      (
        SELECT
          check_id
        FROM
          AP_INVOICE_PAYMENTS_ALL
        WHERE
          invoice_id = :P_INVOICE_ID
      )
  );
  
AP_SELECTED_INVOICES_ALL

SELECT
  *
FROM
  AP_SELECTED_INVOICES_ALL
WHERE
  invoice_id = :P_INVOICE_ID;
  
AP_SELECTED_INVOICE_CHECKS_ALL

SELECT
  asic.*
FROM
  AP_SELECTED_INVOICE_CHECKS_ALL asic,
  AP_SELECTED_INVOICES_ALL asi
WHERE
  asi.invoice_id = :P_INVOICE_ID
AND
  (
    asic.selected_check_id  = asi.pay_selected_check_id
  OR asic.selected_check_id = print_selected_check_id
  );
  
IBY_DOCS_PAYABLE_ALL

SELECT
  *
FROM
  IBY_PAYMENTS_ALL
WHERE
  payment_id IN
  (
    SELECT
      idp.payment_id
    FROM
      IBY_DOCS_PAYABLE_ALL idp
    WHERE
      idp.calling_app_id =200
    AND
      (
        calling_app_doc_unique_ref1 , calling_app_doc_unique_ref2
      )
      IN
      (
        SELECT
          TO_CHAR(aps.checkrun_id) checkrun_id ,
          TO_CHAR(aps.invoice_id)
        FROM
          ap_payment_schedules_all aps
        WHERE
          aps.invoice_id=:P_INVOICE_ID
        UNION
        SELECT
          TO_CHAR(NVL(ac.checkrun_id, ac.check_id)) checkrun_id ,
          TO_CHAR(aip.invoice_id)
        FROM
          ap_invoice_payments_all aip ,
          ap_checks_all ac
        WHERE
          aip.invoice_id =:P_INVOICE_ID
        AND aip.check_id =ac.check_id
      )
  );
  
  
IBY_PAYMENTS_ALL

SELECT
  *
FROM
  IBY_PAYMENTS_ALL
WHERE
  payment_id IN
  (
    SELECT
      idp.payment_id
    FROM
      IBY_DOCS_PAYABLE_ALL idp
    WHERE
      idp.calling_app_id =200
    AND
      (
        calling_app_doc_unique_ref1 , calling_app_doc_unique_ref2
      )
      IN
      (
        SELECT
          TO_CHAR(aps.checkrun_id) checkrun_id ,
          TO_CHAR(aps.invoice_id)
        FROM
          ap_payment_schedules_all aps
        WHERE
          aps.invoice_id=:P_INVOICE_ID
        UNION
        SELECT
          TO_CHAR(NVL(ac.checkrun_id, ac.check_id)) checkrun_id ,
          TO_CHAR(aip.invoice_id)
        FROM
          ap_invoice_payments_all aip ,
          ap_checks_all ac
        WHERE
          aip.invoice_id =:P_INVOICE_ID
        AND aip.check_id =ac.check_id
      )
  );
  
  
IBY_PAY_INSTRUCTIONS_ALL

SELECT
  *
FROM
  IBY_PAY_INSTRUCTIONS_ALL
WHERE
  payment_instruction_id IN
  (
    SELECT
      ipa.payment_instruction_id
    FROM
      IBY_DOCS_PAYABLE_ALL idp ,
      IBY_PAYMENTS_ALL ipa
    WHERE
      idp.calling_app_id =200
    AND ipa.payment_id   =idp.payment_id
    AND
      (
        calling_app_doc_unique_ref1 , calling_app_doc_unique_ref2
      )
      IN
      (
        SELECT
          TO_CHAR(aps.checkrun_id) checkrun_id ,
          TO_CHAR(aps.invoice_id)
        FROM
          ap_payment_schedules_all aps
        WHERE
          aps.invoice_id=:P_INVOICE_ID
        UNION
        SELECT
          TO_CHAR(NVL(ac.checkrun_id, ac.check_id)) checkrun_id ,
          TO_CHAR(aip.invoice_id)
        FROM
          ap_invoice_payments_all aip ,
          ap_checks_all ac
        WHERE
          aip.invoice_id =:P_INVOICE_ID
        AND aip.check_id =ac.check_id
      )
  );
  
  
AP_RECON_DISTRIBUTIONS_ALL 

SELECT
  *
FROM
  AP_RECON_DISTRIBUTIONS_ALL
WHERE
  check_id IN
  (
    SELECT
      check_id
    FROM
      AP_INVOICE_PAYMENTS_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );

Query for AP withholding Tax details

Query for AP withholding Tax details

AP_AWT_TAX_RATES

SELECT DISTINCT
  awt.*
FROM
  ap_tax_codes_all atc,
  ap_awt_tax_rates_all awt
WHERE
  awt.tax_name  = atc.name
AND atc.tax_id IN
  (
    SELECT DISTINCT
      aid.tax_code_id tax_code_id
    FROM
      ap_invoice_distributions_all aid,
      ap_tax_codes_all atc
    WHERE
      aid.tax_code_id    = atc.tax_id
    AND aid.tax_code_id IS NOT NULL
    AND atc.tax_type     = 'AWT'
    AND aid.invoice_id   = :P_INVOICE_ID
  )
UNION
SELECT DISTINCT
  awt.*
FROM
  ap_awt_tax_rates_all awt
WHERE
  awt.tax_rate_id IN
  (
    SELECT DISTINCT
      aid.awt_tax_rate_id
    FROM
      ap_invoice_distributions_all aid
    WHERE
      aid.awt_tax_rate_id IS NOT NULL
    AND aid.invoice_id     = :P_INVOICE_ID
  );


AP_AWT_GROUPS

SELECT DISTINCT
  awt.*
FROM
  ap_awt_groups awt
WHERE
  awt.group_id IN
  (
    SELECT DISTINCT
      aid.awt_group_id
    FROM
      ap_invoice_distributions_all aid
    WHERE
      aid.awt_group_id IS NOT NULL
    AND aid.invoice_id  = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      aid.pay_awt_group_id
    FROM
      ap_invoice_distributions_all aid
    WHERE
      aid.pay_awt_group_id IS NOT NULL
    AND aid.invoice_id      = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      ai.awt_group_id
    FROM
      ap_invoices_all ai
    WHERE
      ai.awt_group_id IS NOT NULL
    AND ai.invoice_id  = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      ai.pay_awt_group_id
    FROM
      ap_invoices_all ai
    WHERE
      ai.pay_awt_group_id IS NOT NULL
    AND ai.invoice_id      = :P_INVOICE_ID
  );


AP_AWT_GROUP_TAXES_ALL

SELECT DISTINCT
  awt.*
FROM
  AP_AWT_GROUP_TAXES_ALL awt
WHERE
  awt.group_id IN
  (
    SELECT DISTINCT
      aid.awt_group_id
    FROM
      ap_invoice_distributions_all aid
    WHERE
      aid.awt_group_id IS NOT NULL
    AND aid.invoice_id  = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      aid.pay_awt_group_id
    FROM
      ap_invoice_distributions_all aid
    WHERE
      aid.pay_awt_group_id IS NOT NULL
    AND aid.invoice_id      = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      ai.awt_group_id
    FROM
      ap_invoices_all ai
    WHERE
      ai.awt_group_id IS NOT NULL
    AND ai.invoice_id  = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      ai.pay_awt_group_id
    FROM
      ap_invoices_all ai
    WHERE
      ai.pay_awt_group_id IS NOT NULL
    AND ai.invoice_id      = :P_INVOICE_ID
  );


AP_AWT_TEMP_DISTRIBUTIONS_ALL


SELECT
  *
FROM
  ap_awt_temp_distributions_all
WHERE
  invoice_id = :P_INVOICE_ID;

Query to get concurrent request set details for parent request set id.

Query to get concurrent request details for parent request set id.

SELECT
  fcr1.request_id ,
  fcp1.user_concurrent_program_name ,
  DECODE(fcr1.phase_code,'C','Completed',fcr1.phase_code) phase_code,
  DECODE(fcr1.status_code,'D', 'Cancelled','E', 'Error' , 'G', 'Warning', 'H',
  'On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C',
  'Normal', fcr1.status_code) status_code,
  fcr1.request_date ,
  fcr1.requested_start_date ,
  fcr1.actual_completion_date,
  fcr1.parent_request_id
FROM
  apps.fnd_concurrent_requests fcr ,
  apps.fnd_concurrent_programs_vl fcp ,
  apps.fnd_concurrent_requests fcr1 ,
  apps.fnd_concurrent_programs_vl fcp1
WHERE
  1                            = 1
AND fcp.concurrent_program_id  = fcr.concurrent_program_id
AND fcp1.concurrent_program_id = fcr1.concurrent_program_id
AND fcr1.parent_request_id     = fcr.request_id
AND fcr.parent_request_id      = 95625343
ORDER BY
  fcr1.requested_start_date DESC;

Query 2:

select request_id ,
  user_concurrent_program_name ,
 DECODE(phase_code,'C','Completed',phase_code) phase_code,
  DECODE(status_code,'D', 'Cancelled','E', 'Error' , 'G', 'Warning', 'H',
  'On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C',
  'Normal', status_code) status_code,
  request_date ,
  requested_start_date ,
  actual_completion_date,
  parent_request_id ,
  ARGUMENT_TEXT
  from apps.FND_CONC_REQ_SUMMARY_V
  where priority_REQUEST_ID=96200984
  order by request_id;

AP(Payables) invoice related important table

Invoice

AP_BATCHES_ALL

SELECT
  *
FROM
  AP_BATCHES_ALL
WHERE
  batch_id IN
  (
    SELECT
      batch_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );

AP_INVOICES_ALL

SELECT DISTINCT
  ai.*,
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' , ai.accts_pay_code_combination_id
  ) "Accts Pay Account"
FROM
  AP_INVOICES_ALL ai
WHERE
  invoice_id = :P_INVOICE_ID  
ORDER BY
  ai.invoice_id ASC;

AP_INVOICE_LINES_ALL
          
SELECT
  *
FROM
  AP_INVOICE_LINES_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_INVOICE_DISTRIBUTIONS_ALL

SELECT
  aid.*,
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' ,
  aid.accts_pay_code_combination_id) "Accts Pay Account",
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' , aid.dist_code_combination_id)
  "Dist Account"
FROM
  AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE
  aid.invoice_id = :P_INVOICE_ID
ORDER BY
  aid.invoice_id,
  aid.invoice_line_number,
  aid.distribution_line_number ASC;

ZX_LINES 
         
SELECT
  *
FROM
  ZX_LINES
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');

zx_lines_summary 
         
SELECT
  *
FROM
  zx_lines_summary
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');


zx_rec_nrec_dist
          
SELECT
  *
FROM
  zx_rec_nrec_dist
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');

zx_lines_det_factors  
        
SELECT
  *
FROM
  zx_lines_det_factors
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');


AP_PAYMENT_SCHEDULES_ALL 
         
SELECT
  *
FROM
  AP_PAYMENT_SCHEDULES_ALL
WHERE
  invoice_id = :P_INVOICE_ID;

ap_recurring_payments_all
 
SELECT DISTINCT
  arp.*
FROM
  ap_recurring_payments_all arp,
  ap_invoices_all ai
WHERE
  ai.recurring_payment_id = arp.recurring_payment_id
AND ai.invoice_id         = :P_INVOICE_ID;


AP_HOLDS_ALL 
         
SELECT
  *
FROM
  AP_HOLDS_ALL
WHERE
  invoice_id = :P_INVOICE_ID;

AP_HOLD_CODES
          
SELECT
  *
FROM
  AP_HOLD_CODES
WHERE
  hold_lookup_code IN
  (
    SELECT
      hold_lookup_code
    FROM
      AP_HOLDS_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );


AP_TERMS
          
SELECT
  *
FROM
  AP_TERMS
WHERE
  term_id IN
  (
    SELECT DISTINCT
      terms_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );



AP_TERMS_LINES
          
SELECT
  *
FROM
  AP_TERMS_LINES
WHERE
  term_id IN
  (
    SELECT DISTINCT
      terms_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );


AP_INVOICE_PREPAYS_ALL 
         
SELECT
  *
FROM
  AP_INVOICE_PREPAYS_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_CHRG_ALLOCATIONS_ALL 

SELECT DISTINCT
  aca.*
FROM
  AP_CHRG_ALLOCATIONS_ALL aca,
  AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE
  (
    aca.charge_dist_id = aid.invoice_distribution_id
  OR aca.item_dist_id  = aid.invoice_distribution_id
  )
AND aid.invoice_id = :P_INVOICE_ID
ORDER BY
  aca.charge_dist_id;

AP_INV_APRVL_HIST_ALL
          
SELECT
  *
FROM
  AP_INV_APRVL_HIST_ALL
WHERE
  invoice_id = :P_INVOICE_ID
ORDER BY
  1;


AP_ALLOCATION_RULES 
         
SELECT
  *
FROM
  AP_ALLOCATION_RULES
WHERE
  invoice_id = :P_INVOICE_ID;

AP_ALLOCATION_RULE_LINES 
         
SELECT
  *
FROM
  AP_ALLOCATION_RULE_LINES
WHERE
  invoice_id = :P_INVOICE_ID;

AP_SELF_ASSESSED_TAX_DIST_ALL
          
SELECT
  *
FROM
  AP_SELF_ASSESSED_TAX_DIST_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_INVOICE_RELATIONSHIPS
           
SELECT
  *
FROM
  AP_INVOICE_RELATIONSHIPS
WHERE
  original_invoice_id = :P_INVOICE_ID
OR related_invoice_id = :P_INVOICE_ID;

Query to get details for Purchase order (PO) approval History

Query to get details for Purchase order approval History 

SELECT
  pah.action_code ,
  pah.object_id ,
  pah.action_date ,
  pah.sequence_num,
  pah.creation_date ,
  prha.segment1 PO_num ,
  prha.wf_item_key ,
  prha.authorization_status ,
  fu.description ,
  papf.full_name hr_full_name ,
  papf.employee_number emp_no ,
  pj.NAME job
FROM
  po.po_action_history pah ,
  po.po_headers_all prha ,
  applsys.fnd_user fu ,
  hr.per_all_people_f papf ,
  hr.per_all_assignments_f paaf ,
  hr.per_jobs pj
WHERE
  object_id           = prha.po_header_id
AND pah.employee_id   = fu.employee_id
AND fu.employee_id    = papf.person_id
AND papf.person_id    = paaf.person_id
AND paaf.job_id       = pj.job_id
AND paaf.primary_flag = 'Y'
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND pah.object_type_code = 'PO'
AND prha.segment1        ='4010231234'
  --AND pah.action_code = 'APPROVE'
ORDER BY pah.sequence_num;

Query to get details for PO Requisition approval History

Query to get details for PO Requisition approval History

SELECT
  pah.action_code ,
  pah.object_id ,
  pah.action_date ,
  pah.sequence_num,
  pah.creation_date ,
  prha.segment1 req_num ,
  prha.wf_item_key ,
  prha.authorization_status ,
  fu.description ,
  papf.full_name hr_full_name ,
  papf.employee_number emp_no ,
  pj.NAME job
FROM
  po.po_action_history pah ,
  po.po_requisition_headers_all prha ,
  applsys.fnd_user fu ,
  hr.per_all_people_f papf ,
  hr.per_all_assignments_f paaf ,
  hr.per_jobs pj
WHERE
  object_id           = prha.requisition_header_id
AND pah.employee_id   = fu.employee_id
AND fu.employee_id    = papf.person_id
AND papf.person_id    = paaf.person_id
AND paaf.job_id       = pj.job_id
AND paaf.primary_flag = 'Y'
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND pah.object_type_code = 'REQUISITION'
AND prha.segment1        ='301000201234'
  --AND pah.action_code = 'APPROVE'
ORDER BY pah.sequence_num;

Query to get PO approval activity workflow history

Query to get Po approval activity workflow history 

SELECT execution_time,
  ias.begin_date,
  ap.name Process,
  ap.display_name ,
  ac.name Activity,
  ac.display_name ,
  ias.activity_status,
  ias.activity_result_code,
  ias.assigned_user,
  ias.notification_id ,
  ntf.status,
  ias.error_name,
  ias.error_message        error_message,
  ias.error_stack          error_stack
FROM applsys.wf_item_activity_statuses ias,
  applsys.wf_process_activities pa,
  apps.wf_activities_vl ac,
  apps.wf_activities_vl ap,
  applsys.wf_items i,
  applsys.wf_notifications ntf
WHERE 1=1
and ias.item_type        = 'POAPPRV'
AND ias.item_key   = '727622-1234'
AND ias.process_activity   = pa.instance_id
AND pa.activity_name       = ac.name
AND pa.activity_item_type  = ac.item_type
AND pa.process_name        = ap.name
AND pa.process_item_type   = ap.item_type
AND pa.process_version     = ap.version
AND i.item_type            = 'POAPPRV'
AND i.item_key             = ias.item_key
AND i.begin_date          >= ac.begin_date
AND i.begin_date           < NVL(ac.end_date, i.begin_date+1)
AND ntf.notification_id(+)= ias.notification_id
ORDER BY 2,1;

Query to check vacation rule in oracle apps

Query to check vacation rule in oracle apps


SELECT
  wfrr.rule_id,
  wfrr.message_type,
  wfrr.message_name,
  wfrr.begin_date,
  wfrr.end_date,
  wfrr.action,
  wfrr.action_argument,
  witv.display_name ,
  wfmv.display_name,
  wfmv.subject,
  wfl.meaning ,
  witv.name,
  wfmv.TYPE,
  wfmv.name ,
  wfl.lookup_type,
  wfl.lookup_code
FROM
  apps.wf_routing_rules wfrr,
  apps.wf_item_types_vl witv,
  apps.wf_messages_vl wfmv,
  apps.wf_lookups wfl
WHERE 1=1
AND wfrr.message_type    = witv.name (+)
AND wfrr.message_type  = wfmv.TYPE (+)
AND wfrr.message_name  = wfmv.name (+)
AND wfrr.action        = wfl.lookup_code
AND wfl.lookup_type    = 'WFSTD_ROUTING_ACTIONS'
AND wfrr.end_date     IS NULL
AND wfrr.message_type IN ('POAPPRV','REQAPPRV','APINV','APEXP','APCCARD')
  --and wfrr.action_argument in ('JCENA','MCGEY')
ORDER BY  begin_date;

Oracle apps Xml report Error while saving Report output-Authentication failed

Some times when we get error like "Authentication failed" when we try save or open concurrent program output which is type of xml publisher.
e.g. please see below given screenshot.

Solution :
If you face this kind of issue then kindly set below given profile options at user level.
Profile option name :-Applications Web Agent

Set its value at user level. If suppose you are working on development instance then you need to set its value as url of development instance as  e.g. http://oracleapps.oracle.com:8021/pls/ebzd

Once done this setup , then you can retest issue.

Query to get profile options details from backend

SELECT
  fpov.profile_option_name ,
  fpov.user_profile_option_name,
  DECODE (TO_CHAR (fpo.level_id), '10001', 'SITE', '10002', 'APP', '10003',
  'RESP', '10005', 'SERVER', '10006', 'ORG', '10004', 'USER', 'NA' )
  profile_Level,
  DECODE (TO_CHAR (fpo.level_id), '10001', '', '10002',
  fa.application_short_name, '10003', fr.responsibility_key, '10005',
  fn.node_name, '10006', hou.NAME, '10004', fu.user_name, 'NA' ) Context_value,
  fpo.profile_option_value value,
  fr.RESPONSIBILITY_KEY,
  fr.RESPONSIBILITY_NAME
FROM
  apps.fnd_profile_options_vl fpov,
  apps.fnd_profile_option_values fpo,
  apps.fnd_user fu,
  apps.fnd_application fa,
  apps.fnd_responsibility_vl fr,
  apps.fnd_nodes fn,
  apps.hr_operating_units hou
WHERE
  1                         = 1
AND fpo.application_id      = fpov.application_id
AND fpo.profile_option_id   = fpov.profile_option_id
AND fu.user_id(+)           = fpo.level_value
AND fr.application_id(+)    = fpo.level_value_application_id
AND fr.responsibility_id(+) = fpo.level_value
AND fa.application_id(+)    = fpo.level_value
AND fn.node_id(+)           = fpo.level_value
AND hou.organization_id(+)  = fpo.level_value
AND fpov.user_profile_option_name LIKE 'GL: Data Access Set%' ;

Query to fetch Concurrent Program and executables details

SELECT
  fcpv.user_concurrent_program_name program_name,
  fcpv.concurrent_program_name program_short_name,
  fat.application_name program_application_name,
  fcpv.description program_description,
  fe.executable_name ,
  fe.execution_file_name ,
  DECODE( fe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P',
  'Report', 'L', 'SQL Loader','Q','SQL*Plus', fe.execution_method_code)
  execution_method,
  fu.user_name created_by,
  fcpv.creation_date
FROM
  fnd_executables fe,
  fnd_application_tl fat,
  fnd_concurrent_programs_vl fcpv,
  fnd_user fu
WHERE
  1                   =1
AND fe.application_id = fat.application_id
AND fe.executable_id  = fcpv.executable_id
AND fat.language      ='US'
AND fu.user_id        = fcpv.created_by
AND fcpv.user_concurrent_program_name LIKE 'Trial Balance';

Query to get GL batch, Journal related important column details.

SELECT
GJH.name Journal_Name,
GJB.name Batch_name,
GJL.ACCOUNTED_CR,
GJL.ACCOUNTED_DR,
gcc.CONCATENATED_SEGMENTS,
GJH.CREATED_BY,
gjc.JE_CATEGORY_NAME,
gl.NAME,
GJH.CURRENCY_CODE,
GJL.DESCRIPTION,
GJB.STATUS
FROM apps.GL_JE_HEADERS GJH,
apps.GL_JE_LINES GJL,
apps.GL_JE_BATCHES GJB,
apps.GL_JE_CATEGORIES gjc,
apps.gl_ledgers gl,
apps.gl_code_combinations_kfv gcc
WHERE GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
and gcc.CODE_COMBINATION_ID=GJL.CODE_COMBINATION_ID
and gjc.JE_CATEGORY_NAME=GJH.JE_CATEGORY
and gl.LEDGER_ID=GJH.LEDGER_ID
AND GJB.NAME LIKE 'GLbatchName%'
and gjh.name like 'JournalHeaderName';

Query to find GL Journal Batch Approval status with history

SELECT DISTINCT
  gjb.NAME batch,
  default_period_name period,
  wn.recipient_role approver,
  DECODE (gjb.approval_status_code, 'A', 'Approved', 'I', 'In Process', 'J',
  'Rejected', 'R', 'Required', 'V', 'Validation Failed', 'Z', 'N/A' ) status,
  wn.begin_date approval_start_date,
  wn.end_date approval_end_date,
  wn.due_date approval_due_date
FROM
  wf_notifications wn,
  wf_items wi,
  gl_je_batches gjb
WHERE
  wn.CONTEXT LIKE 'GLBATCH%'
  ||
  (
    SELECT
      item_key
    FROM
      wf_items a
    WHERE
      a.user_key    = wi.user_key
    AND a.item_type = 'GLBATCH'
    AND ROWNUM      = 1
  )
  || '%'
AND wi.item_type = wn.MESSAGE_TYPE
AND wi.user_key  = gjb.NAME
AND gjb.name LIKE 'BatchName%';

Query to get concurrent Manager status from backend

SELECT   t.user_concurrent_queue_name AS "Concurrent Manager Name",
         b.max_processes AS "Actual Processes",
         b.running_processes AS "Target Processes",
         b.concurrent_queue_name, b.cache_size,
         b.min_processes, b.target_processes, b.target_node, b.sleep_seconds,
         b.diagnostic_level, b.manager_type, b.enabled_flag, t.description
    FROM fnd_concurrent_queues_tl t, fnd_concurrent_queues b
   WHERE b.application_id = t.application_id
    AND b.concurrent_queue_id = t.concurrent_queue_id
    AND b.enabled_flag = 'Y'
    AND t.LANGUAGE = USERENV ('LANG')
    --and  t.user_concurrent_queue_name like 'Out%'
ORDER BY b.max_processes DESC;

Query to get custom Form personalization List

Query :

Select Distinct A.Id,
                A.Form_Name,
                A.Enabled,
                C.User_Form_Name,
                D.Application_Name,
                A.Description,
                Ca.Action_Type,
                Ca.Enabled,
                Ca.Object_Type,
                ca.message_type,
                ca.message_text
  from FND_FORM_CUSTOM_RULES   a,
       FND_FORM                b,
       FND_FORM_TL             c,
       Fnd_Application_Tl      D,
       Fnd_Form_Custom_Actions ca
 where a.form_name = b.form_name
   And B.Form_Id = C.Form_Id
   And B.Application_Id = D.Application_Id
   And A.Enabled = 'Y'
   and a.id = ca.rule_id
   and D.Application_Name = 'Purchasing'
--and A.Form_Name='POXPOVPO'

Get OAF Page Trace File

1.Click the 'Diagnostics' link in top right of screen

2. From the Diagnostic drop down select 'Set Trace Level' and click 'Go'

3.There will be Six Options In the LOV

4. Select The appropriate option

5. Note down the Trace ID Numbers returned and click 'Save'

6 . Perform The Search/Operation.

7. Select "Disable Trace " after Operation.

8. The trace files should be output to the directory returned by the following query

How to checks the concurrent request states/ Status

A PL/SQL procedure can check the status of a concurrent request by calling.

FND_CONCURENT.GET_REQUEST_STATUS
FND_CONCURRENT.WAIT_FOR_REQUEST

 FND_CONCURRENT.GET_REQUEST_STATUS :
-          This function returns the status of a concurrent request
-          If the request is already computed, it also returns the completion message.
-          This function returns both user friendly (translatable) phase and status values as well as developer phase and status vales that can drive program logic.

Syntax  Function FND_CONCURRENT.GET_REQUEST_STATUS
                   ( request_id in out number,
                   application in varchar2 default null,
                   program in varchar2 default null,
                   phase out varchar2,
                   status out varchar,
                   dev_phase out varchar2,
                   dev_status out varchar2,
                   message out varchar2) return BOOLEAN;

-          when application and program are specified, the request_id of the last request for the specified program should be returned to request_id.
-          Phase, and status values should be taken from

FND_LOOKUPS
dev_phase                                          dev_status
pending                                    normal, standby, scheduled, paused
running                                     normal, waiting, resuming, terminating.
Complete                                  normal, Error, warning, cancelled, terminated
Inactive                                     disabled, on-hold, No-manager, supended

FND_REQUEST.WAIT_FOR_REQUEST :
-          This function waits for request completion, then returns the request phase/status and completion message to the caller.
-          Goes to sleep between checks for request completion.

Syntax FND_CONCURRENT.WAIT_FOR_REQUEST
     ( request_id in number default null,
     interval in number default 60,
     max_wait in numbe default 0,
     phase out varchar2,
     status out varchar2,
dev_phase out varchar2,
dev_status out varchar2,
message out varchar2) return BOOLEN;

 FND_CONCURRENT.SET_COMPLETION_STATUS :
-          this function should be called from a concurrent program to set its completion states.
-          This function returns TRUE on success, other wise FALSE.
Syntax  function 
FND_CONCURRENT.SET_COMPLETION_STATUS
     ( status in varchar2,          message in varchar2) return BOOLEAN;
Normal status  warning message any message Error

How To Run Concurrent Program From Back end (Psql) and notify to USER

You can Run Concurrent program from backend by using FND_REQUEST.SUBMIT_REQUEST API.
And By using FND_REQUEST.ADD_NOTIFICATION  API You can send notification mail to particular User.

Below given script is used for one of my client place where we want to run concurrent program from backend.

First you have to initialize apps by using API fnd_global.apps_initialize.

script output gives you concurrent program request id. which you can check from front end.

********************************************************
create or replace procedure XXFNDREQPROC   AS
v_request_id number;
v_request_id1 number;
v_request_id2 number;
v_request_id3 number;
v_request_id4 number;
v_request_id5 number;
v_notify_user  boolean;
l_user_id NUMBER:=0;
l_responsibility_id NUMBER:=55550;
l_resp_appl_id NUMBER:=222;
P_ORG_ID NUMBER;
begin
fnd_global.apps_initialize(l_user_id,l_responsibility_id,l_resp_appl_id);
mo_global.init ('S');
v_notify_user := FND_REQUEST.ADD_NOTIFICATION ('USER_NAME');
--v_test:=v_notify_user;
---dbms_output.PUT_LINE('result'||v_notify_user);
--MO_GLOBAL.SET_ORG_CONTEXT(P_ORG_ID,NULL,'AR');
--MO_GLOBAL.SET_POLICY_CONTEXT('S',P_ORG_ID);
v_request_id := fnd_request.submit_request
(application=>'APPLICATION SHORT NAME',
program=>'CONCURRENT PROG SHORT NAME',
description=>NULL,
start_time=>NULL,
sub_request=>FALSE,
argument1=>'PARAMETER1',
argument2=>'PARAMETER2',
argument3=>'PARAMETER3',
argument4=>'PARAMETER4');


commit;
if v_request_id > 0 then
   dbms_output.put_line('Successfully submitted'||V_REQUEST_ID );
 
else
  dbms_output.put_line('Not Submitted'||V_REQUEST_ID );
  end if;
EXCEPTION WHEN OTHERS THEN              
DBMS_OUTPUT.PUT_LINE(SQLCODE||' Error :'||SQLERRM);
END;


*******************************************************

How to checks the request states?


A PL/SQL procedure can check the status of a concurrent request by calling.

FND_CONCURENT.GET_REQUEST_STATUS
FND_CONCURRENT.WAIT_FOR_REQUEST

 FND_CONCURRENT.GET_REQUEST_STATUS :
-          This function returns the status of a concurrent request
-          If the request is already computed, it also returns the completion message.
-          This function returns both user friendly (translatable) phase and status values as well as developer phase and status vales that can drive program logic.

Syntax  Function FND_CONCURRENT.GET_REQUEST_STATUS
                   ( request_id in out number,
                   application in varchar2 default null,
                   program in varchar2 default null,
                   phase out varchar2,
                   status out varchar,
                   dev_phase out varchar2,
                   dev_status out varchar2,
                   message out varchar2) return BOOLEAN;

-          when application and program are specified, the request_id of the last request for the specified program should be returned to request_id.
-          Phase, and status values should be taken from

FND_LOOKUPS
dev_phase                                          dev_status
pending                                    normal, standby, scheduled, paused
running                                     normal, waiting, resuming, terminating.
Complete                                  normal, Error, warning, cancelled, terminated
Inactive                                     disabled, on-hold, No-manager, supended

FND_REQUEST.WAIT_FOR_REQUEST :
-          This function waits for request completion, then returns the request phase/status and completion message to the caller.
-          Goes to sleep between checks for request completion.

Syntax FND_CONCURRENT.WAIT_FOR_REQUEST
     ( request_id in number default null,
     interval in number default 60,
     max_wait in numbe default 0,
     phase out varchar2,
     status out varchar2,
dev_phase out varchar2,
dev_status out varchar2,
message out varchar2) return BOOLEN;

 FND_CONCURRENT.SET_COMPLETION_STATUS :
-          this function should be called from a concurrent program to set its completion states.
-          This function returns TRUE on success, other wise FALSE.
Syntax  function 
FND_CONCURRENT.SET_COMPLETION_STATUS
     ( status in varchar2,          message in varchar2) return BOOLEAN;
Normal status  warning message any message Error

Concurrent Program Incompatibilities

How to add incompatibilities for concurrent program :
1. Navigate to system Administrator Responsibility.  
Concurrent -> Program -> Define
2. Query concurrent program to which you want to add incompatibilities.

3. click on "Incompatibilities" button from bottom of screen, it will open new form to add incompatible program or set to this concurrent program.



 4. We can add or remove incompatible program list for our concurrent program using this screen.
 suppose i want to add same program as incompatible program to "Invoice Print Preview Report" program. simply we need to add one more line with program details. we can select value for scope as "Set" or "Program".

 
5. There are two types of program incompatibilities, “Global” incompatibilities, and “Domain” incompatibilities. You can define a concurrent program to be globally incompatible with another program that is, the two programs cannot be run simultaneously at all; or you can define a concurrent program to be incompatible with another program in a Conflict Domain. Conflict domains are abstract representations of groups of data. They can correspond to other group identifiers, such as sets of books, or they can be arbitrary.


6. Post all changes kindly save record. After this changes when you submit this concurrent program request it will show concurrent program phase as "Pending" and status as "Standby" until if any compatible program to our concurrent program is in "Running" phase. Our program will Run after all the compatible program we defined in Incompatibilities screen are in Phase "Completed".


Query to find Incompatible Program List:

SELECT distinct fat.application_id,
                to_run_concurrent_program_id,
                fat.APPLICATION_NAME,
                fcpt.user_concurrent_program_name,
                DECODE(TO_RUN_TYPE, 'S', 'Set', 'Program') TYPE,
                DECODE(INCOMPATIBILITY_TYPE, 'G', 'Global', 'Domain') "Incompatibilty Type"
  FROM FND_CONCURRENT_PROGRAM_SERIAL fcps,
       FND_CONCURRENT_PROGRAMS_TL    fcpt,
       FND_APPLICATION_TL            fat
 WHERE fcps.RUNNING_APPLICATION_ID = fat.application_id
   AND fcpt.CONCURRENT_PROGRAM_ID = fcps.TO_RUN_CONCURRENT_PROGRAM_ID
   AND fcpt.LANGUAGE = 'US'
   AND fat.LANGUAGE = 'US'
--AND fcpt.user_concurrent_program_name like  '%Trial Balance%'
 ORDER BY 1, 2;

Query to get concurrent Program details with Parameter list and value set attached to parameter.

Query :

SELECT fcpt.user_concurrent_program_name,
  fcp.concurrent_program_name,
  fcp.concurrent_program_id,
  fav.application_short_name,
  fav.application_name,
  fav.application_id,
  fdfcuv.column_seq_num,
  fdfcuv.end_user_column_name,
  fdfcuv.form_left_prompt prompt,
  ffvs.flex_value_set_name,
  fdfcuv.enabled_flag,
  fdfcuv.required_flag,
  fdfcuv.display_flag
FROM apps.fnd_concurrent_programs fcp,
  apps.fnd_concurrent_programs_tl fcpt,
  apps.fnd_descr_flex_col_usage_vl fdfcuv,
  apps.fnd_application_vl fav,
  apps.fnd_flex_value_sets ffvs
WHERE fcp.concurrent_program_id       = fcpt.concurrent_program_id
AND fav.application_id                = fcp.application_id
AND fcpt.language                     = 'US'
AND ffvs.flex_value_set_id            = fdfcuv.flex_value_set_id
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'  || fcp.concurrent_program_name
AND fcpt.user_concurrent_program_name = 'Trial Balance'
ORDER BY fdfcuv.column_seq_num;

Output of query :

How to use VArray in PLSQL

--Example for VArray-------------
==========================================
DECLARE
  TYPE abc IS VARRAY(20) OF NUMBER;
  v1 abc;
BEGIN
  v1 := abc();
  v1.extend();
  v1(1) := 1234;
  v1.extend();
  v1(2) := 5678;
  v1.extend();
  v1(3) := 7890;
  dbms_output.put_line('FIRST VALUE ' || v1(1));
  dbms_output.put_line('SECOND VALUE ' || v1(2));
  dbms_output.put_line('THIRD VALUE ' || v1(3));
END;
--------
Output
--------
FIRST VALUE 1234
SECOND VALUE 5678
THIRD VALUE 7890



===========================================
DECLARE
  TYPE abc IS VARRAY(20) OF VARCHAR2(100);
  v1 abc;
BEGIN
  v1 := abc();
  v1.extend();
  v1(1) := 'DEMO FOR';
  v1.extend();
  v1(2) := 'VARRAY';
  v1.extend();
  v1(3) := 'EXAMPLE';
  dbms_output.put_line('FIRST VALUE: ' || v1(1));
  dbms_output.put_line('SECOND VALUE: ' || v1(2));
  dbms_output.put_line('THIRD VALUE: ' || v1(3));
END;
------
Output
------
FIRST VALUE: DEMO FOR
SECOND VALUE: VARRAY
THIRD VALUE: EXAMPLE



===========================================
-- USING LOOP--
DECLARE
  CURSOR c1 is
    select vendor_name from ap_suppliers where rownum <= 20;
  --
  TYPE abc IS VARRAY(20) of VARCHAR2(100);
  v1 abc;
  n  number := 1;
  --
BEGIN
  v1 := abc();
  FOR i in c1 loop
    v1.extend();
    v1(n) := i.vendor_name;
    dbms_output.put_line('Vendor Name ' || v1(n));
    n := n + 1;
  END LOOP;
END;
------
Output
------
Vendor Name: Paresh Consulting
Vendor Name: Price Service Co.
Vendor Name: Umesh Damle
Vendor Name: Vijay Kadam
Vendor Name: Kerala Spa
Vendor Name: The Great Punjab Hotel
Vendor Name: Metal Trading Company
Vendor Name: Satish Cartes
Vendor Name: George Ltd
Vendor Name: Hetal Group Ltd
Vendor Name: Human Resource Consultancy
Vendor Name: MRF Ltd
Vendor Name: Vertex Interactive
Vendor Name: Steve Follie
Vendor Name: Communications Services Inc
Vendor Name: J D Service Co.
Vendor Name: J M Consulting
Vendor Name: Vishwas Patil
Vendor Name: Shirish kadam
Vendor Name: Jivan anand

============================================
DECLARE
  TYPE abc is VARRAY(20) OF NUMBER(10);
  v1 abc;
  n  NUMBER := 1;
BEGIN
  v1 := abc();
  for i in 1 .. 10 loop
    v1.extend();
    v1(i) := i;
    dbms_output.put_line(v1(i));
    dbms_output.put_line('count: ' || v1.count);
    dbms_output.put_line('First: ' || v1.first);
    dbms_output.put_line('Last: ' || v1.last);
  END LOOP;
END;

------
Output
------
1
count: 1
First: 1
Last: 1
2
count: 2
First: 1
Last: 2
3
count: 3
First: 1
Last: 3
4
count: 4
First: 1
Last: 4
5
count: 5
First: 1
Last: 5
6
count: 6
First: 1
Last: 6
7
count: 7
First: 1
Last: 7
8
count: 8
First: 1
Last: 8
9
count: 9
First: 1
Last: 9
10
count: 10
First: 1
Last: 10

AP (Payable) Invoice and Payment details Query

Below given query will fetch details for Payable invoice along with its payments details.
It will fetch details like invoice number, currency code, date, supplier name, invoice amount, line amount, term name, check number ,PO number, payment details, operating unit and ledger details.

Query :

select aia.invoice_num,      
       aia.invoice_currency_code,
       aia.invoice_date,
       aps.vendor_name,
       aila.line_number,
       aia.invoice_amount,
       aila.amount line_amount,
       pha.segment1 po_number,
       aila.line_type_lookup_code,
       apt.name Term_name,      
       gcc.concatenated_segments distributed_code_combinations,
       aca.check_number,
       aipa.amount payment_amount,
       apsa.amount_remaining,
       aipa.invoice_payment_type,
       hou.name operating_unit,
       gl.name ledger_name  
  from apps.ap_invoices_all         aia,
       ap_invoice_lines_all         aila,
       ap_invoice_distributions_all aida,
       ap_suppliers aps,
       po_headers_all pha,
       gl_code_combinations_kfv gcc,
       ap_invoice_payments_all aipa,
       ap_checks_all aca,
       ap_payment_schedules_all apsa,
       ap_terms apt,
       hr_operating_units hou,
       gl_ledgers gl
 where aia.invoice_id = aila.invoice_id
   and aila.invoice_id = aida.invoice_id
   and aila.line_number = aida.invoice_line_number
   and aia.vendor_id=aps.vendor_id
   and aia.po_header_id=pha.po_header_id(+)
   and aida.dist_code_combination_id=gcc.code_combination_id
   and aipa.invoice_id(+)=aia.invoice_id
   and aca.check_id   (+)=aipa.check_id
   and apsa.invoice_id=aia.invoice_id
   and apt.term_id=aia.terms_id
   and hou.organization_id=aia.org_id
   and gl.ledger_id=aia.set_of_books_id
   and aia.invoice_num=&p_invoice_num

 
 
 


 
 
 

Query to find Short text documents (attachment)

Below given query will give you details for short text document attachment details.
In this query i passed 2 parameter p_entity_name and p_prim_key_id.
One can pass p_entity_name parameter value as "OE_ORDER_HEADERS" then we must pass p_prim_key_id parameter value as header_id from OE_ORDER_HEADERS_ALL table.

Similarly you can pass p_entity_name value as 'PO_HEADERS', 'PO_LINES', 'OE_ORDER_HEADERS', 'OE_ORDER_LINES','AP_INVOICES','AP_CHECKS'  etc. to get details for short text attached to this entity.

Query :
SELECT fad.seq_num,
       fdct.user_name,
       fdot.title,
       fdot.description,
       fdst.short_text,
       fad.entity_name,
       fdct.user_name
  FROM fnd_attached_documents     fad,
       fnd_documents              fdob,
       fnd_documents_tl           fdot,
       fnd_document_categories_tl fdct,
       fnd_documents_short_text   fdst
 WHERE fad.document_id = fdob.document_id
   AND fad.document_id = fdot.document_id
   AND fdob.document_id = fdot.document_id
   AND fdob.category_id = fdct.category_id
   AND fdot.language = userenv('LANG')
   AND fdct.language = userenv('LANG')
   AND fad.entity_name = &p_entity_name
   AND fad.pk1_value = TO_CHAR(&p_prim_key_id)
   AND fdst.media_id = fdob.media_id;

Screenshot for sample attachment :

Query to find Request group name to which concurrent program is attached.

Below given query will give you details for concurrent program and request group to which CP is attached details.

Query :

SELECT fcpt.user_concurrent_program_name,
       DECODE(frgu.request_unit_type,
              'P', 'Program',
              'S','Set',
              frgu.request_unit_type) Request_Unit_Type,
       fcp.concurrent_program_name short_name,
       frg.application_id,
       frg.request_group_name,
       fat.application_name,
       fa.application_short_name,
       fa.basepath
  FROM fnd_request_groups         frg,
       fnd_request_group_units    frgu,
       fnd_concurrent_programs    fcp,
       fnd_concurrent_programs_tl fcpt,
       fnd_application            fa,
       fnd_application_tl         fat
 WHERE frg.request_group_id = frgu.request_group_id
   AND frgu.request_unit_id = fcp.concurrent_program_id
   AND fcp.concurrent_program_id = fcpt.concurrent_program_id
   AND frg.application_id = fat.application_id
   AND fa.application_id = fat.application_id
   AND fcpt.language = USERENV('LANG')
   AND fat.language = USERENV('LANG')
   AND UPPER(fcpt.user_concurrent_program_name) like
       UPPER('Trial Balance - Detail');