Subledger-Ledger Linkage (Accounts Receivable)

AR (Cash Receipt)

select distinct
       arc.receipt_number
      ,arc.receipt_date
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,glc.segment1
  from ar_cash_receipt_history_all arh
      ,ar_distributions_all ard
      ,ar_cash_receipts_all arc
      ,ar_batches_all arb
      ,gl_code_combinations glc
      ,xla.xla_transaction_entities te
      ,xla_ae_headers xah
      ,xla_ae_lines xal
      ,xla_distribution_links xdl
      ,gl_import_references gir
      ,gl_je_headers gjh
      ,gl_je_lines gjl
 where arh.cash_receipt_history_id = ard.source_id
   and arc.cash_receipt_id = arh.cash_receipt_id
   and arh.batch_id = arb.batch_id (+)
   and ard.source_table = 'CRH'
   and arc.type = 'CASH'
   and arh.posting_control_id <> -3
   and nvl (te.source_id_int_1, -99) = arc.cash_receipt_id
   and te.application_id = 222
   and te.entity_code = 'RECEIPTS'
   and xah.entity_id = te.entity_id
   and xah.event_id = arh.event_id
   and xah.ae_header_id = xal.ae_header_id
   and xal.code_combination_id = ard.code_combination_id
   and xdl.ae_header_id = xah.ae_header_id
   and xdl.ae_header_id = xal.ae_header_id
   and xdl.event_id = xah.event_id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and gjh.je_header_id = gir.je_header_id
   and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   and gjh.je_source = 'Receivables'
   and gjh.je_category = 'Receipts'
   and xah.event_type_code <> 'MANUAL'
   and xal.gl_sl_link_table = 'XLAJEL'
   and gir.je_line_num = gjl.je_line_num
   and gjl.code_combination_id = glc.code_combination_id
   and gjl.je_header_id = gjh.je_header_id
   --and arc.receipt_number = 'MH1206201210' 
;

AR (Misc Receipt)

select distinct
       acr.receipt_number
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
  from gl_code_combinations gcc
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches glb
      ,gl_import_references gir
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,xla_distribution_links xdl
      ,ar_misc_cash_distributions_all msd
      ,ar_distributions_all dist
      ,ar_cash_receipts_all acr
      ,ar_receivables_trx_all art
 where gcc.code_combination_id = gjl.code_combination_id
   and gjh.je_header_id = gjl.je_header_id
   and glb.je_batch_id = gjh.je_batch_id
   and gjl.status = 'P'
   and gjh.actual_flag = 'A'
   and gir.je_header_id = gjh.je_header_id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and xah.ae_header_id = xal.ae_header_id
   and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   and gjh.je_source = 'Receivables'
   and gjh.je_category = 'Misc Receipts'
   and xdl.event_id = xah.event_id
   and xah.ae_header_id = xdl.ae_header_id
   and msd.event_id = xah.event_id
   and msd.posting_control_id <> -3
   and msd.misc_cash_distribution_id = dist.source_id
   and dist.source_type = 'MISCCASH'
   and dist.code_combination_id = gjl.code_combination_id
   and acr.cash_receipt_id = msd.cash_receipt_id
   and acr.type = 'MISC'
   and art.receivables_trx_id = acr.receivables_trx_id  
   --and acr.receipt_number = '100009 IONA GEDDES'  
union
select distinct
       acr.receipt_number
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
  from gl_code_combinations gcc
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches glb
      ,gl_import_references gir
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,xla_distribution_links xdl
      ,ar_cash_receipt_history_all arh
      ,ar_distributions_all dist
      ,ar_cash_receipts_all acr
      ,ar_receivables_trx_all art
 where gcc.code_combination_id = gjl.code_combination_id
   and gjh.je_header_id = gjl.je_header_id
   and glb.je_batch_id = gjh.je_batch_id
   and gjl.status = 'P'
   and gjh.actual_flag = 'A'
   and gir.je_header_id = gjh.je_header_id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and xah.ae_header_id = xal.ae_header_id
   and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   and gjh.je_source = 'Receivables'
   and gjh.je_category = 'Misc Receipts'
   and xdl.event_id = xah.event_id
   and xah.ae_header_id = xdl.ae_header_id
   and arh.event_id = xah.event_id
   and arh.posting_control_id <> -3
   and arh.cash_receipt_history_id = dist.source_id
   and dist.code_combination_id = gjl.code_combination_id
   and dist.line_id = xdl.source_distribution_id_num_1
   and acr.cash_receipt_id = arh.cash_receipt_id
   and acr.type = 'MISC'
   and art.receivables_trx_id = acr.receivables_trx_id  
   --and acr.receipt_number = 'AG308260712NY'
;

AR (Invoice)

select distinct
       rat.trx_number
      ,rat.trx_date transaction_date
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr    
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,glc.segment1,gjh.je_category
  from ra_cust_trx_line_gl_dist_all rac
      ,ra_customer_trx_all rat
      ,ra_batches_all rab
      ,ra_customer_trx_lines_all ral
      ,gl_code_combinations glc
      ,xla_distribution_links xdl
      ,xla_ae_headers xah
      ,xla_ae_lines xal
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
 where rat.customer_trx_id = rac.customer_trx_id
   and rac.customer_trx_line_id = ral.customer_trx_line_id (+)
   and rab.batch_id (+) = rat.batch_id
   and rac.posting_control_id <> -3
   and xdl.source_distribution_id_num_1 =  rac.cust_trx_line_gl_dist_id
   and xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
   and gjh.je_source = 'Receivables'
   and gjh.je_category = 'Sales Invoices'
   and xah.event_type_code <> ' MANUAL'
   and xah.ae_header_id = xdl.ae_header_id
   and xal.ae_header_id = xah.ae_header_id
   and xal.ae_line_num = xdl.ae_line_num
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and gjl.je_header_id = gir.je_header_id
   and gir.je_line_num = gjl.je_line_num
   and gjh.je_header_id = gjl.je_header_id
   and glc.code_combination_id = gjl.code_combination_id
   and rac.code_combination_id = gjl.code_combination_id
   and gjh.je_header_id = gir.je_header_id
   --and rat.trx_number = 'I0069600'
