Script to update salespersons customer site wise in oracle apps R12

SELECT * FROM HZ_PARTIES
WHERE PARTY_NAME LIKE 'DEENA VISION%';

SELECT * FROM HZ_CUST_ACCOUNTS_ALL
WHERE PARTY_ID =94043 ;

SELECT * FROM HZ_CUST_ACCT_SITES_ALL
WHERE CUST_ACCOUNT_ID =62040;

SELECT * FROM HZ_CUST_SITE_USES_ALL
WHERE CUST_ACCT_SITE_ID = 62041;

==============================================================================

select * from jtf_rs_salesreps
where SALESREP_ID =100004040 ;

SELECT SALESREP_ID,A.RESOURCE_NAME,B.ORG_ID
FROM JTF_RS_RESOURCE_EXTNS_TL A,
               jtf_rs_salesreps B
WHERE 1=1
--AND SALESREP_ID =100004040
AND  A.RESOURCE_ID = B.RESOURCE_ID
AND A.RESOURCE_NAME LIKE 'D SENTHIL%';

==================================================

SELECT HCSUA.CUST_ACCT_SITE_ID,HCSUA.ORG_ID,AC.CUSTOMER_ID,AC.CUSTOMER_NAME,CUSTOMER_NUMBER,HCSUA.PRIMARY_SALESREP_ID,
(SELECT  A.RESOURCE_NAME FROM JTF_RS_RESOURCE_EXTNS_TL A,
               jtf_rs_salesreps B
WHERE SALESREP_ID =HCSUA.PRIMARY_SALESREP_ID  --100004040
AND  A.RESOURCE_ID = B.RESOURCE_ID) SALESREP_NAME
FROM AR_CUSTOMERS AC,
              HZ_CUST_ACCOUNTS_ALL HCAA,
              HZ_CUST_ACCT_SITES_ALL HCASA,
              HZ_CUST_SITE_USES_ALL HCSUA
WHERE AC.CUSTOMER_ID = HCAA.CUST_ACCOUNT_ID
AND   HCAA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
AND   HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
AND  AC.CUSTOMER_NUMBER = 'RDD1078';

UPDATE THE TABLE:-

UPDATE HZ_CUST_SITE_USES_ALL
SET PRIMARY_SALESREP_ID = 100018070
WHERE ORG_ID = 182
AND CUST_ACCT_SITE_ID= 62041;

commit;

sql query for list all users who have not logged on in oracle apps r12

We can use this query for your requirement.

SELECT pap.full_name,user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS AM') last_logon_date,

to_char(end_date,'DD-MON-YYYY HH24:MI:SS AM') end_date

FROM fnd_user fu,

     per_all_people_f pap

WHERE fu.employee_id = pap.person_id

AND fu.user_name NOT IN (SELECT user_name

                                         FROM   fnd_user

                                         WHERE  1=1

                                          AND end_date IS NULL

                                           AND  last_logon_date >= TRUNC(sysdate)-2)

ORDER BY pap.full_name

hr_operating_units table data in oracle apps is not coming in windows 10-Oracle Views return no data due to NLS LANGUAGE Settings

I faced some issues when I query the below table data is not fetching in my query:
SELECT * FROM hr_operating_units
SELECT * FROM MTL_CATEGORIES

Solution:

You just need to check the USER ENV Language from the below query.

SELECT USERENV('LANGUAGE') Language FROM DUAL;

After that run the below query(alter language)
ALTER session SET nls_language='AMERICAN'

then check in your table query output:

ex: SELECT * FROM hr_operating_units

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

SELECT USERENV('LANG') FROM DUAL;

                         USERENV(‘LANG’)

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

                         S

     

SELECT * FROM V$NLS_PARAMETERS

where parameter in('NLS_LANGUAGE','NLS_TERRITORY');   

PARAMETER                   VALUE

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

NLS_LANGUAGE SWEDISH

NLS_TERRITORY   SWEDEN

            

b.      Set NLS_LANG value in client (This is permanent Solution)

Windows:

                                                  i.      Go to Start-> run

                                                ii.      Type regedit and click ok

                                              iii.      Drill Down to HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE-> KEY_OraClientxxx_homeX (xxx is the oracle client version and X is the currently used home)

                                              iv.      Double click NLS_LANG and change Value data. For example in the example scenario updated NLS_LANG value to SWEDISH_SWEDEN.WE8MSWIN1252

                                                v.      Close regedit

