R12.2 AP/AR Detail Level Queries

 -- AR transactions data
select 
  a.customer_trx_id erp_id ,
 decode( d.type,'INV', 'Invoice','DM','Debit Note','CM','Credit Note') Transaction_type ,
  d.name Transaction_type_Name,
  a.trx_number,
  a.trx_date,
  c.gl_date ,
  apsa.due_date,
  hp.party_id customer_id,
  hp.party_name customer_name,
  hca.account_number ,
  hca.account_name ,
  apsa.amount_due_original total_amount ,
  apsa.amount_due_original - apsa.amount_due_remaining amount_received,
  apsa.amount_due_remaining remaining_amount,
  decode(apsa.amount_due_remaining,0,'Closed','Open') status,
--  row_number() over (partition by a.customer_trx_id  order by b.link_to_cust_trx_line_id asc nulls first ) line_number,
--  Decode(b.line_type, 'LINE','Item','Tax' ) Line_type ,
 row_number() over (partition by a.customer_trx_id  order by c.account_class asc ) line_number,
  c.account_class ,
  b.description line_description,
--  b.extended_amount line_amount,  
  gcc.concatenated_segments account_code ,
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT1'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      and ffv.flex_value = gcc.segment1 
  )  ||'.'||
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT2'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment2 
  )  ||'.'||
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT3'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment3 
  )  ||'.'|| 
(SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT4'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment4 
  )  ||'.'|| 
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT5'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment5 
  )  ||'.'||  
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT6'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment6 
  )  ||'.'|| 
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT7'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment7 
  )  ||'.'||  
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT8'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      and ffv.flex_value = gcc.segment8 
  )  account_description,
  c.acctd_amount 
FROM apps.ra_customer_trx_all a,
  apps.ra_customer_trx_lines_all b ,
  apps.ra_cust_trx_line_gl_dist_all c,
  apps.ra_cust_trx_types_all d ,
  apps.ar_payment_schedules_all apsa,
  apps.gl_code_combinations_kfv gcc ,
  apps.hz_cust_accounts hca,
  apps.hz_parties hp
where a.org_id             = 4668
--and a.customer_trx_id = 8778052
and a.customer_trx_id      = b.customer_trx_id(+)
and a.customer_trx_id = c.customer_trx_id
and b.customer_trx_line_id(+) = c.customer_trx_line_id
AND c.code_combination_id  = gcc.code_combination_id
AND a.cust_trx_type_id     = d.cust_trx_type_id
AND a.org_id               = d.org_id
AND a.bill_to_customer_id  = hca.cust_account_id
AND hca.party_id           = hp.party_id
AND a.customer_trx_id      = apsa.customer_trx_id
  --     and apsa.amount_due_remaining > 0
 Order by 1
  ;
  
  
-- AR Receipts
  SELECT 
  hp.party_id customer_id,
  hp.party_name customer_name,
  hca.account_number ,
  cr.cash_receipt_id  erp_id ,  
  cr.receipt_number ,
  cr.receipt_date ,
  apsa.due_date maturity_date,
  (SELECT crh.trx_date 
    FROM apps.ar_cash_receipt_history_all crh 
    WHERE crh.cash_receipt_id = cr.cash_receipt_id 
     AND crh.status = 'CLEARED' ) cleared_date,
  (SELECT crh.status  
    FROM apps.ar_cash_receipt_history_all crh 
    WHERE crh.cash_receipt_id = cr.cash_receipt_id 
     AND crh.current_record_flag = 'Y' 
  )  receipt_status,
  cr.comments description , 
    cr.amount receipt_amount ,
  abs(apsa.amount_applied) amount_applied,
  cr.amount + apsa.amount_applied unapplied_amount,
  (SELECT  cba.bank_account_num 
    FROM apps.ce_bank_acct_uses_all cbu ,
          apps.ce_bank_accounts cba 
  WHERE cbu.bank_acct_use_id = cr.remit_bank_acct_use_id
    AND cbu.bank_account_id = cba.bank_account_id )  remit_bank_account_num , 
  (SELECT  cba.iban_number 
    FROM apps.ce_bank_acct_uses_all cbu ,
          apps.ce_bank_accounts cba 
  WHERE cbu.bank_acct_use_id = cr.remit_bank_acct_use_id
    AND cbu.bank_account_id = cba.bank_account_id )  iban_number ,
  (SELECT  cbv.bank_name
    FROM apps.ce_bank_acct_uses_all cbu ,
          apps.ce_bank_accounts cba ,
          apps.ce_banks_v cbv
  WHERE cbu.bank_acct_use_id = cr.remit_bank_acct_use_id
    AND cbu.bank_account_id = cba.bank_account_id
    AND cba.bank_id  = cbv.bank_party_id )  bank_name    ,
  (SELECT  cbbv.bank_branch_name
    FROM apps.ce_bank_acct_uses_all cbu ,
          apps.ce_bank_accounts cba ,
          apps.ce_bank_branches_v cbbv
  WHERE cbu.bank_acct_use_id = cr.remit_bank_acct_use_id
    AND cbu.bank_account_id = cba.bank_account_id
    AND cba.BANK_BRANCH_ID  = cbbv.branch_party_id )  branch_name          
FROM apps.ar_cash_receipts_all cr,
   apps.ar_payment_schedules_all apsa,
  apps.hz_cust_accounts hca,
  apps.hz_parties hp
WHERE cr.org_id             = 4668
AND cr.pay_from_customer  = hca.cust_account_id
AND hca.party_id           = hp.party_id
AND cr.cash_receipt_id      = apsa.cash_receipt_id
  --     and apsa.amount_due_remaining > 0
 ORDER BY 1, 4
  ;
  
-- AP Transactions  (Invoice,DM,CM)
  
  SELECT 
    (SELECT name 
       FROM apps.hr_operating_units
       WHERE organization_id = aia.org_id) OU_name ,  
    aia.vendor_id supplier_id,
    (SELECT vendor_name 
       FROM apps.ap_suppliers pv
       WHERE pv.vendor_id = aia.vendor_id) Supplier_name ,
    (SELECT vendor_site_code NAME 
       FROM apps.ap_supplier_Sites_all  pv
       WHERE pv.vendor_site_id = aia.vendor_site_id) Supplier_site_name ,
    aia.invoice_id erp_invoice_id,
    decode(aia.invoice_type_lookup_code,'STANDARD','Invoice', initcap(aia.invoice_type_lookup_code)||' Memo') invoice_type ,
    aia.invoice_num , 
    aia.invoice_Date,    
    aida.accounting_Date gl_date ,
    aia.invoice_amount ,
    aia.amount_paid ,
    apsa.amount_remaining ,
    Case when apsa.amount_remaining > 0 then 'Open' else 'Closed' end Status ,
    aila.line_number , 
    aila.line_type_lookup_code line_type ,
    aila.description line_description ,
    aida.distribution_line_number  , 
     gcc.concatenated_segments account_code ,              
     (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT1'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment1 
              )  ||'.'||
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT2'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment2 
              )  ||'.'||
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT3'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment3 
              )  ||'.'|| 
            (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT4'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment4 
              )  ||'.'|| 
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT5'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment5 
              )  ||'.'||  
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT6'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment6 
              )  ||'.'|| 
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT7'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment7 
              )  ||'.'||  
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT8'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment8 
              )  account_description ,
      nvl ( (aida.amount), 0)  amount
  FROM apps.ap_invoice_distributions_all aida,
           apps.ap_invoice_lines_all aila,
           apps.ap_invoices_all aia, 
           ap.ap_payment_schedules_all apsa,
           apps.gl_code_combinations_kfv gcc
 WHERE  1 = 1
     AND aia.org_id = 4668
     AND aia.invoice_id = apsa.invoice_id
     AND aida.invoice_id = aia.invoice_id
     AND aila.invoice_id = aida.invoice_id
     AND aila.line_number = aida.invoice_line_number
     AND aia.cancelled_date is null
     AND nvl(aila.discarded_flag,'N') != 'Y'
   --    AND aida.parent_reversal_id is null 
     AND gcc.code_combination_id = aida.dist_code_combination_id
 ORDER BY aia.invoice_id , aila.line_number, aida.distribution_line_number       
       ;

