Launching JDeveloper for a given folder without seeing other projects.

When we are accessing jdeveloper without Admin previleges, we want to open a specific project under our Jdev home, then follow the below steps:

Go to cmd prompt:
set JDEV_USER_DIR=<JdevHone>\<projid>
then
launch jdevloper  <Jdev Bin Install dir>\jdeveloper.exe

example:

set JDEV_USER_DIR=C:\SK\Work\Work\jdev\xxtest
C:\SK\Software\p9879989_R12_GENERIC\jdevbin\jdeveloper.exe

Query to find GL periods for a given legal entity

Query to find GL periods for a given legal entity:

 select gp.period_name
                  from gl_ledger_norm_seg_vals bsv,gl_ledgers gl, gl_periods gp
                 where  1=1
                 AND Bsv.Legal_Entity_Id = :Legal Entity Id
                 And Bsv.Segment_Type_Code = 'B'
                AND sysdate between nvl(bsv.start_date,sysdate-1) and nvl(bsv.end_date,sysdate+1)
                And Gl.Object_Type_Code = 'L'                
                And Gl.Ledger_Category_Code = 'PRIMARY'
                And Gl.Ledger_Id=Bsv.Ledger_Id
                AND gp.period_set_name=gl.period_set_name
                order by gp.start_date
                ;

Derive GL Start and End Dates..

select start_date,end_date
                  from gl_ledger_norm_seg_vals bsv,gl_ledgers gl, gl_periods gp
                 where  1=1
                 AND Bsv.Legal_Entity_Id = :p_Legal_entity_id
                 And Bsv.Segment_Type_Code = 'B'
                AND sysdate between nvl(bsv.start_date,sysdate-1) and nvl(bsv.end_date,sysdate+1)
                And Gl.Object_Type_Code = 'L'                
                And Gl.Ledger_Category_Code = 'PRIMARY'
                And Gl.Ledger_Id=Bsv.Ledger_Id
                AND gp.period_set_name=gl.period_set_name
                and gp.period_name = :p_period_name
                order by gp.start_date

                ;

Query to find legal entity,registration and address details

Query to find legal entity details

SELECT xep.name,                                          -- Legal Entity Name
       xjv.registration_code_le registration_code,        -- Registration Code
       xr.registration_number,                          -- Registration Number
          DECODE (hl.address_line_1, NULL, '', hl.address_line_1 || ', ')
       || DECODE (hl.address_line_2, NULL, '', hl.address_line_2 || ', ')
       || DECODE (hl.address_line_3, NULL, '', hl.address_line_3 || ', ')
       || DECODE (hl.town_or_city, NULL, '', hl.town_or_city || ', ')
       || DECODE (hl.region_2, NULL, '', hl.region_2 || ', ')
       || DECODE (hl.postal_code, NULL, '', hl.postal_code || ', ')
       || DECODE (ftv.territory_short_name,
                  NULL, '',
                  ftv.territory_short_name)
          Address --Address
  FROM xle_registrations xr,
       xle_jurisdictions_vl xjv,
       hr_locations hl,
       fnd_territories_vl ftv,
       xle_entity_profiles xep
 WHERE     1 = 1
       AND xep.legal_entity_id = :legal_entity_id
       AND xr.source_table = 'XLE_ENTITY_PROFILES'
       AND xr.source_id = xep.legal_entity_id
       AND xjv.jurisdiction_id = xr.jurisdiction_id
       AND hl.location_id = xr.location_id
       AND ftv.territory_code = hl.country
       AND SYSDATE BETWEEN NVL (xr.effective_from, SYSDATE - 1)
                       AND NVL (xr.EFFECTIVE_TO, SYSDATE + 1);

Invalid number error when working with DFF attributes..

Invalid number error when working with DFF attributes when using to_number:

When we are working with DFFs, segments can be configured to store numbers but due to user wrong input or some misconfiguration some rows might end with having string with numbers and characters. When we use that attribute in a query and try to compare it to a number or try to convert to a number, the system will throw error. To avoid the error, we can use the following condition in the query:

select * from po_headers_all
where org_id=101
and nvl(length(regexp_replace(attribute5,'^[0-9]+', ''),0)=0;


to find invalid rows you can use below.

nvl(length(regexp_replace(attribute5,'^[0-9]+', ''),0)>0;

API and Query to get AP Invoice Status

API and Query to get AP Invoice Status:

1.  Get Invoice status from API
SELECT ai.invoice_num,
       ai.invoice_amount,
       AP_INVOICES_PKG.GET_APPROVAL_STATUS (ai.invoice_id,
                                            ai.invoice_amount,
                                            ai.payment_status_flag,
                                            ai.invoice_type_lookup_code)
          inv_status
  FROM ap_invoices_all ai
 WHERE invoice_num = '<inv number>' AND org_id = <org id>;



2. Get actual meaning from below query from lookup:
  SELECT lookup_type,
         LOOKUP_CODE,
         MEANING,
         DESCRIPTION,
         TAG,
         START_DATE_ACTIVE,
         END_DATE_ACTIVE,
         ENABLED_FLAG
    FROM FND_LOOKUP_VALUES_VL
   WHERE     (   NVL ('', territory_code) = territory_code
              OR territory_code IS NULL)
         AND (VIEW_APPLICATION_ID = 200)
         AND (SECURITY_GROUP_ID = 0)
         AND lookup_Type = 'NLS TRANSLATION'
         AND LOOKUP_CODE =  '<Inv_status from above query> ' ---'NEVER APPROVED'
ORDER BY LOOKUP_CODE;

Useful Queries

1. Print Date and time using sysdate:
    SELECT TO_CHAR (SYSDATE, 'DD-MM-YY HH:MI:SS') FROM DUAL;

2. Print Numbers from 1 to 10.
 SELECT ROWNUM r
      FROM DUAL
CONNECT BY ROWNUM <= 10;

3. Kill Session
alter system kill session '1266,54057'; --'sid, serial#'

4. Gather Schema stats:
begin
DBMS_STATS.GATHER_TABLE_STATS('AP','AP_SUPPLIER_SITES_ALL');
end;
/

5. Remove special  (control chars) characeters from the string

SELECT REGEXP_REPLACE(colx, '[[:cntrl:]]', ' ') FROM xx_tab;

6. REPLACE ONE OR MORE COMMAS WITH SINGLE COMMA AND SPACE

select regexp_replace(' Address Line1,,Addressline3,,Postal Code,,57000  ',',{1,}', ', ') from dual;

Useful data dictionary views

NLS_DATABASE_PARAMETERS --> lists permanent NLS parameters of the database.NLS_INSTANCE_PARAMETERS lists NLS parameters of the instance.
There is one row for each component.You can query any of DBA_*, ALL_*,USER_*.In the below table I am listing only USER_

Dictionary Name	Details
USER_OBJECTS	Stores details about all the objects in a schema – Object Types – Table, View, Synonym, Trigger, Sequence, Procedure, Function, Package, Package Body, Trigger, Index, Database Link, Type, polcies, Rules (VPD policy rules)
USER_TABLES	Table – Table Space, Disk block allocation details
USER_TAB_COLS	Column of table
USER_VIEWS	Views data dictionary – You can get the view text
USER_SEQUENCES	Sequence information – Seq min, max, increment, etc.
user_synonyms	Synonyms – Reference object details
User_triggers	Stores the all the trigger in the schema. – You can get the trigger text and trigger table etc.
User_trigger_cols	Trigger column details
User_source	Stores all the source code – You can get code for trigger, procedure, package, package body, type body, type, function, procedure.
User_procedures	Lists all the procedure – You can get a list of procedure for a given package.
User_arguments	List of all the parameters for a given procedure. It includes package procedures as well.
User_indexes	List of Indexes ex: select * from user_indexes where table_name=''
user_ind_columns	Index column names
user_dependencies	Dependencies  select * from user_dependencies                  where referenced_name='GL_LEDGER_NORM_SEG_VALS'                  and name not like 'XX%'                 and referenced_type='SYNONYM' and referenced_owner='APPS';

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;