-- File: TimeTraceGetAllDetailDataRecords6X.sql
-- 
-- Purpose:  This script will run through all of the Web Detail Data Records in an attempt to determine which record may be causing an issue.
--  
-- Copyright (C) 2007, 170 Systems, Inc. 
-- 
-- Author:  Aidan Sullivan
--

set serverout on size 50000;
set verify off;
alter session set sql_trace true;
spool TimeTraceGetAllDetailDataRecords6X.txt;

prompt
prompt *********************************** Usage ***********************************
prompt
prompt -- When prompted enter the following values:
prompt
prompt -- invoice_id - The Oracle Invoice_ID associated with the MarkView Document.
prompt -- user_id - The MarkView User who is trying to view the MarkView Document.
prompt
prompt *****************************************************************************
prompt
--DBMS_TRACE.SET_PLSQL_TRACE (trace_level 14); 


declare
  v_DetailLayoutID number;
  v_PrimaryKey     number := &invoice_id;
  v_KeyTable       varchar2(100) := 'aia.invoiceid';
  v_UserID         varchar2(100) := '&user_id';
  
  
  RecordCount           number;
  DataRecordList        mvt_web_utility.DisplayRecordArray_T;
  v_IsEmpty             boolean;
  v_detail_display_name mvt_web_display_detail_layout.detail_display_name%type;

  cursor display_detail_layouts is
    select ddl.detail_layout_id
      from mvt_web_display_detail_layout ddl, mvt_web_display_detail_type ddt
     where ddl.detail_type_id = ddt.detail_type_id
     and ddt.detail_type_name = 'APINVOICE'
     and ddl.include_yn = 'Y';
     

begin
--  dbms_trace.set_plsql_trace (trace_level => 14);
  dbms_output.put_line('v_PrimaryKey:' || v_PrimaryKey);
  dbms_output.put_line('v_KeyTable:' || v_KeyTable);
  dbms_output.put_line('v_UserID:' || v_UserID);
  dbms_output.put_line('***********************************:');
  dbms_output.put_line('***********************************:');

  for display_detail_layout in display_detail_layouts loop
  
    v_DetailLayoutID := display_detail_layout.detail_layout_id;
  
    dbms_output.put_line('v_DetailLayoutID:' || v_DetailLayoutID);
  
    select ddl.detail_display_name
      into v_detail_display_name
      from mvt_web_display_detail_layout ddl
     where ddl.detail_layout_id = v_DetailLayoutID;
    dbms_output.put_line('v_detail_display_name:' || v_detail_display_name);

    if v_detail_display_name != 'Accounting' then
    
      dbms_output.put_line('Start Time:' ||
                           to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
      mvt_web_display_ul.GetDetailDataRecords(DetailLayoutID => v_DetailLayoutID,
                                              PrimaryKey     => v_PrimaryKey,
                                              KeyTable       => v_KeyTable,
                                              UserID         => v_UserID,
                                              ShowQuery      => 'HIDE',
                                              AppsSchema     => null,
                                              PageColor      => '41173F',
                                              IsEmpty        => v_IsEmpty,
                                              RecordCount    => RecordCount,
                                              DataRecord     => DataRecordList);
    
      dbms_output.put_line('End Time:' ||
                           to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
      dbms_output.put_line('RecordCount:' || RecordCount);
    else
      dbms_output.put_line('Accounting Detail Section (content server) - MVERP_DFM_Distribution.GetExistingLinesPanel');
    end if;
  
    dbms_output.put_line('***********************************:');
  end loop;
end;
/
alter session set sql_trace false;
spool off;