Query 1: Reset User Password

Begin
fnd_user_pkg.updateuser(x_user_name => 'SKADIYALA',
x_owner => NULL,
x_unencrypted_password => 'welcome123');
COMMIT;
END;


Query 2:You know table get the package or any where

select * from ALL_DEPENDENCIES
where referenced_name = 'FPC_SPARES_INV_LOC_REP';

Query 3: Select responsibility name along with application name

SELECT application_short_name, frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt, fnd_application fa
WHERE fa.application_id = frt.application_id;

 Get the Xml data file name
  SELECT distinct LobsEO.LANGUAGE,
    LobsEO.LOB_TYPE,
    LobsEO.APPLICATION_SHORT_NAME,
    LobsEO.LOB_CODE,
    LobsEO.TERRITORY,
    LobsEO.FILE_NAME,
    LobsEO.FILE_STATUS,
    header.TEMPLATE_ID,
    header.TEMPLATE_CODE,
    header.DS_APP_SHORT_NAME ,
    header.DATA_SOURCE_CODE ,
    header.TEMPLATE_TYPE_CODE                                       AS TEMPLATE_TYPE,
    language.NAME                                                   AS LANGUAGE_MEANING,
    DECODE(LobsEO.Territory,'00','',territory.TERRITORY_SHORT_NAME) AS TERRITORY_MEANING,
    (
    CASE
      WHEN header.DEPENDENCY_FLAG = 'C'
      OR sample.FILE_NAME        IS NULL
      THEN 'MLSPreviewDisabled'
      ELSE 'MLSPreviewEnabled'
    END) AS PreviewImage,
    (
    CASE
      WHEN LobsEO.TRANS_COMPLETE = 'Y'
      THEN 'Complete'
      ELSE 'Partial'
    END) AS TRANSLATION_STATUS,
    'N'  AS SELECTED
  FROM apps.xdo_templates_vl header,
    apps.XDO_Lobs LobsEO,
    apps.XDO_Lobs sample,
    apps.FND_ISO_LANGUAGES_VL language,
    apps.fnd_territories_vl territory
  WHERE header.APPLICATION_SHORT_NAME = LobsEO.APPLICATION_SHORT_NAME
  AND header.TEMPLATE_CODE            =LobsEO.LOB_CODE
 --- AND LobsEO.lob_type                 = 'MLS_TEMPLATE'
  and header.DS_APP_SHORT_NAME =sample.APPLICATION_SHORT_NAME(+)
  AND header.DATA_SOURCE_CODE         = sample.LOB_CODE(+)
  --AND sample.LOB_TYPE(+)              ='XML_SAMPLE'
  AND lower(LobsEO.LANGUAGE)          =language.ISO_LANGUAGE_2(+)
  AND territory.territory_code(+)     = LobsEO.territory
  and header.TEMPLATE_CODE like'%FPC_944_ITSG_CON_DTL_INVOICE%'

Query 4: Get Menu name for Responsibility ID , You can find out responsibility_id from Query 1

SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = &resp_id
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';

Query 5: Get User name and related assigned responsibilities

SELECT DISTINCT u.user_id, u.user_name user_name,
r.responsibility_name responsiblity,
a.application_name application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY 1;

Query 6: Get Request Group associate with Responsibility Name

SELECT responsibility_name responsibility, request_group_name,
frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

Query 7: Gets Form personalization listing

Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables
applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes

SELECT ffft.user_function_name "User Form Name", ffcr.SEQUENCE,
ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,
ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;

Query 8: Query to view the patch level status of all modules

SELECT a.application_name,
DECODE (b.status, 'i', 'installed', 's', 'shared', 'n / a') status,
patch_level
FROM apps.fnd_application_vl a, apps.fnd_product_installations b
WHERE a.application_id = b.application_id;

Query 9: SQL to view all request who have attached to a responsibility

SELECT responsibility_name, frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'p'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

Query 10: SQL to view all requests who have attached to a responsibility