Make sure you have backup windows registry before modifying it.     

Or

                                                      i.      Click on Computer, select Properties.

                                                      ii.      Select Advance system settings

                                                     iii.      In the Advance tab, select Environment Variables

                                                      iv.      Select New

                                                        v.      Set variable name NLS_LANG and variable value SWEDISH_SWEDEN.WE8MSWIN1252

                                                       vi.      Select ok and you should now see the new environment variable that you just created.

 Linux:

 setenv NLS_LANG <NLS_LANG>

Example: setenv NLS_LANG SWEDISH_SWEDEN. WE8MSWIN1252

3.       Restart the client. Examples, if you are using Toad restart TOAD for the changes to take effect.

http://www.nazmulhuda.info/setting-nls_lang-environment-variable-for-windows-and-unix-for-oracle-database

TABLE REGISTRATION IN ORACLE APPLICATION WITH EXAMPLE

DECLARE
   vc_appl_short_name   CONSTANT VARCHAR2 (40) := 'XXU';
   vc_tab_name          CONSTANT VARCHAR2 (32) := 'XXU_JOBWORK_LINES';
   vc_tab_type          CONSTANT VARCHAR2 (50) := 'T';
   vc_next_extent       CONSTANT NUMBER        := 512;
   vc_pct_free          CONSTANT NUMBER        := 10;
   vc_pct_used          CONSTANT NUMBER        := 70;
BEGIN   -- Start Register Custom Table
   -- Get the table details in cursor
   FOR table_detail IN (SELECT table_name, tablespace_name, pct_free, pct_used,
                              ini_trans, max_trans, initial_extent,
                              next_extent
                          FROM dba_tables
                         WHERE table_name = vc_tab_name)
   LOOP
      -- Call the API to register table
      ad_dd.register_table (p_appl_short_name => vc_appl_short_name,
                            p_tab_name        => table_detail.table_name,
                            p_tab_type        => vc_tab_type,
                            p_next_extent     => NVL(table_detail.next_extent, vc_next_extent),
                            p_pct_free        => NVL(table_detail.pct_free, vc_pct_free),
                            p_pct_used        => NVL(table_detail.pct_used, vc_pct_used)
                           );
   END LOOP; -- End Register Custom Table

   -- Start Register Columns
   -- Get the column details of the table in cursor
   FOR table_columns IN (SELECT column_name, column_id, data_type, data_length,
                               nullable
                          FROM all_tab_columns
                         WHERE table_name = vc_tab_name)
   LOOP
      -- Call the API to register column
      ad_dd.register_column (p_appl_short_name      => vc_appl_short_name,
                             p_tab_name             => vc_tab_name,
                             p_col_name             => table_columns.column_name,
                             p_col_seq              => table_columns.column_id,
                             p_col_type             => table_columns.data_type,
                             p_col_width            => table_columns.data_length,
                             p_nullable             => table_columns.nullable,
                             p_translate            => 'N',
                             p_precision            => NULL,
                             p_scale                => NULL
                            );
   END LOOP;   -- End Register Columns
   -- Start Register Primary Key
   -- Get the primary key detail of the table in cursor
   FOR all_keys IN (SELECT constraint_name, table_name, constraint_type
                      FROM all_constraints
                     WHERE constraint_type = 'P' AND table_name = vc_tab_name)
   LOOP
      -- Call the API to register primary_key
      ad_dd.register_primary_key (p_appl_short_name      => vc_appl_short_name,
                                  p_key_name             => all_keys.constraint_name,
                                  p_tab_name             => all_keys.table_name,
                                  p_description          => 'Register primary key',
                                  p_key_type             => 'S',
                                  p_audit_flag           => 'Y',
                                  p_enabled_flag         => 'Y'
                                 );
      -- Start Register Primary Key Column
      -- Get the primary key column detial in cursor
      FOR all_columns IN (SELECT column_name, POSITION
                            FROM dba_cons_columns
                           WHERE table_name = all_keys.table_name
                             AND constraint_name = all_keys.constraint_name)
      LOOP
         -- Call the API to register primary_key_column
         ad_dd.register_primary_key_column
                                     (p_appl_short_name      => vc_appl_short_name,
                                      p_key_name             => all_keys.constraint_name,
                                      p_tab_name             => all_keys.table_name,
                                      p_col_name             => all_columns.column_name,
                                      p_col_sequence         => all_columns.POSITION
                                     );
      END LOOP; -- End Register Primary Key Column
   END LOOP;    -- End Register Primary Key

   COMMIT;