;

AR (Invoice Adjustment)

select distinct
       rat.trx_number
      ,rat.trx_date
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr    
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,glc.segment1
  from ar_adjustments_all ara
      ,ar_distributions_all ard
      ,ra_customer_trx_all rat
      ,ar_receivables_trx_all art
      ,gl_code_combinations_kfv glc
      ,xla_distribution_links xdl
      ,xla_ae_headers xah
      ,xla_ae_lines xal
      ,xla.xla_transaction_entities te
      ,gl_import_references gir
      ,gl_je_headers gjh
      ,gl_je_lines gjl
 where glc.code_combination_id = ard.code_combination_id
   and ara.adjustment_id = ard.source_id
   and rat.customer_trx_id = ara.customer_trx_id
   and ara.receivables_trx_id = art.receivables_trx_id
   and ard.source_table = 'ADJ'
   and ara.posting_control_id <> -3
   and xdl.source_distribution_id_num_1 = ard.line_id
   and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   and gjh.je_source = 'Receivables'
   and gjh.je_category = 'Adjustment'
   and xdl.event_id = xah.event_id
   and xah.ae_header_id = xdl.ae_header_id
   and xah.ae_header_id = xal.ae_header_id
   and xal.code_combination_id = ard.code_combination_id
   and xah.entity_id = te.entity_id
   and te.application_id = 222
   and te.entity_code = 'ADJUSTMENTS'
   and nvl (te.source_id_int_1, (-99)) = ara.adjustment_id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and gjh.je_header_id = gir.je_header_id
   and gjh.je_header_id = gjl.je_header_id
   and xal.code_combination_id = gjl.code_combination_id
   --and rat.trx_number = 'A0005418'
;

Subledger-Ledger Linkage (Accounts Payables)

AP (Item Lines)

select distinct
       api.invoice_num
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1     
      --,xdl.accounting_line_code
  from gl_import_references gir
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,xla_distribution_links xdl
      ,ap_invoices_all api
      ,ap_invoice_lines_all apl
      ,ap_invoice_distributions_all apd
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_code_combinations gcc     
 where api.invoice_id = apl.invoice_id
   and apl.invoice_id = apd.invoice_id
   and apl.line_number = apd.invoice_line_number
   and gir.gl_sl_link_table = xal.gl_sl_link_table
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and xal.ae_header_id = xah.ae_header_id
   and xdl.ae_header_id = xah.ae_header_id
   and gjl.je_header_id = gjh.je_header_id
   and gjl.je_header_id = gir.je_header_id
   and gjl.je_line_num = gir.je_line_num  
   and xdl.event_id = apd.accounting_event_id
   and xdl.source_distribution_id_num_1 = apd.invoice_distribution_id
   and xdl.source_distribution_type = 'AP_INV_DIST'
   and xal.gl_sl_link_table = 'XLAJEL'
   and xah.event_type_code <> 'MANUAL'
   and gjh.je_source = 'Payables'
   and gjh.je_category = 'Purchase Invoices'
   and gcc.code_combination_id = gjl.code_combination_id
   --and api.invoice_num = 'IE-365010'
;

AP (Prepayment Lines)

select distinct
       api.invoice_num
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
      --,xdl.accounting_line_code
  from gl_import_references gir
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,xla_distribution_links xdl
      ,ap_invoices_all api
      ,ap_invoice_lines_all apl
      ,ap_invoice_distributions_all apd
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_code_combinations gcc
 where api.invoice_id = apl.invoice_id
   and apl.invoice_id = apd.invoice_id
   and apl.line_number = apd.invoice_line_number
   and gir.gl_sl_link_table = xal.gl_sl_link_table
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and xal.ae_header_id = xah.ae_header_id
   and xdl.ae_header_id = xah.ae_header_id
   and gjl.je_header_id = gjh.je_header_id
   and gjl.je_header_id = gir.je_header_id
   and gjl.je_line_num = gir.je_line_num
   and xdl.event_id = apd.accounting_event_id
   and xdl.applied_to_dist_id_num_1 = apd.prepay_distribution_id
   and xdl.source_distribution_type = 'AP_PREPAY'
   and xal.gl_sl_link_table = 'XLAJEL'
   and xah.event_type_code <> 'MANUAL'
   and gjh.je_source = 'Payables'
   and gjh.je_category = 'Purchase Invoices'
   and gcc.code_combination_id = gjl.code_combination_id
   --and api.invoice_num = 'IE-365010'
;

AP (Payments)

select distinct
       aca.checkrun_name
      ,aca.check_number
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
      --,xdl.accounting_line_code
  from ap_payment_hist_dists aphd
      ,ap_payment_history_all aph
      ,ap_checks_all aca
      ,xla_distribution_links xdl
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches gjb
      ,gl_code_combinations gcc
 where xal.ae_header_id = xah.ae_header_id
   and xdl.ae_header_id = xah.ae_header_id
   and xdl.ae_line_num = xal.ae_line_num
   and xdl.ae_header_id = xal.ae_header_id
   and xal.gl_sl_link_table = gir.gl_sl_link_table
   and xal.gl_sl_link_id = gir.gl_sl_link_id
   and gir.je_header_id = gjl.je_header_id
   and gir.je_line_num = gjl.je_line_num
   and gjl.code_combination_id = gcc.code_combination_id
   and gjl.je_header_id = gjh.je_header_id
   and gjh.je_batch_id = gjb.je_batch_id
   and aphd.payment_hist_dist_id = xdl.source_distribution_id_num_1
   and xdl.source_distribution_type = 'AP_PMT_DIST'
   and gjh.je_source = 'Payables'
   and gjh.je_category = 'Payments'
   and xah.event_type_code <> 'MANUAL'
   and xal.gl_sl_link_table = 'XLAJEL'
   and aphd.payment_history_id = aph.payment_history_id
   and aph.check_id = aca.check_id
   --and aca.check_number = '25467'