SELECT responsibility_name, frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'p'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
and  fcpv.user_concurrent_program_name like :program_name
ORDER BY responsibility_name;14

Query 11: SQL to view all types of request Application wise

SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name,
minimum_width,
minimum_length, concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description

Query 10: SQL to view concurrent request processing time, quite useful

SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
,
DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.description||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.ac




Query 12: SQL to view concurrent program and executable ,executable method ,executable file name ,concurrent program short name

SELECT 'Program User Name: ' || fcpt.user_concurrent_program_name prog_name,
'Description: ' || fcpt.description description,
'Executable Name: ' || exe.executable_name exe_name,
'Executable File Nmae: ' || exe.EXECUTION_FILE_NAME,
'Executable File: ' || exe.execution_file_name exe_file_name,
'Execution Type: ' ||
DECODE(exe.execution_method_code,
'B',
'Request Set Stage Function',
'Q',
'SQL*Plus',
'H',
'Host',
'L',
'SQL*Loader',
'A',
'Spawned',
'I',
'PL/SQL Stored Procedure',
'P',
'Oracle Reports',
'S',
'Immediate',
exe.execution_method_code) exe_method,
--fnd.concurrent_program_id,
fnd.CONCURRENT_PROGRAM_NAME Short_name
FROM applsys.fnd_executables exe,
applsys.fnd_concurrent_programs fnd,
applsys.fnd_concurrent_programs_tl fcpt
WHERE exe.executable_id = fnd.executable_id
AND fnd.concurrent_program_id = fcpt.concurrent_program_id
AND fnd.application_id = fcpt.application_id
and exe.execution_method_code='I'
ORDER BY FND.CREATION_DATE DESC



 Query 13: AR To GL



SELECT    gjjlv.period_name             "Period Name"
        , gjb.name                      "Batch Name"
        , gjjlv.header_name             "Journal Entry For"
        , gjjlv.je_source               "Source"
        ,glcc.concatenated_segments     "Accounts"
        , NVL(gjjlv.line_entered_dr,0)  "Entered Debit"
        , NVL(gjjlv.line_entered_cr,0)   "Entered Credit"
        , NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
        , NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
        , gjjlv.currency_code            "Currency"
        , rctype.name                    "Trx type"
        , rcta.trx_number                "Trx Number"
        , rcta.trx_date                  "Trx Date"
        , RA.CUSTOMER_NAME               "Trx Reference"
        , gjh.STATUS                     "Posting Status"
        , TRUNC(gjh.DATE_CREATED)        "GL Transfer Dt"
        , gjjlv.created_by               "Transfer By"
FROM    apps.GL_JE_JOURNAL_LINES_V gjjlv
      , gl_je_lines gje
      , gl_je_headers gjh
      , gl_je_batches gjb
      , ra_customer_trx_all rcta
      , apps.ra_customers ra
      , apps.gl_code_combinations_kfv glcc
      , ra_cust_trx_types_all rctype
WHERE     gjh.period_name IN ('OCT-2008','NOV-2008')
AND       glcc.code_combination_id = gje.code_combination_id
AND       gjh.je_batch_id = gjb.je_batch_id
AND       gjh.je_header_id = gje.je_header_id
AND       gjh.period_name = gjb.default_period_name
AND       gjh.period_name = gje.period_name
AND       gjjlv.period_name = gjh.period_name
AND       gjjlv.je_batch_id = gjh.je_batch_id
AND       gjjlv.je_header_id = gjh.je_header_id
AND       gjjlv.line_je_line_num  = gje.je_line_num
AND       gjjlv.line_code_combination_id = glcc.code_combination_id
AND       gjjlv.line_reference_4 = rcta.trx_number
AND       rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND       rcta.org_id = rctype.org_id
AND       ra.customer_id = rcta.bill_to_customer_id


Query 14: Query to find out concurrent program details and its parameters

SELECT fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , fcp.concurrent_program_id
     , fav.APPLICATION_SHORT_NAME
     , fav.APPLICATION_NAME
     , fav.application_id
     , fdfcuv.end_user_column_name
     , fdfcuv.form_left_prompt prompt
     , fdfcuv.enabled_flag
     , fdfcuv.required_flag
     , fdfcuv.display_flag