END;

What is the difference between 11i and R12 in Oracle Apps

11i
1) Consist of 3C
2) No concept of MOAC.
3) Major table changes like po_vendors.
4) Banks are created in Account Payables.
5) Ap_Invoices_lines_all table in finance is not there.

R12
1) Consist of 4C
2) Introduction of MOAC( Multi org access control)
3) Major table changes (Ap_suppliers)
4) Banks are created in Cash Management.
5) Subledger Accounting is not there.
5) Ap_invoices_lines_all is there.
6) Subledger Accounting is there.

How to know what are the Seeded table,Transactional Tables, Static Data Tables in Oracle Applications

You can tell based on the TABLESPACE the table belongs to - this is the new Oracle Applications Tablespace Model (OATM)

Run the following code to see:

SELECT tablespace_name, table_name
FROM all_tables
WHERE tablespace_name LIKE '%SEED%' -- seeded data
AND table_name LIKE 'FND%'

SELECT tablespace_name, table_name
FROM all_tables
WHERE tablespace_name LIKE '%TX%' -- transaction data
AND table_name LIKE 'FND%'

SELECT tablespace_name, table_name
FROM all_tables
WHERE tablespace_name LIKE '%ARCHIVE%' -- static data
AND table_name LIKE 'FND%'

What are the Aggregate Functions in Oracle SQL

Aggregate Functions

MIN	returns the smallest value in a given column
MAX	returns the largest value in a given column
SUM	returns the sum of the numeric values in a given column
AVG	returns the average value of a given column
COUNT	returns the total number of values in a given column
COUNT(*)	returns the number of rows in a table

Aggregate functions are used to compute against a "returned column of numeric data" from your SELECT statement. They basically summarize the results of a particular column of selected data.

What is the difference between Lookup and Value Set

A "lookup type" consists of lookups that are static values in a list of values. Lookup code validation is a one to one match.

A table-validated "value set" may consist of values that are validated through a SQL statement, which allows the list of values to be dynamic.

Tip

You can define a table-validated value set on any table, including the lookups table. Thus, you can change a lookup type into a table-validated value set that can be used in flexfields.

Area of Difference	Lookup Type	Value Set
List of values	Static	Dynamic if the list is table-validated
Validation of values	One to one match of meaning to code included in a lookup view, or through the determinant of a reference data set	Validation by format or inclusion in a table
Format type of values	char	varchar2, number, and so on
Length of value	Text string up to 30 characters	Any type of variable length from 1 to 4000
Duplication of values	Never. Values are unique.	Duplicate values allowed
Management	Both administrators and end-users manage these, except system lookups or predefined lookups at the system customization level, which can't be modified.	Usually administrators maintain these, except some product "flexfield" codes, such as GL for Oracle Fusion General Ledger that the end-users maintain.

Both lookup types and value sets are used to create lists of values from which users select values.

A lookup type cannot use a value from a value set. However, value sets can use standard, common, or "set-enabled"lookups.

AP_SUPPLIERS , AR_CUSTOMERS DETAILS QUERY IN ORACLE APPS R12

AP_SUPPLIERS MASTER DETAILS QUERY:-
*****************************************

select  a.SEGMENT1 vendor_code,    
       b.VENDOR_ID,
       a.VENDOR_NAME,
       b.VENDOR_SITE_ID,
       (select organization_id
         from hr_operating_units
        where organization_id=b.ORG_ID
       )ou_id,
        (select name
         from hr_operating_units
        where organization_id=b.ORG_ID
       )operating_name,
       b.VENDOR_SITE_CODE,
       (b.ADDRESS_LINE1||b.ADDRESS_LINE2||b.ADDRESS_LINE3||b.CITY||b.STATE||b.ZIP) address,
       null GST_REGISTRATION_NO,    
       c.PAN_NO,
       b.state
  from ap_suppliers a,
     ap_supplier_sites_All b,
     JAI_AP_TDS_VENDOR_HDRS c,
     JAI_CMN_VENDOR_SITES d
