IGC Contract Commitment Archive Purge - draft

Description
Categories: BI Publisher, Financials
Application: Contract Commitment
Source: Contract Commitment Archive Purge Report (XML) - Not Supported: Reserved For Future Use
Short Name: IGCCAPRR_XML
DB package: IGC_IGCCAPRR_XMLP_PKG
select
     ven.vendor_name,
     hist.cc_header_id,
     hist.cc_num,
     fnd1.meaning cc_type,
     head.cc_desc,
     head.cc_preparer_user_id,
     usr1.user_name preparer,
     head.cc_owner_user_id,
     usr2.user_name owner,
     to_char(head.creation_date,'DD-MON-YY') creation_date,
     head.currency_code,
     sum(acct.cc_acct_entered_amt) amount,
     to_char(hist.last_cc_activity_date,'DD-MON-YY') last_cc_activity_date
from igc_cc_archive_history_all hist,
         igc_cc_arc_headers_all head,
         igc_cc_arc_acct_lines acct,
         fnd_lookups fnd1,
         fnd_user usr1,
         fnd_user usr2,
         po_vendors ven
where hist.org_id = head.org_id
    and hist.cc_header_id = head.cc_header_id
    and head.cc_apprvl_status = 'AP'
    and hist.cc_header_id in (select distinct cc_header_id from igc_arc_pur_candidates)
    and head.cc_header_id = acct.cc_header_id
    and fnd1.lookup_type = 'IGC_CC_TYPE'
    and hist.cc_type = fnd1.lookup_code
    and head.cc_state =  'CL'
    and usr1.user_id = head.cc_preparer_user_id
    and usr2.user_id = head.cc_owner_user_id
    and head.vendor_id = ven.vendor_id(+)
    and trunc(hist.last_cc_activity_date) <= trunc(:P_ACTIVITY_DATE)
    and hist.set_of_books_id = :P_SET_OF_BOOKS_ID
   and hist.org_id = :P_ORG_ID
    and hist.archive_done_flag = 'Y'
    and nvl(hist.purge_done_flag,'Y')
    = decode(:P_REPORT_MODE,'AR',nvl(hist.purge_done_flag,'Y'),'PU','Y','N')
group by
    ven.vendor_name,
    hist.cc_header_id,
    hist.cc_num,
    fnd1.meaning,
    head.cc_desc,
    head.cc_preparer_user_id,
    usr1.user_name,
    head.cc_owner_user_id,
    usr2.user_name,
    head.creation_date,
    head.currency_code,
    hist.last_cc_activity_date
Parameter NameSQL textValidation
Organization Id
 
Number
Ledger Id
 
Number