FROM   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl fdfcuv
     , fnd_application_vl fav
WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
AND    fcpl.user_concurrent_program_name = :conc_prg_name
AND    fav.application_id=fcp.application_id
AND    fcpl.LANGUAGE = 'US'
AND    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;


 Query 15: Query to find out responsibility and request group for concurrent program:
SELECT DISTINCT
  FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
  APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
          FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE :conc_prg_name
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';
Posted by Subhash G at 1:58 AM 1 comments
Labels: AOL, CONCURRENT PROGRAM, FND


 Query 16:Concurrent Program with request group details


SELECT   frg.request_group_name
       , fav1.APPLICATION_SHORT_NAME "RequestGroup Appln Shortname"
       , fav1.APPLICATION_NAME "RequestGroup Application Name"
       , fav1.application_id "RequestGroup Application ID"
       , fcp.concurrent_program_name
       , fcpl.user_concurrent_program_name
       , fav.APPLICATION_SHORT_NAME "Con Prog Application Shortname"
       , fav.APPLICATION_NAME "Con Prog Application Name"
       , fav.application_id "Con Prog Application ID"
       , fe.execution_file_name
       , fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpl,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application_vl fav,
       fnd_application_vl fav1
 WHERE frgu.request_unit_id = fcp.concurrent_program_id
   AND fav.application_id=fcp.application_id
   AND fav1.application_id=frgu.application_id
   AND frgu.request_group_id = frg.request_group_id
   AND fe.executable_id = fcp.executable_id
   AND fcp.concurrent_program_name = :conc_prg_name;

Query 17:Query to list concurrent program details with its parameter, values set and default value/type:

  SELECT fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , fav.APPLICATION_SHORT_NAME
      , fav.APPLICATION_NAME
      , fav.application_id
      , fdfcuv.end_user_column_name
      , fdfcuv.form_left_prompt prompt
      , fdfcuv.enabled_flag
      , fdfcuv.required_flag
      , fdfcuv.display_flag
      , fdfcuv.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , fdfcuv.DEFAULT_VALUE    
 FROM   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl fdfcuv
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
      , fnd_application_vl fav
 WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
 AND    fcpl.user_concurrent_program_name = :conc_prg_name
 AND    fcpl.LANGUAGE = 'US'
 AND    fav.application_id=fcp.application_id
 AND    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
 AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
 AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
 AND    flv.lookup_code(+) = fdfcuv.default_type
 AND    flv.LANGUAGE(+) = USERENV ('LANG');

 Query 18:
 Select Distinct C.User_Concurrent_Program_Name, a.user_request_set_name
From
fnd_request_sets_vl a,
fnd_request_set_programs b,
fnd_req_set_stages_form_v d,
fnd_concurrent_programs_vl c
Where
a.Request_Set_Id = d.Request_Set_ID
And A.Request_Set_Id = b.Request_Set_Id
And A.Application_Id = b.Set_Application_Id
And A.Application_Id = d.Set_Application_Id
And d.Request_Set_Stage_Id = b.Request_Set_Stage_Id
--And a.user_request_set_name Like '11i AR Slow Start Reports'
and C.User_Concurrent_Program_Name like'MasTec (AT) CAR Transfer to AP Process WB'
And b.concurrent_program_id = c.concurrent_program_id
And b.Program_Application_Id = C.Application_Id

 Query 19 : AR To GL



SELECT    gjjlv.period_name             "Period Name"
        , gjb.name                      "Batch Name"
        , gjjlv.header_name             "Journal Entry For"
        , gjjlv.je_source               "Source"
        ,glcc.concatenated_segments     "Accounts"
        , NVL(gjjlv.line_entered_dr,0)  "Entered Debit"
        , NVL(gjjlv.line_entered_cr,0)   "Entered Credit"
        , NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
        , NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
        , gjjlv.currency_code            "Currency"
        , rctype.name                    "Trx type"
        , rcta.trx_number                "Trx Number"
        , rcta.trx_date                  "Trx Date"
        , RA.CUSTOMER_NAME               "Trx Reference"
        , gjh.STATUS                     "Posting Status"
        , TRUNC(gjh.DATE_CREATED)        "GL Transfer Dt"
        , gjjlv.created_by               "Transfer By"