where a.vendor_id=b.vendor_id
  and b.VENDOR_ID=c.vendor_id(+)
  and b.VENDOR_SITE_ID=c.VENDOR_SITE_ID(+)
  and b.VENDOR_ID=d.vendor_id(+)
  and b.VENDOR_SITE_ID=d.VENDOR_SITE_ID(+)
   AND (NVL(A.ENABLED_FLAG,'N')='Y' OR NVL(D.INACTIVE_FLAG,'N') <> 'N')
  and b.INACTIVE_DATE is  null
  and a.end_date_active is null
  AND A.SEGMENT1 = 'PRMAAA1'
 order by a.VENDOR_NAME,b.VENDOR_SITE_CODE,b.ORG_ID

AR_CUSTOMERS MASTER DETAILS QUERY:-
**********************************************

select hp.party_number customer_number,
       hca.cust_account_id customer_id,
       hp.party_name customer_name,
       hca.account_number customer_account_number,
       hcas.org_id ou_id,
       (select name from hr_operating_units where organization_id = hcas.org_id) operating_unit_name,
       HCAS.PARTY_SITE_ID CUSTOMER_SITE_ID,
        HPS.PARTY_SITE_NUMBER CUSTOMER_SITE_NUMBER,
       (   HL.ADDRESS1
        || HL.ADDRESS2
        || HL.ADDRESS3
        || HL.ADDRESS4
        || HL.CITY
        || HL.POSTAL_CODE)
          ADDRESS,
       NULL GST_REGISTRATION_NO,
       jac.PAN_NO PAN_NO,
       hl.state,
       HP.PARTY_TYPE CUSTOMER_TYPE
from hz_cust_acct_sites_all hcas,
              hz_cust_accounts_all hca,
              hz_parties hp,
              hz_party_sites hps,
              hz_locations hl,
              JAI_CMN_CUS_ADDRESSES jac
where 1=1
--and hca.account_number = 'RDAAAG2'
and hca.cust_account_id = hcas.cust_account_id
and hca.party_id = hp.party_id
and hcas.party_site_id = hps.party_site_id
and hca.party_id = hps.party_id
and hps.location_id = hl.location_id
and jac.address_id(+) = hcas.CUST_ACCT_SITE_ID
order by customer_name

https://mattermost.siba.ai/siba-demo/channels/town-square

Chat with your Siba Bot via email! Simply put your request in the subject and send email to sibabot@sirvisetti.com and you will get a reply within 5 minutes. Try this: To: sibabot@sirvisetti.com Subject: weather new york Body can be anything or left blank. You should receive an email back from Siba Bot within 5 minutes. Of course, when you get your own Siba, you can use your own email such as sibabot@mycompany.com and also set the reply delay (5 min is default but can be changed as needed). Make sure to try it today and post feedback/comments! http://siba.ai

OPM - GME_BATCH_HEADER Statuses Information

select * from all_objects
where object_name like 'GME%BATCH%'
and object_type = 'TABLE'
------------------------------------------------------------

SELECT o.ORGANIZATION_CODE, o.ORGANIZATION_NAME, o.ORGANIZATION_ID,
gbh.BATCH_NO,decode(gbh.BATCH_STATUS,3,'Completed',4,'Closed',2,'WIP',
'Just Created') Batch_Status,to_char(gbh.creation_date,'DD-MON-YYYY HH24:MI:SS AM')
FROM GME_BATCH_HEADER gbh,
ORG_ORGANIZATION_DEFINITIONS o
WHERE CREATION_DATE >=TO_DATE('01/05/2015','dd-mm-yy')
AND gbh.ORGANIZATION_ID=o.ORGANIZATION_ID
ORDER BY gbh.creation_date desc

--------------------------------------------------------------
select * from gme_batch_header
where batch_no = '1710000426'

BATCH_STATUS = 2 (WIP-Work In Process)
BATCH_STATUS = 1 (Pending)
BATCH_STATUS = 3 (Completed)
BATCH_STATUS = 4 (Closed)
BATCH_STATUS = -1 (Cancelled)
-------------------------------------------------------------------

How to fetch/get/retrive last record from the table in oracle sql

