Oracle Apps SQL query to Fetch AP invoice Rejection Reason in AP Interface
This sql query help you to get Ap invoice interface
rejection records. You can get complete details for the rejected records using
this sql query. It helps you to understand the invoice rejection reason in the
AP invoice interface and can work on this. We cannot get those details in
oracle apps but using this sql query we can get the rejected records with the
detailed reason for the rejection. This is quite useful in the Payables Invoice
interface.
SELECT PARENT_TABLE,
REJECT_LOOKUP_CODE, APIR.CREATION_DATE
FROM APPS.AP_INTERFACE_REJECTIONS APIR
WHERE PARENT_TABLE = 'AP_INVOICES_INTERFACE' AND
PARENT_ID IN
(SELECT INVOICE_ID
FROM APPS.AP_INVOICES_INTERFACE
WHERE INVOICE_NUM = :INVOICE_NUM)
UNION
SELECT PARENT_TABLE, REJECT_LOOKUP_CODE,
APIR.CREATION_DATE
FROM APPS.AP_INTERFACE_REJECTIONS APIR
WHERE PARENT_TABLE =
'AP_INVOICE_LINES_INTERFACE' AND PARENT_ID IN (
SELECT APILI.INVOICE_LINE_ID
FROM APPS.AP_INVOICES_INTERFACE APII,
APPS.AP_INVOICE_LINES_INTERFACE APILI WHERE INVOICE_NUM = :INVOICE_NUM
AND APILI.INVOICE_ID=APII.INVOICE_ID
)
ORDER BY CREATION_DATE DESC
How to Print MICR Font in Oracle
Check Printing Report
MICR
code is
a character-recognition technology used mainly by the banking industry to ease
the processing and clearance of checks and other documents.
The MICR
encoding, called the MICR Line, is at the bottom of checks and other vouchers
and typically includes the document-type indicator, Bank code, Bank
Account Number, check Number, check amount, and a control
indicator.
In oracle, we can
also print these MICR font directly in our check Printing Layouts without doing
lot of efforts to install MICR font in Printers
imply
you need to do is, first you need to register the MICR and E-13b font in Oracle
application and then you need to Create the Font Mapping but before this first
you need to download the MICR and E-13B font file from google and then upload
in Oracle application.
How to Print MICR Font in Oracle Check Printing Report
I
will show the complete steps in detail as below.
Step1:- Go to XML Publisher Administration
Responsibility and then go to Administration Tab and Then Font Files Tab.
Step2:-
Create new Fonts for MICR and E_13b and attach the font file MICR.ttf and
E-13b.ttf in these two fonts.
Step3:-
Now Create Font Mapping for the Fonts which you have created in the Step2.
Step4:- Now go
to your RTF Template and select the Text which you want to print in MICR Font
and then Select the E-13B font.
How to Print MICR Font in Oracle Check Printing Report
This below view will give you
all the child items using in oracle bill of material (BOM) again finished good
and sub-assembly.
CREATE OR REPLACE FORCE VIEW apps.xx_bom_tree
(lev,
component_quantity,
inventory_item_id,
use_item,
primary_uom_code,
comp_item
)
AS
SELECT DISTINCT LEVEL
"LEV", component_quantity, msib.inventory_item_id,
msib2.inventory_item_id use_item, msib2.primary_uom_code,
msib2.segment1
|| '.'
|| msib2.segment2
|| '.'
|| msib2.segment3
|| '.'
|| msib2.segment4
|| '.'
|| msib2.segment5 AS "COMP_ITEM"
/*bic.component_item_id,*/
/*msib.inventory_item_id,*/
/*msib2.inventory_item_id*/
FROM
bom.bom_components_b bic,
bom.bom_structures_b bom,
inv.mtl_system_items_b msib,
inv.mtl_system_items_b msib2
WHERE 1 = 1
AND bic.bill_sequence_id = bom.bill_sequence_id
AND bic.disable_date IS NULL
AND bom.assembly_item_id = msib.inventory_item_id
AND bom.organization_id = msib.organization_id
AND bic.component_item_id = msib2.inventory_item_id
AND bom.organization_id = msib2.organization_id
AND bom.organization_id = 85 /*
organization id here */
-- AND msib2.segment5 = 'YUU
AND bic.effectivity_date < SYSDATE
AND bom.alternate_bom_designator IS NULL
START
WITH msib.segment1
|| msib.segment2
|| msib.segment3
|| msib.segment4
|| msib.segment5 = 'ERRRRRR'
/*
top parent item here */
CONNECT BY NOCYCLE PRIOR
bic.component_item_id = msib.inventory_item_id
ORDER BY LEVEL;
SQL Query to Fetch Accounting
Flex Field segment Values with GL Account Description of all Segments
select gcc.code_combination_id,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||segment6||'.'||segment7||'.'||segment8
segments,
(SELECT description
FROM fnd_flex_values_tl
ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Company'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment1)||'.'||
(SELECT description
FROM fnd_flex_values_tl
ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'SBU'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment2)||'.'||
(SELECT description
FROM fnd_flex_values_tl
ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Location'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment3)||'.'||
(SELECT description
FROM fnd_flex_values_tl
ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Cost Center'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment4)||'.'||
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Account'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment5)||'.'||
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Intercompany'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment6)||'.'||
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Future
1'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment7)||'.'||
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Future
2'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment8) account_description
from gl_code_combinations gcc
SQL Query to Fetch EBS Taxes
with Operating Unit and GL Code in Oracle Apps r12
SELECT
hou.organization_id
org_id,
led.name
ledger,
hou.name
operating_unit,
--
zxr.tax_regime_code
tax_regime_code,
zxr.tax
tax_code,
zxr.tax_status_code
tax_status_code,
zxr.tax_rate_code
tax_rate_code,
zxr.tax_jurisdiction_code tax_jurisdiction_code,
--
zxr.rate_type_code
rate_type_code,
zxr.percentage_rate
percentage_rate,
zxr.effective_from
rate_effective_from,
zxr.effective_to
rate_effective_to,
--
acc.tax_account_ccid
tax_account_ccid,
gcc.concatenated_segments tax_account
FROM
zx_rates_vl
zxr,
zx_accounts
acc,
hr_operating_units
hou,
gl_ledgers
led,
gl_code_combinations_kfv gcc
WHERE
1=1
--
-- AND zxr.tax_regime_code = 'GR
VAT'
-- AND zxr.tax_rate_code =
'GR_AR_DOM'
--
AND acc.tax_account_entity_code =
'RATES'
AND zxr.active_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN
TRUNC (zxr.effective_from) AND
NVL (TRUNC (zxr.effective_to), TRUNC (SYSDATE) + 1)
--
AND led.ledger_id =
hou.set_of_books_id
AND gcc.code_combination_id =
acc.tax_account_ccid
AND hou.organization_id =
acc.internal_organization_id
and hou.organization_id=7890
AND acc.tax_account_entity_id =
zxr.tax_rate_id
SQL Query to
Fetch Oracle Customer Banking Details in Oracle apps R12
select DISTINCT
ac.customer_name
,ac.customer_number, bankacct.*
from iby_pmt_instr_uses_all
instr_assign,iby_external_payers_all payee,iby_ext_bank_accounts
bankacct,hz_cust_accounts_all hcal,ar_customers acwhere
instr_assign.instrument_id = bankacct.ext_bank_account_id
and instr_assign.ext_pmt_party_id =
payee.ext_payer_id
and instr_assign.instrument_type =
'BANKACCOUNT'and instr_assign.payment_flow = 'FUNDS_CAPTURE'and
instr_assign.payment_function = 'CUSTOMER_PAYMENT'and hcal.cust_account_id =
payee.cust_account_id
and ac.customer_number = hcal.account_number
and bankacct.bank_account_num = '4456677777' (Pass
Banks Account or leave blank);
SQL Query to
Fetch Oracle Apps User accounts with Responsibility Names in Oracle apps R12
For Specific Operating Unit
SELECT fu.user_name,
(SELECT FULL_NAME FROM PER_PEOPLE_F
WHERE PERSON_ID=FU.EMPLOYEE_ID
AND EFFECTIVE_END_DATE>SYSDATE)
EMPLOYEE_NAME
,fr.responsibility_name
FROM apps.fnd_user_resp_groups_all furg
,apps.fnd_user fu
,apps.fnd_responsibility_vl fr
,apps.fnd_profile_option_values fpov
,apps.fnd_profile_options_vl fpo
WHERE fu.user_id= furg.user_id
AND
fu.user_name=nvl(:P_USER,fu.user_name)
AND
fr.responsibility_name=nvl(:P_RESP,fr.responsibility_name)
AND fr.responsibility_id=
furg.responsibility_id
AND (furg.end_date >sysdate OR
furg.end_date IS NULL)
AND
fpov.level_value=fr.responsibility_id
AND fpov.profile_option_id=
fpo.profile_option_id
AND
fpov.application_id=fpo.application_id
AND FU.END_DATE IS NULL
AND FR.END_DATE IS NULL
AND
fpo.user_profile_option_name='MO: Operating Unit'
AND fpov.profile_option_value=189
ORDER BY 1
Important
Tables Used By GL Account Analysis SQL Query in Oracle Apps r12
1.GL_CODE_COMBINATIONS
2.GL_JE_LINES
3.GL_JE_HEADERS
4.GL_JE_BATCHES
5.GL_IMPORT_REFERENCES
6.XLA_AE_LINES
7.XLA_TRANSACTION_ENTITIES
8.XLA_AE_HEADERS
9.AP_INVOICES_ALL
10.ap_supplier_sites_all
11.PO_VENDORS
Detail GL
Account Analysis SQL Query in Oracle Apps r12
select JE_NAME,
a.LAST_UPDATE_DATE creation_date,
JE_CATEGORY,
MRN_NO,
a.DESCRIPTION,DOC_SEQUENCE_VALUE,EFFECTIVE_DATE,CURRENCY_CODE,VENDOR_NAME,
VENDOR_SITE_CODE,
INVOICE_NUM,PO,(SELECT MAX(AP.INVOICE_TYPE_LOOKUP_CODE) FROM AP_INVOICES_ALL AP
WHERE INVOICE_NUM = A.INVOICE_NUM) INVOICE_TYPE_LOOKUP_CODE,
INVOICE_DATE,GL_DATE,ENTERED_DR,ENTERED_CR,DR Func_Debit,CR Func_Credit,BAL,
JE_SOURCE,PERIOD,NAME,A.CODE_COMBINATION_ID,
GCC.SEGMENT1
company,apps.gl_flexfields_pkg.get_description_sql(5446,1,segment1)COMPANY_DESCRIPTION,
GCC.SEGMENT2
LOCATION,apps.gl_flexfields_pkg.get_description_sql(5446,2,segment2)
LOCATION_DESCRIPTION,
GCC.SEGMENT3
CITY,apps.gl_flexfields_pkg.get_description_sql(5446,3,segment3)
CITY_DESCRIPTION,
GCC.SEGMENT4
COSTCENTER,apps.gl_flexfields_pkg.get_description_sql(5446,4,segment4)
COSTCENTRE_DESCRIPTION,
GCC.SEGMENT5
ACCOUNT,apps.gl_flexfields_pkg.get_description_sql(5446,5,segment5)
ACCOUNT_DESCRIPTION,
GCC.SEGMENT8
FUTURE1,apps.gl_flexfields_pkg.get_description_sql(5446,8,segment8)
FUTURE1_DESCRIPTION,
GCC.SEGMENT9
FUTURE2,apps.gl_flexfields_pkg.get_description_sql(5446,9,segment9)
FUTURE2_DESCRIPTION
from
(
SELECT JE_NAME,
LAST_UPDATE_DATE,
JE_CATEGORY,
MRN_NO,
DESCRIPTION,
DOC_SEQUENCE_VALUE,
EFFECTIVE_DATE,
CURRENCY_CODE,
VENDOR_NAME,
VENDOR_SITE_CODE,
INVOICE_NUM,
PO,
INVOICE_DATE,
GL_DATE
,
SUM(nvl(ENTERED_DR,0) ) ENTERED_DR,
SUM(nvl(ENTERED_DR,0) ) ENTERED_CR,
SUM(nvl(DR,0) ) DR,
SUM(nvl(CR,0) ) CR,
SUM(bal) bal,
JE_SOURCE,
PERIOD,
NAME,
CODE_COMBINATION_ID,
CODE_COMINATION,
PFROM_DATE,
PTO_DATE,
TYPE,
DES
FROM(
SELECT JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
nvl((select receipt_num from rcv_shipment_headers
where shipment_header_id=(select
shipment_header_id from rcv_transactions
where transaction_id=(SELECT
MAX(RCV_TRANSACTION_ID) FROM AP_INVOICE_distributions_ALL
WHERE INVOICE_ID=AIA.INVOICE_ID
AND AMOUNT=XAL.ACCOUNTED_DR))),(select receipt_num
from rcv_shipment_headers
where shipment_header_id=(select
shipment_header_id from rcv_transactions
where transaction_id=(SELECT
MAX(RCV_TRANSACTION_ID) FROM AP_INVOICE_distributionS_ALL
WHERE INVOICE_ID=AIA.INVOICE_ID)))) MRN_NO,
AIA.DESCRIPTION,
AIA.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
POV.VENDOR_NAME,
PSSA.VENDOR_SITE_CODE,
AIA.INVOICE_NUM,
(select segment1 from po_headers_all
where
po_header_id=(select max(po_header_id) from ap_invoice_lines_all
where
invoice_id=aia.invoice_id)) PO,
AIA.INVOICE_DATE,
AIA.GL_DATE ,
nvl(XAL.ENTERED_DR,0) ENTERED_DR,
nvl(XAL.ENTERED_CR,0) ENTERED_CR,
nvl(XAL.ACCOUNTED_DR,0) DR,
nvl(XAL.ACCOUNTED_CR,0) CR,
nvl(XAL.ACCOUNTED_DR,0) -nvl(XAL.ACCOUNTED_CR,0) bal,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
'AP_INVOICE' NAME,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
CODE_COMINATION,
decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
NULL
TYPE,
NULL DES
FROM
GL_CODE_COMBINATIONS GCC,
GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB,
GL_IMPORT_REFERENCES GIR,
XLA.XLA_AE_LINES XAL,
XLA.XLA_TRANSACTION_ENTITIES XEH,
XLA.XLA_AE_HEADERS XAH
,AP_INVOICES_ALL AIA,
ap_supplier_sites_all PSSA,
PO_VENDORS POV--,
--
fnd_flex_values_vl a,
--fnd_flex_values_vl b
WHERE JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
AND
JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
AND
JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND
JEH.STATUS='P'
AND
TO_DATE('01'||'-'||
JEL.PERIOD_NAME)>=nvl(TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'||
JEL.PERIOD_NAME))
AND
XEH.ENTITY_CODE='AP_INVOICES'
AND TO_DATE('01'||'-'||
JEL.PERIOD_NAME)<=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'||
JEL.PERIOD_NAME))
AND
GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
AND
GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
and
jeh.LAST_UPDATE_DATE>:creation
AND
XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
AND
AIA.VENDOR_ID=PSSA.VENDOR_ID
AND
AIA.VENDOR_SITE_ID=PSSA.VENDOR_SITE_ID
AND
XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
AND
XAH.ENTITY_ID=XEH.ENTITY_ID
AND
AIA.INVOICE_ID=XEH.SOURCE_ID_INT_1
AND
POV.VENDOR_ID=AIA.VENDOR_ID
-- AND
(SELECT DISTINCT SEGMENT1 FROM PO_HEADERS_ALL
--WHERE PO_HEADER_ID IN (SELECT PO_HEADER_ID FROM
PO_DISTRIBUTIONS_ALL
--WHERE PO_DISTRIBUTION_ID IN(SELECT
PO_DISTRIBUTION_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL
--WHERE INVOICE_ID=AIA.INVOICE_ID))) IS NOT NULL
)
GROUP BY JE_NAME,
LAST_UPDATE_DATE,
JE_CATEGORY,
MRN_NO,
DESCRIPTION,
DOC_SEQUENCE_VALUE,
EFFECTIVE_DATE,
CURRENCY_CODE,
VENDOR_NAME,
VENDOR_SITE_CODE,
INVOICE_NUM,
PO,
INVOICE_DATE,
GL_DATE
,
JE_SOURCE,
PERIOD,
NAME,
CODE_COMBINATION_ID,
CODE_COMINATION,
PFROM_DATE,
PTO_DATE,
TYPE,
DES
union
all
------------------------------AP_PAYMENT----------------------------------
SELECT JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
null
MRN_NO,
JEL.DESCRIPTION,
AIA.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
POV.VENDOR_NAME,
AIA.VENDOR_SITE_CODE,
TO_CHAR(AIA.CHECK_NUMBER),
NULL PO,
AIA.CHECK_DATE,
AIA.CHECK_DATE GL_DATE,
SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR,
SUM(nvl(XAL.ACCOUNTED_DR,0) ) DR,
SUM(nvl(XAL.ACCOUNTED_CR,0) ) CR,
SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
'AP_PAYMENT' NAME,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
CODE_COMINATION,
decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
NULL
TYPE,
NULL DES
FROM
GL_CODE_COMBINATIONS GCC,
GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB,
GL_IMPORT_REFERENCES GIR,
XLA.XLA_AE_LINES XAL,
XLA.XLA_TRANSACTION_ENTITIES XEH,
XLA.XLA_AE_HEADERS XAH
,AP_CHECKS_ALL AIA,
PO_VENDORS POV
WHERE JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
AND
JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
AND
JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND
JEH.STATUS='P'
AND
XEH.ENTITY_CODE='AP_PAYMENTS'
and
jeh.LAST_UPDATE_DATE>:creation
AND
TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl(
TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME)
<=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND
GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
AND
GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
AND
XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
AND
XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
AND
XAH.ENTITY_ID=XEH.ENTITY_ID
AND
AIA.CHECK_ID=XEH.SOURCE_ID_INT_1
AND
POV.VENDOR_ID=AIA.VENDOR_ID
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
JEL.DESCRIPTION,
AIA.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
POV.VENDOR_NAME,
AIA.VENDOR_SITE_CODE,
JE_SOURCE,
AIA.CHECK_NUMBER,
AIA.CHECK_DATE,
AIA.CHECK_DATE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
UNION all
---------------------------General_Ledger------------------------------
SELECT
JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
null MRN_NO,
JEL.DESCRIPTION,
jeh.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
vendor_name(jeh.ACCRUAL_REV_JE_HEADER_ID,:from_date,:to_date) VENDOR_NAME,
null VENDOR_SITE_CODE,
TO_CHAR(JEH.DOC_SEQUENCE_VALUE),
NULL PO,
JEH.CREATION_DATE,
JEH.DEFAULT_EFFECTIVE_DATE GL_DATE,
SUM(nvl(JEL.ENTERED_DR,0) ) ENTERED_DR,
SUM(nvl(JEL.ENTERED_CR,0) ) ENTERED_CR,
SUM(nvl(JEL.ACCOUNTED_DR,0) )DR,
SUM(nvl(JEL.ACCOUNTED_CR,0) ) CR,
SUM(nvl(jel.ACCOUNTED_DR,0) )-SUM(nvl(jel.ACCOUNTED_CR,0) ) bal,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
'GL-Manual' NAME,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9,
decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
NULL
TYPE ,
NULL
DES
FROM
GL_CODE_COMBINATIONS GCC,
GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB
WHERE
JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
AND
JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
and
jeh.LAST_UPDATE_DATE>:creation
AND
JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND
JEH.STATUS='P'
AND
TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl(
TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME)
<=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND
JEH.JE_HEADER_ID NOT IN (SELECT GIR.JE_HEADER_ID FROM
GL_IMPORT_REFERENCES GIR)
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
JEL.DESCRIPTION,
jeh.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
JE_SOURCE,
JEL.EFFECTIVE_DATE,
jeh.ACCRUAL_REV_JE_HEADER_ID,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') ,
JEH.DOC_SEQUENCE_VALUE,
JEH.CREATION_DATE,
JEH.DEFAULT_EFFECTIVE_DATE,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
union all
SELECT
JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
(select
receipt_num from rcv_shipment_headers
where shipment_header_id=(select
shipment_header_id from rcv_transactions
where transaction_id=jel.REFERENCE_5)) MRN_NO,
(select
ITEM_DESCRIPTION from po_lines_all
where po_header_id=(select po_header_id from
rcv_transactions where transaction_id=jel.REFERENCE_5)
and po_line_id=(select po_line_id from
rcv_transactions where transaction_id=jel.REFERENCE_5)) DESCRIPTION,
jeh.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
(select
vendor_name from po_vendors
where
vendor_id=( select VENDOR_ID from rcv_transactions
where
TRANSACTION_ID=jel.REFERENCE_5)) VENDOR_NAME,
(select
city from po_vendor_sites_all
where
vendor_site_id=( select VENDOR_site_id from rcv_transactions
where
TRANSACTION_ID=jel.REFERENCE_5)) VENDOR_SITE_CODE,
TO_CHAR(JEH.DOC_SEQUENCE_VALUE),
NULL PO,
JEH.CREATION_DATE,
JEH.DEFAULT_EFFECTIVE_DATE GL_DATE,
SUM(nvl(JEL.ENTERED_DR,0) ) ENTERED_DR,
SUM(nvl(JEL.ENTERED_CR,0) ) ENTERED_CR,
SUM(nvl(JEL.ACCOUNTED_DR,0) )DR,
SUM(nvl(JEL.ACCOUNTED_CR,0) ) CR,
SUM(nvl(jel.ACCOUNTED_DR,0) )-SUM(nvl(jel.ACCOUNTED_CR,0) ) bal,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
'Purchasing_India' NAME,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9,
decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
NULL
TYPE ,
NULL
DES
FROM
GL_CODE_COMBINATIONS GCC,
GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB,
GL_IMPORT_REFERENCES GIR
WHERE
JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
AND
JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
AND
JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
and
jeh.LAST_UPDATE_DATE>:creation
AND
JEH.STATUS='P'
and
JEH.JE_SOURCE='Purchasing India'
AND
TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl(
TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME)
<=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND
GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
AND
GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
JEL.DESCRIPTION,
jeh.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
jel.REFERENCE_5,
JE_SOURCE,
JEL.EFFECTIVE_DATE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') ,
JEH.DOC_SEQUENCE_VALUE,
JEH.CREATION_DATE,
JEH.DEFAULT_EFFECTIVE_DATE,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
UNION
all
-------------------------------AR_RECEIPT------------------------------------
SELECT JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
null MRN_NO,
RE.COMMENTS,
RE.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
RC.CUSTOMER_NAME,
HL.CITY VENDOR_SITE_CODE,
RE.RECEIPT_NUMBER,
NULL PO,
XAL.ACCOUNTING_DATE RECEIPT_DATE,
RE.RECEIPT_DATE GL_DATE,
SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR,
SUM(NVL(XAL.ACCOUNTED_DR,0)) DEBIT,
SUM(NVL(XAL.ACCOUNTED_CR,0)) CREDIT,
SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
'AR_RECEIPT' NAME ,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9 ,
decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
NULL
TYPE,
NULL DES
FROM
AR_CASH_RECEIPTS_ALL RE,
XLA.XLA_TRANSACTION_ENTITIES XEH,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIR,
AR_CUSTOMERS RC,
GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB,
hz_cust_site_uses_all HCSU,
hz_cust_acct_sites_all HCSU1,
hz_party_sites HPS,
HZ_LOCATIONS HL
WHERE
RE.CASH_RECEIPT_ID=XEH.SOURCE_ID_INT_1
AND
XAL.LEDGER_ID=XEH.LEDGER_ID
AND
XEH.ENTITY_ID=XAH.ENTITY_ID
and
jeh.LAST_UPDATE_DATE>:creation
AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND
GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND
TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl(
TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME)
<=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND
XEH.ENTITY_CODE='RECEIPTS'
AND
GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
and
RE.CUSTOMER_SITE_USE_ID=HCSU.SITE_USE_ID
AND
HCSU.CUST_ACCT_SITE_ID=HCSU1.CUST_ACCT_SITE_ID
AND
RE.PAY_FROM_CUSTOMER=HCSU1.CUST_ACCOUNT_ID
AND
HCSU1.PARTY_SITE_ID=HPS.PARTY_SITE_ID
AND
HPS.LOCATION_ID=HL.LOCATION_ID
AND
GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
AND
XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
AND
JEH.STATUS='P'
AND
NVL(RE.PAY_FROM_CUSTOMER,11)=RC.CUSTOMER_ID(+)
AND
JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
AND
JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
AND
JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND
NVL(XAL.ACCOUNTED_DR,0)-NVL(XAL.ACCOUNTED_CR,0)<>0
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
RE.COMMENTS,
RE.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
RC.CUSTOMER_NAME,
HL.CITY,
RE.RECEIPT_NUMBER,
XAL.ACCOUNTING_DATE,
RE.RECEIPT_DATE,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
UNION all
-------------------------------AR_TRANSACTION-------------------------------
SELECT JEH_NAME JE_NAME,
LAST_UPDATE_DATE,
JE_CATEGORY,
MRN_NO,
DESCRIPTION,
DOC_SEQUENCE_VALUE,
EFFECTIVE_DATE,
CURRENCY_CODE,
CUSTOMER_NAME,
VENDOR_SITE_CODE,TRX_NUMBER,PO,TRX_DATE,GL_DATE,ENTERED_DR,ENTERED_DR,DR, CR,
bal,JE_SOURCE,PERIOD,NAME,CODE_COMBINATION_ID,CODE_COMINATION,
PFROM_DATE,PTO_DATE,TYPE,
(SELECT MAX(RCTL.DESCRIPTION)
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
WHERE
RCTL.CUSTOMER_TRX_ID=CUSTOMER_TRX_ID) dES
FROM
(SELECT JEH.NAME JEH_NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
null MRN_NO,
JEL.DESCRIPTION,
RE.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
RC.CUSTOMER_NAME,
hl.city VENDOR_SITE_CODE,
RE.TRX_NUMBER,
NULL PO,
RE.TRX_DATE,
RE.TRX_DATE GL_DATE,
SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR,
SUM(NVL(XAL.ACCOUNTED_DR,0)) DR,
SUM(NVL(XAL.ACCOUNTED_CR,0)) CR,
SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
'AR_TRANSACTION' NAME ,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
CODE_COMINATION,
decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
RCTP.TYPE ,
RE.CUSTOMER_TRX_ID
FROM
RA_CUSTOMER_TRX_ALL RE,
RA_CUST_TRX_TYPES_ALL RCTP,
XLA.XLA_TRANSACTION_ENTITIES XEH,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIR,
AR_CUSTOMERS RC ,
GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB,
hz_cust_site_uses_all HCSU,
hz_cust_acct_sites_all HCSU1,
hz_party_sites HPS,
HZ_LOCATIONS HL
WHERE
RE.CUSTOMER_TRX_ID=XEH.SOURCE_ID_INT_1
AND
XAL.LEDGER_ID=XEH.LEDGER_ID
AND
XEH.ENTITY_ID=XAH.ENTITY_ID
AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
and
jeh.LAST_UPDATE_DATE>:creation
AND
RCTP.CUST_TRX_TYPE_ID=re.CUST_TRX_TYPE_ID
AND
RCTP.ORG_ID=RE.ORG_ID
AND
GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl(
TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME)
<=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND
XEH.ENTITY_CODE='TRANSACTIONS'
AND
JEH.STATUS='P'
AND
RC.CUSTOMER_ID=RE.BILL_TO_CUSTOMER_ID
and RE.BILL_TO_SITE_USE_ID=HCSU.SITE_USE_ID
AND
HCSU.CUST_ACCT_SITE_ID=HCSU1.CUST_ACCT_SITE_ID
AND
RE.SOLD_TO_CUSTOMER_ID=HCSU1.CUST_ACCOUNT_ID
AND
HCSU1.PARTY_SITE_ID=HPS.PARTY_SITE_ID
AND
HPS.LOCATION_ID=HL.LOCATION_ID
AND
JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
AND
JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
AND
JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND
GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
AND
GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
AND
XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
JEL.DESCRIPTION,
RE.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
RC.CUSTOMER_NAME,
hl.city,
RCTP.TYPE,
RE.TRX_NUMBER,
RE.CUSTOMER_TRX_ID,
RE.TRX_DATE,
RE.TRX_DATE,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9)
UNION all
-- inventory----------------------
select
JE_NAME,
LAST_UPDATE_DATE,
JE_CATEGORY,
MRN_NO,
DESCRIPTION1,
DOC_SEQUENCE_VALUE,
EFFECTIVE_DATE,
CURRENCY_CODE,
VENDOR_NAME,
VENDOR_SITE_CODE,
doc_value,
PO,
--CBA.BANK_ACCOUNT_NUM,
ACCOUNTING_DATE,
GL_DATE,
SUM(nvl(ENTERED_DR,0) ) ENTERED_DR,
SUM(nvl(ENTERED_CR,0) ) ENTERED_CR,
SUM(NVL(ACCOUNTED_DR,0)),
SUM(NVL(ACCOUNTED_CR,0)),
SUM(nvl(ACCOUNTED_DR,0) )-SUM(nvl(ACCOUNTED_CR,0) ) bal,
JE_SOURCE,
PERIOD,
NAME ,
CODE_COMBINATION_ID,
code,
PFROM_DATE,
PTO_DATE,
TYPE,
DES from (SELECT JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
(select
receipt_num from rcv_shipment_headers
where shipment_header_id=(select
shipment_header_id from rcv_transactions
where transaction_id=rcv.transaction_id)) MRN_NO,
(select
ITEM_DESCRIPTION from po_lines_all
where po_header_id=(select po_header_id from
rcv_transactions where transaction_id=rcv.transaction_id)
and po_line_id=(select po_line_id from
rcv_transactions where transaction_id=rcv.transaction_id)) DESCRIPTION1,
jeh.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
(select
VENDOR_NAME from po_vendors
where
vendor_id=rcv.vendor_id) VENDOR_NAME,
(select
VENDOR_SITE_code from po_vendor_sites_all
where
VENDOR_SITE_ID=rcv.VENDOR_SITE_ID) VENDOR_SITE_CODE,
(select max(invoice_num) from ap_invoices_all
where
invoice_id=(select max(invoice_id) from ap_invoice_lines_all
where
PO_HEADER_ID=(select distinct po_header_id from rcv_transactions where
transaction_id=rcv.transaction_id)
and
po_line_id=(select distinct po_line_id from rcv_transactions where
transaction_id=rcv.transaction_id))) doc_value,
(select segment1 from po_headers_all where po_header_id=(select po_header_id
from rcv_transactions where transaction_id=rcv.transaction_id)) PO,
--CBA.BANK_ACCOUNT_NUM,
XAL.ACCOUNTING_DATE,
XAL.ACCOUNTING_DATE GL_DATE,
XAL.ENTERED_DR,
XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,
XAL.ACCOUNTED_CR,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
'CASH_MANAGEMENT' NAME ,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
code,
decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
NULL
TYPE,
NULL DES
FROM
XLA.XLA_TRANSACTION_ENTITIES XEH,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIR,
GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB,
rcv_transactions rcv
WHERE
XAL.LEDGER_ID=XEH.LEDGER_ID
AND
XEH.ENTITY_ID=XAH.ENTITY_ID
AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND
GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND
XEH.ENTITY_CODE='RCV_ACCOUNTING_EVENTS'
and
xeh.SOURCE_ID_INT_1=rcv.transaction_id
-- and
rcv.transaction_id=2009
AND
JEH.STATUS='P'
AND
XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
and
jeh.LAST_UPDATE_DATE>:creation
AND
JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND
GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
AND
GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
AND
JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
AND
JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
AND
TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl(
TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME)
<=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME)))
GROUP BY JE_NAME,
LAST_UPDATE_DATE,
JE_CATEGORY,
MRN_NO,
DEscription1,
DOC_SEQUENCE_VALUE,
EFFECTIVE_DATE,
VENDOR_NAME,
VENDOR_SITE_CODE,
CURRENCY_CODE,
doc_value,
PO,
--CBA.BANK_ACCOUNT_NUM,
ACCOUNTING_DATE,
gl_DATE,
JE_SOURCE,
TYPE,
PERIOD,
NAME,
CODE_COMBINATION_ID,code, PFROM_DATE,
PTO_DATE
UNION
all
SELECT JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
(select receipt_num from rcv_shipment_headers
where shipment_header_id=(select
shipment_header_id from rcv_transactions
where
to_char(transaction_id)=nvl(to_char(mmt.Rcv_transaction_id),to_char(mmt.TRANSACTION_REFERENCE))))
MRN_NO,
MSI.DESCRIPTION,
jeh.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
(select
VENDOR_NAME from po_vendors
where
vendor_id=(select vendor_id from rcv_transactions
where
to_char(TRANSACTION_ID)=nvl(to_char(mmt.Rcv_transaction_id),to_char(mmt.TRANSACTION_REFERENCE))))
VENDOR_NAME,
null VENDOR_SITE_CODE,
TO_CHAR(jeh.DOC_SEQUENCE_VALUE),
(select segment1 from po_headers_all where po_header_id=(select po_header_id
from rcv_transactions where
to_char(transaction_id)=nvl(to_char(mmt.Rcv_transaction_id),to_char(mmt.TRANSACTION_REFERENCE))))
PO,
--CBA.BANK_ACCOUNT_NUM,
XAL.ACCOUNTING_DATE,
XAL.ACCOUNTING_DATE GL_DATE,
SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR,
SUM(NVL(XAL.ACCOUNTED_DR,0)),
SUM(NVL(XAL.ACCOUNTED_CR,0)),
SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
'INVENTORY' NAME ,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9 ,
decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
NULL
TYPE,
NULL DES
FROM
XLA.XLA_TRANSACTION_ENTITIES XEH,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIR,
GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_B MSI,
XLA_EVENTS XE
WHERE
XAL.LEDGER_ID=XEH.LEDGER_ID
AND
XEH.ENTITY_ID=XAH.ENTITY_ID
AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND
GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND
XEH.ENTITY_CODE='MTL_ACCOUNTING_EVENTS'
and
jeh.LAST_UPDATE_DATE>:creation
AND
MMT.TRANSACTION_ID=XEH.SOURCE_ID_INT_1
AND
MSI.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID
AND
MSI.ORGANIZATION_ID=MMT.ORGANIZATION_ID
AND
JEH.STATUS='P'
AND
XE.EVENT_ID=XAH.EVENT_ID
AND XE.PROCESS_STATUS_CODE='P'
AND XE.EVENT_STATUS_CODE='P'
AND
XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
AND
JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND
XAH.ACCOUNTING_ENTRY_STATUS_CODE='F'
AND
XAL.APPLICATION_ID=XAH.APPLICATION_ID
AND
XE.APPLICATION_ID=XAH.APPLICATION_ID
AND
XEH.ENTITY_ID=XE.ENTITY_ID
AND
XEH.APPLICATION_ID=XE.APPLICATION_ID
AND
GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
AND
GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
and
NVL(XAL.ACCOUNTED_DR,0)-NVL(XAL.ACCOUNTED_CR,0)<>0
AND
JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
AND
JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
AND
TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl(
TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME)
<=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
MSI.DESCRIPTION,
jeh.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
mmt.Rcv_transaction_id,mmt.TRANSACTION_REFERENCE,
jeh.CURRENCY_CODE,
TO_CHAR(jeh.DOC_SEQUENCE_VALUE),
--CBA.BANK_ACCOUNT_NUM,
XAL.ACCOUNTING_DATE,
XAL.ACCOUNTING_DATE,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||
GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
union
all
SELECT JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
null MRN_NO,
fma.DESCRIPTION,
gir.SUBLEDGER_DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
POV.VENDOR_NAME,
null
VENDOR_SITE_CODE,
TO_CHAR(fma.INVOICE_NUMBER),
fma.PO_NUMBER PO,
jeh.DEFAULT_EFFECTIVE_DATE,
jeh.DEFAULT_EFFECTIVE_DATE GL_DATE,
SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR,
SUM(nvl(XAL.ACCOUNTED_DR,0) ) DR,
SUM(nvl(XAL.ACCOUNTED_CR,0) ) CR,
SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
'Assets' NAME,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
CODE_COMINATION,
decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
NULL
TYPE,
NULL DES
FROM
GL_CODE_COMBINATIONS GCC,
GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB,
GL_IMPORT_REFERENCES GIR,
XLA.XLA_AE_LINES XAL,
XLA.XLA_TRANSACTION_ENTITIES XEH,
XLA.XLA_AE_HEADERS XAH,
FA_ADDITIONS_B fab,
FA_MASS_ADDITIONS fma,
PO_VENDORS POV
WHERE JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
AND
JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
AND
JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND
JEH.STATUS='P'
AND
XEH.ENTITY_CODE='DEPRECIATION'
AND
TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl(
TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME)
<=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND
GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
AND
GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
and
jeh.LAST_UPDATE_DATE>:creation
AND
XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
AND
XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
AND
XAH.ENTITY_ID=XEH.ENTITY_ID
AND
fab.ASSET_ID=XEH.SOURCE_ID_INT_1
and
fab.ASSET_ID=fma.ASSET_NUMBER
AND
POV.VENDOR_ID=fma.PO_VENDOR_ID
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
fma.DESCRIPTION,
gir.SUBLEDGER_DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
POV.VENDOR_NAME,
null,
JE_SOURCE,
fma.INVOICE_NUMBER,
fma.PO_NUMBER,
jeh.DEFAULT_EFFECTIVE_DATE,
jeh.DEFAULT_EFFECTIVE_DATE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
union
all
-----------------------------CASH_MANAGEMENT-----------------------------
SELECT JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
null MRN_NO,
JEL.DESCRIPTION,
jeh.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
nvl(CB.BANK_NAME,gcc.SEGMENT4) VENDOR_NAME,
null VENDOR_SITE_CODE,
TO_CHAR(CCF.TRXN_REFERENCE_NUMBER),
NULL PO,
--CBA.BANK_ACCOUNT_NUM,
XAL.ACCOUNTING_DATE,
CCF.CASHFLOW_DATE GL_DATE,
SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR,
SUM(NVL(XAL.ACCOUNTED_DR,0)),
SUM(NVL(XAL.ACCOUNTED_CR,0)),
SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
'CASH_MANAGEMENT' NAME ,
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9 ,
decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
NULL
TYPE,
NULL DES
FROM
XLA.XLA_TRANSACTION_ENTITIES XEH,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
GL_CODE_COMBINATIONS GCC,
CE_CASHFLOWS CCF,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL CBU,
CE_BANKS_V CB,
GL_IMPORT_REFERENCES GIR,
GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB
WHERE
XAL.LEDGER_ID=XEH.LEDGER_ID
AND
XEH.ENTITY_ID=XAH.ENTITY_ID
AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND
GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND
XEH.SOURCE_ID_INT_1=CCF.CASHFLOW_ID
AND
XEH.ENTITY_CODE='CE_CASHFLOWS'
AND
CCF.CASHFLOW_BANK_ACCOUNT_ID=CBU.BANK_ACCT_USE_ID(+)
AND
CBU.BANK_ACCOUNT_ID=CBA.BANK_ACCOUNT_ID(+)
AND
CBA.BANK_ID=CB.BANK_PARTY_ID(+)
and
jeh.LAST_UPDATE_DATE>:creation
AND
JEH.STATUS='P'
AND
XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
AND
JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND
GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
AND
GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
AND
JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
AND
JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
AND
TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl(
TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME)
<=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
JEH.JE_CATEGORY,
JEL.DESCRIPTION,
jeh.DOC_SEQUENCE_VALUE,
JEL.EFFECTIVE_DATE,
jeh.CURRENCY_CODE,
nvl(CB.BANK_NAME,gcc.SEGMENT4),
TO_CHAR(CCF.TRXN_REFERENCE_NUMBER),
--CBA.BANK_ACCOUNT_NUM,
XAL.ACCOUNTING_DATE,
CCF.CASHFLOW_DATE,
JE_SOURCE,
TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||
GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
)a,GL_CODE_COMBINATIONS GCC
where a.JE_SOURCE=nvl(:p_source,a.JE_SOURCE)
AND A.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
and gcc.segment1=nvl(:p_company,gcc.segment1)
and gcc.segment2=nvl(:p_location,gcc.segment2)
and gcc.segment3=nvl(:p_lob,gcc.segment3)
and gcc.segment4=nvl(:p_cost_centre,gcc.segment4)
and gcc.segment5=nvl(:p_account,gcc.segment5)
and gcc.segment6=nvl(:p_sub_account,gcc.segment6)
and gcc.segment7=nvl(:p_project,gcc.segment7)
order by gl_date;
Steps to
Developed the Cash Bank Book Report Complete Logic in Oracle apps R12
Step1:- You have to use first query for Payments.
In the payment the main table will use is ap_checks_All
There will two columns in the report Payment and
Receipt.
we will pick all the transactions/Checks
which are clear and negotiable,reconciled,RECONCILED UNACCOUNTED,CLEARED BUT
UNACCOUNTED.
The amount in ap_checks_all are mostly of +amt
which is in payment_side
THE PAYMENT TYPE IN (P,OR NULL,M)
The amount in -ve are goes in receipt side (some
time we take refund) so for those amt payment type flag in(R REFUND)
We will use Date parameter between
CHECK_DATE for table ap_checks_all
Other Table will be used in this as below
ce_bank_accounts cba ,
ce_bank_acct_uses_all cbu
-----------------------------------------------------------------------------------------------------------------------------------------------
Step2:- We will use Second query as a union with
first. We will again use the second query from Payments(Ap_Checks_All)
This Query will Handle the Void Checks
We will pick checks from Ap_checks_all
with Status 'Void' and Data Parameter will work on Check Date
Payment Type similar as Above
Payments column take Amount simply
from Ap_Checks_all Table AND Receipt column will be Null.
STATUS=VOIDED
TABLES Will Use
ap_checks_all apc,
ce_bank_accounts cba ,
ce_bank_acct_uses_all cbu
Step3:- We will use Third query as a union
with first and Second. We will again use the Third query from
Payments(AP_PAYMENT_HISTORY_ALL)
In this we use Special
Table AP_PAYMENT_HISTORY_ALL
We will simple pick the check of STATUS=VOID
But here in this query we will put Data
parameter onVOID_DATE
AND ALSO USE TRANSACTION_TYPE IN ('PAYMENT
CANCELLED','REFUND CANCELLED') OF TABLE AP_PAYMENT_HISTORY_ALL.
AMOUNT WE TAKE IN PAYMENTS and RECEIPTS columns are
RECEIPTS column will take Amount simple
from Check Table and Payment Column will Null.
Step4:- This Fourth Query we will use for Those
Invoices Which Use Bank GL Account in Invoice Distribution.
---------------------------------------------------------------------------------
FOR THOSE INVOICES WHICH HIT BANK IN DISTRIBUTIONS.
IN THIS WE LINK THE DIST_CODE_COMBINATION ID TO
BANK ASSET CODE COMBIANTION ID
TABLES USED ARE
ap_invoice_distributions_all apid,
ap_invoices_all api,
po_vendors POV,
gl_code_combinations gcc,
ce_bank_accounts cba
In this Your Check Status will Always will Cleared.
if you found this post helpful and you liked it then follow this blog to
get notifications for the upcoming posts.
Amount We will Take in Payment and Receipts are
Basically in Invoice Distribution amount is
Negative so the amount Which is Negative will go to Payment Column and Positive
Amount will go to Receipt column.
No comments:
Post a Comment