-- AP Payments

SELECT (SELECT name 
             FROM apps.hr_operating_units
             WHERE organization_id = aipa.org_id) OU_name , 
             aca.check_id erp_payment_id,    
             aca.check_number cheq_number,
             aca.check_date cheq_date,
             Initcap(aca.STATUS_LOOKUP_CODE) Cheq_Status,
             aca.cleared_date ,
             nvl(aca.cleared_Amount,aipa.amount) cheq_amount,             
             aca.bank_account_name,
             aca.bank_account_num,
          (SELECT  cbv.bank_name
            FROM apps.ce_bank_acct_uses_all cbu ,
                  apps.ce_bank_accounts cba ,
                  apps.ce_banks_v cbv
          WHERE cbu.bank_acct_use_id =  aca.ce_bank_acct_use_id  
            AND cbu.bank_account_id = cba.bank_account_id
            AND cba.bank_id  = cbv.bank_party_id )  bank_name    ,
          (SELECT  cbbv.bank_branch_name
            FROM apps.ce_bank_acct_uses_all cbu ,
                  apps.ce_bank_accounts cba ,
                  apps.ce_bank_branches_v cbbv
          WHERE cbu.bank_acct_use_id =              aca.ce_bank_acct_use_id  
            AND cbu.bank_account_id = cba.bank_account_id
            AND cba.BANK_BRANCH_ID  = cbbv.branch_party_id )  branch_name       ,   
             pv.vendor_id supplier_id,
             pv.vendor_name supplier_name,
             pvsa.vendor_site_code Supplier_Site,           
             aia.invoice_num supplier_invoice_num ,
             aipa.amount paid_amount,
             aia.invoice_date invoice_date
   FROM  apps.ap_suppliers pv 
          ,   apps.ap_supplier_sites_all pvsa 
          ,   apps.ap_checks_All aca
          ,   apps.ap_invoice_payments_All aipa
          ,   apps.ap_invoices_all aia 
WHERE  aipa.set_of_books_id = 2403
 --  AND aca.check_date >= '01-JAN-2020'
  AND aca.STATUS_LOOKUP_CODE != 'VOIDED'
  AND  aca.vendor_id = pv.vendor_id
  AND  aca.vendor_site_id = pvsa.vendor_site_id
  AND aca.check_id =  aipa.check_id
  AND aipa.invoice_id = aia.invoice_id
ORDER BY 2 ASC ;

Sample XML Data Template

 <?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name="XXTMPLTNAME_DD" defaultPackage="XX_RPT_PKG" version="1.0">
