1.Query to find Schedule of any concurrent program

SELECT fcr.request_id
     , fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
     , fu.user_name requestor
     , fu.description requested_by
     , fu.email_address
     , frt.responsibility_name requested_by_resp
     , trim(fl.meaning) status
     , fcr.phase_code
     , fcr.status_code
     , fcr.argument_text "PARAMETERS"
      , TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
     , TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start  
     , TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
     , DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
     , CASE
          WHEN fcr.hold_flag = 'Y'
             Then Substr(
                    fu.description
                  , 0
                  , 40
                 )
       END last_update_by
     , CASE
          WHEN fcr.hold_flag = 'Y'
             THEN fcr.last_update_date
       END last_update_date
     , fcr.increment_dates
     , CASE WHEN fcrc.CLASS_INFO IS NULL THEN
        'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
       ELSE
        'n/a'
       END run_once
     , CASE WHEN fcrc.class_type = 'P' THEN
        'Repeat every ' ||
        substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
               'N', ' minutes',
               'M', ' months',
               'H', ' hours',
               'D', ' days') ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
               'S', ' from the start of the prior run',
               'C', ' from the completion of the prior run')
       ELSE
         'n/a'
       END set_days_of_week
       , CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
          'Days of week: ' ||
                  decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
                  decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
                  decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
                  decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
                  decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
                  decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
                  decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
         ELSE
           'n/a'
         end  days_of_week
  FROM fnd_concurrent_requests fcr
     , fnd_user fu
     , fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpt
     , fnd_printer_styles_tl fpst
     , fnd_conc_release_classes fcrc
     , fnd_responsibility_tl frt
     , fnd_lookups fl
 WHERE fcp.application_id = fcpt.application_id
   AND fcr.requested_by = fu.user_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND fcr.concurrent_program_id = fcpt.concurrent_program_id
   AND fcr.responsibility_id = frt.responsibility_id
   AND fcr.print_style = fpst.printer_style_name(+)
   AND fcr.release_class_id = fcrc.release_class_id(+)
   AND fcr.status_code = fl.lookup_code
   AND fl.lookup_type = 'CP_STATUS_CODE'
   AND fcr.phase_code = 'P'
   AND 1=1

Order By Fu.Description, Fcr.Requested_Start_Date Asc;

2.Query to find the responsibilities to which concurrent program program is attached


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 = 'Your Program Name'
    ORDER BY 1,2,3,4

3.Query to find any program detail 

SELECT   DISTINCT fcp.user_concurrent_program_name "Concurrent Program Name",
                  fcp.description "Concurrent Program Description",
                  fef.executable_name"Executable Name",fef.description "Executable Description",
                  fef.execution_file_name "Procedure Name"
             FROM fnd_executables_form_v fef,
                  fnd_concurrent_programs_vl fcp
            WHERE fcp.APPLICATION_ID=fef.APPLICATION_ID
              AND fef.EXECUTABLE_ID=fcp.EXECUTABLE_ID
           --AND fef.executable_name='XX_EXECUTABLE'---Youer Executable Name
              AND fcp.user_concurrent_program_name like 'Belron GL%'--Your Consurrent Program name

AutoInvoice Grouping Rules In Oracle

In this post we will see Autoinvoice Grouping Rules in Oracle in detail

AutoInvoice Grouping Rules in oracle

Autoinvoice grouping rules are used to group the lines in Invoices, debit memo and credit memo in oracle. Autoinvoice grouping rules in oracle determine how lines will appear in a invoice or a credit/debit memo.

Steps to create Autoinvoice Grouping Rules in oracle:

Responsibility: Navigate to Receivables Manager

Go to Setup then -> Transactions  then -> Auto Invoice then -> Grouping Rules

AutoInvoice Grouping Rules In Oracle

If you are using line ordering rules then you have to select line ordering rules as well. If we do not give any optional Grouping Characteristics, Autoinvoice will match it to mandatory characteristics. Oracle provides options to assign Optional characteristics for Invoices, Credit Memos and Debit Memos.

AutoInvoice Grouping Rules In Oracle receivables

If you want to provide optional attributes for all invoices, debit memos and credit memos, You need to first select the class as invoice and then give the group by rules , similarly place the cursor on class use the down arrow to define attributes for other classes(Credit Memo and Debit Memo) as well. 

AutoInvoice Grouping Rules in Oracle|AutoInvoice Grouping Rules|| Autoinvoice grouping rules 

How to process refund in oracle account payables

In this post we will see how we process Refund in Accounts payables oracle Account payables

Below are the steps shown  to process refund in oracle payables

Lets take few Assumptions: A credit Memo is already available against a supplier and is also available for creating Refund from the Supplier

Step 1) 

(a) Navigate to responsibility -> Account Payable.

(b) Go to the Payments screen Following the path Payments-> Entry>Payments

Step 2) 

Payment workbench will open up,

In ‘Type’  filed Select  ‘Refund’  and also select values in all required fields(highlighted in yellow)

Refund in Account payable oracle AP

In Payment amount(Enter - amount only )(as this is a refund)

Refund in Accounts payables oracle AP

   Click on the button ‘Enter/Adjust Invoices’

Refund in Accounts payables oracle AP

Select the ‘Invoice Number’  against which refund has to be raised ( this will be the Credit Memo which was already created by supplier) 

Refund In Accounts payables oracle AP

     Click ‘Save’, the payment is completed.

Refund In Accounts payables oracle|Refund In Oracle|Process Refund In Accounts payables AP