FROM    apps.GL_JE_JOURNAL_LINES_V gjjlv
      , gl_je_lines gje
      , gl_je_headers gjh
      , gl_je_batches gjb
      , ra_customer_trx_all rcta
      , apps.ra_customers ra
      , apps.gl_code_combinations_kfv glcc
      , ra_cust_trx_types_all rctype
WHERE     gjh.period_name IN ('OCT-2008','NOV-2008')
AND       glcc.code_combination_id = gje.code_combination_id
AND       gjh.je_batch_id = gjb.je_batch_id
AND       gjh.je_header_id = gje.je_header_id
AND       gjh.period_name = gjb.default_period_name
AND       gjh.period_name = gje.period_name
AND       gjjlv.period_name = gjh.period_name
AND       gjjlv.je_batch_id = gjh.je_batch_id
AND       gjjlv.je_header_id = gjh.je_header_id
AND       gjjlv.line_je_line_num  = gje.je_line_num
AND       gjjlv.line_code_combination_id = glcc.code_combination_id
AND       gjjlv.line_reference_4 = rcta.trx_number
AND       rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND       rcta.org_id = rctype.org_id
AND       ra.customer_id = rcta.bill_to_customer_id


 Query 20:Iexpance with WF

SELECT to_char(aerh.report_header_id) expense_report
       ,aerh.invoice_num
       ,aerh.creation_date
       ,aerl.start_expense_date expense_date
       ,per.employee_number emp_number
       ,substr(last_name || ', ' || first_name, 1, 25) emp_name
       ,per.attribute8 pcc
       ,per.attribute9 dept
       ,substr(aerl.item_description,1,40) exp_type
       ,aerl.amount
       ,aerl.currency_code currency
       ,pp.segment1 project_num
       ,pp.NAME proj_name
       ,pt.task_number
       ,hau.NAME proj_org
       ,flv.meaning er_status
       ,wiav.text_value approver
       ,wias.end_date approval_date
       ,aerh.report_submitted_date date_submited
FROM ap_expense_report_headers aerh,
        ap_expense_report_lines  aerl,
        ap_exp_report_dists      aerd,
        per_people_f              per,
        fnd_lookup_values_vl      flv,
        pa_projects_all           pp,
        pa_tasks                  pt,
        hr_all_organization_units hau,
        wf_item_activity_statuses wias,
wf_item_attribute_values  wiav
WHERE 1 = 1
AND aerh.employee_id = per.person_id
AND SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date
AND aerh.expense_status_code NOT IN ('CANCELLED', 'PAID')
AND aerh.expense_status_code IS NOT NULL
AND flv.lookup_type = 'EXPENSE REPORT STATUS'
AND flv.lookup_code = aerh.expense_status_code
AND aerl.report_header_id = aerh.report_header_id
AND aerl.report_line_id = aerd.report_line_id
AND aerd.project_id = pp.project_id(+)
AND aerd.task_id = pt.task_id(+)
AND aerd.project_id = pt.project_id(+)
AND hau.organization_id(+) = pp.carrying_out_organization_id
AND wias.ACTIVITY_RESULT_CODE='APPROVED'
AND wias.item_type = 'APEXP'
AND wias.item_key = to_char(aerh.report_header_id)
AND wias.process_activity = (SELECT MIN(process_activity)
                              FROM wf_item_activity_statuses wis2
                              WHERE wis2.item_key = wias.item_key
                              AND wis2.item_type = wias.item_type
                              AND wis2.ACTIVITY_RESULT_CODE = wias.ACTIVITY_RESULT_CODE)