<properties>
<property name="xml_tag_case" value="upper" />
</properties>
<parameters>
<parameter name="PARAM1"  dataType="VARCHAR2"/>
</parameters>
<dataQuery>
<sqlStatement name="Q_MAILING_DTL">
<![CDATA[
 Select null TO_EMAIL,
        null CC_EMAIL,
        null BCC_EMAIL
  from dual
]]>
</sqlStatement>
<sqlStatement name="Q_PAYMENT_DETAILS">
<![CDATA[
SELECT  
hca.cust_account_id,
hca.account_number,
hca.account_name,
hp.party_id,
hp.party_number,
hp.party_name customer_name,
hp.email_address email_address,
  hp.PRIMARY_PHONE_COUNTRY_CODE
|| hp.PRIMARY_PHONE_COUNTRY_CODE
|| hp.PRIMARY_PHONE_NUMBER
  mobile_number,
pn.location_id,
pn.location_code,
hou.set_of_books_id,
trx.customer_trx_id,
trx.trx_number,
trx.trx_date,
apsa.amount_due_original ,
apsa.amount_due_remaining ,
(Select SUM(UNAPPLIED_AMOUNT) 
  from 
  ( Select  (CRA.AMOUNT - SUM(RAA.AMOUNT_APPLIED)) UNAPPLIED_AMOUNT 
          from  APPS.HZ_CUST_ACCOUNTS HCA2,
                  APPS.AR_CASH_RECEIPTS_ALL CRA,
                  APPS.AR_CASH_RECEIPT_HISTORY_ALL CRHA,
                  APPS.AR_RECEIVABLE_APPLICATIONS_ALL RAA
    where hca2.party_id = hp.party_id
      and CRA.PAY_FROM_CUSTOMER = HCA2.CUST_ACCOUNT_ID
  and cra.cash_receipt_id      = crha.cash_receipt_id
  AND crha.current_record_flag = 'Y'
  and crha.status              = 'CLEARED'                
  and cra.cash_receipt_id = raa.cash_receipt_id(+)
  and raa.application_type(+) = 'CASH'
  and raa.display(+) = 'Y'
  and raa.status(+) = 'APP' 
group by  cra.cash_receipt_id  , 
  cra.receipt_number , 
  CRA.PAY_FROM_CUSTOMER ,
  CRA.AMOUNT  )
   )   unapplied_amount_available
FROM pn_locations_all pn,
        ra_customer_trx_all trx,
        ar_payment_schedules_all apsa ,
        apps.hz_cust_accounts hca,
        apps.hz_parties hp,
        apps.hr_operating_units hou
WHERE     1 = 1
 trx.customer_trx_id = apsa.customer_trx_id
AND trx.bill_to_customer_id = hca.CUST_ACCOUNT_ID 
AND hca.account_name = pn.location_code
AND hca.party_id = hp.party_id
AND hp.party_id = :PARAM1
AND trx.org_id = hou.organization_id
AND (SELECT SUM (NVL (amount_due_remaining, 0))
  FROM ar_payment_schedules_all
WHERE customer_trx_id = trx.customer_trx_id) > 100
]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReportTrigger" source="XX_RPT_PKG.BEFOREREPORT"/> 
<dataStructure>
  <group name="G_HEADERS" dataType="VARCHAR2" source="Q_MAILING_DTL">
<element name="TO_EMAIL" dataType="VARCHAR2" value="TO_EMAIL"/>
<element name="CC_EMAIL" dataType="VARCHAR2" value="CC_EMAIL"/>
<element name="BCC_EMAIL" dataType="VARCHAR2" value="BCC_EMAIL"/>
    <group name="G_DETAILS" dataType="VARCHAR2" source="Q_PAYMENT_DETAILS">
<element name="CUST_ACCOUNT_ID" dataType="VARCHAR2" value="CUST_ACCOUNT_ID"/>
<element name="ACCOUNT_NUMBER" dataType="VARCHAR2" value="ACCOUNT_NUMBER"/>
<element name="ACCOUNT_NAME" dataType="VARCHAR2" value="ACCOUNT_NAME"/>
<element name="PARTY_ID" dataType="VARCHAR2" value="PARTY_ID"/>
<element name="PARTY_NUMBER" dataType="VARCHAR2" value="PARTY_NUMBER"/>
<element name="CUSTOMER_NAME" dataType="VARCHAR2" value="CUSTOMER_NAME"/>
<element name="EMAIL_ADDRESS" dataType="VARCHAR2" value="EMAIL_ADDRESS"/>
<element name="MOBILE_NUMBER" dataType="VARCHAR2" value="MOBILE_NUMBER"/>
<element name="LOCATION_ID" dataType="VARCHAR2" value="LOCATION_ID"/>
<element name="LOCATION_CODE" dataType="VARCHAR2" value="LOCATION_CODE"/>
<element name="TRX_NUMBER" dataType="VARCHAR2" value="TRX_NUMBER"/>
<element name="TRX_DATE" dataType="VARCHAR2" value="TRX_DATE"/>
<element name="SET_OF_BOOKS_ID" dataType="VARCHAR2" value="SET_OF_BOOKS_ID"/>
<element name="ORG_ID" dataType="VARCHAR2" value="ORG_ID"/>
<element name="AMOUNT_DUE_ORIGINAL" dataType="VARCHAR2" value="AMOUNT_DUE_ORIGINAL"/>
<element name="AMOUNT_DUE_REMAINING" dataType="VARCHAR2" value="AMOUNT_DUE_REMAINING"/>
<element name="UNAPPLIED_AMOUNT_AVAILABLE" dataType="VARCHAR2" value="UNAPPLIED_AMOUNT_AVAILABLE"/>
   </group>
 </group>    
</dataStructure>
<dataTrigger name="afterReportTrigger" source="XX_RPT_PKG.AFTERREPORT"/> 
</dataTemplate>

Sample Bursting CTL File

 <?xml version="1.0" encoding="UTF-8"?>
<!-- $Header: BURSTING_FILE_XXCUS_FILE_NAME.xml 115.1 2023/01/22 22:28:59 xdouser noship $ -->
<!-- dbdrv: none -->
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
 <xapi:globalData location="stream"></xapi:globalData >
 <xapi:request select="/XXP_DATA_GRP_NAME/LIST_G_HDRS/G_HDRS">
 <xapi:delivery>
  <xapi:email server="smtp.abc.com" port="587" from="notification@abc.com"  reply-to="">
      <xapi:message id="194" to="${TO_EMAIL}" cc="${CC_EMAIL}" bcc="${BCC_EMAIL}" attachment="true" content-type="text/html" subject="Payment Defaults Report">
<![CDATA[
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Cleared - But Unapplied Receipts</title>
<link href="https://fonts.googleapis.com/css?family=Open+Sans:400,600,700" rel="stylesheet">
<style>
.new
{
border:1px solid #c6c5c5;padding:3px 5px; font-family: ''Open Sans'', sans-serif;font-weight:400;font-size:11px;color:#231f20;display:block;
}
</style>
</head>
<body style="background-color:#f9f9f9;padding:10px 0;margin:0;">
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0" style="background-color:#f9f9f9;">
  <tr>
    <td align="center"><table align="center" width="600" border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td align="center">
          <table align="center" width="600" border="0" cellspacing="0" cellpadding="0" style="background-color:#fff;">
              <tr>
                <td><a href="www.abc.com" target="_blank"><img src="http://images.communications.abc.com/EloquaImages/clients/abcProperties/%7Be8702507-5e6f-409c-8bcc-99536db65bfa%7D_abcheaderbanner.jpg" alt="img" width="600" height="120" border="0" style="display:block;"></a></td>
              </tr>
            </table>
          </td>
        </tr>
        <tr>
          <td style="background-color:#ffffff;padding-top:25px;padding-left:30px;padding-right:30px;" align="center">
          <table align="center" width="540" border="0" cellspacing="0" cellpadding="0" style="background-color:#ffffff;">
              <tr>
                <td align="left" valign="top" style="padding-bottom:13px;"><span style="font-family: ''Open Sans'', sans-serif;font-weight:400;font-size:13px;color:#231f20;display:block;">
<strong>Dear All,</strong><br><br>
Please refer to attached excel file for your reviewe and action.<br><br>
<p style="font-weight:400; font-size:13px;  color:#231f20;">Kind  Regards,<br><br>
<strong>abc ERP</strong><br/></p>
</span></td>
              </tr>
            </table></td>
        </tr>
    </table></td>
  </tr>
    </table></td>
  </tr>
</table>
</body>
</html>
]]>   
</xapi:message>
  </xapi:email>
 </xapi:delivery>
<xapi:document output="Order_Payment_Default_Details" output-type="excel" delivery="194">
<xapi:template type="excel" location="xdo://XXCUS.XXTMPLTNAME.en.US/?getSource=true">
</xapi:template>
</xapi:document>
 </xapi:request>
</xapi:requestset>

AP Invoice Interface Processing Scripts

-- Find rejected invoices with reasons
SELECT  (SELECT listagg(REF.REJECT_LOOKUP_CODE,' -- ') within group (order by  REF.parent_id asc ) 
                FROM  apps.ap_interface_rejections REF
               WHERE  ail.invoice_line_id  = REF.parent_id ) LINE_ERROR ,               
             (SELECT listagg(REF.REJECT_LOOKUP_CODE,' -- ') within group (order by  REF.parent_id asc ) 
                FROM  apps.ap_interface_rejections REF
               WHERE ai.invoice_id = REF.parent_id ) header_error ,                                                     
               AI.STATUS , 
               ai.invoice_id ,
               AI.invoice_num,
               ai.vendor_id,
               ai.vendor_site_id,
               ai.invoice_Date,
               ai.gl_date,
               ai.description,
               ail.project_id,
               ail.task_id,
               ail.expenditure_type ,
               ail.expenditure_organization_id
  FROM apps.ap_invoices_interface ai        ,
  AP.ap_invoice_lines_interface ail               
WHERE trunc(ai.creation_date) >= '01-JAN-2023'
    AND ai.invoice_num  LIKE 'OTT%'
    AND nvl(ai.status,'PENDING') != 'PROCESSED' 
    AND ail.invoice_id = ai.invoice_id 
  ORDER BY ai.creation_date ASC   ;
  
-- Update correct OU site IDs
update AP_INVOICES_INTERFACE INTF 
  Set INTF.VENDOR_SITE_ID =   (select PVSA.VENDOR_SITE_ID 
                                                         from AP_SUPPLIERS PV,
                                                                 AP_SUPPLIER_SITES_ALL PVSA  
                                                        where PV.VENDOR_ID = INTF.VENDOR_ID
                                                          and PV.VENDOR_ID =  PVSA.VENDOR_ID
                                                            and PVSA.ORG_ID = intf.org_id )     
where STATUS != 'PROCESSED' 
  and ORG_ID = 5983 
  and not exists ( select 1 
                               from AP_SUPPLIER_SITES_ALL PVSA 
                              where PVSA.VENDOR_SITE_ID = INTF.VENDOR_SITE_ID 
                               and pvsa.org_id = intf.org_id   )  ;
-- Set all unprocessed as Rejected
update AP_INVOICES_INTERFACE INTF 
  Set INTF.status = 'REJECTED' 
where STATUS != 'PROCESSED' 
  and ORG_ID = 5983  ;

-- Lines Data verification   
select *
 from AP_INVOICE_LINES_INTERFACE AIL 
 Where ail.invoice_id in 
(Select invoice_id   
   from AP_INVOICES_INTERFACE INTF 
where STATUS != 'PROCESSED' 
  and ORG_ID = 5983 ) ;
  
  
-- Update project details for sustainable city  
update AP_INVOICE_LINES_INTERFACE AIL 
   Set project_id = 185507 ,
         TASK_ID =    337583 ,
         EXPENDITURE_TYPE = 'Sales Comm.-External' ,
         expenditure_organization_id = 6763
 Where ail.invoice_id in 
(select INVOICE_ID   
   from AP_INVOICES_INTERFACE INTF 
where STATUS != 'PROCESSED' 
  and ORG_ID = 5983 ) ;
  
  
-- Remove duplicate invoices   
-- 1. Lines
Delete  AP_INVOICE_LINES_INTERFACE AIL 
 Where ail.invoice_id in 
(Select invoice_id   
   from AP_INVOICES_INTERFACE INTF 
WHERE STATUS != 'PROCESSED' 
  and org_id in (711,5983)
  and exists (select 1 from AP_INVOICES_ALL AIA 
                         WHERE AIA.INVOICE_NUM  = INTF.INVOICE_NUM 
                           AND aia.org_id = intf.org_id 
                           and aia.cancelled_date is null )  ) ;
-- 2. Headers  
Delete AP_INVOICES_INTERFACE INTF 
WHERE STATUS != 'PROCESSED' 
  AND org_id IN (711,5983)
  AND EXISTS (SELECT 1 FROM AP_INVOICES_ALL AIA 
                         WHERE AIA.INVOICE_NUM  = INTF.INVOICE_NUM 
                              AND aia.org_id = intf.org_id 
                            and aia.cancelled_date is null  );
                           
                           


--- usd invoice
update apps.ap_invoices_interface ai             
  set  ai.invoice_amount = 11740.94,
        ai.invoice_currency_code = 'USD',
        ai.payment_currency_code = 'USD' ,
        ai.exchange_rate_type = 'Corporate',
        ai.exchange_date = sysdate - 1  ,
        ai.gl_date = sysdate ,
        ai.exchange_rate =  3.6725,
        ai.status = '',
        ai.request_id = ''
where trunc(creation_date) >= '01-JAN-2023'
    AND invoice_num  LIKE 'TEST1'  ;

OAF Commands

-- Page import commands:

java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/xx/oracle/apps/ar/customer/webui/searchPG.xml -rootdir  $JAVA_TOP -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.0.33)(PORT=1611))(CONNECT_DATA=(SID= UAT)))"

