Useful Dynamic Performance (V$) Views

V$ Views vs GV$ Views:V$ views are CONTAINER_DATA objects. When a user connected to the root queries a V$ view, the query results will depend on the CONTAINER_DATA attribute for users. For almost every V$ view,  Oracle has a corresponding GV$ (global V$) view. In Oracle Real Application Clusters, querying a GV$ view retrieves the V$ view information from all qualified instances. In addition to the V$ information, each GV$ view contains an extra column named INST_ID of data type NUMBER. The INST_ID column displays the instance number from which the associated V$ view information was obtained.
V$ACCESS displays objects in the database that are currently locked and the sessions that are accessing them.V$ACTIVE_INSTANCES displays the mapping between instance names and instance numbers for all instances that have the database currently mounted.V$AQ displays statistics for the queues in the database.V$CONTEXT lists set attributes in the current session.V$CONTROLFILE lists the names of the control files.V$DATABASE displays information about the database from the control file.V$DATAFILE contains datafile information from the control file.V$DBFILE lists all datafiles making up the database. This view is retained for historical compatibility. Use of V$DATAFILE is recommended instead.V$DBLINK describes all database links (links with IN_TRANSACTION = YES) opened by the session issuing the query on V$DBLINK. These database links must be committed or rolled back before being closed.V$INSTANCE view displays the state of the current instance.V$LICENSE contains information about license limits.V$NLS_PARAMETERS view contains current values of NLS parameters. =>NLS_CALENDAR, NLS_CHARACTERSET, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_ISO_CURRENCY, NLS_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_SORT, NLS_TERRITORY, NLS_UNION_CURRENCY, NLS_NCHAR_CHARACTERSET, NLS_COMPV$NLS_VALID_VALUES lists all valid values for NLS parameters. V$OBJECT_USAGE to monitor index usage. The view displays statistics about index usage gathered from the database. All indexes that have been used at least once can be monitored and displayed in this view. V$OPTION lists options that are installed with the Oracle Database.V$PARAMETER displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.V$PARAMETER2 displays information about the initialization parameters that are currently in effect for the session, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values displayed in the V$SYSTEM_PARAMETER2 view.Presenting the list parameter values in this format enables you to quickly determine the values for a list parameter. For example, if a parameter value is a, b, then the V$PARAMETER view does not tell you if the parameter has two values (both a and b) or one value (a, b). V$PARAMETER2 makes the distinction between the list parameter values clear.V$SESSION view lists session information for each current session.V$SGA displays summary information about the system global area (SGA).V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered.V$SQL_BIND_DATA For each distinct bind variable in each cursor owned by the session querying this view/V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.V$SQL_PLAN_STATISTICS provides execution statistics at the row source level for each child cursor.V$SQLTEXT contains the text of SQL statements belonging to shared SQL cursors in the SGA.V$VERSION displays version numbers of core library components in the Oracle Database. 

Useful Supplier Queries

1. Supplier Details:

SELECT     s.segment1 supplier_number,                
                s.vendor_name supplier_name,
                s.vendor_name_alt supplier_alternate_name,
                ss.vendor_site_code supplier_site_code,
                ss.vendor_site_id supplier_site_id,
                hp.jgzz_fiscal_code tax_payer_id,
                ss.org_id operating_unit,                
                s.end_date_active supplier_inactive_date,
                ss.inactive_date supplier_site_inactive_date,
                NVL (ss.terms_id, s.terms_id) payment_term_id,
                hp.jgzz_fiscal_code national_id,
                ss.vat_code tax_code,
                NVL (ss.awt_group_id, s.awt_group_id)
                   withholding_tax_group_id,              
                s.party_id,
                ss.party_site_id               
           FROM ap_suppliers s,
                ap_supplier_sites_all ss               
                hz_parties hp,
                hz_party_sites hps
          WHERE     1 = 1
                AND s.vendor_type_lookup_code = lv.lookup_code
                AND ss.org_id = g_org_id
                AND s.vendor_id = ss.vendor_id
                AND hp.party_id = s.party_id

                AND hps.party_site_id = ss.party_site_id;

2. Query to get Supplier Tax Registration Number:

SELECT tax_reference
  FROM hz_organization_profiles hzop, ap_suppliers aps
 WHERE     1 = 1
       AND aps.segment1='1000' --> Supplier Number
       AND hzop.party_id = aps.party_id
       AND hzop.effective_end_date IS NULL;

3.Payment Terms:

SELECT name
           FROM ap_terms_vl

          WHERE term_id = <Terms Id from Query 1>;

4. Withhold Tax Group:

SELECT name, last_update_date
           FROM ap_awt_groups

          WHERE GROUP_ID = <withholding_tax_group_id from Query 1>

5.Supplier Payment Method:


  SELECT pv.payment_method_name
             FROM iby_external_payees_all ip,
                  iby_ext_party_pmt_mthds pm,
                  iby_payment_methods_vl pv
            WHERE     1 = 1
                  AND ip.supplier_site_id IS NULL
                  AND ip.payee_party_id = <Party Id from query 1>  --supp party id
                  AND ip.ext_payee_id = pm.ext_pmt_party_id
                  AND pm.payment_method_code = pv.payment_method_code

         ORDER BY pm.primary_flag DESC, pm.last_update_date DESC;


6.Supplier site Payment method:

SELECT pv.payment_method_name,
                  pm.last_update_date,
                  pv.last_update_date pm_last_update_date
             FROM iby_external_payees_all ip,
                  iby_ext_party_pmt_mthds pm,
                  iby_payment_methods_vl pv
            WHERE     ip.supplier_site_id = <Supp site Id from query 1>
                  AND ip.org_id = p_org_id
                  AND ip.ext_payee_id = pm.ext_pmt_party_id
                  AND pm.payment_method_code = pv.payment_method_code

         ORDER BY pm.primary_flag DESC, pm.last_update_date DESC;


7. Supplier Contact details Including phone,  email and fax details:

SELECT hp.person_last_name || ' ' || hp.person_first_name
                     contact_name,
                  hcpe.email_address,
                  LTRIM (
                     RTRIM (hcpp.phone_area_code || ' ' || hcpp.phone_number))
                     AS primary_phone_number,
                  LTRIM (
                     RTRIM (hcpf.phone_area_code || ' ' || hcpf.phone_number))
                     fax_number,
                  hzr.end_date,
                  hzr.last_update_date
             FROM hz_parties hp,
                  hz_relationships hzr,
                  hz_party_usg_assignments hpua,
                  hz_contact_points hcpp,
                  hz_contact_points hcpe,
                  hz_contact_points hcpf
            WHERE     hp.party_id = hzr.subject_id
                  AND hzr.object_id = <<p_supplier_party_id>> -- party of supplier
                  AND hzr.relationship_type = 'CONTACT'
                  AND hzr.relationship_code = 'CONTACT_OF'
                  AND hzr.subject_type = 'PERSON'
                  AND hzr.object_type = 'ORGANIZATION'
                  AND hzr.status IN ('A', 'I')
                  AND hpua.party_id = hp.party_id
                  AND hpua.status_flag IN ('A', 'I')
                  AND hpua.party_usage_code = 'SUPPLIER_CONTACT'
                  AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
                  AND hcpp.owner_table_id(+) = hzr.party_id
                  AND hcpp.phone_line_type(+) = 'GEN'
                  AND hcpp.contact_point_type(+) = 'PHONE'
                  AND hcpp.primary_flag(+) = 'Y'
                  AND hcpe.owner_table_name(+) = 'HZ_PARTIES'
                  AND hcpe.owner_table_id(+) = hzr.party_id
                  AND hcpe.contact_point_type(+) = 'EMAIL'
                  AND hcpe.primary_flag(+) = 'Y'
                  AND (hcpe.status IS NULL OR hcpe.status IN ('A', 'I'))
                  AND hcpf.owner_table_name(+) = 'HZ_PARTIES'
                  AND hcpf.owner_table_id(+) = hzr.party_id
                  AND hcpf.contact_point_type(+) = 'PHONE'
                  AND hcpf.phone_line_type(+) = 'FAX'
                  AND (hcpf.status IS NULL OR hcpf.status IN ('A', 'I'))
                  AND (hcpp.status IS NULL OR hcpp.status IN ('A', 'I'))

         ORDER BY hzr.end_date DESC;