;

AP (Treasury Confirmation)

select distinct
       aca.checkrun_name
      ,aca.check_number
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr     
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
      --,xdl.accounting_line_code
  from ap_payment_hist_dists aphd
      ,ap_invoice_payments_all aip
      ,fv_treasury_confirmations_all ftc
      ,ap_checks_all aca
      ,xla_distribution_links xdl
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches gjb
      ,gl_code_combinations gcc
 where xal.ae_header_id = xah.ae_header_id
   and xdl.ae_header_id = xah.ae_header_id
   and xdl.ae_line_num = xal.ae_line_num
   and xdl.ae_header_id = xal.ae_header_id
   and xal.gl_sl_link_table = gir.gl_sl_link_table
   and xal.gl_sl_link_id = gir.gl_sl_link_id
   and gir.je_header_id = gjl.je_header_id
   and gir.je_line_num = gjl.je_line_num
   and gjl.code_combination_id = gcc.code_combination_id
   and gjl.je_header_id = gjh.je_header_id
   and gjh.je_batch_id = gjb.je_batch_id
   and aphd.payment_hist_dist_id = xdl.source_distribution_id_num_1
   and xdl.source_distribution_type = 'FV_TREASURY_CONFIRMATIONS_ALL'
   and gjh.je_source = 'Payables'
   and xah.event_type_code <> 'MANUAL'
   and aphd.invoice_payment_id = aip.invoice_payment_id
   and aip.check_id = aca.check_id
   and aca.payment_instruction_id = ftc.payment_instruction_id
   --and aca.check_number = '25467'
;

Subledger-Ledger Linkage (Purchasing)

PO (Receipt)

select distinct
       rsh.receipt_num
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr    
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
  from rcv_shipment_headers rsh
      ,rcv_shipment_lines rsl
      ,rcv_transactions rt
      ,rcv_receiving_sub_ledger rrsl
      ,xla_distribution_links xdl
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches gjb
      ,gl_code_combinations gcc
 where rsl.shipment_header_id = rsh.shipment_header_id
   and rt.shipment_line_id = rsl.shipment_line_id
   and rrsl.rcv_transaction_id = rt.transaction_id
   and rrsl.rcv_sub_ledger_id = xdl.source_distribution_id_num_1
   and xdl.ae_line_num = xal.ae_line_num
   and xdl.ae_header_id = xal.ae_header_id
   and xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
   and xal.gl_sl_link_table = gir.gl_sl_link_table
   and xal.gl_sl_link_id = gir.gl_sl_link_id
   and xah.ae_header_id = xdl.ae_header_id
   and xah.ae_header_id = xal.ae_header_id
   and xdl.event_id = xah.event_id
   and gir.je_header_id = gjl.je_header_id
   and gir.je_line_num = gjl.je_line_num
   and gjl.je_header_id = gjh.je_header_id
   and gjh.je_batch_id = gjb.je_batch_id
   and gcc.code_combination_id = gjl.code_combination_id
   --and rsh.receipt_num = '23478'
;

PO (Transaction)

select pha.segment1 po#
      ,plla.shipment_num
      ,pra.release_num
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr    
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
  from po_headers_all pha
      ,po_lines_all pla
      ,po_line_locations_all plla
      ,po_distributions_all pda
      ,po_releases_all pra
      ,xla_distribution_links xdl
      ,xla_ae_lines xal
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches gjb
      ,gl_code_combinations gcc
 where pla.po_header_id = pha.po_header_id
   and plla.po_line_id = pla.po_line_id
   and pda.line_location_id = plla.line_location_id
   and pda.po_release_id = pra.po_release_id(+) 
   and pda.po_distribution_id = xdl.source_distribution_id_num_1
   and xdl.source_distribution_type = 'PO_DISTRIBUTIONS_ALL'
   and xdl.application_id = 201
   and xdl.ae_line_num = xal.ae_line_num
   and xdl.ae_header_id = xal.ae_header_id
   and xal.gl_sl_link_table = gir.gl_sl_link_table
   and xal.gl_sl_link_id = gir.gl_sl_link_id
   and gir.je_header_id = gjl.je_header_id
   and gir.je_line_num = gjl.je_line_num  
   and gjl.je_header_id = gjh.je_header_id
   and gjh.je_batch_id = gjb.je_batch_id
   and gjl.code_combination_id = gcc.code_combination_id
   --and pha.segment1= 'P12367450'
;

PDF Attachments are Blank in iPhone/iPad/Other Tablets

Few months back a customer of my client complained that he was not able to open the email attachments(Purchase Orders/Transactions etc.) from their tablets(iPad, iPhone etc.), but the same attachment was fine with desktop or laptop.

Reason: Attachment with html email body is not working in tablets and 'sendmail' plugin was being used with html email body.

Solution to this issue is to use text email body instead of html one.

'mutt' plugin instead of 'sendmail' resolved the issue.

Lets have a look at some mutt commands

mutt -s "$P_SUBJECT" -a $P_ATTFILE $P_MAIL_TO <
/AKUMAR/D1/AKTEST/apps/apps_st/appl/ak/12.0.0/bin/$P_EMAIL_BODY

In the above case it will take 'email from' details from .muttrc profile.
If you want to change the email from, either edit the .muttrc file or use below command