-- OACore Bounce

./admanagedsrvctl.sh stop oacore_server1

./admanagedsrvctl.sh start oacore_server1

PLSQL Code Sinppets

 -- Assign missing Doc sequence values
Declare 
CURSOR v_receipts IS
   SELECT receipt_number , 
               receipt_Date ,
               cra.cash_receipt_id ,
               org_id ,
              cra.receipt_method_id,
             ( SELECT listagg(DISTINCT fdsa.doc_sequence_id,',') WITHIN GROUP (ORDER BY fdsa.set_of_books_id ASC) 
                 FROM fnd_doc_sequence_categories  fdsc, 
                          fnd_doc_sequence_assignments fdsa 
                WHERE fdsc.application_id = fdsa.application_id  
                   AND fdsc.table_name = 'AR_CASH_RECEIPTS_ALL'
                   AND fdsc.code = fdsa.category_code 
                   AND trunc(SYSDATE) BETWEEN fdsa.start_date AND nvl(fdsa.end_date,SYSDATE+1)  
                   AND (fdsc.NAME,fdsa.set_of_books_id) in (  SELECT rm.NAME, 
(SELECT hou.set_of_books_id 
FROM ar_receipt_method_accounts_all rma,
  hr_operating_units hou
WHERE rma.receipt_method_id = rm.receipt_method_id 
   AND rma.org_id = hou.organization_id
   AND rma.org_id = cra.org_id )
  FROM ar_receipt_methods rm
WHERE rm.receipt_method_id = cra.receipt_method_id
   AND rm.end_date IS NULL  ) )  
   doc_seq_id   , 
               cra.status                       
FROM ar_cash_receipts_all cra
WHERE org_id = 711 
AND creation_date >= '01-JAN-2021'
AND doc_sequence_id IS NULL
AND doc_sequence_value IS NULL 
AND status NOT IN ('REV')   ;
lv_doc_seq_stmt VARCHAR2(200);
lv_doc_seq_val NUMBER;
lv_count NUMBER := 0;
BEGIN
  FOR rec IN v_receipts loop
    
     dbms_output.put_line(   rec.RECEIPT_NUMBER ||' - '|| rec.RECEIPT_DATE ||' - '||rec.CASH_RECEIPT_ID||' - '||rec.ORG_ID
                                 ||' - '||rec.RECEIPT_METHOD_ID||' - '||rec.DOC_SEQ_ID||' - '||rec.STATUS  );
     IF rec.DOC_SEQ_ID IS NULL OR instr(rec.DOC_SEQ_ID,',') > 0 THEN
        CONTINUE;
     END IF;  
  
     lv_doc_seq_stmt := 'Select fnd_doc_seq_'||rec.DOC_SEQ_ID||'_s.nextval from dual';   
      EXECUTE IMMEDIATE lv_doc_seq_stmt INTO lv_doc_seq_val;   
     dbms_output.put_line('Generated sequence value : '|| lv_doc_seq_val );  
   
    IF lv_doc_seq_val IS NOT NULL THEN 
   
     UPDATE ar_cash_receipts_all cra
         SET cra.doc_sequence_id = rec.doc_seq_id
              , cra.doc_sequence_value =  lv_doc_seq_val
        WHERE cash_receipt_id = rec.cash_receipt_id   
          AND org_id = rec.org_id    ;    
      lv_count := lv_count +1;   
   End if;  
END loop;
  dbms_output.put_line(lv_count|| ' records updated successfully.' );  
  COMMIT;
Exception 
 WHEN others THEN
    dbms_output.put_line( sqlerrm );  
    ROLLBACK; 
End;
-- grant select to custom users
begin
EXECUTE IMMEDIATE 'Grant execute on apps.pa_proj_detail_v to xxuser';
end;
-- Vendor Site Creation Script
DECLARE
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data varchar2(1000);
l_idx number ;
l_message varchar2(2000);
l_vendor_site_id NUMBER;
l_party_site_id NUMBER;
l_location_id NUMBER;
BEGIN
fnd_global.apps_initialize(3262,52371,200);
FOR rec IN (
    SELECT DISTINCT pv.vendor_id , pv.vendor_name,
        pvsa.address_line1,pvsa.address_line2,pvsa.country_of_origin_code
    FROM   ap_suppliers pv, ap_supplier_sites_all pvsa
    WHERE  pv.vendor_id = pvsa.vendor_id
      and (pvsa.org_id = 711 )
      AND NOT EXISTS 
      (SELECT 1 
        FROM ap_supplier_Sites_all pvs 
        WHERE pvs.vendor_id = pv.vendor_id 
          and pvs.org_id = 5983 ) ) Loop
    l_vendor_site_rec.vendor_id := rec.vendor_id ;    
    l_vendor_site_rec.vendor_site_code := 'New Site' ;
    l_vendor_site_rec.address_line1 := rec.address_line1;
    l_vendor_site_rec.address_line2 := rec.address_line2;
    l_vendor_site_rec.country := rec.country_of_origin_code;
    l_vendor_site_rec.org_id := 5983;
    l_vendor_site_rec.purchasing_site_flag:= 'Y';
    l_vendor_site_rec.pay_site_flag := 'Y';
    l_vendor_site_rec.rfq_only_site_flag := 'N';
    pos_vendor_pub_pkg.create_vendor_site ( p_vendor_site_rec => l_vendor_site_rec,
                                                                  x_return_status => l_return_status,
                                                                  x_msg_count => l_msg_count,
                                                                  x_msg_data => l_msg_data,
                                                                  x_vendor_site_id => l_vendor_site_id,
                                                                  x_party_site_id => l_party_site_id,
                                                                  x_location_id => l_location_id );
   
   IF l_return_status = 'S' Then    COMMIT; End if;
      dbms_output.put_line('return_status: '||l_return_status);    
    IF (l_msg_count > 0)
     THEN
        FOR i IN 1..FND_MSG_PUB.Count_Msg
        LOOP
           FND_MSG_PUB.Get (p_msg_index       => i,
                            p_encoded         => 'F',
                            p_data            => l_message,
                            p_msg_index_out   => l_idx );
             dbms_output.put_line('Errors : '||l_message);
          
        end loop;        
     end if;  
 End loop;
