Find planned orders by supplier

One of our customers wanted to see the value of planned orders for a particular supplier, dollarized at cost. This simple Excel-Out report gives them a simple view of their purchasing power and allows them bargain with the vendor for the entire volume of planned purchasing rather then from one PO to another.

p_paremeter01 -  the name of the ASCP plan, required
p_paremeter02 -  vendor name, optional


SELECT order_type,order_type_text order_type,substr(ORGANIZATION_CODE,5,4) Branch,
       ITEM_SEGMENTS Item,  DESCRIPTION,
       NEW_DUE_DATE Due_date, SOURCE_VENDOR_NAME,SOURCE_VENDOR_SITE_CODE ,  
        quantity, UOM_CODE,NEED_BY_DATE,STANDARD_COST,WEIGHT,BUYER_NAME,
        STANDARD_COST * quantity value
  FROM MSC_ORDERS_V a
 WHERE    plan_id =(select plan_id from msc_plans where compile_designator=p_paremeter01)
 and order_type_text='Planned order'
 and supplier_name =nvl(p_parameter02, supplier_name)
  AND category_set_id = 1
 
 

AR rebate taken

This Excel-Out report displays the Receivables rebate / discount the customers took for early payment including all the customer and payment details.
The only parameter is p_parameter01 representing the number of months for which to report the discounts. You can enhance it further and limit it by customer if needed.


SELECT hca.account_number "Customer Account #"
, hp.party_name  "Customer Name"
, rcta.trx_number  "Invoice #"
, rcta.trx_date  "Invoice Date"
,apsa.amount_due_original  "Invoice Amount"
,araa.amount_applied  "Payment Amount"
, acra.receipt_number "Receipt #"
, acra.RECEIPT_DATE  "Payment Date"
, ceil(acra.RECEIPT_DATE-rcta.trx_date) "Payment date - Invoice date"
, acra.amount  "Receipt Amount"
,TO_NUMBER (
             DECODE (
                SIGN (APPLIED_PAYMENT_SCHEDULE_ID),
                -1, NULL,
                  NVL (EARNED_DISCOUNT_TAKEN, 0)
                + NVL (UNEARNED_DISCOUNT_TAKEN, 0))) discount,EARNED_DISCOUNT_TAKEN, UNEARNED_DISCOUNT_TAKEN
FROM hz_parties hp
, hz_cust_accounts hca
, ra_customer_trx_all rcta
, ar_cash_receipts_all acra
, ar_payment_schedules_all apsa
, ar_receivable_applications_all araa
, ar_lookups al
where 1=1
and hp.party_id = hca.party_id
and rcta.sold_to_customer_id = hca.cust_account_id
and rcta.org_id = 81
and rcta.customer_trx_id = apsa.customer_trx_id
and araa.applied_customer_trx_id = apsa.customer_trx_id
and acra.cash_receipt_id = araa.cash_receipt_id
and acra.status = al.lookup_code
and al.lookup_type = 'PAYMENT_TYPE'
and trunc(months_between(sysdate,araa.creation_date)) <=to_number(p_parameter01)
and TO_NUMBER (
             DECODE (
                SIGN (APPLIED_PAYMENT_SCHEDULE_ID),
                -1, NULL,
                  NVL (EARNED_DISCOUNT_TAKEN, 0)
                + NVL (UNEARNED_DISCOUNT_TAKEN, 0)))!=0
                order by rcta.trx_number
;

Find all concurrent requests in a request set plus all of their children

This Excel-Out query allows you to find all the requests submitted from a request set and/or all children spawned from inside the body of a request, including grandchildren, grand-grandchildren, etc. The query syntax uses hierarchical query logic to find all the branches of the query tree, regardless of how they were submitted.

The Excel-Out p_parameter01 is the ID of the top concurrent request that initiated the run. Use the standard '10/Number' value set to define it.  I find it particularly useful for diagnosing issues with my ASCP plan run as I have over 200 requests submitted and the report shows me details of the execution , including sequence, duration, start, end, concurrent manager used, completion status and all the parameters for each request.

Since the Default Value field in concurrent request parameters  where we paste the query allows only 2000 characters, you need to split the select statement into 2, and paste it into parameters 991 and 992. They will be concatenated together during fun time. I convenient place to split is after the first decode statement and I marked that in the comments.