Example :

select * from (
  select * from oe_order_headers_all
   order by creation_date desc
  )
 where rownum = 1;

India - Creditor Trial Balance Report in Query View

Main View:
APPS.XXCUSTOM_CREDITOR_TRIAL_V

Inner View:
APPS.XXCUSTOM_CREDITOR_TRIAL


CREATE OR REPLACE FORCE VIEW APPS.XXCUSTOM_CREDITOR_TRIAL_V
(DEBIT_AMOUNT_TOTAL,
CREDIT_AMOUNT_TOTAL,
SUPPLIER_NAME,
SUPPLIER_NUMBER,
SUPPLIER_ID,
ACCOUNTING_DATE,
ORG_ID
) AS
SELECT sum(DEBIT_AMOUNT_DR), sum(CREDIT_AMOUNT_CR),VENDOR_NAME,SEGMENT1,VENDOR_ID,ACCOUNTING_DATE,ORG_ID  FROM XXTEST_CREDITOR_BALANCES
GROUP BY VENDOR_NAME,SEGMENT1,VENDOR_ID,ACCOUNTING_DATE,ORG_ID


SELECT SUM(DEBIT_AMOUNT_TOTAL),SUM(CREDIT_AMOUNT_TOTAL) FROM XXTEST_CREDITOR_TRIAL
WHERE SUPPLIER_NUMBER = 'PUU1005'
AND ORG_ID = 102