Exception WHEN others THEN 
   dbms_output.put_line(sqlerrm);
END;

-- Re enable users in test server

DECLARE

   TYPE v_user_array IS VARRAY(4) OF VARCHAR2(30) ;   

   v_users   v_user_array := v_user_array('TEST1','TEST1','TEST1','TEST1');  

BEGIN

   FOR i IN 1..v_users.count LOOP

 

        fnd_user_pkg.enableuser(username => v_users(i) );

        

        IF fnd_user_pkg.changepassword(v_users(i),'test1234') THEN

                 COMMIT;

        END IF;

  

    END loop;

   DBMS_OUTPUT.put_line(   'Users Re-Enabled.');

   

  For rec in ( SELECT fa.application_short_name, fr.responsibility_key,

          fsg.security_group_key, frt.description    

     FROM apps.fnd_responsibility fr,

          fnd_application fa,

          fnd_security_groups fsg,

          fnd_responsibility_tl frt

    WHERE frt.responsibility_name in ('Application Developer','ERP Support')

      AND frt.LANGUAGE = USERENV ('LANG')

      AND frt.responsibility_id = fr.responsibility_id

      AND fr.application_id = fa.application_id

      AND fr.data_group_id = fsg.security_group_id ) Loop

   fnd_user_pkg.addresp (username            => v_users(1),

                         resp_app            => rec.application_short_name,

                         resp_key            => rec.responsibility_key,

                         security_group      => rec.security_group_key,

                         description         => rec.description,

                         start_date          => SYSDATE,

                         end_date            => NULL

                        );

  End loop ;

   COMMIT;

   DBMS_OUTPUT.put_line(   'Responsiblities Assigned Successfully');

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line

                         (   'Unable to attach responsibility to user due to'

                          || SQLCODE

                          || ' '

                          || SUBSTR (SQLERRM, 1, 100)

                         );

END;

-- Profile option

UPDATE FND_PROFILE_OPTION_VALUES 

SET PROFILE_OPTION_VALUE = Decode(PROFILE_OPTION_ID,1058,'Y',3214,'N') 

WHERE PROFILE_OPTION_ID in (1058,3214) --diagnostics

AND LEVEL_VALUE = 22219

AND LEVEL_ID = 10004 

AND ROWNUM = 1;

COMMIT;

--- profile update script

UPDATE FND_PROFILE_OPTION_VALUES 

SET PROFILE_OPTION_VALUE = '24000' 

WHERE PROFILE_OPTION_ID = 7499 --upload file size

AND LEVEL_VALUE = 22219 

AND LEVEL_ID = 10004 

AND ROWNUM = 1;

Commit;

-- Drop and Create WF adhoc Role

Declare

  l_role_name          VARCHAR2(50) := 'XX_FYI_ROLE';

        l_role_display_name  VARCHAR2(100) := 'Internal FYI Role';

        l_role_list          VARCHAR2(500) := NULL;

        l_wf_cnt             NUMBER := 0;

        vc_proc_name        VARCHAR2(30) := 'SET_FYI_ROLE';

    BEGIN

        SELECT

            LISTAGG(user_name, ',') WITHIN GROUP(

                ORDER BY

                    user_name

            )

        INTO l_role_list

        FROM

            xx_hierarchy_all        h,

            xx_hierarchy_group_all  g

        WHERE

                g.group_id = h.group_id

            AND g.group_name = 'Internal FYI'

            AND sysdate BETWEEN nvl(h.start_date_active, sysdate - 1) AND nvl(h.end_date_active, sysdate + 1);

  SELECT

            COUNT(1)

        INTO l_wf_cnt

        FROM

            wf_roles

        WHERE

            name = 'XX_FYI_ROLE';

        IF l_wf_cnt > 0 THEN

             

             WF_DIRECTORY.SetAdHocRoleExpiration (role_name=> 'XX_SALE_COMM_FYI_ROLE' ,

                                                  expiration_date=> sysdate);

  

 

            WF_PURGE.Directory (sysdate);

           

           commit;

           

        END IF;

     

            wf_directory.createadhocrole(role_name => l_role_name, 

                                        role_display_name => l_role_display_name,

                                        language => NULL,

                                        territory => NULL,

                                        role_description => NULL,

                                        notification_preference => 'MAILHTM2',

                                       --role_users => l_role_list,

                                        email_address => NULL,

                                        fax => NULL,

                                        status => 'ACTIVE',

                                        expiration_date => NULL,

                                        parent_orig_system => NULL,

                                        parent_orig_system_id => NULL,

                                        owner_tag => NULL);

                                        

wf_directory.adduserstoadhocrole(role_name => l_role_name, 

                                                         role_users => l_role_list);                                       

         

         commit;

         

    EXCEPTION

        WHEN OTHERS THEN

             dbms_output.put_line(sqlerrm);

    END;

-- Initiate Workflow Instance

Declare 

 l_itemtype   VARCHAR2(30) := 'XXCUSTPROC';

l_itemkey   VARCHAR2(30) := 'DUP1353';

l_user_id    NUMBER :=15302;

l_user_name  FND_USER.USER_NAME%TYPE := 'TEST1';

l_count      NUMBER;

l_case_id    NUMBER := 1353;

l_wf_key NUMBER;

 

BEGIN

wf_engine.createprocess(l_itemtype, l_itemkey, l_itemtype);

wf_engine.setitemuserkey(itemtype => l_itemtype,

itemkey => l_itemkey,

userkey => l_itemkey);

wf_engine.setitemattrNumber(itemtype      => l_itemtype,

                             ITEMKEY       => l_itemkey,

                             ANAME         => 'WORKFLOW_STARTED_BY_ID',

                             AVALUE        => l_user_id);

                             

wf_engine.setitemattrText(itemtype      => l_itemtype,

                             ITEMKEY       => l_itemkey,

                             ANAME         => 'WORKFLOW_STARTED_BY_USER',

                             AVALUE        => l_user_name);

wf_engine.setitemattrNumber(itemtype      => l_itemtype,

                             ITEMKEY       => l_itemkey,

                             ANAME         => 'PENDING_COUNT',

                             AVALUE        => 5 );

  wf_engine.setitemattrNumber(itemtype      => l_itemtype,

                                   ITEMKEY       => l_itemkey,

                                   ANAME         => 'HEADER_ID',

                                   AVALUE        => l_case_id);

wf_engine.setitemattrText(itemtype      => l_itemtype,

                             ITEMKEY       => l_itemkey,

                             ANAME         => 'ITEM_NO',

                             AVALUE        => 'DM-01361' );

   wf_engine.setitemowner(itemtype => l_itemtype

,itemkey => l_itemkey

,owner => l_user_name);

      wf_engine.startprocess(l_itemtype, l_itemkey);

      

        commit;             

                                                                       

EXCEPTION

WHEN others THEN

dbms_output.put_line ('Error in set userkey processs:' || sqlerrm);

