SELECT
DECODE(:P_ORG_CODE,'ALL','ALL',HAOU.NAME ) ORGANIZATION_NAME
,TRUNC(SYSDATE) SYSDATE1
,TO_CHAR(SYSDATE,'HH:MI:SS AM') SYSTIME1
,:P_FROM_DATE
,:P_TO_DATE
,BPIL_STATEMENT_LINE(CR.CASH_RECEIPT_ID) STATEMENT_LINE_ID
-- ,CR.LAST_UPDATE_DATE
,DECODE (RT.SEGMENT1, 'W', 'WEST'
,'C', 'CENTRAL'
,'N', 'NORTH'
,'S', 'SOUTH'
,'E', 'EAST' ) REGION
,MP.ORGANIZATION_CODE
, CASE HCAA.SALES_CHANNEL_CODE WHEN '0' THEN 'RETAIL'
WHEN '2' THEN 'GENERAL INDUSTRIAL'
WHEN '3' THEN 'AUTOMOTIVE'
WHEN '4' THEN 'PROTECTON'
WHEN '6' THEN 'POWDER COATING'
ELSE NULL END "B_LINE"
,JRS. NAME SALES_REP_NAME
,HCA.ACCOUNT_NUMBER CUSTOMER_CODE
,HP.PARTY_NAME CUSTOMER_NAME
-- ,CON_REMM.CASH_RECEIPT_ID
,CON_REMM.PR_DATE
,CR.RECEIPT_NUMBER PR_NUMBER
-- ,DECODE(CON_REMM.BATCH_ID,-1,NULL,CON_REMM.BATCH_ID) BATCH_ID
,CON_REMM.PIS_DATE
,AB.NAME PIS_NUMBER
,CR.AMOUNT
-- ,RCL.STATUS
,CON_REMM.PR_DATE - CON_REMM.PIS_DATE DAY_DIFF_IN_PRS_AND_PIS_DATE
,TRUNC(RCL.TRX_DATE) CLEAR_DATE
,TRUNC(RCL.TRX_DATE) - CON_REMM.PIS_DATE DAY_DIFF_IN_PIS_AND_CLR_DATE
FROM
(SELECT CASH_RECEIPT_ID,TRX_DATE,BATCH_ID FROM AR_CASH_RECEIPT_HISTORY_ALL A
WHERE status ='CLEARED'
AND NOT EXISTS (SELECT 1 FROM AR_CASH_RECEIPT_HISTORY_ALL B
WHERE B.CASH_RECEIPT_ID=A.CASH_RECEIPT_ID
AND B.STATUS='REVERSED')
AND A.CASH_RECEIPT_ID=230886--2308861
) RCL,
(SELECT
CON.CASH_RECEIPT_ID,
CON.TRX_DATE PR_DATE,
REMM.TRX_DATE PIS_DATE,
NVL(REMM.BATCH_ID,-1) BATCH_ID
FROM
(SELECT * FROM AR_CASH_RECEIPT_HISTORY_ALL A
WHERE STATUS='CONFIRMED'
AND NOT EXISTS (SELECT 1 FROM AR_CASH_RECEIPT_HISTORY_ALL B
WHERE B.CASH_RECEIPT_ID=A.CASH_RECEIPT_ID
AND B.STATUS='REVERSED' )
AND A.CASH_RECEIPT_ID=230886--2308861
) CON,
--
(SELECT * FROM AR_CASH_RECEIPT_HISTORY_ALL A
WHERE STATUS='REMITTED'
AND NOT EXISTS (SELECT 1 FROM AR_CASH_RECEIPT_HISTORY_ALL B
WHERE B.CASH_RECEIPT_ID=A.CASH_RECEIPT_ID
AND B.STATUS='REVERSED' --)
AND A.cash_receipt_id=230886)--2308861
) REMM
WHERE CON.CASH_RECEIPT_ID=REMM.CASH_RECEIPT_ID(+)) CON_REMM
,AR_BATCHES_ALL AB
,AR_CASH_RECEIPTS_ALL CR
,HZ_CUST_SITE_USES_ALL HCSUA
,HZ_CUST_ACCT_SITES_ALL HCASA
,HZ_CUST_ACCOUNTS HCA
,HZ_PARTIES HP
,RA_TERRITORIES RT
,JTF_RS_SALESREPS JRS
,HZ_CUST_ACCOUNTS_ALL HCAA
,AR_RECEIPT_METHODS ARM
,MTL_PARAMETERS MP
,HR_ALL_ORGANIZATION_UNITS HAOU
-- ,AR_PAYMENT_SCHEDULES_ALL APSA
WHERE -- CON_REMM.BATCH_ID=AB.BATCH_ID(+)
RCL.BATCH_ID=AB.BATCH_ID(+)
AND CON_REMM.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CR.CUSTOMER_SITE_USE_ID = HCSUA.SITE_USE_ID -- IF WE DO NOT ADD OUTER JOIN , ROWS WILL BE REDUCED TO 131
AND HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID -- IF WE DO NOT ADD OUTER JOIN , ROWS WILL BE REDUCED TO 131
AND HCASA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND HCSUA.TERRITORY_ID = RT.TERRITORY_ID
AND HCSUA.PRIMARY_SALESREP_ID = JRS.SALESREP_ID
AND HCAA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
AND CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND UPPER(ARM.NAME) LIKE '%CHEQUE%' -- THIS LINE WILL REDUCE THE LINES TO 58 FROM 131
AND HCSUA.WAREHOUSE_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND RCL.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CR.CASH_RECEIPT_ID =230886--2308861
AND CON_REMM.CASH_RECEIPT_ID = RCL.CASH_RECEIPT_ID
AND TRUNC(RCL.TRX_DATE) >= :P_FROM_DATE
AND TRUNC(RCL.TRX_DATE) <= :P_TO_DATE
AND MP.ORGANIZATION_CODE = DECODE(:P_ORG_CODE,'ALL',MP.ORGANIZATION_CODE,:P_ORG_CODE)-- ON ORG_CODE = 031 LINES WILL REDUCE TO 56
ORDER BY MP.ORGANIZATION_CODE ASC, CR.LAST_UPDATE_DATE DESC;
FROM ce_bank_branches_v abb,
ce_bank_accts_gt_v aba,
ce_lookups l1,
ce_lookups l2,
--ce_statement_reconciliations sr,
ce_statement_reconcils_all sr,
ce_statement_lines sl,
ce_statement_headers sh
WHERE l1.lookup_type = 'BANK_TRX_TYPE'
AND l1.lookup_code = sl.trx_type
AND l2.lookup_type = 'STATEMENT_LINE_STATUS'
AND l2.lookup_code = sl.status
AND abb.branch_party_id = aba.bank_branch_id
AND aba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID,aba.bank_account_id)
AND aba.bank_account_id = sh.bank_account_id
AND sr.status_flag(+) = 'M'
AND sr.current_record_flag(+) = 'Y'
AND sr.statement_line_id(+) = sl.statement_line_id
AND sl.statement_header_id = sh.statement_header_id
SELECT ACA.ATTRIBUTE10 ORG,
CBA.BANK_ACCOUNT_NAME,
GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6 GL_CODE,
ACA.CHECK_NUMBER,
NVL(ACA.CHECK_VOUCHER_NUM, TO_CHAR(ACA.DOC_SEQUENCE_VALUE)) VCH,
ACA.CHECK_DATE,
ACA.AMOUNT,
CLEARED_DATE,
ACA.STATUS_LOOKUP_CODE
FROM AP_CHECKS_ALL ACA,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_BANK_ACCOUNTS CBA,
GL_CODE_COMBINATIONS GCC
WHERE ACA.CE_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND CBA.CASH_CLEARING_CCID = GCC.CODE_COMBINATION_ID
and cba.BANK_ACCOUNT_NAME = NVL (:BANK_ACCOUNT_NAME, cba.BANK_ACCOUNT_NAME)
and ACA.CHECK_DATE BETWEEN '01-DEC-2013' AND '24-MAR-2014'
--and aca.STATUS_LOOKUP_CODE ='RECONCILED'
ORDER BY
ACA.CHECK_ID;
DECODE(:P_ORG_CODE,'ALL','ALL',HAOU.NAME ) ORGANIZATION_NAME
,TRUNC(SYSDATE) SYSDATE1
,TO_CHAR(SYSDATE,'HH:MI:SS AM') SYSTIME1
,:P_FROM_DATE
,:P_TO_DATE
,BPIL_STATEMENT_LINE(CR.CASH_RECEIPT_ID) STATEMENT_LINE_ID
-- ,CR.LAST_UPDATE_DATE
,DECODE (RT.SEGMENT1, 'W', 'WEST'
,'C', 'CENTRAL'
,'N', 'NORTH'
,'S', 'SOUTH'
,'E', 'EAST' ) REGION
,MP.ORGANIZATION_CODE
, CASE HCAA.SALES_CHANNEL_CODE WHEN '0' THEN 'RETAIL'
WHEN '2' THEN 'GENERAL INDUSTRIAL'
WHEN '3' THEN 'AUTOMOTIVE'
WHEN '4' THEN 'PROTECTON'
WHEN '6' THEN 'POWDER COATING'
ELSE NULL END "B_LINE"
,JRS. NAME SALES_REP_NAME
,HCA.ACCOUNT_NUMBER CUSTOMER_CODE
,HP.PARTY_NAME CUSTOMER_NAME
-- ,CON_REMM.CASH_RECEIPT_ID
,CON_REMM.PR_DATE
,CR.RECEIPT_NUMBER PR_NUMBER
-- ,DECODE(CON_REMM.BATCH_ID,-1,NULL,CON_REMM.BATCH_ID) BATCH_ID
,CON_REMM.PIS_DATE
,AB.NAME PIS_NUMBER
,CR.AMOUNT
-- ,RCL.STATUS
,CON_REMM.PR_DATE - CON_REMM.PIS_DATE DAY_DIFF_IN_PRS_AND_PIS_DATE
,TRUNC(RCL.TRX_DATE) CLEAR_DATE
,TRUNC(RCL.TRX_DATE) - CON_REMM.PIS_DATE DAY_DIFF_IN_PIS_AND_CLR_DATE
FROM
(SELECT CASH_RECEIPT_ID,TRX_DATE,BATCH_ID FROM AR_CASH_RECEIPT_HISTORY_ALL A
WHERE status ='CLEARED'
AND NOT EXISTS (SELECT 1 FROM AR_CASH_RECEIPT_HISTORY_ALL B
WHERE B.CASH_RECEIPT_ID=A.CASH_RECEIPT_ID
AND B.STATUS='REVERSED')
AND A.CASH_RECEIPT_ID=230886--2308861
) RCL,
(SELECT
CON.CASH_RECEIPT_ID,
CON.TRX_DATE PR_DATE,
REMM.TRX_DATE PIS_DATE,
NVL(REMM.BATCH_ID,-1) BATCH_ID
FROM
(SELECT * FROM AR_CASH_RECEIPT_HISTORY_ALL A
WHERE STATUS='CONFIRMED'
AND NOT EXISTS (SELECT 1 FROM AR_CASH_RECEIPT_HISTORY_ALL B
WHERE B.CASH_RECEIPT_ID=A.CASH_RECEIPT_ID
AND B.STATUS='REVERSED' )
AND A.CASH_RECEIPT_ID=230886--2308861
) CON,
--
(SELECT * FROM AR_CASH_RECEIPT_HISTORY_ALL A
WHERE STATUS='REMITTED'
AND NOT EXISTS (SELECT 1 FROM AR_CASH_RECEIPT_HISTORY_ALL B
WHERE B.CASH_RECEIPT_ID=A.CASH_RECEIPT_ID
AND B.STATUS='REVERSED' --)
AND A.cash_receipt_id=230886)--2308861
) REMM
WHERE CON.CASH_RECEIPT_ID=REMM.CASH_RECEIPT_ID(+)) CON_REMM
,AR_BATCHES_ALL AB
,AR_CASH_RECEIPTS_ALL CR
,HZ_CUST_SITE_USES_ALL HCSUA
,HZ_CUST_ACCT_SITES_ALL HCASA
,HZ_CUST_ACCOUNTS HCA
,HZ_PARTIES HP
,RA_TERRITORIES RT
,JTF_RS_SALESREPS JRS
,HZ_CUST_ACCOUNTS_ALL HCAA
,AR_RECEIPT_METHODS ARM
,MTL_PARAMETERS MP
,HR_ALL_ORGANIZATION_UNITS HAOU
-- ,AR_PAYMENT_SCHEDULES_ALL APSA
WHERE -- CON_REMM.BATCH_ID=AB.BATCH_ID(+)
RCL.BATCH_ID=AB.BATCH_ID(+)
AND CON_REMM.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CR.CUSTOMER_SITE_USE_ID = HCSUA.SITE_USE_ID -- IF WE DO NOT ADD OUTER JOIN , ROWS WILL BE REDUCED TO 131
AND HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID -- IF WE DO NOT ADD OUTER JOIN , ROWS WILL BE REDUCED TO 131
AND HCASA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND HCSUA.TERRITORY_ID = RT.TERRITORY_ID
AND HCSUA.PRIMARY_SALESREP_ID = JRS.SALESREP_ID
AND HCAA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
AND CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND UPPER(ARM.NAME) LIKE '%CHEQUE%' -- THIS LINE WILL REDUCE THE LINES TO 58 FROM 131
AND HCSUA.WAREHOUSE_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND RCL.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CR.CASH_RECEIPT_ID =230886--2308861
AND CON_REMM.CASH_RECEIPT_ID = RCL.CASH_RECEIPT_ID
AND TRUNC(RCL.TRX_DATE) >= :P_FROM_DATE
AND TRUNC(RCL.TRX_DATE) <= :P_TO_DATE
AND MP.ORGANIZATION_CODE = DECODE(:P_ORG_CODE,'ALL',MP.ORGANIZATION_CODE,:P_ORG_CODE)-- ON ORG_CODE = 031 LINES WILL REDUCE TO 56
ORDER BY MP.ORGANIZATION_CODE ASC, CR.LAST_UPDATE_DATE DESC;
FROM ce_bank_branches_v abb,
ce_bank_accts_gt_v aba,
ce_lookups l1,
ce_lookups l2,
--ce_statement_reconciliations sr,
ce_statement_reconcils_all sr,
ce_statement_lines sl,
ce_statement_headers sh
WHERE l1.lookup_type = 'BANK_TRX_TYPE'
AND l1.lookup_code = sl.trx_type
AND l2.lookup_type = 'STATEMENT_LINE_STATUS'
AND l2.lookup_code = sl.status
AND abb.branch_party_id = aba.bank_branch_id
AND aba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID,aba.bank_account_id)
AND aba.bank_account_id = sh.bank_account_id
AND sr.status_flag(+) = 'M'
AND sr.current_record_flag(+) = 'Y'
AND sr.statement_line_id(+) = sl.statement_line_id
AND sl.statement_header_id = sh.statement_header_id
SELECT ACA.ATTRIBUTE10 ORG,
CBA.BANK_ACCOUNT_NAME,
GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6 GL_CODE,
ACA.CHECK_NUMBER,
NVL(ACA.CHECK_VOUCHER_NUM, TO_CHAR(ACA.DOC_SEQUENCE_VALUE)) VCH,
ACA.CHECK_DATE,
ACA.AMOUNT,
CLEARED_DATE,
ACA.STATUS_LOOKUP_CODE
FROM AP_CHECKS_ALL ACA,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_BANK_ACCOUNTS CBA,
GL_CODE_COMBINATIONS GCC
WHERE ACA.CE_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND CBA.CASH_CLEARING_CCID = GCC.CODE_COMBINATION_ID
and cba.BANK_ACCOUNT_NAME = NVL (:BANK_ACCOUNT_NAME, cba.BANK_ACCOUNT_NAME)
and ACA.CHECK_DATE BETWEEN '01-DEC-2013' AND '24-MAR-2014'
--and aca.STATUS_LOOKUP_CODE ='RECONCILED'
ORDER BY
ACA.CHECK_ID;
=========================================
CREATE OR REPLACE FUNCTION BPIL_STATEMENT_LINE(P_RECEIPT_ID IN NUMBER)
RETURN NUMBER
IS
L_LINE_ID NUMBER;
BEGIN
BEGIN
SELECT CSL.STATEMENT_LINE_ID
INTO L_LINE_ID
FROM AR_CASH_RECEIPTS_ALL ACR,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_STATEMENT_HEADERS CSA,
CE_STATEMENT_LINES CSL
WHERE CBAUA.BANK_ACCT_USE_ID = ACR.REMIT_BANK_ACCT_USE_ID
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND ACR.CASH_RECEIPT_ID=P_RECEIPT_ID--230886
AND CSA.STATEMENT_HEADER_ID =CSL.STATEMENT_HEADER_ID
AND CSA.BANK_ACCOUNT_ID = CBAUA.BANK_ACCOUNT_ID
AND CSL.BANK_TRX_NUMBER = ACR.RECEIPT_NUMBER;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END;
RETURN L_LINE_ID;
END;
RETURN L_LINE_ID;
END;
==============================================================
CREATE OR REPLACE FUNCTION BPIL_STATEMENT_LINE(P_RECEIPT_ID IN NUMBER)
RETURN NUMBER
IS
L_LINE_ID NUMBER;
BEGIN
BEGIN
SELECT CSL.STATEMENT_LINE_ID
INTO L_LINE_ID
FROM AR_CASH_RECEIPTS_ALL ACR,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_STATEMENT_HEADERS CSA,
CE_STATEMENT_LINES CSL
WHERE CBAUA.BANK_ACCT_USE_ID = ACR.REMIT_BANK_ACCT_USE_ID
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND ACR.CASH_RECEIPT_ID=P_RECEIPT_ID--230886
AND CSA.STATEMENT_HEADER_ID =CSL.STATEMENT_HEADER_ID
AND CSA.BANK_ACCOUNT_ID = CBAUA.BANK_ACCOUNT_ID
AND CSL.BANK_TRX_NUMBER = ACR.RECEIPT_NUMBER;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END;
RETURN L_LINE_ID;
END;
RETURN L_LINE_ID;
END;
==============================================================
Joins between Cash Management Tables
================================================
FROM ce_bank_accounts cba,
ce_bank_acct_uses_ou remit_bank,
zx_rates_b vat,
zx_accounts accounts,
hz_cust_accounts cust,
hz_parties party,
ar_receipt_methods rec_method,
ar_receipt_classes rc,
hz_cust_site_uses_all site_uses,
ar_lookups crh_note_status,
ar_lookups l_rev_cat,
ar_lookups l_rev_reason,
ar_lookups l_ref_type,
gl_daily_conversion_types gl_dct,
ar_cash_receipt_history_all crh_rem,
ar_batches_all rem_bat,
ar_receivables_trx_all rec_trx,
ar_distribution_sets_all dist_set,
ar_payment_schedules_all ps,
ar_cash_receipt_history_all crh_current,
ar_batches_all bat,
ar_batches_all bat_br,
ar_cash_receipts cr,
ar_cash_receipt_history_all crh_first_posted,
hz_parties notesbranchparty,
hz_parties notesbankparty,
hz_relationships notesbrrel,
ce_bank_branches_v bb,
iby_ext_bank_accounts_v eba
WHERE cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND crh_note_status.lookup_type(+) = 'AR_NOTE_STATUS'
AND crh_note_status.lookup_code(+) = crh_current.note_status
AND remit_bank.bank_acct_use_id(+) = cr.remit_bank_acct_use_id
AND remit_bank.org_id(+) = cr.org_id
AND vat.tax_rate_id(+) = cr.vat_tax_id
AND accounts.tax_account_entity_id(+) = cr.vat_tax_id
AND accounts.tax_account_entity_code(+) = 'RATES'
AND cr.receipt_method_id = rec_method.receipt_method_id
AND rec_method.receipt_class_id = rc.receipt_class_id
AND cr.customer_site_use_id = site_uses.site_use_id(+)
AND cr.org_id = site_uses.org_id(+)
AND cr.receivables_trx_id = rec_trx.receivables_trx_id(+)
AND cr.org_id = rec_trx.org_id(+)
AND cr.distribution_set_id = dist_set.distribution_set_id(+)
AND cr.org_id = dist_set.org_id(+)
AND l_rev_cat.lookup_type(+) = 'REVERSAL_CATEGORY_TYPE'
AND l_rev_cat.lookup_code(+) = cr.reversal_category
AND l_rev_reason.lookup_type(+) = 'CKAJST_REASON'
AND l_rev_reason.lookup_code(+) = cr.reversal_reason_code
AND l_ref_type.lookup_code(+) = cr.reference_type
AND l_ref_type.lookup_type(+) = 'CB_REFERENCE_TYPE'
AND gl_dct.conversion_type(+) = cr.exchange_rate_type
AND crh_rem.cash_receipt_id(+) = cr.cash_receipt_id
AND crh_rem.org_id(+) = cr.org_id
AND NOT EXISTS (
SELECT /*+ INDEX(CRH3 AR_CASH_RECEIPT_HISTORY_N1) */
cash_receipt_history_id
FROM ar_cash_receipt_history_all crh3
WHERE crh3.status = 'REMITTED'
AND crh3.cash_receipt_id = cr.cash_receipt_id
AND crh3.cash_receipt_history_id <
crh_rem.cash_receipt_history_id)
AND crh_rem.status(+) = 'REMITTED'
AND crh_rem.batch_id = rem_bat.batch_id(+)
AND crh_rem.org_id = rem_bat.org_id(+)
AND rem_bat.TYPE(+) = 'REMITTANCE'
AND ps.cash_receipt_id(+) = cr.cash_receipt_id
AND ps.org_id(+) = cr.org_id
AND crh_current.cash_receipt_id = cr.cash_receipt_id
AND crh_current.org_id = cr.org_id
AND crh_current.current_record_flag = NVL ('Y', cr.receipt_number)
AND crh_first_posted.batch_id = bat.batch_id(+)
AND crh_first_posted.org_id = bat.org_id(+)
AND bat.TYPE(+) = 'MANUAL'
AND crh_first_posted.cash_receipt_id(+) = cr.cash_receipt_id
AND crh_first_posted.org_id(+) = cr.org_id
AND crh_first_posted.first_posted_record_flag(+) = 'Y'
AND crh_first_posted.batch_id = bat_br.batch_id(+)
AND crh_first_posted.org_id = bat_br.org_id(+)
AND bat_br.TYPE(+) = 'BR'
AND notesbranchparty.party_id(+) = cr.issuer_bank_branch_id
AND notesbrrel.object_id = notesbankparty.party_id(+)
AND notesbrrel.subject_id(+) = notesbranchparty.party_id
AND notesbrrel.relationship_type(+) = 'BANK_AND_BRANCH'
AND notesbrrel.relationship_code(+) = 'BRANCH_OF'
AND notesbrrel.subject_table_name(+) = 'HZ_PARTIES'
AND notesbrrel.subject_type(+) = 'ORGANIZATION'
AND notesbrrel.object_table_name(+) = 'HZ_PARTIES'
AND notesbrrel.object_type(+) = 'ORGANIZATION'
AND remit_bank.bank_account_id = cba.bank_account_id(+)
AND bb.branch_party_id(+) = cba.bank_branch_id
AND eba.ext_bank_account_id(+) = cr.customer_bank_account_id
AND cr.org_id = accounts.internal_organization_id(+) /* bug 6804246 */
select gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4,gcc.segment5,gcc.segment6,acrh.*
from xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc,
ar_cash_receipt_history_all acrh
where xah.ae_header_id = xal.ae_header_id
and xah.event_id =acrh.event_id
and acrh.account_code_combination_id=gcc.code_combination_id
and xal.code_combination_id = gcc.code_combination_id
-- and gcc.code_combination_id= 769593
and acrh.cash_receipt_id =1111645
SELECT *
FROM xla_ae_headers xah, xla_ae_lines xal
-- gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND xal.accounting_class_code IN ('BANK_CHG', 'UNAPP')
AND xah.je_category_name = 'Receipts'
-- and xal.code_combination_id = gcc.code_combination_id
-- AND xal.code_combination_id = 8221
AND xah.application_id = 222
AND xah.application_id = xal.application_id
AND xah.period_name IN ('JAN-13-14','FEB-13-14','MAR-13-14','APR-13-14',
'MAY-13-14','JUN-13-14','JUL-13-14','AUG-13-14',
'SEP-13-14','OCT-13-14','NOV-14-14','DEC-13-14')
AND xal.code_combination_id IN (7346, 8257, 8251, 7595, 8221, 8215);
FROM ce_bank_accounts cba,
ce_bank_acct_uses_ou remit_bank,
zx_rates_b vat,
zx_accounts accounts,
hz_cust_accounts cust,
hz_parties party,
ar_receipt_methods rec_method,
ar_receipt_classes rc,
hz_cust_site_uses_all site_uses,
ar_lookups crh_note_status,
ar_lookups l_rev_cat,
ar_lookups l_rev_reason,
ar_lookups l_ref_type,
gl_daily_conversion_types gl_dct,
ar_cash_receipt_history_all crh_rem,
ar_batches_all rem_bat,
ar_receivables_trx_all rec_trx,
ar_distribution_sets_all dist_set,
ar_payment_schedules_all ps,
ar_cash_receipt_history_all crh_current,
ar_batches_all bat,
ar_batches_all bat_br,
ar_cash_receipts cr,
ar_cash_receipt_history_all crh_first_posted,
hz_parties notesbranchparty,
hz_parties notesbankparty,
hz_relationships notesbrrel,
ce_bank_branches_v bb,
iby_ext_bank_accounts_v eba
WHERE cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND crh_note_status.lookup_type(+) = 'AR_NOTE_STATUS'
AND crh_note_status.lookup_code(+) = crh_current.note_status
AND remit_bank.bank_acct_use_id(+) = cr.remit_bank_acct_use_id
AND remit_bank.org_id(+) = cr.org_id
AND vat.tax_rate_id(+) = cr.vat_tax_id
AND accounts.tax_account_entity_id(+) = cr.vat_tax_id
AND accounts.tax_account_entity_code(+) = 'RATES'
AND cr.receipt_method_id = rec_method.receipt_method_id
AND rec_method.receipt_class_id = rc.receipt_class_id
AND cr.customer_site_use_id = site_uses.site_use_id(+)
AND cr.org_id = site_uses.org_id(+)
AND cr.receivables_trx_id = rec_trx.receivables_trx_id(+)
AND cr.org_id = rec_trx.org_id(+)
AND cr.distribution_set_id = dist_set.distribution_set_id(+)
AND cr.org_id = dist_set.org_id(+)
AND l_rev_cat.lookup_type(+) = 'REVERSAL_CATEGORY_TYPE'
AND l_rev_cat.lookup_code(+) = cr.reversal_category
AND l_rev_reason.lookup_type(+) = 'CKAJST_REASON'
AND l_rev_reason.lookup_code(+) = cr.reversal_reason_code
AND l_ref_type.lookup_code(+) = cr.reference_type
AND l_ref_type.lookup_type(+) = 'CB_REFERENCE_TYPE'
AND gl_dct.conversion_type(+) = cr.exchange_rate_type
AND crh_rem.cash_receipt_id(+) = cr.cash_receipt_id
AND crh_rem.org_id(+) = cr.org_id
AND NOT EXISTS (
SELECT /*+ INDEX(CRH3 AR_CASH_RECEIPT_HISTORY_N1) */
cash_receipt_history_id
FROM ar_cash_receipt_history_all crh3
WHERE crh3.status = 'REMITTED'
AND crh3.cash_receipt_id = cr.cash_receipt_id
AND crh3.cash_receipt_history_id <
crh_rem.cash_receipt_history_id)
AND crh_rem.status(+) = 'REMITTED'
AND crh_rem.batch_id = rem_bat.batch_id(+)
AND crh_rem.org_id = rem_bat.org_id(+)
AND rem_bat.TYPE(+) = 'REMITTANCE'
AND ps.cash_receipt_id(+) = cr.cash_receipt_id
AND ps.org_id(+) = cr.org_id
AND crh_current.cash_receipt_id = cr.cash_receipt_id
AND crh_current.org_id = cr.org_id
AND crh_current.current_record_flag = NVL ('Y', cr.receipt_number)
AND crh_first_posted.batch_id = bat.batch_id(+)
AND crh_first_posted.org_id = bat.org_id(+)
AND bat.TYPE(+) = 'MANUAL'
AND crh_first_posted.cash_receipt_id(+) = cr.cash_receipt_id
AND crh_first_posted.org_id(+) = cr.org_id
AND crh_first_posted.first_posted_record_flag(+) = 'Y'
AND crh_first_posted.batch_id = bat_br.batch_id(+)
AND crh_first_posted.org_id = bat_br.org_id(+)
AND bat_br.TYPE(+) = 'BR'
AND notesbranchparty.party_id(+) = cr.issuer_bank_branch_id
AND notesbrrel.object_id = notesbankparty.party_id(+)
AND notesbrrel.subject_id(+) = notesbranchparty.party_id
AND notesbrrel.relationship_type(+) = 'BANK_AND_BRANCH'
AND notesbrrel.relationship_code(+) = 'BRANCH_OF'
AND notesbrrel.subject_table_name(+) = 'HZ_PARTIES'
AND notesbrrel.subject_type(+) = 'ORGANIZATION'
AND notesbrrel.object_table_name(+) = 'HZ_PARTIES'
AND notesbrrel.object_type(+) = 'ORGANIZATION'
AND remit_bank.bank_account_id = cba.bank_account_id(+)
AND bb.branch_party_id(+) = cba.bank_branch_id
AND eba.ext_bank_account_id(+) = cr.customer_bank_account_id
AND cr.org_id = accounts.internal_organization_id(+) /* bug 6804246 */
select gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4,gcc.segment5,gcc.segment6,acrh.*
from xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc,
ar_cash_receipt_history_all acrh
where xah.ae_header_id = xal.ae_header_id
and xah.event_id =acrh.event_id
and acrh.account_code_combination_id=gcc.code_combination_id
and xal.code_combination_id = gcc.code_combination_id
-- and gcc.code_combination_id= 769593
and acrh.cash_receipt_id =1111645
SELECT *
FROM xla_ae_headers xah, xla_ae_lines xal
-- gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND xal.accounting_class_code IN ('BANK_CHG', 'UNAPP')
AND xah.je_category_name = 'Receipts'
-- and xal.code_combination_id = gcc.code_combination_id
-- AND xal.code_combination_id = 8221
AND xah.application_id = 222
AND xah.application_id = xal.application_id
AND xah.period_name IN ('JAN-13-14','FEB-13-14','MAR-13-14','APR-13-14',
'MAY-13-14','JUN-13-14','JUL-13-14','AUG-13-14',
'SEP-13-14','OCT-13-14','NOV-14-14','DEC-13-14')
AND xal.code_combination_id IN (7346, 8257, 8251, 7595, 8221, 8215);