8. Supplier site contact details:

SELECT hp.person_last_name || ' ' || hp.person_first_name

                     contact_name,

                  hcpe.email_address,

                  LTRIM (

                     RTRIM (hcpp.phone_area_code || ' ' || hcpp.phone_number))

                     AS primary_phone_number,

                  LTRIM (

                     RTRIM (hcpf.phone_area_code || ' ' || hcpf.phone_number))

                     fax_number,

                  hzr.end_date,

                  hzr.last_update_date

             FROM hz_parties hp,

                  hz_party_sites hps,

                  hz_relationships hzr,

                  ap_supplier_contacts ascs,

                  hz_party_usg_assignments hpua,

                  hz_contact_points hcpp,

                  hz_contact_points hcpe,

                  hz_contact_points hcpf

            WHERE     1 = 1

                  AND NVL (hps.end_date_active, SYSDATE) >= SYSDATE

                  AND hzr.relationship_type = 'CONTACT'

                  AND hzr.relationship_code = 'CONTACT_OF'

                  AND hzr.subject_type = 'PERSON'

                  AND hzr.subject_table_name = 'HZ_PARTIES'

                  AND hzr.object_type = 'ORGANIZATION'

                  AND hzr.object_table_name = 'HZ_PARTIES'

                  AND hzr.status = 'A'

                  AND hps.party_id = hzr.object_id

                  AND hps.party_site_id = p_supplier_party_site_id

                  AND hzr.relationship_id = ascs.relationship_id

                  AND hzr.party_id = ascs.rel_party_id

                  AND hps.party_site_id = ascs.org_party_site_id

                  AND hzr.subject_id = ascs.per_party_id

                  AND hp.party_id = hzr.subject_id

                  AND hpua.party_id = hp.party_id

                  AND hpua.status_flag IN ('A', 'I')

                  AND hpua.party_usage_code = 'SUPPLIER_CONTACT'

                  AND hcpp.owner_table_name(+) = 'HZ_PARTIES'

                  AND hcpp.owner_table_id(+) = hzr.party_id

                  AND hcpp.phone_line_type(+) = 'GEN'

                  AND hcpp.contact_point_type(+) = 'PHONE'

                  AND hcpp.primary_flag(+) = 'Y'

                  AND hcpe.owner_table_name(+) = 'HZ_PARTIES'

                  AND hcpe.owner_table_id(+) = hzr.party_id

                  AND hcpe.contact_point_type(+) = 'EMAIL'

                  AND hcpe.primary_flag(+) = 'Y'

                  AND (hcpe.status IS NULL OR hcpe.status IN ('A', 'I'))

                  AND hcpf.owner_table_name(+) = 'HZ_PARTIES'

                  AND hcpf.owner_table_id(+) = hzr.party_id

                  AND hcpf.contact_point_type(+) = 'PHONE'

                  AND hcpf.phone_line_type(+) = 'FAX'

                  AND (hcpf.status IS NULL OR hcpf.status IN ('A', 'I'))

                  AND (hcpp.status IS NULL OR hcpp.status IN ('A', 'I'))

         ORDER BY hzr.end_date DESC;

9. Bank account owner details:

SELECT hp.party_name, iba.last_update_date

             FROM iby_account_owners iba, hz_parties hp

            WHERE     iba.ext_bank_account_id = p_bank_account_id

                  AND iba.account_owner_party_id = hp.party_id

         ORDER BY NVL (primary_flag, 'N') DESC;

10. Supplier Bank Details:

SELECT s.segment1 supplier_number,

                hps.attribute1 legacy_supplier_number,

                ss.vendor_site_code supplier_site_code,

                ss.org_id operating_unit,

                ss.vendor_site_id supplier_site_id,

                s.party_id party_id,

                ib_pmt_instr.order_of_preference priority_number,

                ib_pmt_instr.start_date,

                ib_pmt_instr.end_date,

                ib_pmt_instr.last_update_date instr_last_update_date -- bank account details

                                                                    ,

                bank_ac.bank_account_num bank_account_number,

                bank_ac.ext_bank_account_id bank_account_id,

                bank_ac.bank_account_name payee,

                bank_ac.last_update_date bank_ac_last_update_date -- bank branch details

                                                                 ,

                bank_branch.bank_branch_name_alt supp_bank_name,

                bank_branch.branch_number supp_bank_branch_num,

                bank_branch.bank_number supp_bank_num,

                   bank_branch.address_line1

                || ','

                || bank_branch.address_line2

                || ','

                || bank_branch.address_line3

                || ','

                || bank_branch.address_line4

                || ','

                || bank_branch.city

                || ','

                || bank_branch.state

                   supp_bank_branch_address,

                bank_branch.eft_swift_code swift_code

           FROM ap_suppliers s,

                ap_supplier_sites_all ss,

              

                hz_party_sites hps,

                iby_external_payees_all ib_xt_payee,

                iby_pmt_instr_uses_all ib_pmt_instr,

                iby_ext_bank_accounts bank_ac,

                ce_bank_branches_v bank_branch

          WHERE     1 = 1

                AND ss.org_id =  <<g_org_id>>

                AND s.vendor_id = ss.vendor_id

                AND hps.party_site_id = ss.party_site_id

                AND ib_xt_payee.supplier_site_id = ss.vendor_site_id

                AND ib_xt_payee.payee_party_id = hps.party_id

                AND ib_pmt_instr.ext_pmt_party_id = ib_xt_payee.ext_payee_id

                AND ib_pmt_instr.instrument_type = 'BANKACCOUNT'

                AND ib_pmt_instr.instrument_id = bank_ac.ext_bank_account_id

                AND bank_ac.branch_id = bank_branch.branch_party_id(+)

11. Banks at Supplier Site Level:

SELECT s.segment1 supplier_number,

                hps.attribute1 legacy_supplier_number,

                ss.vendor_site_code supplier_site_code,

                ss.org_id operating_unit,

                ss.vendor_site_id supplier_site_id,

                s.party_id party_id,

                ib_pmt_instr.order_of_preference priority_number,

                ib_pmt_instr.start_date,

                ib_pmt_instr.end_date,

                ib_pmt_instr.last_update_date instr_last_update_date -- bank account details

                                                                    ,

                bank_ac.bank_account_num bank_account_number,

                bank_ac.ext_bank_account_id bank_account_id,

                bank_ac.bank_account_name,

                bank_ac.last_update_date bank_ac_last_update_date -- bank branch details

                                                                 ,

                bank_branch.bank_branch_name_alt supp_bank_name,

                bank_branch.branch_number supp_bank_branch_num,

                bank_branch.bank_number supp_bank_num,

                   bank_branch.address_line1

                || ','

                || bank_branch.address_line2

                || ','

                || bank_branch.address_line3

                || ','

                || bank_branch.address_line4

                || ','

                || bank_branch.city

                || ','

                || bank_branch.state

                   supp_bank_branch_address,

                bank_branch.eft_swift_code swift_code

           FROM ap_suppliers s,

                ap_supplier_sites_all ss,              

                hz_party_sites hps,

                iby_external_payees_all ib_xt_payee,

                iby_pmt_instr_uses_all ib_pmt_instr,

                iby_ext_bank_accounts bank_ac,

                ce_bank_branches_v bank_branch

          WHERE     1 = 1

                

                AND ss.org_id = <<g_org_id>>

                AND s.vendor_id = ss.vendor_id

                AND hps.party_site_id = ss.party_site_id

                AND ib_xt_payee.supplier_site_id IS NULL

                AND ib_xt_payee.payee_party_id = hps.party_id

                AND ib_pmt_instr.ext_pmt_party_id = ib_xt_payee.ext_payee_id

                AND ib_pmt_instr.instrument_type = 'BANKACCOUNT'

                AND ib_pmt_instr.PAYMENT_FUNCTION = 'PAYABLES_DISB'

                AND ib_pmt_instr.instrument_id = bank_ac.ext_bank_account_id

                AND bank_ac.branch_id = bank_branch.branch_party_id(+)

                AND NOT EXISTS

                       (SELECT 'Y'

                          FROM iby_external_payees_all ib_xt_payee,

                               iby_pmt_instr_uses_all ib_pmt_instr

                         WHERE     ib_xt_payee.supplier_site_id =

                                      ss.vendor_site_id

                               AND ib_pmt_instr.ext_pmt_party_id =

                                      ib_xt_payee.ext_payee_id

                               AND ib_pmt_instr.instrument_type =

                                      'BANKACCOUNT');

