Tuesday 19 September 2017

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;

Friday 8 September 2017

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;

Thursday 7 September 2017

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;











Tuesday 5 September 2017

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;

How to resolve issue for BIP RTF template XML tags showing value as <?ref: 0000xx?>

 How to resolve issue for BIP RTF template XML tags showing value as <?ref: 0000xx?>  Sometime these xml data tags automatically chang...