CREATE OR REPLACE FORCE VIEW APPS.XXCUSTOM_CREDITOR_TRIAL_V
(VENDOR_SITE_CODE,
VENDOR_ID ,
INVOICE_TYPE_LOOKUP_CODE ,
INVOICE_NUM ,
INVOICE_DATE,
INVOICE_DESCRIPTION ,
CCID ,
INVOICE_CURRENCY_CODE ,
EXCHANGE_RATE ,
DEBIT_AMOUNT_DR ,
CREDIT_AMOUNT_CR ,
ACCT_DR ,
ACCT_CR ,
PAYMENT_NUM ,
PAY_ACCOUNTING_DATE ,
CHECK_NUMBER ,
SEGMENT1 ,
VENDOR_NAME ,
VENDOR_TYPE_LOOKUP_CODE ,
PO_DISTRIBUTION_ID ,
EXCHANGE_RATE_TYPE ,
ORG_ID ,
BATCH_ID ,
EXCHANGE_DATE ,
INVOICE_ID ,
ACCOUNTING_DATE ,
VOUCHER_NUM ,
LIABILITY_ACC ,
LIABILITY_DESC ,
SRT ) AS
SELECT povs.vendor_site_code,
                 pov.Vendor_id,
                 api.invoice_type_lookup_code,
                 api.invoice_num,
                 api.invoice_date,
                 api.description description,
                 apd.dist_code_combination_id ccid,
                 api.invoice_currency_code,
                 api.exchange_rate,
                 /*Decode(api.invoice_type_lookup_code,
                        'CREDIT',
                        abs(z.amt_val),
                        0) DR_VAL,
                 Decode(api.invoice_type_lookup_code, 'CREDIT', 0, z.amt_val) CR_VAL, */
                 --Removed discount amount taken from CR and DR for bug#7689858
                 DECODE (api.invoice_type_lookup_code,
                         'CREDIT', ABS (z.amt_val),
                         0, api.invoice_type_lookup_code,
                         'DEBIT', ABS (z.amt_val),
                         0)
                    DR_VAL,
                 DECODE (api.invoice_type_lookup_code,
                         'CREDIT', 0,
                         z.amt_val, api.invoice_type_lookup_code,
                         'DEBIT', 0,
                         z.amt_val)
                    CR_VAL,
                 0 acct_dr,
                 0 acct_cr,
                 NULL payment_num,
                 TO_CHAR (apd.accounting_date, 'dd-MON-yyyy')
                    pay_accounting_date,
                 NULL check_number,
                 pov.segment1,
                 pov.vendor_name,
                 pov.vendor_type_lookup_code,
                 apd.po_distribution_id,
                 api.exchange_rate_type,
                 api.org_id,
                 api.batch_id,
                 api.exchange_date,
                 api.invoice_id,
                 apd.accounting_date,
                 api.DOC_SEQUENCE_VALUE voucher_num,
                 (SELECT yy.SEGMENT3
                    FROM fnd_flex_values_vl xx, GL_CODE_COMBINATIONS_KFV yy
                   WHERE     xx.FLEX_VALUE = yy.SEGMENT3
                         AND yy.CODE_COMBINATION_ID =
                                api.ACCTS_PAY_CODE_COMBINATION_ID
                         AND ROWNUM <= 1)
                    LIABILITY_ACC,
                 (SELECT xx.DESCRIPTION
                    FROM fnd_flex_values_vl xx, GL_CODE_COMBINATIONS_KFV yy
                   WHERE     xx.FLEX_VALUE = yy.SEGMENT3
                         AND yy.CODE_COMBINATION_ID =
                                api.ACCTS_PAY_CODE_COMBINATION_ID
                         AND ROWNUM <= 1)
                    LIABILITY_DESC,
                 TO_NUMBER (TO_CHAR (apd.accounting_date, 'YYYYMMDD')) srt
            FROM ap_invoices_all api,
                 ap_invoice_lines_all apil, /* Added by Ramananda for bug#4454818  */
                 ap_invoice_distributions_all apd,
                 po_vendors pov,
                 po_vendor_sites_all povs,
                 (  SELECT NVL (SUM (apd.amount), 0) amt_val, api.invoice_id
                      FROM ap_invoices_all api,
                           ap_invoice_lines_all apil, /* Added by Ramananda for bug#4454818  */
                           ap_invoice_distributions_all apd,
                           po_vendors pov,
                           po_vendor_sites_all povs
                     WHERE     api.invoice_id = apd.invoice_id
                           AND apil.invoice_id = api.invoice_id /* Added by Ramananda for bug#4454818, start*/
                           AND apil.line_number = apd.invoice_line_number /* Added by Ramananda for bug#4454818, end*/
                           AND api.vendor_id = pov.vendor_id
--                           AND (   api.vendor_id = :p_vendor_id
--                                OR :p_vendor_id IS NULL)
--                           AND (   NVL (pov.Vendor_Type_Lookup_Code, 'NULL') =
--                                      :P_Vendor_Type_Lookup_Code
--                                OR :P_Vendor_Type_Lookup_Code IS NULL) /*Added by nprashar for bug # 7207441*/
                           /*AND     pov.vendor_type_lookup_code = NVL(:p_vendor_type_lookup_code, pov.vendor_type_lookup_code) Commented by nprashar for bug # 7154601*/
                           AND api.invoice_type_lookup_code <> 'PREPAYMENT'
--                           AND (api.org_id = :p_org_id OR api.org_id IS NULL)
                           AND api.vendor_site_id = povs.vendor_site_id
--                           AND (   api.vendor_site_id = :p_vendor_site_id
--                                OR :p_vendor_site_id IS NULL)
--                           AND TRUNC (apd.accounting_date) BETWEEN :p_from_date
--                                                               AND :p_to_date
                           AND apd.match_status_flag = 'A'
                           AND apil.line_type_lookup_code <> 'PREPAY'
                  GROUP BY api.invoice_id) z
           WHERE     api.invoice_id = z.invoice_id
                 AND api.invoice_id = apd.invoice_id
                 AND apil.invoice_id = api.invoice_id /* Added by Ramananda for bug#4454818, start*/
                 AND apil.line_number = apd.invoice_line_number /* Added by Ramananda for bug#4454818, end*/
                 AND apd.ROWID =
                        (SELECT ROWID
                           FROM ap_invoice_distributions_all
                          WHERE     ROWNUM = 1
                                AND invoice_id = apd.invoice_id
--                                AND TRUNC (accounting_date) BETWEEN :p_from_date
--                                                                AND :p_to_date
                                AND match_status_flag = 'A')
                 AND api.vendor_id = pov.vendor_id
--                 AND (api.vendor_id = :p_vendor_id OR :p_vendor_id IS NULL)
--                 AND (   NVL (pov.Vendor_Type_Lookup_Code, 'NULL') =
--                            :P_Vendor_Type_Lookup_Code
--                      OR :P_Vendor_Type_Lookup_Code IS NULL) /*Added by nprashar for bug # 7207441*/
                 /*AND     pov.vendor_type_lookup_code = NVL(:p_vendor_type_lookup_code, pov.vendor_type_lookup_code) Commented by nprashar for bug # 7154601*/
                  -- on 22-07-01 comments removed on 07-12-2001 to print the report for all the vendor types
                 AND api.invoice_type_lookup_code <> 'PREPAYMENT'
                 AND apd.match_status_flag = 'A'