AP Invoice Queries

List of PR Requesters for AP Invoice:
 SELECT DISTINCT pda1.deliver_to_person_id
           FROM ap_invoice_distributions_all aida,
                po_distributions_all pda,
                po_line_locations_all plla,
                po_lines_all pla,
                po_distributions_all pda1
          WHERE     aida.invoice_id = p_Transaction_id
                AND aida.po_distribution_id = pda.po_distribution_id
                AND pda.line_location_id = plla.line_location_id
                AND pda.po_line_id = pla.po_line_id
                AND pla.po_line_id = pda1.po_line_id
                AND pda1.deliver_to_person_id IS NOT NULL
                AND (   (    plla.inspection_required_flag = 'N'
                         AND plla.receipt_required_flag = 'N'
                         AND pda.distribution_type = 'STANDARD'
                         AND pda.po_distribution_id = pda1.po_distribution_id)
                     OR EXISTS
                           (SELECT 1
                              FROM ap_invoices_all aia
                             WHERE     aia.invoice_id = aida.invoice_id
                                   AND aia.invoice_type_lookup_code =
                                          'PREPAYMENT'));


List of Project Mangers for Projects on AP Invoice:
SELECT ppp.person_id
             FROM ap_invoice_distributions_all aid, pa_project_players ppp
            WHERE     aid.invoice_id = p_transaction_id
                  AND aid.project_id = ppp.project_id
                  AND ppp.person_id IS NOT NULL
                  AND ppp.project_role_type = 'PROJECT MANAGER'
                  AND TRUNC (SYSDATE) BETWEEN TRUNC (
                                                 NVL (ppp.start_date_active,
                                                      SYSDATE))
                                          AND TRUNC (
                                                 NVL (ppp.end_date_active,
                                                      SYSDATE + 1))
         GROUP BY ppp.person_id;


List of Task Mangers for Projects on AP Invoice:
SELECT pt.task_manager_person_id person_id
             FROM ap_invoice_distributions_all aid, pa_tasks pt
            WHERE     invoice_id = p_transaction_id
                  AND aid.task_id = pt.task_id
                  AND pt.task_manager_person_id IS NOT NULL
                  AND TRUNC (SYSDATE) BETWEEN TRUNC (
                                                 NVL (pt.start_date, SYSDATE))
                                          AND TRUNC (
                                                 NVL (pt.completion_date,
                                                      SYSDATE + 1))
         GROUP BY pt.task_manager_person_id;

Useful Queries for Expense Reports

Useful Queries for Expense Reports:

1. Project Managers for a project on Expense Report:

SELECT ppp.person_id
             FROM ap_exp_report_dists_all aerd, pa_project_players ppp
            WHERE     aerd.report_header_id = p_transaction_id
                  AND aerd.project_id = ppp.project_id
                  AND ppp.person_id IS NOT NULL
                  AND ppp.project_role_type = 'PROJECT MANAGER'
                  AND TRUNC (SYSDATE) BETWEEN TRUNC (ppp.start_date_active)
                                          AND TRUNC (
                                                 NVL (ppp.end_date_active,
                                                      SYSDATE + 1))

         GROUP BY ppp.person_id;

2.Task managers fro a task on Expense Report:

SELECT pt.task_manager_person_id person_id
             FROM ap_exp_report_dists_all aerd, pa_tasks pt
            WHERE     aerd.report_header_id = p_transaction_id
                  AND aerd.task_id = pt.task_id
                  AND pt.task_manager_person_id IS NOT NULL
                  AND TRUNC (SYSDATE) BETWEEN TRUNC (
                                                 NVL (pt.start_date, SYSDATE))
                                          AND TRUNC (
                                                 NVL (pt.completion_date,
                                                      SYSDATE + 1))
         GROUP BY pt.task_manager_person_id;

Useful Queries for Purchase Requisitions