mutt -e "set from=$P_MAIL_FROM" -s "$P_SUBJECT" -a $P_ATTFILE $P_MAIL_TO <
/AKUMAR/D1/AKTEST/apps/apps_st/appl/ak/12.0.0/bin/$P_EMAIL_BODY

There is another profile value in .muttrc 'realname' which is the name/description of the emailid ('from' profile value). You can use below command to override these two profile values

mutt -e "my_hdr From: Email Test Team <$P_MAIL_FROM>" -s "$P_SUBJECT" -a $P_ATTFILE $P_MAIL_TO < /AKUMAR/D1/AKTEST/apps/apps_st/appl/ak/12.0.0/bin/$P_EMAIL_BODY

Generally you can find the .muttrc file in the $HOME directory as a hidden file.

Project related Purchase Orders not accessible from iProcurement

Reason: Only if the user is a key member of the project can access that project related Purchase Order from iProcurement.

There are two ways to resolve this

1. Set the user as member of the related project
OR
2. Set PO: Enforce Project Security profile to NO

> There is another way to get the Purchase Order Details from iProcurement portal

Navigation:
iProcurement Responsibility > Requests > Submit Requests

Search for program 'PO Output for Communication'

> Run for that purchase Order, the output will give you the PO Document.

Key segments value set details (Values/Effective/Hierarchy/Qualifiers)

-----------------------------------------
select ffvs.flex_value_set_name
        ,ffv.flex_value
        ,ffvt.description
        ,ffv.start_date_active
        ,ffv.end_date_active
        ,decode(ffv.enabled_flag,'N','No','Yes') enabled
        ,decode(ffv.summary_flag,'N','No','Yes') parent
        ,ffhv.hierarchy_code rollup_group
        -- Qualifiers
        ,decode(substr(to_char(ffv.compiled_value_attributes),1,1),'N','No','Yes') budgeting_allowed
        ,decode(substr(to_char(ffv.compiled_value_attributes),3,1),'N','No','Yes') posting_allowed
        ,(select acct_type_desc2
            from gl_acct_typ gat
           where gat.acct_type_code = substr(to_char(ffv.compiled_value_attributes),5,1) ) account_type
        ,decode(substr(to_char(ffv.compiled_value_attributes),7,1),'N','No','Yes') third_party_control
        ,decode(substr(to_char(ffv.compiled_value_attributes),9,1),'N','No','Yes') reconcile
 from fnd_flex_values ffv
        ,fnd_flex_values_tl ffvt
        ,fnd_flex_value_sets ffvs
        ,fnd_id_flex_segments fifs
        ,fnd_flex_hierarchies_vl ffhv
where ffv.flex_value_id = ffvt.flex_value_id
    and ffv.flex_value_set_id = ffvs.flex_value_set_id
    and fifs.flex_value_set_id = ffvs.flex_value_set_id
    and ffvs.flex_value_set_name = <<'AK_Account'>>
    and fifs.id_flex_code = 'GL#'
    and ffv.structured_hierarchy_level = ffhv.hierarchy_id(+)
order by ffv.flex_value
;

------------------------------------------------

Merging Discoverer workbooks or Moving/Copying Worksheet from one workbook to another

May be I am missing something (suggestions are most welcome) but to achieve these I am going with the new workbook approach.

Steps:

1> Login to Discoverer Desktop

2> Open all the workbooks you want to merge

3> Now click the 'Restore Down' button (with Double square symbol ) of the last opened workbook.
   > You can see all the opened workbooks in the same window.

4> select the 1st worksheet of the 1st workbook and drop into the vacant space of the window with 'Control' button pressed.
   Note: Without 'Ctrl' it will completely move the sheet from workbook, with 'Ctrl' it will just copy the sheet from the workbook
   > So 'Ctrl' button pressed and left click on the worksheet and drag to the vacant area.
   >> It will create a new workbook with that worksheet, do the same drag and drop for other worksheets from all the workbooks to the new workbook.
     >> Save the new workbook with the name as required.
     >> At the end, share this workbook to the respective users.

Related Post: Renaming Discoverer Workbooks

Renaming Discoverer Workbook

You cannot rename the existing workbook, what you can do is, save the workbook with new name and delete the existing one.(OR you can go with the Oracle non supported approach and update the base table eul5_documents)

Issue with this approach is, you need to re-share this workbook again with all the original users .

Steps:

1> Run the below script and note down the user name with which workbook has been shared

select fu1.user_name||'||'||fu1.description "Workbook Owner",
       doc_name "Workbook Name",doc_description "Description",
       fu2.user_name||'||'||fu2.description "Shared With"
  from eul_us.eul5_eul_users usrs,
       eul_us.eul5_documents docs,
       eul_us.eul5_access_privs privs,
       fnd_user fu1,
       fnd_user fu2
 where privs.gd_doc_id = docs.doc_id
   and usrs.eu_id = privs.ap_eu_id
   and '#'||fu1.user_id = ap_created_by
   and '#'||fu2.user_id = eu_username
   --and fu2.user_name = 'KABHAY'
   --and doc_name like 'Project Transaction%'
   ;

2> Login to Discoverer Desktop, take the backup of the workbook on database/local machine

3> Save as this workbook with the <New Name>
   >> File > Save As...

4> Delete the original workbook (This step will remove all the sharing)
   >> File > Manage Workbooks > Delete...

5> Share the renamed workbook with the users again (user list from 1st step)
   >> File > Manage Workbooks > Sharing...

AR API to change Receipt from Unidentified to Unapplied

Recently I had a requirement to move unapplied AR receipts from one customer to another customer and apply to the related customer invoice.
I did not find any API to update the customer details of an unapplied receipt, nasty way to update the customer on unapplied receipt is to update related base tables (as_cash_receipts_all and ar_payment_schedules_all), I won't suggest to go with this.