END;

 --  Delete adhoc role 

  DECLARE

     l_role_name varchar2(80) := 'XX_FYI_ROLE';

  

  BEGIN

               for rec in (SELECT user_name, role_name,user_orig_system,user_orig_system_id,role_orig_system,role_orig_system_id

                                   FROM  wf_user_roles

                                  WHERE role_name = 'XX_FYI_ROLE' -- l_role_name

                                    ) Loop

                                  

                             wf_directory.DeleteUserRole( p_username   => rec.user_name ,

                                                                        p_rolename     => rec.role_name,

                                                                        p_userOrigSystem => rec.user_orig_system,

                                                                        p_userorigSystemID => rec.user_orig_system_id,

                                                                        p_roleOrigSystem => rec.role_orig_system,

                                                                        p_roleOrigSystemID => rec.role_orig_system_id ) ;        

              END loop;

              

         --     wf_directory.adduserstoadhocrole(role_name => l_role_name, role_users => 'TEST1');

              

              COMMIT;

  dbms_output.put_line( 'User w/ role assignment is complete ... ' );

  Exception 

   WHEN others THEN 

     dbms_output.put_line( sqlerrm );

 End;  

-- to enforce date format on a dff field

-- Validation Type special

FND PLSQL "declare

l_value varchar2(15) := :!value ;

l_valid NUMBER := 0 ;

BEGIN 

SELECT  1

 INTO l_valid

FROM dual

Where l_value like  '__-___-____'

 and to_date(l_value,'DD-MON-YYYY') between '01-JAN-2000' and '31-DEC-2147'  ;

EXCEPTION  

 WHEN OTHERS THEN

  fnd_message.set_name( 'FND', 'FND_GENERIC_MESSAGE' ) ;

  fnd_message.set_token( 'MESSAGE', 'Date must be in DD-MON-YYYY format only.' );

  fnd_message.raise_error ;

END;

"

R12.2 Miscellaneous Queries P1

 --  FA Asset Register Query

SELECT fa.asset_number,

       fa.asset_id,

       REPLACE(fa.description,chr(10))  description,

       fa.attribute1 sub_category1,

       fa.attribute2 sub_category2,

       fc.segment1 major_cat,

       fc.segment2 minor_cat,

       (select keys.segment1

         from  fa_asset_keywords keys 

        WHERE  KEYS.code_combination_id = fa.asset_key_ccid

          AND rownum = 1 

        ) asset_key,

       fdp.fiscal_year,

       fdp.period_name,

       fdp.period_num,

       (SELECT loc.segment1||loc.segment2||loc.segment3 

         FROM fa_locations loc, 

              fa_distribution_history  dist

        WHERE  dist.asset_id = fa.asset_id

           AND        dist.location_id = loc.location_id

           AND rownum = 1 ) loc,

         (SELECT first_name || ' ' || last_name 

           FROM per_all_people_f f, fa_distribution_history  dist

          WHERE   dist.asset_id = fa.asset_id

             AND dist.assigned_to = f.person_id  

             AND f.effective_end_date = '31-DEC-4712'

             AND rownum = 1 )  employee_name,     

       fdd.book_type_code,

       fb.date_placed_in_service date_in_use , 

       add_months(fb.date_placed_in_service,fb.life_in_months) service_end_date ,

       fdd.deprn_run_date,

       fdd.COST Current_cost,

       fdd.deprn_amount mtd_deprn,

       fdd.ytd_deprn,

       fdd.deprn_reserve acc_deprn,

       fdd.COST - fdd.deprn_reserve - nvl(       (SELECT sum(impairment_amount) 

         FROM apps.fa_impairments fi

          WHERE fi.asset_id = fa.asset_id

            AND impairment_date <= fdp.calendar_period_close_date

            AND status='POSTED'), 0  ) Net_Book_Value,

       (SELECT sum(impairment_amount) 

         FROM apps.fa_impairments fi

          WHERE fi.asset_id = fa.asset_id

            AND impairment_date <= fdp.calendar_period_close_date

            AND status='POSTED') impairment_amount,

      ( SELECT  max(vendor_name)

      FROM    FA_INVOICE_DETAILS_V

      WHERE   ASSET_ID = fa.ASSET_ID  ) Supplier ,

      (SELECT max(gc.segment3)

         FROM fa_distribution_history  dist,

              gl_code_combinations gc

        WHERE dist.code_combination_id = gc.code_combination_id 

           AND dist.asset_id = fa.asset_id

           AND rownum = 1

        ) segment3,           

      (SELECT max(ffv.description)

         FROM fa_distribution_history  dist,

              gl_code_combinations gc,

              fnd_flex_values_vl ffv

        WHERE  dist.asset_id = fa.asset_id

           AND  dist.code_combination_id = gc.code_combination_id 

           AND gc.segment3 = ffv.flex_value 

           AND ffv.FLEX_VALUE_SET_ID = 1023631

           AND ffv.ENABLED_FLAG = 'Y' 

           ) segment3_desc,                      

      ( SELECT  max(PO_NUMBER)

      FROM    FA_INVOICE_DETAILS_V

      WHERE   ASSET_ID = fa.ASSET_ID  )  po_number,        

       ( SELECT  max(aia.DOC_SEQUENCE_VALUE)

          FROM    FA_INVOICE_DETAILS_V V,

                      AP_INVOICES_ALL aia

          WHERE  v.INVOICE_NUMBER = aia.invoice_num

          AND     V.ASSET_ID = fa.ASSET_ID) Voucher_num,  

       (SELECT  max(INVOICE_NUMBER)

        FROM    FA_INVOICE_DETAILS_V v

      WHERE   v.ASSET_ID = fa.ASSET_ID )   INVOICE_NUMBER,

       (SELECT  MAX(aia.invoice_date)

        FROM    FA_INVOICE_DETAILS_V v,

                        ap_invoices_All aia

      WHERE   v.INVOICE_NUMBER = aia.invoice_num

        AND   v.ASSET_ID = fa.ASSET_ID)   INVOICE_DATE,

       (SELECT max(aia.invoice_amount)

        FROM    FA_INVOICE_DETAILS_V v,

                   AP_INVOICES_ALL aia

      WHERE  v.INVOICE_NUMBER = aia.invoice_num 

           AND v.ASSET_ID = fa.ASSET_ID  )   INVOICE_AMOUNT,        

      (SELECT max(gc.segment4)

         FROM fa_distribution_history  dist,

              gl_code_combinations gc

        WHERE dist.code_combination_id = gc.code_combination_id 

           AND dist.asset_id = fa.asset_id

           AND ROWNUM = 1

        ) Deprn_exp_acc ,                      

      CASE WHEN (add_months(fb.date_placed_in_service,fb.life_in_months)  > SYSDATE and add_months(fb.date_placed_in_service,fb.life_in_months) - sysdate > 360 ) THEN  

        Trunc(months_between(add_months(fb.date_placed_in_service,fb.life_in_months) , SYSDATE) /12) 

       ELSE 0

       END remaing_life_years,

      CASE WHEN (add_months(fb.date_placed_in_service,fb.life_in_months) > SYSDATE and add_months(fb.date_placed_in_service,fb.life_in_months) - sysdate > 30 ) THEN 

        ceil( mod(months_between(add_months(fb.date_placed_in_service,fb.life_in_months) , SYSDATE)  , 12) )   

      else 0 end    Remaing_life_Months

  FROM fa_additions       fa,

        fa_books fb, 

        fa_categories fc ,

       fa_deprn_periods   fdp,

       fa.fa_deprn_detail fdd

 WHERE fa.asset_id = fdd.asset_id

--   AND fa.asset_id IN (490626,407294, 406514,490627,490629)

   AND  fa.asset_id = fb.asset_id

   AND fa.asset_category_id = fc.category_id

   AND fdp.period_counter = fdd.period_counter

   AND fdp.book_type_code = fdd.book_type_code

   AND fdd.book_type_code = 'ALDAR CORP BOOK'

   AND fdp.period_counter BETWEEN 24265 AND 24276

   ORDER BY fa.asset_number , fdp.period_num ;