1.Query to find requisition total amount:

 SELECT nvl(SUM(decode(order_type_lookup_code, 
'RATE', amount, 
'FIXED PRICE', amount, 
quantity * unit_price)), 0) req_amount,
po_ame_setup_pvt.get_function_currency(max(requisition_header_id)) req_func_currency, 
po_ame_setup_pvt.get_rate_type(max(requisition_header_id)) rate_type

from   po_requisition_lines_all 
where requisition_header_id = po_ame_setup_pvt.get_new_req_header_id(3915049)
and  nvl(cancel_flag, 'N') = 'N'
and  nvl(modified_by_agent_flag, 'N') = 'N';
select approved_date from po_requisition_headers_all where requisition_header_id=3915049;

2.Latest Change Request/Change Order details:
Change Order gets created for a Requisition when the req lines created onto Purchase order and then  a change need in Requisition.

 SELECT MAX (change_request_group_id) change_request_group_id, MAX(last_update_date) last_update_date
           FROM po_change_requests
          WHERE     document_header_id = p_transaction_id

                AND document_type = 'REQ';

PO_REQUISITION_HEADERS_ALL.CHANGE_PENDING_FLAG => Indicates whether requisition is in Change Order process or not

3. Requisition amount from a change request

 SELECT NVL (
                   SUM (
                      DECODE (
                         pcr3.action_type,
                         'CANCELLATION', 0,
                         DECODE (
                            prl.order_type_lookup_code,
                            'RATE', NVL (pcr4.new_amount, prl.amount),
                            'FIXED PRICE', NVL (pcr4.new_amount, prl.amount),
                              NVL (pcr1.new_price, prl.unit_price)
                            * NVL (pcr2.new_quantity, prl.quantity)))),
                   0)
           FROM po_requisition_lines_all prl,
                po_change_requests pcr1,
                po_change_requests pcr2,
                po_change_requests pcr3,
                po_change_requests pcr4
          WHERE     prl.requisition_line_id = pcr1.document_line_id(+)
                AND pcr1.change_request_group_id(+) =
                       l_change_request_group_id
                AND pcr1.request_level(+) = 'LINE'
                AND pcr1.change_active_flag(+) = 'Y'
                AND pcr1.new_price(+) IS NOT NULL
                AND prl.requisition_line_id = pcr2.document_line_id(+)
                AND pcr2.change_request_group_id(+) =
                       l_change_request_group_id
                AND pcr2.request_level(+) = 'LINE'
                AND pcr2.action_type(+) = 'DERIVED'
                AND pcr2.new_quantity(+) IS NOT NULL
                AND prl.requisition_line_id = pcr4.document_line_id(+)
                AND pcr4.change_request_group_id(+) =
                       l_change_request_group_id
                AND pcr4.request_level(+) = 'LINE'
                AND pcr4.action_type(+) = 'DERIVED'
                AND pcr4.new_amount(+) IS NOT NULL
                AND prl.requisition_line_id = pcr3.document_line_id(+)
                AND pcr3.change_request_group_id(+) =
                       l_change_request_group_id
                AND pcr3.request_level(+) = 'LINE'
                AND pcr3.action_type(+) = 'CANCELLATION'
                AND prl.requisition_header_id = p_transaction_id
                AND NVL (prl.modified_by_agent_flag, 'N') = 'N'

                AND NVL (prl.cancel_flag, 'N') = 'N';

4. Last action on PO Requisition:

SELECT TRUNC (MAX (NVL (action_date, SYSDATE)))
           FROM PO_ACTION_HISTORY
          WHERE     1 = 1
                AND object_type_code = 'REQUISITION'
                AND object_id = p_transaction_id

                AND action_code = 'SUBMIT';

5. Project Managers for Requisition:

 SELECT ppp.person_id
             FROM po_requisition_lines_all pla,
                  po_req_distributions_all pda,
                  pa_project_players ppp
            WHERE     1 = 1
                  AND requisition_header_id = p_transaction_id
                  AND pda.requisition_line_id = pla.requisition_line_id
                  AND pda.project_id = ppp.project_id
                  AND ppp.person_id IS NOT NULL
                  AND TRUNC (SYSDATE) BETWEEN TRUNC (
                                                 NVL (ppp.start_date_active,
                                                      SYSDATE))
                                          AND TRUNC (
                                                 NVL (ppp.end_date_active,
                                                      SYSDATE + 1))
                  AND ppp.project_role_type = 'PROJECT MANAGER'

         GROUP BY ppp.person_id;