There is an API to move unidentified receipt (Receipt without customer details) to unapplied receipts.
So solution offered was to run data loader for unapplied receipts to remove customers to make those to unidentified receipts and then use API ar_receipt_update_api_pub.update_receipt_unid_to_unapp to make it unapplied on required customer and then using API apps.ar_receipt_api_pub.apply, apply that receipt to related customer invocie.

Please find the sample script for API 'ar_receipt_update_api_pub.update_receipt_unid_to_unapp'

---------------------------------------------------------

declare
   cursor cur_unid_rcpts
   is
    select acr.org_id
          ,acr.receipt_number
          ,hca.cust_account_id
          ,acr.cash_receipt_id
     from ar_cash_receipts_all acr
         ,hz_cust_accounts hca
    where acr.status = 'UNID'
      -- Account number stored in attribute3
      and trim(leading 0 from acr.attribute3 )= hca.account_number
      and acr.type = 'CASH'
      and acr.receipt_number = 'AKTest'--<<ReceiptNumber>>
      order by acr.receipt_number;
         
   x_return_status               varchar2 (200);
   x_msg_count                   number;
   x_msg_data                    varchar2 (200);
   x_status                      varchar2 (200);
   p_count                       number;

begin

   for rec_unid_rcpts in cur_unid_rcpts loop
  
       -- Setting up the org context
       apps.mo_global.init ('AR');
       mo_global.set_policy_context('S' , rec_unid_rcpts.org_id) ;
        
       ar_receipt_update_api_pub.update_receipt_unid_to_unapp
              (p_api_version                    => 1.0,
               p_init_msg_list                  => apps.fnd_api.g_true,
               p_commit                         => apps.fnd_api.g_false,
               p_validation_level               => apps.fnd_api.g_valid_level_full,
               x_return_status                  => x_return_status,
               x_msg_count                      => x_msg_count,
               x_msg_data                       => x_msg_data,
               p_cash_receipt_id                => rec_unid_rcpts.cash_receipt_id,
               p_pay_from_customer          => rec_unid_rcpts.cust_account_id,
               x_status                              => x_status
              );
       dbms_output.put_line ('API Status = ' || x_return_status);
       dbms_output.put_line ('Receipt Status = ' || x_status);

       if x_msg_count = 1 then
          dbms_output.put_line ('API Error Msg: ' || x_msg_data);
       elsif x_msg_count > 1 then
      
          for i in 1 .. x_msg_count
          loop
             x_msg_data := fnd_msg_pub.get (i, fnd_api.g_false);
             dbms_output.put_line ('Error Message_' || i || ': ' || x_msg_data);
          end loop;
       else
          -- Commit the changes
          commit;  
       end if;
   end loop;  
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm); 
end;
---------------------------------------------------------

XML File data to Database Table

XML File data to Database Table

1> Get the xml file,
    >> If it is from any concurrent request, get it from server and change the extension from .out to .xml

select outfile_name
  from fnd_concurrent_requests
 where request_id = <request id> ;

2> create table xx_xml_tst_tbl (
                                xml_data   xmltype
                               ,request_id number
                               );

3> Create a directory, link to any appropriate directory of Database tier.
    >> Here I am using    '/usr/tmp' directory.

Run this sql script.
CREATE directory XXHW_DOC_DWNLD AS '/usr/tmp';

4> Place the converted .xml file to /usr/tmp  -- DB Tier

5> Get the characterset used and insert the xml data into the table we created above

select value
  from nls_database_parameters
 where parameter='NLS_CHARACTERSET';

INSERT INTO xx_xml_tst_tbl
VALUES (XMLTYPE(bfilename('XXHW_XML_DWNLD', 'o2438606V2.xml'), nls_charset_id('AL32UTF8')),2438606)

6> Now get the xml data into columns and rows.

Example 1: Test data(Basic)

  <?xml version="1.0" ?>
- <PONOREC>
- <NORECEIPT_DETAILS>
  <Created>24-JUN-12</Created>
  <Requisition_x0020_Num>111001000058</Requisition_x0020_Num>
  <PO_x0020_Number>111001000052</PO_x0020_Number>
  <RECEIVING_STATUS>Not Received</RECEIVING_STATUS>
  <Business_x0020_Unit>XXX - School of Life</Business_x0020_Unit>
  <Type>Standard Purchase Order</Type>
  <Supplier_Name>Hospitality Services - Internal</Supplier_Name>
  <Supplier_Site>CATERING</Supplier_Site>
  </NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
  <Created>20-DEC-12</Created>
  <Requisition_x0020_Num>666601002002</Requisition_x0020_Num>
  <PO_x0020_Number>666601001860</PO_x0020_Number>
  <RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
  <Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
  <Type>Standard Purchase Order</Type>
  <Supplier_Name>Testing LTD</Supplier_Name>
  <Supplier_Site>HERTS</Supplier_Site>
  </NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
  <Created>11-JAN-13</Created>
  <Requisition_x0020_Num>666601002091</Requisition_x0020_Num>
  <PO_x0020_Number>666601001936</PO_x0020_Number>
  <RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
  <Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
  <Type>Standard Purchase Order</Type>
  <Supplier_Name>Testing2 LTD</Supplier_Name>
  <Supplier_Site>EDINBURGH</Supplier_Site>
  </NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
  <Created>11-JAN-13</Created>
  <Requisition_x0020_Num>666601002094</Requisition_x0020_Num>
  <PO_x0020_Number>666601001937</PO_x0020_Number>
  <RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
  <Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
  <Type>Standard Purchase Order</Type>
  <Supplier_Name>CHARITABLE SERVICES</Supplier_Name>
  <Supplier_Site>MACCLESFIELD</Supplier_Site>
  </NORECEIPT_DETAILS>
  </PONOREC>

> Get the data