-- Resp wise MO assignment Detail Query

SELECT 

--       a.responsibility_id ,

--       a.application_id ,

       responsibility_name , 

     ( SELECT NAME 

          from hr_operating_units 

        where  organization_id =  fnd_profile.value_specific('ORG_ID',1,a.responsibility_id, a.application_id) )  mo_operating_unit,     

      (Select SECURITY_PROFILE_NAME

        from per_security_profiles s 

        Where S.SECURITY_PROFILE_ID  = fnd_profile.value_specific('XLA_MO_SECURITY_PROFILE_LEVEL',1,A.responsibility_id,A.application_id))  mo_security_profile  

 FROM fnd_responsibility_vl A,

         fnd_application_vl   b 

 WHERE REPLACE(responsibility_name,'&','') in ( '','','' )

   AND A.application_id = b.application_id  ;

-- AP Invoice full Details query

SELECT pv.vendor_name,

             pvsa.vendor_site_code,

             aia.invoice_num,

 --            aia.invoice_id,

             aia.description,

             initcap(aia.invoice_type_lookup_code) invoice_type,

             (SELECT meaning

              FROM fnd_lookup_values 

              WHERE lookup_type = 'AP_WFAPPROVAL_STATUS' 

               AND lookup_code =  aia.wfapproval_status)  approval_status_meaning,

             aia.invoice_date ,

            (SELECT MAX(trunc(aida1.accounting_date)) 

             FROM ap_invoice_distributions_all aida1 

             WHERE aida1.invoice_id = aia.invoice_id

              AND aida1.LINE_TYPE_LOOKUP_CODE = 'ITEM'

             ) gl_date, 

            CASE 

             when aia.wfapproval_status = 'WFAPPROVED' then 

               (SELECT MAX(creation_date)

                FROM ap_inv_aprvl_hist_all aiha

                WHERE aiha.invoice_id = aia.invoice_id

                 AND aiha.org_id = aia.org_id

                 AND aiha.response IN ('APPROVED') 

                ) 

             WHEN aia.wfapproval_status = 'MANUALLY APPROVED'  THEN 

               (SELECT nvl(MAX(creation_date),aia.last_update_date)

                FROM ap_inv_aprvl_hist_all aiha

                WHERE aiha.invoice_id = aia.invoice_id

                 AND aiha.org_id = aia.org_id

                 AND aiha.response IN ('APPROVED','MANUALLY APPROVED') 

                )           

               WHEN aia.wfapproval_status IN ('REJECTED')  THEN 

                 ( SELECT MAX(creation_date) 

                      FROM ap_inv_aprvl_hist_all aiha

                   WHERE aiha.invoice_id = aia.invoice_id

                      AND aiha.org_id = aia.org_id

                      AND aiha.response IN ('REJECT') 

                    )

                 ELSE 

                  Null  

                End Approve_Date,

             aia.invoice_received_date ,

             aia.goods_received_date,

             ps.due_date,             

             aia.doc_sequence_value voucher_num,

             ( SELECT Min(gcc.segment3)

                  FROM apps.ap_invoice_distributions_all aida ,

                             gl_code_combinations gcc

                  WHERE  aida.org_id= aia.org_Id

                    AND aida.invoice_id = aia.invoice_id

                    AND aida.line_type_lookup_code = 'ITEM'

                    AND aida.DIST_code_combination_id = gcc.code_combination_id ) project_no,

          (SELECT sum( aila.amount )  

            FROM ap_invoice_lines_all aila 

           WHERE aila.invoice_id = aia.invoice_id

            AND aila.line_type_lookup_code = 'ITEM'

            )   invoice_amount,

           (SELECT nvl(sum( aila.amount ),0)  

            FROM ap_invoice_lines_all aila 

           WHERE aila.invoice_id = aia.invoice_id

            AND aila.line_type_lookup_code = 'PREPAY'

            )   prepay_amount,   

         (SELECT nvl(sum( aila.amount ) ,0) 

          FROM ap_invoice_lines_all aila 

         WHERE aila.invoice_id =  aia.invoice_id

          AND aila.line_type_lookup_code = 'TAX')  tax_amount  ,                             

         (SELECT NVL(Sum(aipa.amount),0) 

            FROM ap_invoice_payments_all aipa,

                     ap_checks_all aca  

           WHERE aipa.invoice_id = aia.invoice_id 

           AND aipa.check_id = aca.check_id

           and aca.status_lookup_code != 'VOIDED' )  amount_paid,

             ps.amount_remaining  outstanding

   FROM  ap_suppliers pv 

          ,   ap_supplier_sites_all pvsa 

          ,   ap_invoices_all aia  

          , ap_payment_schedules_all ps 

WHERE  aia.org_id = 1250

   AND AIA.INVOICE_ID in ( select distinct AIDA.INVOICE_ID

                                          from APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA 

                                          WHERE  AIDA.ORG_ID= 1250

                                            AND AIDA.LINE_TYPE_LOOKUP_CODE = 'ITEM'

                                            AND TRUNC(AIDA.ACCOUNTING_DATE) BETWEEN '01-JUL-2010' AND '31-MAR-2023' )

   AND aia.invoice_type_lookup_code = 'STANDARD'

   AND aia.cancelled_date IS NULL

   AND  aia.vendor_id = pv.vendor_id

   AND  aia.vendor_site_id = pvsa.vendor_site_id

   AND aia.invoice_id = ps.invoice_id

ORDER BY 7 ASC ;

-- incident status update detail

select  msib.segment1 unit_code , 

            inc.incident_number sr_number , 

            aud.creation_date  status_change_date,

            (  SELECT status.name

                FROM  cs_incident_statuses_tl status

                WHERE  status.incident_status_id =  aud.OLD_INCIDENT_STATUS_ID

                    AND status.language = userenv('LANG')

            )  From_status ,

            (  SELECT status.name

                FROM  cs_incident_statuses_tl status

                WHERE  status.incident_status_id =  aud.INCIDENT_STATUS_ID

                    and status.language = userenv('LANG')

            )  To_Status    

  FROM

      cs_incidents_all_b   inc,

      mtl_system_items_b MSIB, 

      cs_incidents_audit_b  aud       

  WHERE AUD.CREATION_DATE >= '05-MAR-2023'

      and inc.incident_id = aud.incident_id

      and inc.inventory_item_id = msib.inventory_item_id 

      AND aud.CHANGE_INCIDENT_STATUS_FLAG = 'Y'

      and aud.old_incident_status_id is not null

      AND aud.old_incident_status_id != aud.incident_status_id

      and inc.incident_type_id = 17002

      and aud.creation_date = (select max(aud1.creation_date)  

                                              from  cs_incidents_audit_b  aud1

                                              where aud1.incident_id = inc.incident_id 

                                              and aud1.change_incident_status_flag = 'Y'

                                              AND aud1.OLD_INCIDENT_STATUS_ID is not null )

       ORDER BY   inc.incident_number   , AUD.CREATION_DATE

-- Incident detail query

