ORACLE EBS PL/SQL

Monday, 1 June 2015

AR Receipt Query

select --cr.RECEIPT_NUMBER,
       cr.RECEIPT_DATE R_DATE,
       cr.AMOUNT   R_AMOUNT,
       cr.CUSTOMER_RECEIPT_REFERENCE R_Check_No,
       ct.BILL_TO_CUSTOMER_ID R_CUSTOMER,
       CR.ORG_ID,
       ct.TRX_NUMBER
from AR_CASH_RECEIPTS_ALL           cr,
       ra_customer_trx_all            ct,
       ar_receivable_applications_all app,
       ra_terms                       rt
where /*cr.CUSTOMER_RECEIPT_REFERENCE is not null
   and */cr.STATUS = 'APP'
   and app.APPLIED_CUSTOMER_TRX_ID = ct.CUSTOMER_TRX_ID
   and app.CASH_RECEIPT_ID = cr.CASH_RECEIPT_ID
   and rt.term_id = ct.TERM_ID
--   and (upper(rt.name) = upper('CASH') OR (upper(rt.name) like 'PDC%'))
   and cr.ORG_ID = ct.ORG_ID
--and cr.ORG_ID           = 221
;

Monday, 25 May 2015

Remove Sunday Between To dates of a month

SELECT (TO_DATE(:TO_DATE) - (TO_DATE(:from_date)) + 1) -
(SELECT COUNT(days)
FROM (SELECT (TO_DATE(TO_CHAR(TO_DATE(:from_date), 'YYYYMMDD'),
'YYYYMMDD') + LEVEL - 1) days
FROM DUAL
CONNECT BY LEVEL <=
TO_NUMBER(TO_CHAR(TO_DATE(:TO_DATE), 'DD')))
WHERE TO_CHAR(days, 'DY') = 'SUN') AS total_working_days
FROM DUAL

Converting Numeric Value to Words In Oracle

This Function will convert a numeric value into Word..
--------------------------------------------------------

You need to pass the numberic valueas a Parameter..
and it will return a words

CREATE OR REPLACE FUNCTION APPS.num_to_word (val_in IN NUMBER)
RETURN VARCHAR2
IS
word VARCHAR2 (140);
char_in VARCHAR2 (5);
val_now NUMBER;
num NUMBER;
unit NUMBER;
pos NUMBER;
npos NUMBER;
nxt NUMBER;
both VARCHAR2 (1);
BEGIN
pos := 1;
val_now := val_in;
word := ' Only';
LOOP
EXIT WHEN nxt = 0;
IF pos = 1
THEN
nxt := TRUNC (val_now);
/* message(to_char(nxt),acknowledge);
pause; */
num := (val_now - nxt) * 100;
/* message(to_char(num),acknowledge);
pause; */
ELSIF ( (pos = 3) OR (pos = 7))
THEN
num := (val_now) MOD (10);
nxt := (val_now - num) / 10;
ELSE
num := (val_now) MOD (100);
nxt := (val_now - num) / 100;
END IF;
IF (num > 19)
THEN
unit := (num) MOD (10);
/* message('unit : '||to_char(unit),acknowledge);
pause; */
num := (num - unit) / 10;
/* message('num : '||to_char(num),acknowledge);
pause; */
ELSE
unit := num;
num := 0;
END IF;
npos := pos;
IF (nxt > 0)
THEN
pos := pos + 1;
val_now := nxt;
END IF;
IF ( (num != 0) AND (unit != 0))
THEN
both := '-';
ELSE
both := '';
END IF;
IF ( (num != 0) OR (unit != 0) OR (npos = 6))
THEN
IF npos = 1
THEN
WORD :=
return_tens (num)
|| both
|| return_unit (unit)
|| 'Paise '
|| word;
IF (nxt > 0)
THEN
WORD := 'And ' || WORD;
END IF;
ELSIF npos = 2
THEN
WORD := return_tens (num) || both || return_unit (unit) || word;
ELSIF npos = 3
THEN
WORD :=
return_tens (num)
|| both
|| return_unit (unit)
|| 'Hundred '
|| word;
ELSIF npos = 4
THEN
WORD :=
return_tens (num)
|| both
|| return_unit (unit)
|| 'Thousand '
|| word;
ELSIF npos = 5
THEN
WORD :=
return_tens (num)
|| both
|| return_unit (unit)
|| 'Lakh '
|| word;
ELSIF npos = 6
THEN
WORD :=
return_tens (num)
|| both
|| return_unit (unit)
|| 'Crore '
|| word;
ELSIF npos = 7
THEN
WORD :=
return_tens (num)
|| both
|| return_unit (unit)
|| 'Hundred '
|| word;
END IF;
END IF;
END LOOP;
IF npos > 1
THEN
word := word;
END IF;
RETURN (word);
END;

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