AND wiav.item_type = wias.item_type
AND wiav.item_key = wias.item_key                            
AND wiav.name = 'APPROVER_DISPLAY_NAME'
AND ROWNUM<=5

 Query 20:Iexpance with AME

    SELECT erh.invoice_num expense_report
          ,(SELECT full_name
            FROM   per_people_f
            WHERE  person_id = erh.employee_id
            AND    SYSDATE BETWEEN effective_start_date AND effective_end_date) employee_name
          ,ppf.full_name submitter_name
          ,erh.week_end_date week_end_date
          ,erh.description description
          ,erh.total amount
          ,(SELECT ppf.full_name
            FROM   per_all_people_f ppf
                  ,fnd_user         fu
            WHERE  fu.employee_id = ppf.person_id
            AND    SYSDATE BETWEEN ppf.effective_start_date AND nvl(ppf.effective_end_date, SYSDATE)
            AND    fu.user_name =
                   (SELECT ame.NAME
                     FROM   ame_approvals_history ame
                     WHERE  ame.transaction_id = erh.report_header_id
                     AND    ame.approval_status = 'APPROVE'
                     AND    ame.row_timestamp = (SELECT MAX(ame1.row_timestamp)
                                                 FROM   ame_approvals_history ame1
                                                 WHERE  ame1.transaction_id = ame.transaction_id))) approver
          ,apinv.creation_date invoice_imported_date
    FROM   ap_expense_report_headers_all erh
          ,fnd_user                      fndu
          ,per_all_people_f              ppf
          ,ap_invoices_all               apinv
    WHERE  erh.created_by = fndu.user_id
          --and apinv.invoice_num = 'CAD-IE116487'
    AND    fndu.employee_id = ppf.person_id
    AND    SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date
    AND    erh.SOURCE = 'SelfService'
    AND    erh.workflow_approved_flag IN ('Y', 'A')
    AND    apinv.SOURCE = 'SelfService'
    AND    erh.invoice_num = apinv.invoice_num
    AND    apinv.payment_status_flag <> 'Y' -- invoices not paid
    AND    apinv.cancelled_amount IS NULL -- Exclude cancelled invoices
          --and apinv.creation_date < ('11-JAN-2006')
    AND    trunc(apinv.creation_date) BETWEEN (trunc(p_start_date)) AND (trunc(p_end_date))
    ORDER  BY erh.invoice_num
             ,erh.week_end_date;


-------------
AR Invoice
-------------------
SELECT rt.name "Term Name" ,
rct.trx_number "Invoice Number",
RCT.ORG_ID,
rct.creation_date "Creation Date",
rct.trx_date "Invoice Date",
rac.customer_number "Customer Number",
rac.customer_name "Customer Name",
DECODE(aps.status,'OP','Open','CL','Close', aps.status)invoice_status,
INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1
FROM apps.ra_customer_trx_all rct,
apps.ar_payment_schedules_all aps,
apps.ra_terms_vl rt,
apps.ra_customers rac
WHERE rct.customer_trx_id = aps.customer_trx_id
AND rct.term_id = rt.term_id
AND rct.bill_to_customer_id = rac.customer_id
and aps.status = 'OP'
AND rt.name in ( 'ADVANCE', '1%10NET30','2%DIS-45','3% 18/NET 30','ARREARS-NET 30','CIT Leasing','COD','FPC LEASE',
'GE Finance1','GE-CDF','ML NET 30','NET 120 DAYS','NET 30 PROX (1)','NET 48','NET 65','NET 7 DAYS','PREPAID','PREPAYMENT',
'TCFC','TEXTRON')
order by rt.name desc

------------------.
SELECT rt.description,
       rt.name terms_code,
       discount_days,
       discount_percent,
       due_days "Grace Period Net Days"              --,       RELATIVE_AMOUNT
  FROM apps.ra_terms_vl rt, ra_terms_lines_discounts rtld, ra_terms_lines rtl
 WHERE     rt.term_id = rtl.term_id(+)
       AND rtl.term_id = rtld.term_id(+)
       AND rt.end_date_active IS NULL