6. Task managers for a requisition:

  SELECT pt.task_manager_person_id person_id
           FROM po_requisition_lines_all pla,
                po_req_distributions_all pda,
                pa_tasks pt
          WHERE     1 = 1
                AND requisition_header_id = p_transaction_id
                AND pda.requisition_line_id = pla.requisition_line_id
                AND pda.task_id = pt.task_id
                AND pt.task_manager_person_id IS NOT NULL
                AND TRUNC (SYSDATE) BETWEEN TRUNC (pt.start_date)
                                        AND TRUNC (
                                               NVL (pt.completion_date,

                                                    SYSDATE + 1));

7. Requisition Preparer and requester details:

SELECT DISTINCT h.org_id,
                         h.approved_date,
                         h.preparer_id,
                         l.to_person_id requester_id    
           FROM po_requisition_headers_all h, po_requisition_lines_all l 
          WHERE     h.requisition_header_id = p_transaction_id

                AND h.requisition_header_id = l.requisition_header_id;

Customizing Send Separate Remittance Advices and XML Output for Send Separate Remittance Advices

Customizing Send Separate Remittance Advices and  XML Output for Send Separate Remittance Advices:

Send Separate Remittance Advices Program uses the same XML extract used for formatting of payments within the payment instruction except that it includes information about delivery method and address in case of Remittance advice program.

One can customize the IBY_FD_EXTRACT_EXT_PUB extension package to add custom XML tags.

Refer Notes:

*** Format Customization in Oracle Payments for Oracle Applications Release 12 (Doc ID 787467.1) 

*** R12 Oracle Payments Processing 'How To' documents (Doc ID 579132.1) and section How to Create or Modify a Payment Format Using XML Builder

*** R12: Can Users Customize XML Extract? (Doc ID 457539.1)

https://docs.oracle.com/cd/E26401_01/doc.122/e48766/T456136T484670.htm#3695657

XML Output for the payment instruction will be saved in iby_trxn_documents

SELECT xmltype(document) 

   FROM iby_trxn_documents 

      WHERE payment_instruction_id=<>;

PLSQL Script to raise a business event

Raise a business event using PL/SQL:


DECLARE
   l_event_name            VARCHAR2 (240)      := 'xx.oracle.apps.send.cancelpo';
   l_event_parameter_list  wf_parameter_list_t := wf_parameter_list_t ();
   L_ORG_ID number := 101;
   L_PO_HEADER_ID Number := 100;
   l_event_data clob;

   --
BEGIN

         wf_event.addparametertolist (p_name                => 'ORG_ID'
                                    , p_value                       => L_ORG_ID
                                    , p_parameterlist               => l_event_parameter_list
                                      );
         wf_event.addparametertolist (p_name                        => 'PO_HEADER_ID'
                                    , p_value                       => L_PO_HEADER_ID
                                    , p_parameterlist               => l_event_parameter_list
                                      );
         wf_event.addparametertolist (p_name                        => 'USER_ID'
                                    , p_value                       => fnd_global.user_id
                                    , p_parameterlist               => l_event_parameter_list
                                      );
         wf_event.addparametertolist (p_name                        => 'RESP_ID'
                                    , p_value                       => fnd_global.resp_id
                                    , p_parameterlist               => l_event_parameter_list
                                      );
         wf_event.addparametertolist (p_name                        => 'RESP_APPL_ID'
                                    , p_value                       => fnd_global.resp_appl_id
                                    , p_parameterlist               => l_event_parameter_list
                                      );
         --

        l_event_data :=  '<EVENT_XML_DATA>
                <SUPPLIER>ABC LTD</SUPPLIER>
                <PO_NUMBER>1234</PO_NUMBER>
  </EVENT_XML_DATA>';

         wf_event.raise(p_event_name => l_event_name,
                        p_event_key =>  L_PO_HEADER_ID ,  //SYS_GUID ()
                        p_event_data => l_event_data,
                        p_parameters => l_event_parameter_list,
                        p_send_date => Sysdate);

EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/