In the above function i have used two more functions
for ten and units

Function for Returning Tens
--------------------------------

CREATE OR REPLACE FUNCTION APPS.return_tens (val_in in number) RETURN VARCHAR2 AUTHID CURRENT_USER IS
char_in varchar2(5);
char_out varchar2(12);
BEGIN
char_in := to_char(val_in);
if char_in = '0'
then
char_out := '';
elsif char_in = '1'
then
char_out := '';
elsif char_in = '2'
then
char_out := 'Twenty';
elsif char_in = '3'
then
char_out := 'Thirty';
elsif char_in = '4'
then
char_out := 'Forty';
elsif char_in = '5'
then
char_out := 'Fifty';
elsif char_in = '6'
then
char_out := 'Sixty';
elsif char_in = '7'
then
char_out := 'Seventy';
elsif char_in = '8'
then
char_out := 'Eighty';
elsif char_in = '9'
then
char_out := 'Ninety';
else
char_out := 'none';
end if;
return (char_out);
END;

The Fucntion for Returing Units
----------------------------------------

CREATE OR REPLACE function APPS.return_unit (val_in in number) RETURN varchar2 AUTHID CURRENT_USER IS
char_in varchar2(5);
char_out varchar2(12);
BEGIN
char_in := to_char(val_in);
if char_in = '0'
then
char_out := ' ';
elsif char_in = '1'
then
char_out := 'One ';
elsif char_in = '2'
then
char_out := 'Two ';
elsif char_in = '3'
then
char_out := 'Three ';
elsif char_in = '4'
then
char_out := 'Four ';
elsif char_in = '5'
then
char_out := 'Five ';
elsif char_in = '6'
then
char_out := 'Six ';
elsif char_in = '7'
then
char_out := 'Seven ';
elsif char_in = '8'
then
char_out := 'Eight ';
elsif char_in = '9'
then
char_out := 'Nine ';
elsif char_in = '10'
then
char_out := 'Ten ';
elsif char_in = '11'
then
char_out := 'Eleven ';
elsif char_in = '12'
then
char_out := 'Twelve ';
elsif char_in = '13'
then
char_out := 'Thirteen ';
elsif char_in = '14'
then
char_out := 'Fourteen ';
elsif char_in = '15'
then
char_out := 'Fifteen ';
elsif char_in = '16'
then
char_out := 'Sixteen ';
elsif char_in = '17'
then
char_out := 'Seventeen ';
elsif char_in = '18'
then
char_out := 'Eighteen ';
elsif char_in = '19'
then
char_out := 'Nineteen ';
else
char_out := 'none';
end if;
return (char_out);
END;

Delete XML Publisher Records of a Concurrent

SELECT *
FROM XDO_DS_DEFINITIONS_B
WHERE DATA_SOURCE_CODE = 'XX_DATA_DEF_CODE';

SELECT *
FROM XDO_DS_DEFINITIONS_TL
WHERE DATA_SOURCE_CODE = 'XX_DATA_DEF_CODE';

SELECT *
FROM XDO_LOBS
WHERE LOB_CODE = 'XX_DATA_DEF_CODE';