--                 AND api.org_id = :p_org_id
                 --and 1<>1
                 AND api.vendor_site_id = povs.vendor_site_id
--                 AND (   api.vendor_site_id = :p_vendor_site_id
--                      OR :p_vendor_site_id IS NULL)
                 AND (   (api.invoice_type_lookup_code <> 'DEBIT')
                      OR (    (api.invoice_type_lookup_code = 'DEBIT')
                          AND --or
                              (NOT EXISTS
                                      (SELECT '1'
                                         FROM ap_invoice_payments_all app,
                                              ap_checks_all apc
                                        WHERE     app.check_id = apc.check_id
                                              AND app.invoice_id =
                                                     api.invoice_id
                                              AND apc.payment_type_flag = 'R'))))
UNION ALL
SELECT povs.vendor_site_code,
                 pov.Vendor_id,
                 CASE
                    WHEN api.invoice_type_lookup_code = 'PREPAYMENT'
                    THEN
                       'PREPAYMENT'
                    ELSE
                       'PAYMENT'
                 END
                    invoice_type_lookup_code,
                 CASE
                    WHEN api.invoice_type_lookup_code = 'PREPAYMENT'
                    THEN
                       api.invoice_num
                    ELSE
                       NULL
                 END
                    invoice_num,
                 CASE
                    WHEN api.invoice_type_lookup_code = 'PREPAYMENT'
                    THEN
                       API.INVOICE_DATE
                    ELSE
                       APC.CHECK_DATE
                 END
                    INVOICE_DATE,
                 app.description                           /*APC.description*/
                                                           /*apd.description*/
                                description, --By nprashar for bug 8307469 added by sridhar
                 app.accts_pay_code_combination_id ccid,
                 api.payment_currency_code,
                 --    api.invoice_type_lookup_code,
                 apc.exchange_rate,
                 CASE
                    WHEN REVERSAL_INV_PMT_ID IS NOT NULL
                    THEN
                       DECODE (
                          api.invoice_type_lookup_code,
                          'CREDIT', DECODE (status_lookup_code,
                                            'VOIDED', app.amount,
                                            ABS (app.amount)),
                          0)
                    ELSE
                       DECODE (
                          api.invoice_type_lookup_code,
                          'CREDIT', DECODE (status_lookup_code, 'VOIDED', 0, 0),
                          app.amount)
                 END
                    dr_val,
                 CASE
                    WHEN REVERSAL_INV_PMT_ID IS NOT NULL
                    THEN
                       DECODE (
                          api.invoice_type_lookup_code,
                          'CREDIT', DECODE (status_lookup_code, 'VOIDED', 0, 0),
                          app.amount)
                    ELSE
                       DECODE (
                          api.invoice_type_lookup_code,
                          'CREDIT', DECODE (status_lookup_code,
                                            'VOIDED', app.amount,
                                            ABS (app.amount)),
                          0)
                 END
                    cr_val, --Added discount amount taken in cr and dr for bug#7889858
                 0 acct_dr,
                 0 acct_cr,
                 DECODE (api.payment_status_flag,
                         'Y', TO_CHAR (apc.doc_sequence_value),
                         'P', TO_CHAR (apc.doc_sequence_value),
                         TO_CHAR (apc.doc_sequence_value), 'N',
                         NULL)
                    payment_num,
                 DECODE (api.payment_status_flag,
                         'Y', TO_CHAR (app.accounting_date, 'dd-MON-yyyy'),
                         'P', TO_CHAR (app.accounting_date, 'dd-MON-yyyy'))
                    pay_accounting_date,
                 DECODE (api.payment_status_flag,
                         'Y', TO_CHAR (apc.check_number),
                         'P', TO_CHAR (apc.check_number))
                    check_number,
                 pov.segment1,
                 pov.vendor_name,
                 pov.vendor_type_lookup_code,
                 apil.po_distribution_id            /*apd.po_distribution_id*/
                                        ,        --By nprashar for bug 8307469
                 apc.exchange_rate_type,
                 api.org_id,
                 api.batch_id,
                 apc.exchange_date,
                 api.invoice_id,
                 app.accounting_date,
                 CASE
                    WHEN invoice_type_lookup_code = 'PREPAYMENT'
                    THEN
                       apI.DOC_SEQUENCE_VALUE
                    ELSE
                       apC.DOC_SEQUENCE_VALUE
                 END
                    voucher_num,
                 (SELECT yy.SEGMENT3
                    FROM fnd_flex_values_vl xx, GL_CODE_COMBINATIONS_KFV yy
                   WHERE     xx.FLEX_VALUE = yy.SEGMENT3
                         AND yy.CODE_COMBINATION_ID =
                                api.ACCTS_PAY_CODE_COMBINATION_ID
                         AND ROWNUM <= 1)
                    LIABILITY_ACC,
                 (SELECT xx.DESCRIPTION
                    FROM fnd_flex_values_vl xx, GL_CODE_COMBINATIONS_KFV yy
                   WHERE     xx.FLEX_VALUE = yy.SEGMENT3
                         AND yy.CODE_COMBINATION_ID =
                                api.ACCTS_PAY_CODE_COMBINATION_ID
                         AND ROWNUM <= 1)
                    LIABILITY_DESC,
                 TO_NUMBER (TO_CHAR (app.accounting_date, 'YYYYMMDD')) srt
            FROM ap_invoices_all api,
                 ap_invoice_lines_all apil, /* Added by Ramananda for bug#4454818  */
                 --ap_invoice_distributions_all apd, by nprashar for bug 8307469
                 po_vendors pov,
                 --ap_invoice_payments_all app,
                 ap_invoice_payments_v app,
                 ap_checks_all apc,
                 po_vendor_sites_all povs
           WHERE /*api.invoice_id = apd.invoice_id  By nprashar for bug 8307469
          and*/
                apil .invoice_id = api.invoice_id /* Added by Ramananda for bug#4454818, start*/
                 --and     apil.line_number = apd.invoice_line_number /* Added by Ramananda for bug#4454818, end*/ by nprashar for bug 8307469
                 --AND    apd.rowid = (select rowid from ap_invoice_distributions_all where rownum=1 and invoice_id=apd.invoice_id and match_status_flag='A') by nprashar for bug 8307469
                 AND api.vendor_id = pov.vendor_id
                 AND app.invoice_id = api.invoice_id
                 AND app.check_id = apc.check_id