select
cque.USER_CONCURRENT_QUEUE_NAME conc_manager,
       fcr.request_id    "Request ID",
       fcr.requested_by "User",
       substr(DECODE (FCR.DESCRIPTION,  NULL,
       FCPTL.USER_CONCURRENT_PROGRAM_NAME,
       FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name",
        decode(trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date)*24, 24 ) ),0,null,trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date)*24, 24 ) )||':')||
       lpad( trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date)*24*60, 60 ) ),2,'0')||':'||  
 lpad(trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date)*24*60*60, 60 ) ),2,'0') "Run time in mi:ss",
       fcr.oracle_process_id "Trace File ID" ,
         decode(fcr.phase_code,'R', 'Running'
                            ,'C', 'Completed'
                            ,'P', 'Pending'
                            ,fcr.phase_code)  phase_code,
       decode(fcr.status_code,'T', 'Terminating'
                             ,'X', 'Terminated'
                             ,'C', 'Normal'
                             ,'I', 'Scheduled'
                             ,'R', 'Normal'
                             ,'G', 'Warning'
                             ,'W', 'Paused'
                             ,'E','Error'
                             ,fcr.status_code) status_code,
       to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS')   "Submitted",
       /*break the query after here, paste the rest into parameter 992*/
         decode(trunc( mod( (fcr.actual_start_date - fcr.request_date)*24, 24 ) ),0,null,trunc( mod( (fcr.actual_start_date - fcr.request_date)*24, 24 ) )||':')||
       lpad( trunc( mod( (fcr.actual_start_date - fcr.request_date)*24*60, 60 ) ),2,'0')||':'||  
 lpad(trunc( mod( (fcr.actual_start_date - fcr.request_date)*24*60*60, 60 ) ),2,'0') "Delay",
       to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS')  "Start Time",
       to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",
       fcr.argument_text "Parameters"
  from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
               fcr1.request_id
          from fnd_concurrent_requests fcr1
         where 1=1
         start with fcr1.request_id = p_parameter01
       connect by prior fcr1.request_id = fcr1.parent_request_id) x,
       fnd_concurrent_requests fcr,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcptl,
       fnd_concurrent_queues_vl cque,
       fnd_concurrent_processes cproc
 where fcr.request_id = x.request_id
   and fcr.concurrent_program_id = fcp.concurrent_program_id
   and fcr.program_application_id = fcp.application_id
   and fcp.application_id = fcptl.application_id
   and fcp.concurrent_program_id = fcptl.concurrent_program_id
   and fcptl.language = 'US'
and cproc.CONCURRENT_QUEUE_ID = cque.CONCURRENT_QUEUE_ID
and cproc.CONCURRENT_PROCESS_ID = fcr.controlling_manager
   order by
 decode(fcr.status_code,'E',1,2), decode(fcr.request_type,'S',1,2),
 fcr.actual_completion_date

Concurrent Request Assignment Responsibility

This is a very handy System Administrator report that shows you all responsibilities that have access to a report through their request group.

Creating reports in Excel-Out directly in System Administrator responsibility  is so quick that it may be more convenient to store your frequently running queries as Excel-Out reports rather than queries on your local or shared drives.

This report has one parameter, the name of the concurrent program. Use the '150 Character Optional' standard value set or create your own value set that contains list of all concurrent programs. You can create that by copying the 'Concurrent Program Name' standard value set and removing the WHERE condition.

select resp.responsibility_name,
pr.user_concurrent_program_name,
ex.executable_name,
ex.application_name
from FND_EXECUTABLES_FORM_V ex,
FND_CONCURRENT_PROGRAMS_VL pr,
FND_REQUEST_GROUP_UNITS rgu,
FND_REQUEST_GROUPS rg,
FND_RESPONSIBILITY_VL resp
where 1=1
and pr.executable_id=ex.executable_id
and rgu.request_group_id=rg.request_group_id
and rgu.request_unit_id=pr.concurrent_program_id
and resp.request_group_id=rg.request_group_id
and pr.user_concurrent_program_name =p_parameter01

Scheduled Concurrent Requests Report

This query can be used in Excel-Out to show all concurrent requests that are scheduled and pending or put on hold, together with their parameters and the time of the next run, frequency of run and schedule type.

