View For Getting PO Details

CREATE OR REPLACE VIEW apps.xx_po_details_v (authorization_status,
                                                      org_id,
                                                      org_name,
                                                      organization_id,
                                                      ship_plant,
                                                      doc_type,
                                                      sub_doc_type,
                                                      document_no,
                                                      revision_num,
                                                      supplier_name,
                                                      currency_code,
                                                      quantity,
                                                      unit_price,
                                                      doc_value,
                                                      tax_amount,
                                                      total_value,
                                                      payment_trem,
                                                      SOURCE,
                                                      cat,
                                                      po_header_id,
                                                      po_line_id,
                                                      item_id,
                                                      release_num,
                                                      revoverable_taxes,
                                                      nonrevoverable_taxes,
                                                      need_by_date,
                                                      location_code,
                                                      avg_cost,
                                                      line_type,
                                                      special_remark,
                                                      line_num,
                                                      item_description
                                                     )
AS
   SELECT a.authorization_status, a.org_id,
          (SELECT xx.NAME
             FROM hr_operating_units xx
            WHERE xx.organization_id = a.org_id) org_name,
          c.ship_to_organization_id organization_id,
          (SELECT xx.organization_code
             FROM mtl_parameters xx
            WHERE xx.organization_id = c.ship_to_organization_id) ship_plant,
          a.type_lookup_code || ' PO' doc_type,
          CASE
             WHEN (SELECT segment1
                     FROM mtl_categories_b xx
                    WHERE xx.category_id = b.category_id) LIKE
                                                          'GEN%'
              OR (SELECT segment1
                    FROM mtl_categories_b xx
                   WHERE xx.category_id = b.category_id) LIKE 'SER%'
                THEN 'INDIRECT'
             ELSE 'DIRECT'
          END sub_doc_type,
          a.segment1 document_no, a.revision_num,
          (SELECT xx.vendor_name
             FROM ap_suppliers xx
            WHERE xx.vendor_id = a.vendor_id) supplier_name, a.currency_code,
          c.quantity, b.unit_price, c.quantity * b.unit_price doc_value,
          d.tax_amount,
          c.quantity * b.unit_price + NVL (d.tax_amount, 0) total_value,
          (SELECT xx.NAME
             FROM ap_terms xx
            WHERE xx.term_id = a.terms_id) payment_trem, a.attribute1 SOURCE,
          (SELECT segment1
             FROM mtl_categories_b xx
            WHERE xx.category_id = b.category_id) cat, a.po_header_id,
          b.po_line_id, b.item_id,
          (SELECT MAX (x.release_num)
             FROM po.po_releases_all x
            WHERE x.po_header_id = a.po_header_id) release_num,
          (SELECT SUM (xx.tax_amount)
             FROM jai_po_taxes xx
            WHERE xx.line_location_id = c.line_location_id
              AND xx.modvat_flag = 'Y') revoverable_taxes,
          (SELECT SUM (xx.tax_amount)
             FROM jai_po_taxes xx
            WHERE xx.line_location_id =
                                      c.line_location_id
              AND xx.modvat_flag = 'N') nonrevoverable_taxes,
          c.need_by_date,
          (SELECT xx.location_code
             FROM hr_locations_all xx
            WHERE xx.location_id = c.ship_to_location_id) location_code,
          (SELECT SUM (xx.item_cost)
             FROM cst_item_costs xx
            WHERE xx.inventory_item_id = b.item_id
              AND xx.organization_id = c.ship_to_organization_id
              AND xx.cost_type_id = 2) avg_cost,
          DECODE (b.line_type_id,
                  1, 'Goods',
                  1020, 'Services',
                  'OSP'
                 ) line_type,
             a.attribute3
          || a.attribute4
          || a.attribute5
          || a.attribute6
          || a.attribute7
          || a.attribute8
          || a.attribute9
          || a.attribute10 special_remark,
          b.line_num, b.item_description
    FROM po_headers_all a,
          po_lines_all b,
          po_line_locations_all c,
          jai_po_line_locations d
    WHERE a.po_header_id = b.po_header_id
      AND b.po_line_id = c.po_line_id
      AND c.line_location_id = d.line_location_id(+)
      AND NVL (a.authorization_status, 'INCOMPLETE') NOT IN ('APPROVED', 'IN PROCESS')
      AND NVL (a.closed_code, 'OPEN') = 'OPEN'
      AND NVL (d.tax_amount, 0) != 0
      AND NVL ((SELECT segment1
                  FROM mtl_categories_b xx
                 WHERE xx.category_id = b.category_id), '00') NOT LIKE '00%'
      AND a.creation_date > SYSDATE - 300;