SELECT *
FROM XDO_CONFIG_VALUES
WHERE DATA_SOURCE_CODE = 'XX_DATA_DEF_CODE';

Delete the Data Definitions:

-- API to delete Data Definition from XDO_DS_DEFINITIONS_B and XDO_DS_DEFINITIONS_TL table
BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW (,);
COMMIT;
END;

-- Delete Data Templates, xml schema etc. from XDO_LOBS table (There is no API)
DELETE FROM XDO_LOBS
WHERE LOB_CODE =
AND APPLICATION_SHORT_NAME =
AND LOB_TYPE IN
('XML_SCHEMA',
'DATA_TEMPLATE',
'XML_SAMPLE',
'BURSTING_FILE');

-- Delete from XDO_CONFIG_VALUES (if required)
DELETE FROM XDO_CONFIG_VALUES
WHERE APPLICATION_SHORT_NAME =
AND DATA_SOURCE_CODE = ;

Queries for the Templates:

SELECT *
FROM XDO_TEMPLATES_B
WHERE TEMPLATE_CODE = 'XX_TEMPLATE_CODE';

SELECT *
FROM XDO_TEMPLATES_TL
WHERE TEMPLATE_CODE = 'XX_TEMPLATE_CODE';

SELECT *
FROM XDO_LOBS
WHERE LOB_CODE = 'XX_TEMPLATE_CODE';

SELECT *
FROM XDO_CONFIG_VALUES
WHERE TEMPLATE_CODE = 'XX_TEMPLATE_CODE';

Delete the templates:

-- API to delete Data Definition from XDO_TEMPLATES_B and XDO_TEMPLATES_TL table
BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW (, );
COMMIT;
END;

-- Delete the Templates from XDO_LOBS table (There is no API)
DELETE FROM XDO_LOBS
WHERE LOB_CODE =
AND APPLICATION_SHORT_NAME =
AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

-- Delete from XDO_CONFIG_VALUES (if required)
DELETE FROM XDO_CONFIG_VALUES
WHERE APPLICATION_SHORT_NAME =
AND TEMPLATE_CODE =
AND DATA_SOURCE_CODE = ;

Query's Related to Concurrent and request group

SELECT DISTINCT
FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
FRG.APPLICATION_ID =FAPP.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE 'XXXX%'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US'

Find all responsibilities and concurrent program names based on the executable Name

select --r.rowid rrowid,p.rowid prowid,e.rowid erowid,fcp.rowid frowid,
e.executable_name,responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u,
fnd_executables e,
fnd_concurrent_programs fcp
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and executable_name like '%SEARCH EXECUTABLE PATTERN%'
and fcp.executable_id = e.executable_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
and fcp.concurrent_program_id = p.concurrent_program_id

Find concurrent programs attached to certain responsibility.
select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'YOUR RESPONSIBILITY KEY')
))

Find all responsibilities having the "search concurrent program" attached to the request group
------->
select responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
---------------<

Find concurrent Program Names based on Responsibility Key Value
select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'Your Responsibility Key Search Pattern')
))

SELECT a.application_name || '---->' ||
b.request_group_name || '---->' || b.DESCRIPTION
FROM fnd_application_vl a, fnd_request_groups b, fnd_request_group_units c ,fnd_concurrent_programs_vl d
WHERE c.request_group_id = b.request_group_id
AND c.request_unit_id = d.concurrent_program_id
AND c.application_id = a.application_id
and d.user_concurrent_program_name = 'Sona Enable Disable Users'

Finding Responsibility of a Concurrent

select resp.responsibility_name
from fnd_responsibility_vl resp fnd_request_group_units rgu fnd_concurrent_programs_vl prog
where resp.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = prog.concurrent_program_id
AND rgu.unit_application_id = prog.application_id
AND prog.user_concurrent_program_name = '.................'

Supplier Ledger Query in R12