select r.request_id,
       p.user_concurrent_program_name || case
         when p.user_concurrent_program_name = 'Report Set' then
          (select ' - ' || s.user_request_set_name
             from fnd_request_sets_tl s
            where s.application_id = r.argument1
              and s.request_set_id = r.argument2
              and language = 'US')
         when p.user_concurrent_program_name = 'Check Periodic Alert' then
          (select ' - ' || a.alert_name
             from alr_alerts a
            where a.application_id = r.argument1
              and a.alert_id = r.argument2
              and language = 'US')
       end concurrent_program_name,
       case
         when p.user_concurrent_program_name != 'Report Set' and
              p.user_concurrent_program_name != 'Check Periodic Alert' then
          r.argument_text
       end argument_text,
       r.requested_start_date next_run,
       r.hold_flag on_hold,
       decode(c.class_type,
              'P',
              'Periodic',
              'S',
              'On Specific Days',
              'X',
              'Advanced',
              c.class_type) schedule_type,
       case
         when c.class_type = 'P' then
          'Repeat every ' ||
          substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
          decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
                 'N',
                 ' minutes',
                 'M',
                 ' months',
                 'H',
                 ' hours',
                 'D',
                 ' days') ||
          decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
                 'S',
                 ' from the start of the prior run',
                 'C',
                 ' from the completion of the prior run')
         when c.class_type = 'S' then
          nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
          decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
          decode(sign(to_number(substr(c.class_info, 33))),
                 '1',
                 'Days of week: ' ||
                 decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
                 decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
                 decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
                 decode(substr(c.class_info, 36, 1), '1', 'We ') ||
                 decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
                 decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
                 decode(substr(c.class_info, 39, 1), '1', 'Sa '))
       end schedule,
       c.date1 start_date,
       c.date2 end_date,
       c.class_info
  from fnd_concurrent_requests r,
       fnd_conc_release_classes c,
       fnd_concurrent_programs_tl p,
       (SELECT release_class_id,
               substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
          FROM (select release_class_id,
                       rank() over(partition by release_class_id order by s) a,
                       s
                  from (select c.class_info,
                               l,
                               c.release_class_id,
                               decode(substr(c.class_info, l, 1),
                                      '1',
                                      to_char(l)) s
                          from (select level l from dual connect by level <= 31),
                               fnd_conc_release_classes c
                         where c.class_type = 'S')
                 where s is not null)
        CONNECT BY PRIOR
                    (a || release_class_id) = (a - 1) || release_class_id
         START WITH a = 1
         group by release_class_id) dates
 where r.phase_code = 'P'
   and c.application_id = r.release_class_app_id
   and c.release_class_id = r.release_class_id
   and nvl(c.date2, sysdate + 1) > sysdate
   and c.class_type is not null
   and p.concurrent_program_id = r.concurrent_program_id
   and p.application_id = r.program_application_id
   and p.language = 'US'
   and dates.release_class_id(+) = r.release_class_id
 order by on_hold, next_run;

Open INV and PO periods checklist

This Excel-Out open Inventory and Purchasing periods checklist comes very handy at the end of a month.

SELECT
opu.name as operating_unit
, per.organization_id as inv_org_id
, par.organization_code as inv_org_code
, per.period_name
, per.period_year
, per.period_num
, flv.meaning as status
, per.period_start_date
, per.schedule_close_date
FROM
org_acct_periods per
, fnd_lookup_values flv
, mtl_parameters par
, hr_all_organization_units org1
, hr_all_organization_units_tl otl
, hr_organization_information org2
, hr_organization_information org3
, hr_operating_units opu
WHERE 1=1
AND flv.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS'
AND flv.enabled_flag(+) = 'Y'
AND per.organization_id = par.organization_id
AND flv.lookup_code(+) = decode(nvl(per.period_close_date,sysdate), per.period_close_date, decode(per.open_flag, 'N', decode(summarized_flag,'N',65,66), 'Y', 4, 'P', 2, 4), 3 )
AND flv.language = 'US'
AND upper(flv.meaning) != 'CLOSED'
AND per.organization_id = org1.organization_id
AND org1.organization_id = otl.organization_id
AND org1.organization_id = org2.organization_id
AND org1.organization_id = org3.organization_id
AND org2.org_information_context = 'Accounting Information'
AND org3.org_information_context = 'CLASS'
AND org3.org_information1 = 'INV'
AND org3.org_information2 = 'Y'
AND org2.org_information3 = opu.organization_id
union all
select 'ALL',null,'Purchasing', period_name, null, period_num,
decode(closing_status,'O','Open', 'C','Closed', 'F','Future', 'N','Never', closing_status) , START_DATE,END_DATE
from  gl_period_statuses where application_id = 201
and closing_status!='C'
and START_DATE<sysdate+15
and Start_date>to_date('01-JAN-15','dd-mon-yyy')
ORDER BY 1, 2, 4 , 6 desc;