Oracle Applications Knowledge Sharing- Ajay Atre

--Script to pull menus, submenu, functions
--Run the script in 11i and R12 seperately save the extract
SELECT 
menu_id,
         (SELECT menu_name
            FROM FND_MENUS menu
           WHERE menu_id = menuent.menu_id)
            menu_name,
         (SELECT USER_MENU_NAME
            FROM FND_MENUS_vl menu
           WHERE menu_id = menuent.menu_id)
            USER_MENU_NAME,
         ENTRY_SEQUENCE,
         PROMPT,
         DESCRIPTION,
         GRANT_FLAG,
         SUB_MENU_ID,
         (SELECT MENU_NAME
            FROM FND_MENUS_VL sub
           WHERE sub.menu_id = menuent.SUB_MENU_ID)
            SUB_MENU_name,
         FUNCTION_ID,
         CASE
            WHEN Function_id IS NOT NULL
            THEN
               (SELECT FUNCTION_name
                  FROM fnd_form_functions fun
                 WHERE fun.function_id = menuent.FUNCTION_ID)
         END
            FUNCTION_name,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         CREATED_BY,
         CREATION_DATE,
         LAST_UPDATE_LOGIN
   FROM FND_MENU_ENTRIES_VL menuent
--WHERE (MENU_Id in (83718,83719))
ORDER BY 2, ENTRY_SEQUENCE;








--Script to pull responsibility,request_group, Menu submenu, functions
--Run the script in 11i and R12 seperately save the extract
SELECT responsibility_id,
       RESPONSIBILITY_NAME,
       (SELECT REQUEST_GROUP_NAME
          FROM fnd_request_groups rg
         WHERE rg.REQUEST_GROUP_ID = fr.REQUEST_GROUP_ID)
          REQUEST_GROUP_NAME,
       fr.LAST_UPDATE_DATE,
       fr.LAST_UPDATED_BY,
       fr.CREATED_BY,
       fr.CREATION_DATE,
       fr.LAST_UPDATE_LOGIN,
       a.*
  FROM apps.fnd_responsibility_vl fr,
       (  SELECT menu_id,
                 (SELECT menu_name
                    FROM FND_MENUS menu
                   WHERE menu_id = menuent.menu_id)
                    menu_name,
                 (SELECT USER_MENU_NAME
                    FROM FND_MENUS_vl menu
                   WHERE menu_id = menuent.menu_id)
                    USER_MENU_NAME,
                 ENTRY_SEQUENCE,
                 PROMPT,
                 DESCRIPTION,
                 GRANT_FLAG,
                 SUB_MENU_ID,
                 (SELECT MENU_NAME
                    FROM FND_MENUS_VL sub
                   WHERE sub.menu_id = menuent.SUB_MENU_ID)
                    SUB_MENU_name,
                 FUNCTION_ID,
                 CASE
                    WHEN Function_id IS NOT NULL
                    THEN
                       (SELECT FUNCTION_name
                          FROM fnd_form_functions fun
                         WHERE fun.function_id = menuent.FUNCTION_ID)
                 END
                    FUNCTION_name,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATED_BY,
                 CREATION_DATE,
                 LAST_UPDATE_LOGIN
            FROM FND_MENU_ENTRIES_VL menuent
        ORDER BY 2, ENTRY_SEQUENCE) a
 WHERE fr.menu_id = a.menu_id;

DECLARE
   -- This will prints the whole menu tree for a given responsibility, run for responsibility as a parameters into v_resp_name


   v_resp_name   VARCHAR2 (100) := '&RESPONSIBILITY_NAME';
   v_menu_id     NUMBER;
   v_main_menu   VARCHAR2 (100);


   PROCEDURE get_menu (p_menu_id IN VARCHAR2, p_level IN NUMBER)
   IS
      CURSOR cur_get_submenus
      IS
           SELECT e.entry_sequence,
                  m.menu_name,
                  m.user_menu_name,
                  e.sub_menu_id,
                  e.function_id,
                  f.function_name,
                  e.prompt,
                  f.web_html_call,
                  f.user_function_name,
                  e.grant_flag
             FROM fnd_menus_vl m,
                  fnd_menu_entries_vl e,
                  fnd_form_functions_vl f
            WHERE     e.sub_menu_id = m.menu_id(+)
                  AND e.function_id = f.function_id(+)
                  AND e.menu_id = p_menu_id
         ORDER BY 1;
      l_spaces   VARCHAR2 (30) := '';
      granted    VARCHAR2 (30);
   BEGIN
      -- for addting space
      FOR i IN 1 .. p_level
      LOOP
         l_spaces := l_spaces || '..';
      END LOOP;

      FOR c IN cur_get_submenus
      LOOP
         granted := '';
         IF c.grant_flag = 'Y'
         THEN
            granted := ' [granted]';
         END IF;
         IF c.sub_menu_id IS NULL
         THEN
            DBMS_OUTPUT.put_line (
                  l_spaces
               || 'FUNCTION- '
               || NVL (c.prompt, '[hidden]')
               || granted
               || ' '
               || c.function_name
               || ' ('
               || c.user_function_name
               || ')');
         -- dbms_output.put_line(l_spaces || '...........src=' || c.web_html_call);
         ELSE
            DBMS_OUTPUT.put_line (
                  l_spaces
               || 'MENU ('
               || p_level
               || ') '
               || NVL (c.prompt, '[hidden]')
               || granted
               || ' '
               || c.menu_name
               || ' ('
               || c.user_menu_name
               || ')');
         END IF;
         get_menu (c.sub_menu_id, p_level + 1);
      END LOOP;
   END;

BEGIN
   SELECT menu_id
     INTO v_menu_id
     FROM fnd_responsibility_vl
    WHERE responsibility_name = v_resp_name;
   SELECT menu_name
     INTO v_main_menu
     FROM fnd_menus
    WHERE menu_id = v_menu_id;
   DBMS_OUTPUT.put_line ('MAIN MENU- ' || v_main_menu);
   get_menu (v_menu_id, 1);