SELECT evt.*
from   xx_xml_tst_tbl xxt
      ,xmltable('/PONOREC/NORECEIPT_DETAILS'
                   passing (xxt.xml_data)
                   columns  Created           varchar2(40)   path 'Created' -- <XML Tag name>
                           ,Requi_num         varchar2(40)   path 'Requisition_x0020_Num'
                           ,PO_Num            varchar2(40)   path 'PO_x0020_Number'
                           ,Status            varchar2(100)  path 'RECEIVING_STATUS'
                           ,BU                varchar2(240)  path 'Business_x0020_Unit'
                           ,Type              varchar2(40)   path 'Type'
                           ,Supplier_Name     varchar2(256)  path 'Supplier_Name'
                           ,Supplier_Site     varchar2(256)  path 'Supplier_Site'
               ) evt                                   
where  request_id = 2438606
order by evt.Requi_num ;

> Sample Output

Example 2 : Test data (with two layers)

INSERT INTO xx_xml_tst_tbl
VALUES (XMLTYPE(bfilename('XXHW_XML_DWNLD', 'o2421932V2.xml'), nls_charset_id('AL32UTF8')),2421932)

<?xml version="1.0" encoding="UTF-8"?>
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<XXHWAPSRA>
  <LIST_G_SEL_CHECKS>
    <G_SEL_CHECKS>
      <VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>
      <C_VENDOR_NUMBER>10000155</C_VENDOR_NUMBER>
      <VENDOR_NAME>VENDOR11</VENDOR_NAME>
      <PAYMENT_NUMBER_>98010</PAYMENT_NUMBER_>
      <LIST_G_SEL_INV_DATA>
        <G_SEL_INV_DATA>
          <C_IP_CHECK_ID>98010</C_IP_CHECK_ID>
          <C_INVOICE_DATE>28-APR-13</C_INVOICE_DATE>
          <C_INVOICE_NUM>123456</C_INVOICE_NUM>
        </G_SEL_INV_DATA>
      </LIST_G_SEL_INV_DATA>
    </G_SEL_CHECKS>
    <G_SEL_CHECKS>
      <VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>     
      <C_VENDOR_NUMBER>10002911</C_VENDOR_NUMBER>
      <VENDOR_NAME>VENDOR22</VENDOR_NAME>
      <PAYMENT_NUMBER_>98011</PAYMENT_NUMBER_>
      <LIST_G_SEL_INV_DATA>
        <G_SEL_INV_DATA>
          <C_IP_CHECK_ID>98011</C_IP_CHECK_ID>
          <C_INVOICE_DATE>28-APR-13</C_INVOICE_DATE>
          <C_INVOICE_NUM>1234567</C_INVOICE_NUM>
        </G_SEL_INV_DATA>
      </LIST_G_SEL_INV_DATA>
    </G_SEL_CHECKS>
    <G_SEL_CHECKS>
      <VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>
      <C_VENDOR_NUMBER>863</C_VENDOR_NUMBER>
      <VENDOR_NAME>VENDOR33</VENDOR_NAME>
      <PAYMENT_NUMBER_>98012</PAYMENT_NUMBER_>
      <LIST_G_SEL_INV_DATA>
        <G_SEL_INV_DATA>
          <C_IP_CHECK_ID>98012</C_IP_CHECK_ID>
          <C_INVOICE_DATE>29-APR-13</C_INVOICE_DATE>
          <C_INVOICE_NUM>12345678</C_INVOICE_NUM>
        </G_SEL_INV_DATA>
      </LIST_G_SEL_INV_DATA>
      <LIST_G_SEL_INV_DATA>
        <G_SEL_INV_DATA>
          <C_IP_CHECK_ID>98012_1</C_IP_CHECK_ID>
          <C_INVOICE_DATE>29-APR-13</C_INVOICE_DATE>
          <C_INVOICE_NUM>12345678_1</C_INVOICE_NUM>
        </G_SEL_INV_DATA>
      </LIST_G_SEL_INV_DATA>
    </G_SEL_CHECKS>
  </LIST_G_SEL_CHECKS>
</XXHWAPSRA>

> Get the data

SELECT hed.Vendor_Type
      ,hed.vendor_num
      ,hed.payment_num
      ,hed.vendor_name
      ,evt.*
from   xx_xml_tst_tbl xxt
      ,xmltable('/XXHWAPSRA/LIST_G_SEL_CHECKS/G_SEL_CHECKS'
                   passing (xxt.xml_data)
                   columns  Vendor_Type           varchar2(40)   path 'VENDOR_TYPE_LOOKUP_CODE'
                           ,Vendor_Num            varchar2(40)   path 'C_VENDOR_NUMBER'
                           ,Vendor_Name           varchar2(246)  path 'VENDOR_NAME'
                           ,Payment_Num           varchar2(40)   path 'PAYMENT_NUMBER_'
                           ,evt   xmltype     path 'LIST_G_SEL_INV_DATA' -- Inner Layer
               ) Hed 
      ,xmltable('/LIST_G_SEL_INV_DATA/G_SEL_INV_DATA'
                   passing (Hed.evt)
                   columns  Check_id              varchar2(40)   path 'C_IP_CHECK_ID'
                           ,Inv_Date              varchar2(40)   path 'C_INVOICE_DATE'
                           ,Inv_Num               varchar2(40)   path 'C_INVOICE_NUM'
               ) evt                                   
where  request_id = 2421932
order by hed.vendor_num ;

> Sample Output

>> If you want to store these data into table.

> Create a table

create table xx_requi_tbl (created varchar2(20), requi_num varchar2(40), po_num varchar2(40), status varchar2(100), bu varchar2(240), type varchar2(40), supplier_name varchar2(256), supplier_site varchar2(256)) ;

>> Insert into table

declare
   v_xml_data  xmltype := xmltype(bfilename('XXHW_XML_DWNLD', 'o2438606V2.xml'), nls_charset_id('AL32UTF8')) ;