--                 AND (api.vendor_id = :p_vendor_id OR :p_vendor_id IS NULL)
--                 AND TRUNC (app.accounting_date) BETWEEN :p_from_date
--                                                     AND :p_to_date
--                 AND api.org_id = :p_org_id
--                 AND (   NVL (pov.Vendor_Type_Lookup_Code, 'NULL') =
--                            :P_Vendor_Type_Lookup_Code
--                      OR :P_Vendor_Type_Lookup_Code IS NULL) /*Added by nprashar for bug # 7207441*/
                 AND apc.status_lookup_code IN
                        ('CLEARED',
                         'NEGOTIABLE',
                         'VOIDED',
                         'RECONCILED UNACCOUNTED',
                         'RECONCILED',
                         'CLEARED BUT UNACCOUNTED')
                 --AND    apd.match_status_flag='A'
                 AND api.vendor_site_id = povs.vendor_site_id
                 --and apc.check_id=276647
--                 AND (   api.vendor_site_id = :p_vendor_site_id
--                      OR :p_vendor_site_id IS NULL)
                 AND EXISTS
                        (SELECT '1'
                           FROM ap_invoice_distributions_all apd
                          WHERE     apd.invoice_id = api.invoice_id
                                AND apd.match_status_flag = 'A'
                                AND apil.line_number = apd.invoice_line_number
                                AND NVL (apil.po_distribution_id, -999) =
                                       NVL (apd.po_distribution_id, -999) --Condition changed  by nprashar for bug 8307469
                                AND apd.ROWID =
                                       (SELECT ROWID
                                          FROM ap_invoice_distributions_all
                                         WHERE     ROWNUM = 1
                                               AND invoice_id = apd.invoice_id))