END;

-- ----------------------------------------------------------------------------------------
--  Query To get All Notifications Sent By A Particular Workflow
-- ----------------------------------------------------------------------------------------
select  wn.notification_id nid,
        wn.context,
        wn.group_id,
        wn.status,
        wn.mail_status,
        wn.message_type,
        wn.message_name,
        wn.access_key,
        wn.priority,
        wn.begin_date,
        wn.end_date,
        wn.due_date,
        wn.callback,
        wn.recipient_role,
        wn.responder,
        wn.original_recipient,
        wn.from_user,
        wn.to_user,
        wn.subject
from    wf_notifications wn, wf_item_activity_statuses wias
where  wn.group_id = wias.notification_id
and  wias.item_type =  '&item_type'
and  wias.item_key =  '&item_key';

-- -----------------------------------------------------------------------------------------------------------------------------
-- To Check Workflow Mailer Up or down
-- -----------------------------------------------------------------------------------------------------------------------------
SELECT component_name as Component, component_status as Status FROM fnd_svc_components WHERE component_type = 'WF_MAILER'
-- --------------------------------------------------------------------------------------------------------------
--  Query To get the Activity Statuses For All Workflow Activities For A Particular Item Type and Item key
-- --------------------------------------------------------------------------------------------------------------
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   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 = '&item_type'
   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)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   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 = '&item_type'
   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)
 ORDER BY 2,
          1
/
-- -------------------------------------------------------------------------------------------
-- Query To get Errored Workflow Activities For A Particular Item Type/ Item Key
-- -------------------------------------------------------------------------------------------
SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.activity_status = 'ERROR'
   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 = '&item_type'
   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)
 ORDER BY ias.execution_time
-- ---------------------------------------------------------------------------------------------------
-- Query To get Errored Process Activity Statuses For A Particular Item Type/Item Key
-- ----------------------------------------------------------------------------------------------------
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   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.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   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.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY 2,
          1
-- ---------------------------------------------------------------------------------------------
--  Query To get The Errored Activities For A Particular Item Type/Item Key
-- ---------------------------------------------------------------------------------------------
SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.activity_status = 'ERROR'
   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.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY ias.execution_time
/
 Query To get Out Attribute Values Of A Workflow:
SELECT NAME attr_name,
       nvl(text_value,
           nvl(to_char(number_value),
               to_char(date_value))) VALUE
  FROM wf_item_attribute_values
 WHERE item_type = upper('&item_type')
   AND item_key = nvl('&item_key',
                      item_key)
/
-- -----------------------------------------------------------------------------------------------
--  Query To get Out Number Of Deferred Workflow Activities
-- -----------------------------------------------------------------------------------------------
SELECT COUNT(1),
       was.item_type
  FROM apps.wf_items                  wi,
       apps.wf_item_activity_statuses was,
       apps.wf_process_activities     pra
 WHERE wi.item_type = was.item_type
   AND wi.item_key = was.item_key
   AND wi.end_date IS NULL
   AND was.end_date IS NULL
   AND was.activity_status = 'DEFERRED'
      --AND was.item_type = 'REQAPPRV'
   AND was.item_type = wi.item_type
   AND pra.instance_id(+) = was.process_activity
 GROUP BY was.item_type;
 -- ------------------------------------------------------------------------------------------------
 --  Query To Get The Details Of Various Workflow Agent Listeners And Their Statuses
 -- ------------------------------------------------------------------------------------------------
SELECT t.component_name,
       p.owner,
       p.queue_table,
       t.correlation_id
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || '.' || p.name = o.queue_name
   AND t.component_type LIKE 'WF_%AGENT%';                         