SELECT  

                                      ( SELECT   ACCOUNT.account_number

                                          FROM  hz_cust_accounts account

                                          WHERE  ACCOUNT.cust_account_id = inc.account_id

                                      ) account_number,

                                      inc.incident_id,

                                      inc.incident_number          SR_number,

                                      inc.incident_date      reported_Date,

                                      ( SELECT  type.name

                                          FROM cs_incident_types_tl type

                                          WHERE type.incident_type_id = inc.incident_type_id

                                         AND TYPE.language = userenv('LANG')

                                      ) SR_type,

                                     (  SELECT  problem.meaning

                                          FROM fnd_lookup_values problem

                                          WHERE  problem.lookup_code = inc.problem_code

                                              AND problem.lookup_type = 'REQUEST_PROBLEM_CODE'

                                              AND problem.language = userenv('LANG')

                                              AND problem.view_application_id = 170

                                              AND problem.security_group_id = fnd_global.lookup_security_group(problem.lookup_type

                                              , problem.view_application_id)

                                      )  problem_code , 

                                      (  SELECT status.name

                                          FROM  cs_incident_statuses_tl status

                                          WHERE  status.incident_status_id = inc.incident_status_id

                                              AND status.language = userenv('LANG')

                                      )  Current_SR_status,                  

                                      aud.creation_date status_Change_Date ,

                                      aud.creation_time status_change_time ,

                                      (select nvl(ppf.full_name,rs.user_name) 

                                         from  fnd_user rs,

                                                   per_all_people_F ppf 

                                        where  rs.user_id = aud.last_updated_by

                                          and rs.employee_id = ppf.person_id(+)

                                            and sysdate between ppf.effective_Start_date(+) and ppf.effective_end_Date(+) ) status_changed_by ,

                                      (  SELECT status.name

                                          FROM  cs_incident_statuses_tl status

                                          WHERE  status.incident_status_id =  aud.OLD_INCIDENT_STATUS_ID

                                              AND status.language = userenv('LANG')

                                      )  From_status ,

                                      (  SELECT status.name

                                          FROM  cs_incident_statuses_tl status

                                          WHERE  status.incident_status_id =  aud.INCIDENT_STATUS_ID

                                              AND status.language = userenv('LANG')

                                      )  To_Status    

                                  FROM

                                      cs_incidents_all_b        inc,

                                      CS_INCIDENTS_AUDIT_B    AUD 

                                  WHERE inc.incident_date between '01-JUL-2022' and sysdate+1

                                      AND inc.incident_id = aud.incident_id

                                      AND aud.CHANGE_INCIDENT_STATUS_FLAG = 'Y'

                                      AND aud.OLD_INCIDENT_STATUS_ID is not null

                                      AND inc.incident_type_id = 15002

                                     ORDER BY  INC.INCIDENT_ID , AUD.CREATION_DATE

                                     ;

                                     

-- payroll element entry value query

select petf.element_name||' -- '||  peevf.screen_entry_value

from pay_element_types_f petf,

  pay_element_links_f pelf,

  pay_element_entries_f peef,

  per_all_assignments_f paaf,

  per_all_people_f papf,

  pay_element_entry_values_f peevf,

  pay_input_values_f pivf

WHERE 1 = 1

and paaf.assignment_id = 10000

and pelf.element_type_id = petf.element_type_id

and petf.element_name = 'Allowance'

and peef.element_type_id = petf.element_type_id

and trunc (sysdate) between trunc (peef.effective_start_date) and trunc (peef.effective_end_date)

and trunc (sysdate) between trunc (petf.effective_start_date) and trunc (petf.effective_end_date)

and trunc (sysdate) between trunc (pelf.effective_start_date) and trunc (pelf.effective_end_date)

and paaf.assignment_id = peef.assignment_id

and trunc (sysdate) between trunc (paaf.effective_start_date) and trunc (paaf.effective_end_date)

and trunc (sysdate) between trunc (peevf.effective_start_date) and trunc (peevf.effective_end_date)

AND papf.person_id               = paaf.person_id

and papf.current_emp_or_apl_flag = 'Y'

and trunc (sysdate) between trunc (papf.effective_start_date) and trunc (papf.effective_end_date)

and peevf.element_entry_id = peef.element_entry_id

AND pivf.input_value_id    = peevf.input_value_id

and  peevf.screen_entry_value is not null

;

-- AR Invoice Amount applied query

SELECT ACRA.CASH_RECEIPT_ID RECEIPT_ID,

       RECEIPT_NUMBER, 

       RECEIPT_DATE , 

       (SELECT STATUS 

         FROM AR_CASH_RECEIPT_HISTORY_ALL 

         WHERE CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID 

         and CURRENT_RECORD_FLAG = 'Y' ) RECEIPT_STATUS ,

       ARAA.APPLY_DATE,

       --ARAA.APPLICATION_TYPE,

       RCTA.CUSTOMER_TRX_ID Invoice_Id,

       RCTA.TRX_NUMBER,

       RCTA.TRX_DATE,

       APSA.AMOUNT_DUE_ORIGINAL TRX_AMOUNT,

       ARAA.AMOUNT_APPLIED RECEIPT_APPLIED,   

       APSA.AMOUNT_DUE_REMAINING REMAINING_AMOUNT   

FROM AR_CASH_RECEIPTS_ALL ACRA,

     AR_RECEIVABLE_APPLICATIONS_ALL ARAA,

     RA_CUSTOMER_TRX_ALL RCTA,

     AR_PAYMENT_SCHEDULES_ALL apsa

WHERE acra.RECEIPT_NUMBER = '83248324'

  AND ACRA.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS = 'APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID 

  and RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID  ;

-- Get lookup value meaning

SELECT HR_GENERAL_UTILITIES.GET_LOOKUP_MEANING('XXPN_GLOBAL_DEBUG_OPTIONS','SMS_DEBUG_FLAG') SMS_DEBUG,

 HR_GENERAL_UTILITIES.GET_LOOKUP_MEANING('XXPN_GLOBAL_DEBUG_OPTIONS','CONC_DEBUG_FLAG') conc_debug

 from dual;

-- Receipt unapplied amount

 (SELECT Sum( ACR.AMOUNT  -   ( NVL((SELECT sum(ARAA.AMOUNT_APPLIED)  

                                                    FROM  apps.AR_RECEIVABLE_APPLICATIONS_ALL ARAA

                                                  WHERE ARAA.CASH_RECEIPT_ID  = ACR.CASH_RECEIPT_ID 

                                                       AND ARAA.DISPLAY = 'Y'

                                                        and ARAA.STATUS = 'APP'

                                                        AND ARAA.APPLICATION_TYPE = 'CASH'

                                                       ),0) ) )

       FROM    APPS.AR_CASH_RECEIPTS_ALL          ACR,

                    APPS.AR_CASH_RECEIPT_HISTORY_ALL   ACRH 

      WHERE  ACR.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID

         AND ACR.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID

         AND ACRH.CURRENT_RECORD_FLAG = 'Y'

         AND ACRH.STATUS = 'CLEARED'

             --AND trunc(acrh.trx_date) = trunc(sysdate) - 1    

       ) 

-- User assigned Resps Query

      SELECT 

                         USR.USER_ID,

                         usr.user_name,

                         resp.responsibility_name,

                         RESP.RESPONSIBILITY_ID ,

                         GRP.DESCRIPTION ,

                         grp.responsibility_application_id ,

                         grp.start_date assigned_date ,

                         GRP.END_DATE ASSIGNMENT_END_DATE,

                         GRP.LAST_UPDATED_BY

            FROM fnd_user usr,

                  FND_USER_RESP_GROUPS_DIRECT grp ,

                  fnd_responsibility_vl resp

            WHERE USR.user_id = grp.user_id

              AND grp.responsibility_id = resp.responsibility_id

              and grp.responsibility_application_id = resp.application_id  

              and USR.USER_NAME = 'TEST1'

              and ( USR.END_DATE is null or USR.END_DATE >= TRUNC(sysdate) )

             -- and ( RESP.END_DATE is not null  )

              and ( grp.end_date = '31-DEC-22'  )

              order by usr.user_name;