begin

   insert into xx_requi_tbl (created, requi_num, po_num, status, bu, type, supplier_name, supplier_site)
   select evt.*
     from xmltable('/PONOREC/NORECEIPT_DETAILS'
                   passing (v_xml_data)
                   columns  created           varchar2(20)   path 'Created'
                              ,requi_num         varchar2(40)   path 'Requisition_x0020_Num'
                              ,po_num            varchar2(40)   path 'PO_x0020_Number'
                                 ,status            varchar2(100)  path 'RECEIVING_STATUS'
                                  ,bu                varchar2(240)  path 'Business_x0020_Unit'
                                 ,type              varchar2(40)   path 'Type'
                                ,supplier_name     varchar2(256)  path 'Supplier_Name'
                                ,supplier_site     varchar2(256)  path 'Supplier_Site'
                 ) evt ;   
  commit;                             
 exception
    when others then
       dbms_output.put_line('Error: '||sqlerrm); 
 end;

> Select the data

select *
  from xx_requi_tbl ;

> Sample Output

AR Transaction: FRM-40735: POST-QUERY trigger raised unhandled exception ORA-06502

FRM-40735: POST-QUERY trigger raised unhandled exception ORA-06502

ORA-01403: no data found

AR Transaction form. -- When trying to query transaction.

Reason:  ra_customer_trx_all.ct_reference filed, in table the length of the column is 170 byte but in the form it is only 30 byte long.

Solution: Make the ct_reference field length to 30 characters long

Check for the related invoice/Credit Notes, if it is, you need to update the reference field for both.

select cn.trx_number cn
        ,cn.ct_reference cn_reference
        ,cn.interface_header_attribute1 cn_attri
        ,inv.trx_number invocie
        ,inv.ct_reference inv_reference
        ,inv.interface_header_attribute1 inv_attri
  from ra_customer_trx_all cn
         ,ra_customer_trx_all inv
 where cn.previous_customer_trx_id = inv.customer_trx_id
    and cn.trx_number = <Credit Note#>
    and cn.org_id = <Org Id> ;
   
update ra_customer_trx_all set ct_reference = '<30 Char long reference>'
 where trx_number in ('<Inv#>','<CN#>')
    and org_id = <Org Id> ;   

>> If this does not solve the problem, then there must be default setup for Reference

Goto, Receivables > Setup > Transactions > Sources, and check the setup for 'Reference Field Default Value'.

For Ex, if it is 'interface_header_attribute1', update this as well with the same value.

update ra_customer_trx_all set interface_header_attribute1 = '<30 Char long reference>'
 where trx_number in ('<Inv#>','<CN#>')
    and org_id = <Org Id> ;

Commit the changes and try again.

APP-FND-00756: Cannot find combination CCID=4321 CODE=MSTK SET=111

APP-FND-00756: Cannot find combination CCID=4321 CODE=MSTK SET=111

It is a problem with the stock locator.

Recompile the stock locator flexfield , ensure it is freezed.

Setup > Financials > Key >  Segments

 > Application = 'Inventory'   Flexfield Title = 'Stock Locators'

   > Check 'Freeze Flexfield Definition'  save/compile

*Note :- Even though you are not using stock locator in your organization, this error can populate.

Deployment of Business Event/Subscription

There are two ways to achieve this.

1> Using WFXLoad

Utility wmximport, a java class available in package oracle.apps.fnd.wf is used to download and
upload the business event/subscription.

Syntax:

adjava WFXLoad -d         --[to indicate download or upload :-u]
appsusername apps-password
Database Machine name
Database Port Number
Oracle Instance Name         --[from two task]
thin                                      --[for jdbc thin driver connectivity]
Language --[US in this case]
Event/Subscription File Name    --[into which event definition is downloaded or uploaded from]
EVENTS or SUBSCRIPTION
Name of the event

Machine name  --> fnd_profile.value('CSF_EMAP_DS_HOST')/'CSF: eLocation Map host name'
Port name     --> fnd_profile.value('CSF_MAP_DB_PORT')/'CSF: Database port to receive data'
Oracle Instance Name  --> Database name

OR

Open your Database Connection File(.dbc file), you can find it in $FND_SECURE directory

Machine name              --> HOST
Port name                    --> PORT
Oracle Instance Name  --> SID/SERVICE NAME

Example: Download

Event
adjava oracle.apps.fnd.wf.WFXLoad -d apps <<apps password>> <<HOST Name>>:<<PORT Number>>:<<SID>> thin US ./xx_test_event.wfx EVENTS oracle.apps.xxpo.test_busi_event;

Subscription
adjava oracle.apps.fnd.wf.WFXLoad -d apps <<apps password>> <<HOST Name>>:<<PORT Number>>:<<SID>> thin US ./xx_test_subscription.wfx SUBSCRIPTIONS oracle.apps.xxpo.test_busi_event;

Example: Upload

Event
adjava oracle.apps.fnd.wf.WFXLoad -u apps <<apps password>> <<HOST Name>>:<<PORT Number>>:<<SID>> thin US ./xx_test_event.wfx EVENTS oracle.apps.xxpo.test_busi_event;

Subscription
adjava oracle.apps.fnd.wf.WFXLoad -u apps <<apps password>> <<HOST Name>>:<<PORT Number>>:<<SID>> thin US ./xx_test_subscription.wfx SUBSCRIPTIONS oracle.apps.xxpo.test_busi_event;

Note: Please remove <<>>, also the drawback of this uploading script is that it can create a new business event/subscription but it can not upgrade the existing one.


2> Using wf_Events_pkg/wf_event_subscriptions_pkg packaged procedure
Download
declare
   cursor cur_event
   is
     select we.guid event_guid
           ,wes.guid subscription_guid
       from wf_events we
           ,wf_event_subscriptions wes
      where we.name = 'oracle.apps.xxpo.test_busi_event' --<<Business Event Name>>
        and wes.event_filter_guid = we.guid;  
   l_xml_data_e varchar2(32000); 
   l_xml_data_s varchar2(32000);      