-- --------------------------------------------------------------------------------------------------
--  Query To get Records That Are Pending In Each Of The Workflow Agent Listener Queues
-- --------------------------------------------------------------------------------------------------
SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||
       ')''||'' Count: ''||count(*) c from ' || p.owner || '.' || p.queue_table ||
       ' where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ' ||
       nvl2(t.correlation_id,
            'and corrid like ''' || t.correlation_id || ''' ',
            NULL) || 'having count(*)>0;'
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || '.' || p.name = o.queue_name
   AND t.component_type LIKE 'WF_%AGENT%';

EDI queries
-- -----------------------------------------------------------------------------------
-- Processing Rule - Address Level
-- -----------------------------------------------------------------------------------   
  SELECT distinct
     hcasa.ece_tp_location_code "Ship-to Location"
     ,hp.party_name
     ,hca.account_number
    , hl.city
    , hl.postal_code
    , hl.province
    , hl.county
    , hl.address1 || hl.address2 || hl.address3 || hl.address4
                                                                   address
    , mci.customer_item_number
    , mci.customer_item_desc
    , mif.item_number
    , mif.description
    , mcc.commodity_code
    , terms.customer_id
    -----------DEMAND FENCES---------------------------------------------
    , terms.pln_frozen_day_from
    , terms.pln_frozen_day_to 
    , terms.pln_firm_day_from  
    , terms.pln_firm_day_to 
    , terms.pln_forecast_day_from
    , terms.pln_forecast_day_to
    , terms.pln_mrp_forecast_day_from
    , terms.pln_mrp_forecast_day_to
    , terms.shp_frozen_day_from
    , terms.shp_frozen_day_to  
    , terms.shp_firm_day_from  
    , terms.shp_firm_day_to
    , terms.shp_forecast_day_from
    , terms.shp_forecast_day_to
    , terms.shp_mrp_forecast_day_from
    , terms.shp_mrp_forecast_day_to
    , terms.seq_frozen_day_from
    , terms.seq_frozen_day_to        
    , terms.seq_firm_day_from  
    , terms.seq_firm_day_to
    , terms.seq_forecast_day_from
    , terms.seq_forecast_day_to          
    , terms.seq_mrp_forecast_day_from 
    , terms.seq_mrp_forecast_day_to
----------------DEMAND mANAGEMENT--------------------------------------------
   -- , terms.schedule_hierarchy_code "Consume Demand Hierarchy Code"
    --,(SELECT meaning from fnd_lookups where lookup_code like terms.schedule_hierarchy_code) "Consume Demand Hierarchy"
   -- , terms.unshipped_firm_disp_cd "ATS PreHorizon Disp Code"
   -- ,(SELECT meaning from fnd_lookups where lookup_code like terms.unshipped_firm_disp_cd) "ATS PreHorizon Disposition"
   -- , terms.unship_firm_cutoff_days "ATS Horizon Cutoff Days"
    , terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
    , terms.ship_delivery_rule_name "Ship Delivery Code"
    , decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday',
                                           'H','Friday','J','Saturday','K','Sunday','N','As Directed',
                                           'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday',
                                           'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday',
                                           'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday',
                                           '16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately',
                                           '21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern"
    , terms.demand_tolerance_above "Demand Tolerance Positive"
    , terms.demand_tolerance_below "Demand Tolerance Negative"
    , terms.round_to_std_pack_flag "Std Pack Round to"
     , terms.std_pack_qty     "STD Pack Qty"
     -------------------Order Management------------------------------------
     , ooh.order_number       "Order Number"
     --, ott.NAME               "Sales Order Type"
     , terms.intransit_time   "Intransit Time"
     , terms.time_uom_code    "Intransit UOM Code"
     , decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
     , terms.exclude_non_workdays_flag "Exclude Non-Workdays"
    ---------------------CUM MANAGEMENT------------------------------------   
    --, terms.cum_control_code
   -- ,(select meaning from fnd_lookups where lookup_code like terms.cum_control_code)"CUM Management Type"
   -- , terms.cum_org_level_code "CUM Org Level"
   -- , terms.cum_shipment_rule_code "Shipment Rule Code"
   -- , terms.cust_shipto_terms_id
    ---------------------GENERAL------------------------------------------
    --, terms.cust_assign_supplier_cd "Assigned Supplier Code"
    , terms.address_id
    , terms.customer_id
    , terms.ship_from_org_id
    , terms.ship_method
    , terms.intransit_time
    , terms.time_uom_code
    --, terms.cum_current_record_year
    --, terms.cum_previous_record_year
    , terms.cum_current_start_date
    , terms.cum_previous_start_date
   -- , terms.cum_yesterd_time_cutoff
    --, terms.cust_assign_supplier_cd
   -- , terms.customer_rcv_calendar_cd
    , terms.freight_code
    --, terms.supplier_shp_calendar_cd
    --, terms.unship_firm_cutoff_days
    , terms.use_edi_sdp_code_flag
    , terms.inactive_date
   -- , terms.match_across_key
    --, terms.match_within_key
    , terms.header_id
    , terms.price_list_id
    --, terms.critical_attribute_key
    , terms.customer_contact_id
    , terms.supplier_contact_id
    , terms.agreement_id
    , terms.agreement_name
    , terms.future_agreement_id
    , terms.future_agreement_name
    , terms.comments
    , terms.last_updated_by
    , terms.last_update_date
    , terms.creation_date
    , terms.created_by
    , terms.attribute_category
    , terms.attribute1
    , terms.attribute2
    , terms.attribute3
    , terms.attribute4
    , terms.attribute5
    , terms.attribute6
    , terms.attribute7
    , terms.attribute8
    , terms.attribute9
    , terms.attribute10
    , terms.attribute11
    , terms.attribute12
    , terms.attribute13
    , terms.attribute14
    , terms.attribute15
    , terms.last_update_login
    , terms.request_id
    , terms.program_application_id
    , terms.program_id
    , terms.program_update_date
    , terms.tp_attribute1
    , terms.tp_attribute2
    , terms.tp_attribute3
    , terms.tp_attribute4
    , terms.tp_attribute5
    , terms.tp_attribute6
    , terms.tp_attribute7
    , terms.tp_attribute8
    , terms.tp_attribute9
    , terms.tp_attribute10
    , terms.tp_attribute11
    , terms.tp_attribute12
    , terms.tp_attribute13
    , terms.tp_attribute14
    , terms.tp_attribute15
    , terms.tp_attribute_category
    --, terms.intransit_calc_basis
    --,(select meaning from fnd_lookups where lookup_code=terms.intransit_calc_basis)"intransit calc basis"
    , terms.default_ship_from
    , terms.pln_frozen_flag
    , terms.shp_frozen_flag
    , terms.seq_frozen_flag
    , terms.issue_warning_drop_parts_flag
    , terms.org_id
    , terms.blanket_number
    , terms.release_rule
    , terms.release_time_frame
    , terms.release_time_frame_uom
    , terms.exclude_non_workdays_flag
    , terms.disable_create_cum_key_flag
 FROM apps.mtl_customer_items mci
    , apps.mtl_commodity_codes mcc
      , apps.XXAR4385_RA_CUST_R12_V racust
      , apps.XXAR4385_RA_ADDRESS_R12_V raadd
    , apps.rlm_cust_shipto_terms_all terms,
     APPS.HZ_PARTIES HP,
     APPS.HZ_PARTY_SITES HPS,
     HZ_CUST_ACCT_SITES_ALL HCASA,
       HZ_LOCATIONS HL,
       HZ_CUST_ACCOUNTS HCA
    , apps.mtl_item_flexfields mif
    , apps.mtl_customer_item_xrefs mcix
    , apps.oe_order_headers_All ooh
    --, oe_transaction_types_tl ott
WHERE terms.address_id = mci.address_id
AND
  terms.address_id IS NOT NULL
  AND  hp.party_id=hps.party_id
  and    hps.party_site_id=hcasa.party_site_id
  and    hps.location_id=hl.location_id               
  AND   hca.party_id=hp.party_id
  AND hca.cust_account_id = terms.customer_id
  AND hcasa.cust_acct_site_id = terms.address_id
  AND mci.commodity_code_id = mcc.commodity_code_id
  AND mif.inventory_item_id = mcix.inventory_item_id
  AND mif.organization_id = mcix.master_organization_id
  AND mcix.customer_item_id = mci.customer_item_id
   and terms.ship_from_org_id in (select organization_id from org_organization_definitions where organization_code in ('JES','MHF') )
  AND mcix.preference_number =
        (SELECT MIN (preference_number)
           FROM mtl_customer_item_xrefs
          WHERE customer_item_id = mci.customer_item_id
            AND inactive_flag <> 'Y')
  and ooh.header_id=terms.header_id
--  and  hca.account_number=:customer_number
  and hcasa.ece_tp_location_code is not null;
 
  -- -----------------------------------------------------------------------------------
  -- Processing Rule - Customer level
  -- -----------------------------------------------------------------------------------
 
 
SELECT  (select organization_code from org_organization_definitions where organization_id=terms.ship_from_org_id)"Organization code"
         , racust.account_number
         , hp.party_name
       , terms.pln_frozen_day_from 
       , terms.pln_frozen_day_to  
       , terms.pln_firm_day_from   
       , terms.pln_firm_day_to  
       , terms.pln_forecast_day_from
       , terms.pln_forecast_day_to
       , terms.pln_mrp_forecast_day_from
       , terms.pln_mrp_forecast_day_to
       , terms.shp_frozen_day_from 
       , terms.shp_frozen_day_to   
       , terms.shp_firm_day_from   
       , terms.shp_firm_day_to
       , terms.shp_forecast_day_from
       , terms.shp_forecast_day_to
       , terms.shp_mrp_forecast_day_from 
       , terms.shp_mrp_forecast_day_to
       , terms.seq_frozen_day_from 
       , terms.seq_frozen_day_to         
       , terms.seq_firm_day_from   
       , terms.seq_firm_day_to
       , terms.seq_forecast_day_from 
       , terms.seq_forecast_day_to           
       , terms.seq_mrp_forecast_day_from  
       , terms.seq_mrp_forecast_day_to
       ,(SELECT meaning from fnd_lookups where lookup_code like terms.schedule_hierarchy_code) "Consume Demand Hierarchy"
       ,(SELECT meaning from fnd_lookups where lookup_code like terms.unshipped_firm_disp_cd) "ATS PreHorizon Disposition"
       , terms.unship_firm_cutoff_days "ATS Horizon Cutoff Days"
       , terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
       , terms.ship_delivery_rule_name "Ship Delivery Code"
       , decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday',
                                              'H','Friday','J','Saturday','K','Sunday','N','As Directed',
                                              'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday',
                                              'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday',
                                              'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday',
                                              '16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately',
                                              '21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern"
       , terms.demand_tolerance_above "Demand Tolerance Positive"
       , terms.demand_tolerance_below "Demand Tolerance Negative"
       , terms.round_to_std_pack_flag "Std Pack Round to"
        , terms.std_pack_qty     "STD Pack Qty"
        , ooh.order_number       "Order Number"
       , terms.intransit_time   "Intransit Time"
        , terms.time_uom_code    "Intransit UOM Code"
        , decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
        ,(select meaning from fnd_lookups where lookup_code=terms.intransit_calc_basis)"intransit calc basis"
        , terms.exclude_non_workdays_flag "Exclude Non-Workdays"
       , terms.cum_control_code
       ,(select meaning from fnd_lookups where lookup_code like terms.cum_control_code)"CUM Management Type"
       , terms.cum_org_level_code "CUM Org Level"
       , terms.cum_shipment_rule_code "Shipment Rule Code"
       , terms.cust_shipto_terms_id
       , terms.cust_assign_supplier_cd "Assigned Supplier Code"
       , terms.address_id
       , terms.customer_id
       , terms.ship_from_org_id
       , terms.ship_method
       , terms.cum_current_record_year
       , terms.cum_previous_record_year
       , terms.cum_current_start_date
       , terms.cum_previous_start_date
       , terms.cum_yesterd_time_cutoff
       , terms.cust_assign_supplier_cd
       , terms.customer_rcv_calendar_cd
       , terms.freight_code
       , terms.supplier_shp_calendar_cd
       , terms.unship_firm_cutoff_days
       , terms.use_edi_sdp_code_flag
       , terms.inactive_date
       , terms.match_across_key
       , terms.match_within_key
       , terms.header_id
       , terms.price_list_id
       , terms.critical_attribute_key
       , terms.customer_contact_id
       , terms.supplier_contact_id
       , terms.agreement_id
       , terms.agreement_name
       , terms.future_agreement_id
       , terms.future_agreement_name
       , terms.comments
       , terms.last_updated_by
       , terms.last_update_date
       , terms.creation_date
       , terms.created_by
       , terms.attribute_category
       , terms.attribute1
       , terms.attribute2
       , terms.attribute3
       , terms.attribute4
       , terms.attribute5
       , terms.attribute6
       , terms.attribute7
       , terms.attribute8
       , terms.attribute9
       , terms.attribute10
       , terms.attribute11
       , terms.attribute12
       , terms.attribute13
       , terms.attribute14
       , terms.attribute15
       , terms.last_update_login
       , terms.request_id
       , terms.program_application_id
       , terms.program_id
       , terms.program_update_date
       , terms.tp_attribute1
       , terms.tp_attribute2
       , terms.tp_attribute3
       , terms.tp_attribute4
       , terms.tp_attribute5
       , terms.tp_attribute6
       , terms.tp_attribute7
       , terms.tp_attribute8
       , terms.tp_attribute9
       , terms.tp_attribute10
       , terms.tp_attribute11
       , terms.tp_attribute12
       , terms.tp_attribute13
       , terms.tp_attribute14
       , terms.tp_attribute15
       , terms.tp_attribute_category
       , terms.intransit_calc_basis
       , terms.default_ship_from
       , terms.pln_frozen_flag
       , terms.shp_frozen_flag
       , terms.seq_frozen_flag
       , terms.issue_warning_drop_parts_flag
       , terms.org_id
       , terms.blanket_number
       , terms.release_rule
       , terms.release_time_frame
       , terms.release_time_frame_uom
       , terms.disable_create_cum_key_flag
    FROM   apps.hz_cust_accounts racust
         ,apps.hz_parties hp
       , apps.rlm_cust_shipto_terms_all terms
       , apps.org_organization_definitions orgdef
       , apps.oe_order_headers_All ooh
   WHERE 
    racust.cust_account_id = terms.customer_id
     and  hp.party_id=racust.party_id
     AND orgdef.organization_id = terms.ship_from_org_id
     AND terms.address_id IS NULL
      and ooh.header_id=terms.header_id; 
  
-- -----------------------------------------------------------------------------------
--    Processing Rule - Item Level
-- -----------------------------------------------------------------------------------
SELECT distinct  cust_acct.account_number customer_number ,acct_site.CUST_ACCT_SITE_ID 
        ,loc.location_id 
       , mci.customer_item_number 
       , mci.customer_item_desc 
       , mif.item_number 
       , mif.description 
       , mcc.commodity_code 
       , terms.customer_id 
       , terms.pln_frozen_day_from  
       , terms.pln_frozen_day_to   
       , terms.pln_firm_day_from    
       , terms.pln_firm_day_to   
       , terms.pln_forecast_day_from 
       , terms.pln_forecast_day_to 
       , terms.pln_mrp_forecast_day_from 
       , terms.pln_mrp_forecast_day_to 
       , terms.shp_frozen_day_from  
       , terms.shp_frozen_day_to    
       , terms.shp_firm_day_from    
       , terms.shp_firm_day_to 
       , terms.shp_forecast_day_from 
       , terms.shp_forecast_day_to 
       , terms.shp_mrp_forecast_day_from  
       , terms.shp_mrp_forecast_day_to 
       , terms.seq_frozen_day_from  
       , terms.seq_frozen_day_to          
       , terms.seq_firm_day_from    
       , terms.seq_firm_day_to 
       , terms.seq_forecast_day_from  
       , terms.seq_forecast_day_to            
       , terms.seq_mrp_forecast_day_from   
       , terms.seq_mrp_forecast_day_to 
       , terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
       , terms.ship_delivery_rule_name "Ship Delivery Code" 
       , decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday', 
                                              'H','Friday','J','Saturday','K','Sunday','N','As Directed', 
                                              'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday', 
                                              'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday', 
                                              'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday', 
                                              '16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately', 
                                              '21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern" 
       , terms.demand_tolerance_above "Demand Tolerance Positive"
       , terms.demand_tolerance_below "Demand Tolerance Negative" 
       , terms.round_to_std_pack_flag "Std Pack Round to"
        , terms.std_pack_qty "STD Pack Qty"
         , ooh.order_number "Order Number"
        , terms.intransit_time "Intransit Time"
        , terms.time_uom_code  "Intransit UOM Code"
        , decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
        , terms.exclude_non_workdays_flag "Exclude Non-Workdays"
       , terms.address_id 
       , terms.customer_id 
       , terms.ship_from_org_id 
       , terms.ship_method 
       , terms.intransit_time 
       , terms.time_uom_code 
      , terms.cum_current_start_date 
       , terms.cum_previous_start_date 
       , terms.freight_code 
       , terms.use_edi_sdp_code_flag 
       , terms.inactive_date 
       , terms.header_id 
       , terms.price_list_id 
       , terms.customer_contact_id 
       , terms.supplier_contact_id 
       , terms.agreement_id 
       , terms.agreement_name 
       , terms.future_agreement_id 
       , terms.future_agreement_name 
       , terms.comments 
       , terms.last_updated_by 
       , terms.last_update_date 
       , terms.creation_date 
       , terms.created_by 
       , terms.attribute_category 
       , terms.attribute1 
       , terms.attribute2 
       , terms.attribute3 
       , terms.attribute4 
       , terms.attribute5 
       , terms.attribute6 
       , terms.attribute7 
       , terms.attribute8 
       , terms.attribute9 
       , terms.attribute10 
       , terms.attribute11 
       , terms.attribute12 
       , terms.attribute13 
       , terms.attribute14 
       , terms.attribute15 
       , terms.last_update_login 
       , terms.request_id 
       , terms.program_application_id 
       , terms.program_id 
       , terms.program_update_date 
       , terms.tp_attribute1 
       , terms.tp_attribute2 
       , terms.tp_attribute3 
       , terms.tp_attribute4 
       , terms.tp_attribute5 
       , terms.tp_attribute6 
       , terms.tp_attribute7 
       , terms.tp_attribute8 
       , terms.tp_attribute9 
       , terms.tp_attribute10 
       , terms.tp_attribute11 
       , terms.tp_attribute12 
       , terms.tp_attribute13 
       , terms.tp_attribute14 
       , terms.tp_attribute15 
       , terms.tp_attribute_category 
        , terms.default_ship_from 
       , terms.pln_frozen_flag 
       , terms.shp_frozen_flag 
       , terms.seq_frozen_flag 
       , terms.issue_warning_drop_parts_flag 
       , terms.org_id 
       , terms.blanket_number 
       , terms.release_rule 
       , terms.release_time_frame 
       , terms.release_time_frame_uom 
       , terms.exclude_non_workdays_flag 
       , terms.disable_create_cum_key_flag 
    FROM apps.mtl_customer_items mci 
       , apps.mtl_commodity_codes mcc 
        ,hz_parties party  
          ,hz_cust_accounts cust_acct 
          ,hz_party_sites party_site 
         ,hz_locations loc 
         ,hz_cust_acct_sites_all acct_site 
         , apps.rlm_cust_item_terms_all terms 
       , apps.mtl_item_flexfields mif 
       , apps.mtl_customer_item_xrefs mcix 
       , apps.oe_order_headers_All ooh 
    WHERE terms.customer_item_id = mci.customer_item_id 
     AND terms.address_id IS NOT NULL 
    And cust_acct.CUST_ACCount_ID = terms.customer_id 
            AND cust_acct.PARTY_ID = party.party_id 
     and party.party_id=party_site.party_id 
     and acct_site.party_site_id = party_site.party_site_id 
      AND loc.location_id = party_site.location_id 
      AND acct_site.cust_acct_site_id=terms.address_id 
     AND mci.commodity_code_id = mcc.commodity_code_id 
     AND mif.inventory_item_id = mcix.inventory_item_id 
     AND mif.organization_id = mcix.master_organization_id 
     AND mcix.customer_item_id = mci.customer_item_id 
     AND mcix.preference_number = 
           (SELECT MIN (preference_number) 
              FROM mtl_customer_item_xrefs 
             WHERE customer_item_id = mci.customer_item_id 
               AND inactive_flag <> 'Y') 
     and ooh.header_id=terms.header_id
     and terms.ship_from_org_id in (625,626);
 
-- -----------------------------------------------------------------------------------
-- Trading Partner Query
-- -----------------------------------------------------------------------------------
SELECT    etg.tp_group_code           "Group"
              ,etg.tp_group_description  "Description"
              ,hp.party_name         "Customer Name"--rc.customer_name          "Customer Name"       --mk
              ,HCA.ACCOUNT_NUMBER        "Customer_number" --,rc.CUSTOMER_NUMBER   "Customer Number"  --mk
              ,HL.address1||' '||HL.city||' '||HL.state     "Site Name"
              ,hcasa.ece_tp_location_code   "EDI Location"
              ,eth.tp_code                     "Partner"
              ,eth.tp_description            "Description"
              ,eth.tp_reference_ext1      "Reference1"
              ,eth.tp_reference_ext2      "Reference2"
              ,sub1.document                "Transaction"
              ,sub1.document_type2       "Type"
              ,sub1.translator_code         "Translator Code"  
              ,sub1.document_standard   "Document Standard"
              ,sub1.map_code                "Map"
              ,pvs.ece_tp_location_code  "TP Location Code"
              ,sub1.document_type
              ,sub1.edi_flag
              ,sub1.print_flag
              ,sub1.test_flag
              ,sub1.ATTRIBUTE_CATEGORY "TP Transaction"
              ,sub1.ATTRIBUTE1 "Account No"
              ,sub1.ATTRIBUTE2 "Account Name"
              ,sub1.ATTRIBUTE3 "Bank Name"
              ,eth.ATTRIBUTE_CATEGORY "Trading Partner"
              ,eth.attribute9 "Remit To Name"
              ,eth.attribute10 "Remit To Street"
              ,eth.attribute11 "Remit To City"
              ,eth.attribute12 "Remit To State"
              ,eth.attribute13 "Remit To Postal Code"
              ,eth.attribute14 "Remit To Country"
              ,eth.attribute15 "Remit To VAT NO"
FROM           hz_cust_accounts          hca     --ra_addresses_all         ra
             ,po_vendor_sites_all      pvs
             ,ap_bank_branches  ab
             ,hr_locations            hr
             ,ece_tp_headers      eth
             ,ece_tp_group         etg
             ,hz_parties          hp             --ra_customers         rc --mk
             ,hz_party_sites      hps            --mk
             ,hz_locations        hl             --mk
             ,hz_cust_acct_sites_All  hcasa      --mk
            ,( SELECT    etd.document_id,
                              etd.document_type,
                              etd.translator_code,
                              etd.edi_flag,
                              etd.print_flag,
                              etd.test_flag,
                              elv1.meaning document,
                              elv2.meaning document_type2,
                              etd.tp_header_id,
                              etd.document_standard,
                              em.map_code,
                              etd.ATTRIBUTE_CATEGORY,
                              etd.ATTRIBUTE1,
                              etd.ATTRIBUTE2,
                              etd.ATTRIBUTE3
               FROM      ece_tp_details etd,
                             ece_lookup_values elv1,
                             ece_lookup_values elv2,
                             ece_mappings       em
               WHERE    elv1.lookup_type = 'DOCUMENT'
               AND        elv1.lookup_code = etd.document_id
              AND        etd.map_id = em.map_id
              AND        elv1.enabled_flag = 'Y'
              AND        elv2.lookup_type = etd.document_id||':DOCUMENT_TYPE'
              AND        elv2.lookup_code = etd.document_type
              AND        elv2.enabled_flag = 'Y'
             ) sub1
WHERE  etg.tp_group_id = eth.tp_group_id
AND       eth.tp_header_id = pvs.tp_header_id (+)
AND      eth.tp_header_id = hcasa.tp_header_id (+)  --mk
AND      eth.tp_header_id = ab.tp_header_id (+)
AND      eth.tp_header_id = hr.tp_header_id (+)
AND      eth.tp_header_id = sub1.tp_header_id
AND      eth.tp_header_id = hcasa.tp_header_id
AND      hca.party_id = hp.party_id              --mk                             
AND      HP.PARTY_ID=HPS.PARTY_ID                --mk
AND      HPS.LOCATION_ID=HL.LOCATION_ID          --mk
AND      HPS.PARTY_SITE_ID=HCASA.PARTY_SITE_ID   --mk
--AND      HCA.ACCOUNT_NUMBER=:account_number 
--AND     hp.party_name=:party_name
--and     hcasa.ece_tp_location_code=:EDI_LOCATION
ORDER BY     etg.tp_group_code, eth.tp_code 

PO SQLS


-- -----------------------------------------------------------------------------------
-- Purchase/Internal Requisition details
-- -----------------------------------------------------------------------------------
SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by,
       poh.segment1 "PO #", ppx.full_name "Requestor Name",
       prh.description "Req Description", prh.authorization_status,
       prh.note_to_authorizer, prh.type_lookup_code, prl.line_num,
       prl.line_type_id, prl.item_description, prl.unit_meas_lookup_code,
       prl.unit_price, prl.quantity, prl.quantity_delivered, prl.need_by_date,
       prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date,
       prl.quantity_cancelled, prl.cancel_date, prl.cancel_reason
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       per_people_x ppx,
       po_headers_all poh,
       po_distributions_all pda
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND ppx.person_id = prh.preparer_id
   AND prh.type_lookup_code = 'PURCHASE'  --'INTERNAL'
   AND prd.requisition_line_id = prl.requisition_line_id
   AND pda.req_distribution_id = prd.distribution_id
   AND pda.po_header_id = poh.po_header_id

 -- -----------------------------------------------------------------------------------
 -- SQL Query to extract Oracle Purchase Order Information
 -- -----------------------------------------------------------------------------------
SELECT
  poh.po_header_id,
  poh.type_lookup_code PO_TYPE,
  poh.authorization_status PO_STATUS,
  poh.segment1 PO_NUMBER,
  pov.vendor_name SUPPLIER_NAME,
  povs.vendor_site_code Location,
  hrls.location_code Ship_To,
  hrlb.location_code Bill_to,
  pol.line_num ,
  msib.segment1 Item,
  pol.unit_price,
  pol.quantity,
  pod.amount_billed Amount,
  pod.destination_subinventory,
  ppf.full_name Buyer_Name,
  poh.closed_Code
FROM
  PO_HEADERS_ALL poh,
  PO_LINES_ALL pol,
  mtl_system_items_b msib,
  PO_LINE_LOCATIONS_ALL poll,
  PO_DISTRIBUTIONS_ALL pod,
  po_vendors pov,
  po_vendor_sites_All povs,
  hr_locations_all hrls,
  hr_locations_all hrlb,
  per_all_people_f ppf,
  po_line_types polt
WHERE
  1                         =1
AND polt.line_type_id     = pol.line_type_id
AND povs.vendor_site_id     = poh.vendor_site_id
AND pov.vendor_id           = poh.vendor_id
AND pol.item_id             = msib.inventory_item_id
AND msib.organization_id    = &application_id
AND poh.po_header_id        = pol.po_header_id
AND pol.po_line_id          = pod.po_line_id
AND poll.line_location_id   = pod.line_location_id
AND poh.ship_to_location_id = hrls.location_id
AND poh.bill_to_location_id = hrlb.location_id
AND poh.agent_id            = ppf.person_id
AND poh.segment1            = &LP1;
 -- -----------------------------------------------------------------------------------
 -- Receiving transactions Details for PO
 -- -----------------------------------------------------------------------------------
SELECT   ph.segment1 po_num, ood.organization_name, pol.po_line_id,
         pll.quantity, rsh.receipt_source_code, rsh.vendor_id,
         rsh.vendor_site_id, rsh.organization_id, rsh.shipment_num,
         rsh.receipt_num, rsh.ship_to_location_id, rsh.bill_of_lading,
         rsl.shipment_line_id, rsl.quantity_shipped, rsl.quantity_received,
         rct.transaction_type, rct.transaction_id,
         NVL (rct.source_doc_quantity, 0) transaction_qty
    FROM rcv_transactions rct,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         po_lines_all pol,
         po_line_locations_all pll,
         po_headers_all ph,
         org_organization_definitions ood
   WHERE 1 = 1
     AND rct.po_header_id = ph.po_header_id
     AND rct.po_line_location_id = pll.line_location_id
     AND rct.po_line_id = pol.po_line_id
     AND rct.shipment_line_id = rsl.shipment_line_id
     AND rsl.shipment_header_id = rsh.shipment_header_id
     AND rsh.ship_to_org_id = ood.organization_id
ORDER BY rct.transaction_id 

 -- -----------------------------------------------------------------------------------
 -- Internal Requisitions not having Internal Sales Order
 -- -----------------------------------------------------------------------------------
 
   SELECT   rqh.segment1, rql.line_num, rql.requisition_header_id,
         rql.requisition_line_id, rql.item_id, rql.unit_meas_lookup_code,
         rql.unit_price, rql.quantity, rql.quantity_cancelled,
         rql.quantity_delivered, rql.cancel_flag, rql.source_type_code,
         rql.source_organization_id, rql.destination_organization_id,
         rqh.transferred_to_oe_flag
    FROM po_requisition_lines_all rql, po_requisition_headers_all rqh
   WHERE rql.requisition_header_id = rqh.requisition_header_id
     AND rql.source_type_code = 'INVENTORY'
     AND rql.source_organization_id IS NOT NULL
     AND NOT EXISTS (
            SELECT 'existing internal order'
              FROM oe_order_lines_all lin
             WHERE lin.source_document_line_id = rql.requisition_line_id
               AND lin.source_document_type_id = 10)
ORDER BY rqh.requisition_header_id, rql.line_num
 -- -----------------------------------------------------------------------------------
 -- Purchase Requisition not having Purchase Order
 -- -----------------------------------------------------------------------------------
SELECT   prh.segment1 "PR NUM", TRUNC (prh.creation_date) "CREATED ON",
         TRUNC (prl.creation_date) "Line Creation Date", prl.line_num "Seq #",
         msi.segment1 "Item Num", prl.item_description "Description",
         prl.quantity "Qty", TRUNC (prl.need_by_date) "Required By",
         ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER"
    FROM po.po_requisition_headers_all prh,
         po.po_requisition_lines_all prl,
         apps.per_people_f ppf1,
         (SELECT DISTINCT agent_id, agent_name
                     FROM apps.po_agents_v) ppf2,
         po.po_req_distributions_all prd,
         inv.mtl_system_items_b msi,
         po.po_line_locations_all pll,
         po.po_lines_all pl,
         po.po_headers_all ph
   WHERE prh.requisition_header_id = prl.requisition_header_id
     AND prl.requisition_line_id = prd.requisition_line_id
     AND ppf1.person_id = prh.preparer_id
     AND prh.creation_date BETWEEN ppf1.effective_start_date
                               AND ppf1.effective_end_date
     AND ppf2.agent_id(+) = msi.buyer_id
     AND msi.inventory_item_id = prl.item_id
     AND msi.organization_id = prl.destination_organization_id
     AND pll.line_location_id(+) = prl.line_location_id
     AND pll.po_header_id = ph.po_header_id(+)
     AND pll.pl_line_id = pl.po_line_id(+)
     AND prh.authorization_status = 'APPROVED'
     AND pll.line_location_id IS NULL
     AND prl.closed_code IS NULL
     AND NVL (prl.cancel_flag, 'N') <> 'Y'
ORDER BY 1, 2

-- -----------------------------------------------------------------------------------     
-- PO’s which does not have any Purchase requisition
-- -----------------------------------------------------------------------------------
      
SELECT   prh.segment1 "PR NUM", TRUNC (prh.creation_date) "CREATED ON",
         TRUNC (prl.creation_date) "Line Creation Date", prl.line_num "Seq #",
         msi.segment1 "Item Num", prl.item_description "Description",
         prl.quantity "Qty", TRUNC (prl.need_by_date) "Required By",
         ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER"
    FROM po.po_requisition_headers_all prh,
         po.po_requisition_lines_all prl,
         apps.per_people_f ppf1,
         (SELECT DISTINCT agent_id, agent_name
                     FROM apps.po_agents_v) ppf2,
         po.po_req_distributions_all prd,
         inv.mtl_system_items_b msi,
         po.po_line_locations_all pll,
         po.po_lines_all pl,
         po.po_headers_all ph
   WHERE prh.requisition_header_id = prl.requisition_header_id
     AND prl.requisition_line_id = prd.requisition_line_id
     AND ppf1.person_id = prh.preparer_id
     AND prh.creation_date BETWEEN ppf1.effective_start_date
                               AND ppf1.effective_end_date
     AND ppf2.agent_id(+) = msi.buyer_id
     AND msi.inventory_item_id = prl.item_id
     AND msi.organization_id = prl.destination_organization_id
     AND pll.line_location_id(+) = prl.line_location_id
     AND pll.po_header_id = ph.po_header_id(+)
     AND pll.po_line_id = pl.po_line_id(+)
     AND prh.authorization_status = 'APPROVED'
     AND pll.line_location_id IS NULL
     AND prl.closed_code IS NULL
     AND NVL (prl.cancel_flag, 'N') <> 'Y'
ORDER BY 1, 2    
-- -----------------------------------------------------------------------------------
-- Purchase orders with Approval, Invoice & Payment details
-- -----------------------------------------------------------------------------------
SELECT a.org_id "ORG ID", e.segment1 "VENDOR NUM",
       e.vendor_name "SUPPLIER NAME",
       UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
       f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
       f.city "CITY", f.country "COUNTRY",
       TO_CHAR (TRUNC (d.creation_date)) "PO Date", d.segment1 "PO NUM",
       d.type_lookup_code "PO Type", c.quantity_ordered "QTY ORDERED",
       c.quantity_cancelled "QTY CANCELLED", g.item_id "ITEM ID",
       g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
         (NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
         )
       * NVL (g.unit_price, 0) "PO Line Amount",
       (SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
          FROM po.po_headers_all ph
         WHERE ph.po_header_id = d.po_header_id) "PO Approved?",
       a.invoice_type_lookup_code "INVOICE TYPE",
       a.invoice_amount "INVOICE AMOUNT",
       TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
       a.invoice_num "INVOICE NUMBER",
       (SELECT DECODE (x.match_status_flag,
                       'A', 'Approved'
                      )
          FROM ap.ap_invoice_distributions_all x
         WHERE x.invoice_distribution_id = b.invoice_distribution_id)
                                                          "Invoice Approved?",
       a.amount_paid, h.amount, h.check_id, h.invoice_payment_id "Payment Id",
       i.check_number "Cheque Number",
       TO_CHAR (TRUNC (i.check_date)) "Payment Date"
  FROM ap.ap_invoices_all a,
       ap.ap_invoice_distributions_all b,
       po.po_distributions_all c,
       po.po_headers_all d,
       po.po_vendors e,
       po.po_vendor_sites_all f,
       po.po_lines_all g,
       ap.ap_invoice_payments_all h,
       ap.ap_checks_all i
 WHERE a.invoice_id = b.invoice_id
   AND b.po_distribution_id = c.po_distribution_id(+)
   AND c.po_header_id = d.po_header_id(+)
   AND e.vendor_id(+) = d.vendor_id
   AND f.vendor_site_id(+) = d.vendor_site_id
   AND d.po_header_id = g.po_header_id
   AND c.po_line_id = g.po_line_id
   AND a.invoice_id = h.invoice_id
   AND h.check_id = i.check_id
   AND f.vendor_site_id = i.vendor_site_id
   AND c.po_header_id IS NOT NULL
   AND a.payment_status_flag = 'Y'
   AND d.type_lookup_code != 'BLANKET'