How to print 'Run By User' in Oracle Report Output

To capture the user name who is submitting concurrent request, by using fnd_global.user_id, follow the below steps:

1. Create a user parameter 'P_CONC_REQUEST_ID' of NUMBER type.

2. Add the below code in 'BEFORE REPORT' trigger:

SRW.USER_EXIT('FND SRWINIT');

3. Add the below code in 'AFTER REPORT' trigger:

SRW.USER_EXIT('FND SRWEXIT');

4. Use the below code to get the concurrent request id:

                                                  DECLARE

                                                         v_conc_req_by varchar2(1000);

                                                         v_conc_req_id number;

                                                  BEGIN

                                                 v_conc_req_id := fnd_global.user_id;

                                                If v_conc_req_id is not NULL then 

                                                           SELECT user_name into   v_conc_req_by

                                                          FROM fnd_user

                                                          WHERE user_id = fnd_global.user_id; 

                                                          return(v_conc_req_by);

                                              Else

                                          return NULL;

                                                      End If;

                                                  END;

Note: We may get the error "REP-1415: 'beforereport': Unknown user exit 'FND'" while running the RDF in Report Builder.

Oracle support says it is not supported feature to run them on the desktop.Sometimes you can comment out those user exits and run the report, then uncomments them before saving and sending to applications.

solution from Oracle is to run the report from Oracle concurrent manager.

How to print 'Request Id' in Oracle Report Output

To capture the concurrent request id which is submitted is by using fnd_global.conc_request_id, follow the below steps:

1. Create a user parameter 'P_CONC_REQUEST_ID' of NUMBER type.

2. Add the below code in 'BEFORE REPORT' trigger:

SRW.USER_EXIT('FND SRWINIT');

3. Add the below code in 'AFTER REPORT' trigger:

SRW.USER_EXIT('FND SRWEXIT');

4. Use the below code to get the concurrent request id:

                                                  DECLARE

                                                      v_conc_req_id number;

                                                  BEGIN

                                                      v_conc_req_id := fnd_global.conc_request_id; 

                                                      return(v_conc_req_id);

                                                  END;

Note: We may get the error "REP-1415: 'beforereport': Unknown user exit 'FND'" while running the RDF in Report Builder.

Oracle support says it is not supported feature to run them on the desktop.Sometimes you can comment out those user exits and run the report, then uncomments them before saving and sending to applications.

solution from Oracle is to run the report from Oracle concurrent manager.

Oracle Alert Back-End Query

Tables:
ALR_ALERTS -  It stores the alerts definition and the query
ALR_ACTIONS - It stores alert action information, ex. email subject, body and other infromation
ALR_ACTIONS_V -- It is a view based on ALR_ACTIONS

Query:
SELECT alert_name,
       description,
       start_date_active,
       Decode (alert_condition_type, 'P', 'Periodic',
                                     'E', 'Event') ALERT_TYPE,
       frequency_type,
       insert_flag,
       update_flag
FROM   apps.alr_alerts
WHERE  enabled_flag = 'Y'
       AND end_date_active IS NULL
       AND alert_name LIKE 'AA%';

SELECT *
FROM   apps.alr_actions_v
WHERE  subject LIKE 'XX%Notification%'; 

/*Query to Find Active Oracle Alert Information in EBS R12*/
SELECT aa.alert_name,
       aa.start_date_active,
       al.meaning                    "FREQUENCY_TYPE",
       Decode (frequency_type, 'B', days_between_checks,
                               'W', weekly_check_day,
                               'M', monthly_check_day_num,
                               'C', days_between_checks,
                               NULL) "Days",
       aav.to_recipients,
       aav.cc_recipients,
       aav.bcc_recipients,
       aav.subject,
       aav.msg_body