begin
   for rec_event in cur_event
   loop
      -- Get XML data for Event Definition
      l_xml_data_e:=wf_Events_pkg.generate(rec_event.event_guid);
      dbms_output.put_line ('Event-xml data: '||l_xml_data_e);
      dbms_output.put_line (chr(10));
      -- Get XML data for Event Subscription Definition
      l_xml_data_s:=wf_event_subscriptions_pkg.generate(rec_event.subscription_guid);
      dbms_output.put_line ('Subscription-xml data: '||l_xml_data_s);
   end loop;
exception  
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end; 

Upload
declare
-- Event XML Data generated from wf_Events_pkg.generate
l_xml_data_e varchar2(32000) := '<WF_TABLE_DATA>
  <WF_EVENTS>
    <VERSION>1.0</VERSION>
    <GUID>#NEW</GUID>
    <NAME>oracle.apps.xxpo.test_busi_event</NAME>
    <TYPE>EVENT</TYPE>
    <STATUS>ENABLED</STATUS>
    <GENERATE_FUNCTION/>
    <OWNER_NAME>purchasing</OWNER_NAME>
    <OWNER_TAG>PO</OWNER_TAG>
    <CUSTOMIZATION_LEVEL>U</CUSTOMIZATION_LEVEL>
    <LICENSED_FLAG>Y</LICENSED_FLAG>
    <JAVA_GENERATE_FUNC/>
    <DISPLAY_NAME>oracle.apps.xxpo.test_busi_event</DISPLAY_NAME>
    <DESCRIPTION/>
    <IREP_ANNOTATION>
/*#
 *
 *
 * @rep:scope public
 * @rep:displayname oracle.apps.xxpo.requisition4
 * @rep:product PO
 * @rep:category BUSINESS_ENTITY
 */
</IREP_ANNOTATION>
  </WF_EVENTS>
</WF_TABLE_DATA>';

-- Event Subscription XML Data generated from wf_event_subscriptions_pkg.generate
l_xml_data_s varchar2(32000) := '<WF_TABLE_DATA>
  <WF_EVENT_SUBSCRIPTIONS>
    <VERSION>1.0</VERSION>
    <GUID>DF5A21A5FF09XXXX040C389B5973638</GUID>
    <SYSTEM_GUID>D470427B6B92D9FDXXXXB59767E7</SYSTEM_GUID>
    <SOURCE_TYPE>LOCAL</SOURCE_TYPE>
    <SOURCE_AGENT_GUID/>
    <EVENT_FILTER_GUID>oracle.apps.xxpo.test_busi_event</EVENT_FILTER_GUID>
    <PHASE>101</PHASE>
    <STATUS>ENABLED</STATUS>
    <RULE_DATA>KEY</RULE_DATA>
    <OUT_AGENT_GUID/>
    <TO_AGENT_GUID/>
    <PRIORITY>50</PRIORITY>
    <RULE_FUNCTION>xx_test_event_func</RULE_FUNCTION>
    <JAVA_RULE_FUNC/>
    <STANDARD_TYPE/>
    <STANDARD_CODE/>
    <ON_ERROR_CODE>ABORT</ON_ERROR_CODE>
    <ACTION_CODE>CUSTOM_RG</ACTION_CODE>
    <WF_PROCESS_TYPE/>
    <WF_PROCESS_NAME/>
    <PARAMETERS/>
    <OWNER_NAME>purchasing</OWNER_NAME>
    <OWNER_TAG>PO</OWNER_TAG>
    <CUSTOMIZATION_LEVEL>U</CUSTOMIZATION_LEVEL>
    <LICENSED_FLAG>Y</LICENSED_FLAG>
    <DESCRIPTION/>
    <EXPRESSION/>
  </WF_EVENT_SUBSCRIPTIONS>
</WF_TABLE_DATA>';

begin
   -- Create Event Definition
   wf_events_pkg.receive(l_xml_data_e);
   -- Create Event Subscription Definition
   wf_event_subscriptions_pkg.receive(l_xml_data_s);
   commit;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;

Delete Business Event and Subscription

------------------------
declare
   cursor cur_event
   is
     select we.guid event_guid
           ,wes.guid subscription_guid
       from wf_events we
           ,wf_event_subscriptions wes
      where we.name = 'oracle.apps.xxpo.test_busi_event' --<<Business Event Name>>
        and wes.event_filter_guid = we.guid;  
begin
   for rec_event in cur_event
   loop
      wf_events_pkg.delete_row(rec_event.event_guid);
      wf_event_subscriptions_pkg.delete_row(rec_event.subscription_guid);
   end loop;
   commit;
exception  
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;

Business Event - Possible Errors

1>> Check for possible errors

       

              a>. select notification_id

       from wf_notifications
     where subject like '%<business event name>%’;

b>. select wf_notification.getbody(<notification id of business event>)
      from dual;

2>> Check if Agent Listener is down

Navigation :: Workflow Administrator Web Applications > Oracle Applications Manager > Workflow Manager.

3>> Event Error Name: WFE_DISPATCH_RULE_ERR
     Event Error Message: 3825: Error '-4061 - ORA-04061: existing state of has been invalidated 

Reason: When a package is compiled, all copies in the shared pool are flagged as invalid. The error is caused because the notification mailer is reading an old version of the package stored in memory 

Solution: 
1. Stop the Notification Mailer and Workflow Deferred Notification Agent Listener
2. Recompile the APPS schema through ADADMIN (You should have 'apps' and 'system' password)
3. Start the Notification Mailer and agent listener and re-test 

4>> Event Error Message: No Event Subscriptions exist for this Event 

Run below concurrent program
System Administrator > "Synchronize Product License and Workflow BES License"