The Below query is for supplier ledger in r12..
It will give you detail of invoices made against a particular supplier ...
weather the amountis credited or debited ..
and payment made through check details will also we can get thourgh the query..

/* Formatted on 1/21/2011 4:12:53 PM (QP5 v5.115.810.9015) */
(SELECT AIDA.accounting_date GL_DATE,
b.vendor_name,
a.invoice_num INVNO,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END)
),
0
)
DR_Amount,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END)
),
0
)
CR_Amount,
SUBSTR (a.description, 1, 40) NARRATION,
NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
NVL (a.invoice_type_lookup_code, '-') TYPE,
NVL (d.batch_name, '--') BATCH,
a.org_id,
a.SOURCE SOURCE,
gcc.segment4 Main_code,
b.segment1 Supplier_code,
c.VENDOR_SITE_CODE,
a.invoice_id
---------- NULL ledger_id,apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
-------- apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
--------- A.INVOICE_ID,
------- A.INVOICE_AMOUNT,
--------- A.PAYMENT_STATUS_FLAG,
-------- A.INVOICE_TYPE_LOOKUP_CODE
----- ) INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all c,
apps.AP_BATCHES_ALL d,
apps.ap_invoice_lines_all aila,
apps.ap_invoice_distributions_all aida,
--apps.ap_invoice_distributions_all aida,
apps.gl_code_combinations gcc
WHERE a.vendor_id = b.vendor_id
AND a.vendor_id = c.vendor_id
AND a.batch_id = d.batch_id(+)
AND a.invoice_id = aila.invoice_id
AND a.invoice_id = aida.invoice_id
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
---and aida.INVOICE_LINE_NUMBER =1
AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
--and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) NOT IN
('NEVER APPROVED'))
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) NOT IN
('CANCELLED')
AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
AND a.org_id = :org_id
AND b.vendor_id = :supplier
-- and a.invoice_id =3085480
GROUP BY a.invoice_num,
SUBSTR (a.description, 1, 40),
a.DOC_SEQUENCE_VALUE,
a.invoice_type_lookup_code,
a.org_id,
INVOICE_AMOUNT,
a.SOURCE,
b.vendor_name,
b.segment1,a.PAYMENT_CURRENCY_CODE,
c.VENDOR_SITE_CODE,
d.batch_name,
a.invoice_id,
gcc.segment4,
AIDA.accounting_date,
APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
)
union all
SELECT AIDA.accounting_date GL_DATE,
b.vendor_name,
a.invoice_num INVNO,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END)
),
0
)
DR_Amount,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END)
),
0
)
CR_Amount,
SUBSTR (a.description, 1, 40) NARRATION,
NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
NVL (a.invoice_type_lookup_code, '-') TYPE,
NVL (d.batch_name, '--') BATCH,
a.org_id,
a.SOURCE SOURCE,
gcc.segment4 Main_code,
b.segment1 Supplier_code,
c.VENDOR_SITE_CODE,
a.invoice_id
---------- NULL ledger_id,apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
-------- apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
--------- A.INVOICE_ID,
------- A.INVOICE_AMOUNT,
--------- A.PAYMENT_STATUS_FLAG,
-------- A.INVOICE_TYPE_LOOKUP_CODE
----- ) INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all c,
apps.AP_BATCHES_ALL d,
apps.ap_invoice_lines_all aila,
apps.ap_invoice_distributions_all aida,
--apps.ap_invoice_distributions_all aida,
apps.gl_code_combinations gcc
WHERE a.vendor_id = b.vendor_id
AND a.vendor_id = c.vendor_id
AND a.batch_id = d.batch_id(+)
AND a.invoice_id = aila.invoice_id
AND a.invoice_id = aida.invoice_id
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
---and aida.INVOICE_LINE_NUMBER =1
AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
--and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) IN
('NEVER APPROVED'))
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) NOT IN
('CANCELLED')
AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
AND a.org_id = :org_id
AND b.vendor_id = :supplier
-- and a.invoice_id =3085480
GROUP BY a.invoice_num,
SUBSTR (a.description, 1, 40),
a.DOC_SEQUENCE_VALUE,
a.invoice_type_lookup_code,
a.org_id,
INVOICE_AMOUNT,
a.SOURCE,
b.vendor_name,
b.segment1,a.PAYMENT_CURRENCY_CODE,
c.VENDOR_SITE_CODE,
d.batch_name,
a.invoice_id,
gcc.segment4,
AIDA.accounting_date,
APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
)
UNION all
SELECT AIDA.accounting_date GL_DATE,
b.vendor_name,
a.invoice_num INVNO,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END)
),
0
)
DR_Amount,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END)
),
0
)
CR_Amount,
SUBSTR (a.description, 1, 40) NARRATION,
NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
NVL (a.invoice_type_lookup_code, '-') TYPE,
NVL (d.batch_name, '--') BATCH,
a.org_id,
a.SOURCE SOURCE,
gcc.segment4 Main_code,
b.segment1 Supplier_code,
c.VENDOR_SITE_CODE, a.invoice_id
-- NULL ledger_id,AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
-- apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
-- A.INVOICE_ID,
-- A.INVOICE_AMOUNT,
-- A.PAYMENT_STATUS_FLAG,
-- A.INVOICE_TYPE_LOOKUP_CODE
-- ) INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all c,
apps.AP_BATCHES_ALL d,
apps.ap_invoice_lines_all aila,
apps.ap_invoice_distributions_all aida,
--apps.ap_invoice_distributions_all aida,
apps.gl_code_combinations gcc
WHERE a.vendor_id = b.vendor_id
AND a.vendor_id = c.vendor_id
AND a.batch_id = d.batch_id(+)
AND a.invoice_id = aila.invoice_id
AND a.invoice_id = aida.invoice_id
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
---and aida.INVOICE_LINE_NUMBER =1
AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
--and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) NOT IN
('NEVER APPROVED'))
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) IN
('CANCELLED')
AND A.CANCELLED_DATE >= :to_date
AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
AND a.org_id = :org_id
AND b.vendor_id = :supplier
-- and a.invoice_id =3085480
GROUP BY a.invoice_num,
SUBSTR (a.description, 1, 40),
a.DOC_SEQUENCE_VALUE,
a.invoice_type_lookup_code,
a.org_id,
INVOICE_AMOUNT,
a.SOURCE,
b.vendor_name,
b.segment1,a.PAYMENT_CURRENCY_CODE,
c.VENDOR_SITE_CODE,
d.batch_name,
a.invoice_id,
gcc.segment4,
AIDA.accounting_date,
APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
)
UNION ALL
SELECT AIDA.accounting_date GL_DATE,
b.vendor_name,
a.invoice_num INVNO,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END)
),
0
)
DR_Amount,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END)
),
0
)
CR_Amount,
SUBSTR (a.description, 1, 40) NARRATION,
NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
NVL (a.invoice_type_lookup_code, '-') TYPE,
NVL (d.batch_name, '--') BATCH,
a.org_id,
a.SOURCE SOURCE,
gcc.segment4 Main_code,
b.segment1 Supplier_code,
c.VENDOR_SITE_CODE,
a.invoice_id
-- NULL ledger_id,APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
-- apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
-- A.INVOICE_ID,
-- A.INVOICE_AMOUNT,
-- A.PAYMENT_STATUS_FLAG,
-- A.INVOICE_TYPE_LOOKUP_CODE
-- ) INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all c,
apps.AP_BATCHES_ALL d,
apps.ap_invoice_lines_all aila,
apps.ap_invoice_distributions_all aida,
--apps.ap_invoice_distributions_all aida,
apps.gl_code_combinations gcc
WHERE a.vendor_id = b.vendor_id
AND a.vendor_id = c.vendor_id
AND a.batch_id = d.batch_id(+)
AND a.invoice_id = aila.invoice_id
AND a.invoice_id = aida.invoice_id
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
---and aida.INVOICE_LINE_NUMBER =1
AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
--and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) NOT IN
('NEVER APPROVED'))
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) IN
('CANCELLED')
AND A.CANCELLED_DATE <= :to_date
AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
AND a.org_id = :org_id
AND b.vendor_id = :supplier
-- and a.invoice_id =3085480
GROUP BY a.invoice_num,
SUBSTR (a.description, 1, 40),
a.DOC_SEQUENCE_VALUE,
a.invoice_type_lookup_code,
a.org_id,
INVOICE_AMOUNT,
a.SOURCE,
b.vendor_name,
b.segment1,
c.VENDOR_SITE_CODE,a.PAYMENT_CURRENCY_CODE,
d.batch_name,
a.invoice_id,
gcc.segment4,
AIDA.accounting_date,
APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) )
UNION
(SELECT c.ACCOUNTING_DATE GL_Date,
b.vendor_name,
a.invoice_num INVNO,
NVL (
SUM(NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT else c.amount end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT else c.amount end)
END)
)),
0
)
DR_Amount,
NVL (
SUM(NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT) else ABS (c.amount) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT) else ABS (c.amount) end)
END)
)),
0
)
CR_Amount,
TO_CHAR (d.CHECK_NUMBER) Narration,
d.DOC_SEQUENCE_VALUE Vr_no,
NVL (TO_CHAR (d.PAYMENT_METHOD_LOOKUP_CODE), 'CHECK') TYPE,
NULL Batch,
a.org_id,
NULL Source,
f.segment4 "account code",
b.segment1 Supplier_code,
e.VENDOR_SITE_CODE,
a.invoice_id
-- a.SET_OF_BOOKS_ID ledger_id,NULL POSTING_FLAG,NULL INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all e,
apps.ap_invoice_payments_all c,
apps.ap_checks_all d,
apps.gl_code_combinations f
WHERE a.vendor_id = b.vendor_id
AND a.org_id = c.org_id
AND e.VENDOR_SITE_ID = a.VENDOR_SITE_ID
AND a.vendor_id = e.vendor_id
AND a.invoice_id = c.invoice_id(+)
AND c.check_id = d.check_id(+)
AND c.org_id = d.org_id(+)
AND f.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND b.vendor_id = :supplier
-- and a.SET_OF_BOOKS_ID=1
--- and d.CHECK_NUMBER = 4951
AND d.status_lookup_code <> 'VOIDED'
AND a.org_id = :org_id
AND c.ACCOUNTING_DATE BETWEEN :fromdate AND :to_date
GROUP BY c.ACCOUNTING_DATE,
b.vendor_name,
a.invoice_num,
TO_CHAR (d.CHECK_NUMBER),
d.DOC_SEQUENCE_VALUE,
d.PAYMENT_METHOD_LOOKUP_CODE,
a.org_id,
f.segment4,
b.segment1,
e.VENDOR_SITE_CODE,
a.invoice_id,
a.SET_OF_BOOKS_ID,a.PAYMENT_CURRENCY_CODE
UNION all
SELECT c.ACCOUNTING_DATE GL_Date,
b.vendor_name,
a.invoice_num INVNO,
NVL (
SUM(NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT else c.amount end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT else c.amount end)
END)
)),
NULL
)
DR_Amount,
NVL (
SUM(NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT) else ABS (c.amount) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT) else ABS (c.amount) end)
END)
)),
NULL
)
CR_Amount,
TO_CHAR (d.CHECK_NUMBER) Narration,
d.DOC_SEQUENCE_VALUE Vr_no,
NVL (TO_CHAR (d.PAYMENT_METHOD_LOOKUP_CODE), 'CHECK') TYPE,
NULL Batch,
a.org_id,
NULL Source,
f.segment4 "account code",
b.segment1 Supplier_code,
e.VENDOR_SITE_CODE,
a.invoice_id
-- a.SET_OF_BOOKS_ID ledger_id,NULL POSTING_FLAG,NULL INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all e,
apps.ap_invoice_payments_all c,
apps.ap_checks_all d,
apps.gl_code_combinations f
WHERE a.vendor_id = b.vendor_id
AND a.org_id = c.org_id
AND e.VENDOR_SITE_ID = a.VENDOR_SITE_ID
AND a.vendor_id = e.vendor_id
AND a.invoice_id = c.invoice_id(+)
AND c.check_id = d.check_id(+)
AND c.org_id = d.org_id(+)
AND f.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND b.vendor_id =:supplier
-- and a.SET_OF_BOOKS_ID=1
--- and d.CHECK_NUMBER = 4951
AND d.status_lookup_code = 'VOIDED'
and c.ACCOUNTING_DATE!=d.void_date
AND d.void_date <= :to_date
AND a.org_id = :org_id
AND c.ACCOUNTING_DATE BETWEEN :fromdate AND :to_date
GROUP BY c.ACCOUNTING_DATE,
b.vendor_name,
a.invoice_num,
TO_CHAR (d.CHECK_NUMBER),
d.DOC_SEQUENCE_VALUE,
d.PAYMENT_METHOD_LOOKUP_CODE,a.PAYMENT_CURRENCY_CODE,
a.org_id,
f.segment4,
b.segment1,
e.VENDOR_SITE_CODE,
a.invoice_id,
a.SET_OF_BOOKS_ID
union all
SELECT c.ACCOUNTING_DATE GL_Date,
b.vendor_name,
a.invoice_num INVNO,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT)) else ABS (sum(c.amount)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT)) else ABS (sum(c.amount)) end)
END)
),
0
)
DR_Amount,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT)) else ABS (sum(c.amount)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT)) else ABS (sum(c.amount)) end)
END)
),
0
)
CR_Amount,
TO_CHAR (d.CHECK_NUMBER) Narration,
d.DOC_SEQUENCE_VALUE Vr_no,
NVL (TO_CHAR (d.PAYMENT_METHOD_LOOKUP_CODE), 'CHECK') TYPE,
NULL Batch,
a.org_id,
NULL Source,
f.segment4 "account code",
b.segment1 Supplier_code,
e.VENDOR_SITE_CODE,
a.invoice_id
-- a.SET_OF_BOOKS_ID ledger_id,NULL POSTING_FLAG,NULL INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all e,
apps.ap_invoice_payments_all c,
apps.ap_checks_all d,
apps.gl_code_combinations f
WHERE a.vendor_id = b.vendor_id
AND a.org_id = c.org_id
AND e.VENDOR_SITE_ID = a.VENDOR_SITE_ID
AND a.vendor_id = e.vendor_id
AND a.invoice_id = c.invoice_id(+)
AND c.check_id = d.check_id(+)
AND c.org_id = d.org_id(+)
AND f.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND b.vendor_id =:supplier
-- and a.SET_OF_BOOKS_ID=1
--- and d.CHECK_NUMBER = 4951
AND d.status_lookup_code = 'VOIDED'
and c.ACCOUNTING_DATE=d.void_date
-- AND d.void_date >= :to_date
AND a.org_id = :org_id
-- and a.invoice_id=2805181
AND c.ACCOUNTING_DATE BETWEEN :fromdate AND :to_date
GROUP BY c.ACCOUNTING_DATE,
b.vendor_name,
a.invoice_num,
TO_CHAR (d.CHECK_NUMBER),
d.DOC_SEQUENCE_VALUE,
d.PAYMENT_METHOD_LOOKUP_CODE,
a.org_id,
f.segment4,a.PAYMENT_CURRENCY_CODE,
b.segment1,
e.VENDOR_SITE_CODE,
a.invoice_id,
a.SET_OF_BOOKS_ID,a.INVOICE_TYPE_LOOKUP_CODE )
---- order by 2