FROM   alr_actions_v aav,
       alr_alerts aa,
       alr_lookups al
WHERE  1 = 1
       AND aav.alert_id = aa.alert_id
       AND aa.enabled_flag = 'Y'
       AND aa.frequency_type = al.lookup_code
       AND al.lookup_type = 'ALERT_FREQUENCY_TYPE'
-- AND UPPER (aa.alert_name) LIKE UPPER ('%XX%')
; 

/*Query to find Submitter Oracle Alert Requests*/
SELECT rq.parent_request_id            "Parent Req. ID",
       rq.request_id                   "Req. ID",
       tl.user_concurrent_program_name "User Concurrent Program Name",
       --rq.description,
       Decode (rq.description, NULL, tl.user_concurrent_program_name,
                               rq.description
                               || ' ('
                               || tl.user_concurrent_program_name
                               || ')') "Program Name",
       rq.actual_start_date            "Start Date",
       rq.actual_completion_date       "Completion Date",
       rq.argument_text                parameter,
       Round(( rq.actual_completion_date - rq.actual_start_date ) * 1440, 2) "Runtime (in Minutes)",
       Round(( ( rq.actual_completion_date - rq.actual_start_date ) * 1440 ) /
             60, 2) "Runtime (in Hrs)"
FROM   applsys.fnd_concurrent_programs_tl tl,
       applsys.fnd_concurrent_requests rq
WHERE  tl.application_id = rq.program_application_id
       AND tl.concurrent_program_id = rq.concurrent_program_id
       AND tl.LANGUAGE = Userenv('LANG')
       AND rq.actual_start_date IS NOT NULL
       AND rq.actual_completion_date IS NOT NULL
       AND rq.status_code = 'C' --Status : Normal 
       --and rq.request_id = 104184867
       AND tl.user_concurrent_program_name IN ( 'Check Periodic Alert' )
--and rq.description = '-----'
ORDER  BY 7 DESC; 

How to add a day/hour/minute/second to a date value in Oracle

The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. 
Some other Examples:

Description	Date Expression
Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)

Ex:

select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') DTE, to_char(sysdate - 1/24,'dd-mon-yyyy hh:mi:ss') DTE_BFR_1Hr from dual;

Now, how to print difference between these 2 dates in Hr:

SELECT to_number( to_char(to_date('1','J') + (sysdate - (sysdate - 1/24)), 'J') - 1)  days,

           to_char(to_date('00:00:00','HH24:MI:SS') + (sysdate - (sysdate - 1/24)), 'HH24:MI:SS') time,

           to_number(to_char(to_date('00:00:00','HH24:MI:SS') + (sysdate - (sysdate - 1/24)), 'HH24')) time2

from dual;           

Query to find responsibilities assigned to user in oracle apps

SELECT fu.user_name                "User Name",

       frt.responsibility_name     "Responsibility Name",

       furg.start_date             "Start Date",

       furg.end_date               "End Date",      

       fr.responsibility_key       "Responsibility Key",

       fa.application_short_name   "Application Short Name"

  FROM fnd_user_resp_groups_direct        furg,

       applsys.fnd_user                   fu,

       applsys.fnd_responsibility_tl      frt,

       applsys.fnd_responsibility         fr,

       applsys.fnd_application_tl         fat,

       applsys.fnd_application            fa

 WHERE furg.user_id             =  fu.user_id

   AND furg.responsibility_id   =  frt.responsibility_id

   AND fr.responsibility_id     =  frt.responsibility_id

   AND fa.application_id        =  fat.application_id

   AND fr.application_id        =  fat.application_id

   AND frt.language             =  USERENV('LANG')

   AND UPPER(fu.user_name)      =  UPPER('<USER_NAME>')  

   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))  

   --if you want to see the current "Active" responsibilities of the user, remove the comment in above line.

 ORDER BY frt.responsibility_name;

(OR)

SELECT b.user_name, c.responsibility_name, a.start_date, a.end_date

  FROM fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c

 WHERE a.user_id = b.user_id

   AND a.responsibility_id = c.responsibility_id

   AND b.user_name = UPPER ('<USER_NAME>');