Oracle Apps R12 Techno - Functional: December 2016

Sunday, 18 December 2016

India - Creditor Trial Balance Report in Query View

Main View:
APPS.XXCUSTOM_CREDITOR_TRIAL_V

Inner View:
APPS.XXCUSTOM_CREDITOR_TRIAL

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

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

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

India - Debtors Trial Balance Report of Query View

India Debtor Trial Balance Query view:-
******************************************************************
select --*
SUM(AMOUNT)
from xx_debtor_trial_v
WHERE ORG_ID = 82
AND CUSTOMER_ID = 3064

******************************************************************
CREATE OR REPLACE FORCE VIEW APPS.XX_DEBTOR_TRIAL_V
(
AMOUNT,
OTHER_CURRENCY_AMOUNT,
CUSTOMER_ID,
CUSTOMER_NAME,
TRX_DATE,
ORG_ID
)
AS
SELECT SUM (AMOUNT), -- SUM(decode(nvl(exchange_rate,1),1,0,AMOUNT_OTHER_CURRENCY)) 10090032 25NOV
SUM (AMOUNT_OTHER_CURRENCY),
A.CUSTOMER_ID,
A.customer_name,
A.trx_date,
A.ORG_ID
-- INTO v_tran_tot_amt, v_func_tot_amt
FROM (SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
d.gl_date,
B.CUSTOMER_TRX_ID,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
NULL receipt_number,
NULL receipt_date,
SUBSTR (b.comments, 1, 50) remarks,
d.code_combination_id account_id,
b.invoice_currency_code currency_code,
b.exchange_rate,
d.amount amount,
(d.amount * NVL (b.exchange_rate, 1)) amount_other_currency,
f.TYPE,
b.customer_trx_id,
d.customer_trx_line_id,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_ALL B,
RA_CUST_TRX_LINE_GL_DIST_ALL D,
GL_CODE_COMBINATIONS E,
RA_CUST_TRX_TYPES_ALL F,
ar_payment_schedules_all G
WHERE a.party_id = hzca.party_id
AND b.bill_to_customer_id = hzca.cust_account_id
AND b.complete_flag = 'Y'
AND d.customer_trx_id = b.customer_trx_id
AND d.account_class = 'REC'
AND e.code_combination_id = d.code_combination_id
AND f.cust_trx_type_id = b.cust_trx_type_id
AND f.TYPE IN ('INV', 'CM', 'DM', 'DEP')
AND d.latest_rec_flag = 'Y'
AND g.customer_trx_id = b.customer_trx_id
-- AND b.ORG_ID = :P_ORG_ID
--AND trunc(b.trx_date) between trunc(p_start_date) AND trunc(:p_end_date)
-- AND TRUNC (G.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND g.payment_schedule_id IN
(SELECT MIN (payment_schedule_id)
FROM ar_payment_schedules_all
WHERE customer_trx_id = g.customer_trx_id)
UNION
-- Following Query for Cash receipts
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
e.gl_date,
0,
NULL,
NULL,
b.receipt_number,
TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
NULL,
d.cash_ccid account_id,
b.currency_code,
b.exchange_rate,
b.amount * -1 amount, --bug10090032 29nov
(b.amount * -1 * NVL (b.exchange_rate, 1))
amount_other_currency, --bug10090032 29nov
'REC' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ar_cash_receipts_all B,
gl_code_combinations C,
ar_receipt_method_accounts_all D,
ar_cash_receipt_history_all E,
ar_payment_schedules_all F
/*COMMENTED for bug#10090032
(SELECT MIN(acrh1.cash_receipt_history_id) cash_receipt_history_id,
acrh1.cash_receipt_id
FROM ar_cash_receipt_history_all acrh1
WHERE TRUNC(acrh1.gl_date) < TRUNC(p_start_date)
AND acrh1.status <> 'REVERSED'
AND NOT EXISTS (select '1' from ar_cash_receipt_history_all acrh2 where acrh2.cash_receipt_id = acrh1.cash_receipt_id and trunc(acrh2.gl_date) < trunc((p_start_date)) )

AND acrh1.P_ORG_ID = P_ORGANIZATION_ID
GROUP BY acrh1.cash_receipt_id) G*/
--Added the above inline view by sanjikum for Bug # 3962497
WHERE a.party_id = hzca.party_id
AND b.pay_from_customer = hzca.cust_account_id
AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
AND d.receipt_method_id = b.receipt_method_id
AND d.cash_ccid = c.code_combination_id
AND e.cash_receipt_id = b.cash_receipt_id
--AND e.cash_receipt_id = g.cash_receipt_id
--AND e.cash_receipt_history_id = g.cash_receipt_history_id
--start additions for bug#10090032
AND e.cash_receipt_history_id IN
(SELECT MIN (incrh.cash_receipt_history_id)
FROM ar_cash_receipt_history_all incrh
WHERE incrh.cash_receipt_id =
b.cash_receipt_id
AND incrh.status <> 'REVERSED')
--added the above 2 conditions by sanjikum for Bug # 3962497
AND f.cash_receipt_id = b.cash_receipt_id
-- AND b.ORG_ID = :P_ORG_ID
--AND e.current_record_flag = 'Y' --commented by sanjikum for Bug # 3962497
-- AND TRUNC (e.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
UNION
-- Following Query for Receipt WriteOff
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
--e.gl_date , 10137368
g.gl_date, --bug#10137368
0,
NULL,
NULL,
b.receipt_number,
TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
NULL,
d.cash_ccid account_id,
b.currency_code,
b.exchange_rate,
g.amount_applied amount,
(g.amount_applied * NVL (b.exchange_rate, 1))
amount_other_currency,
'W/O' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ar_cash_receipts_all B,
gl_code_combinations C,
ar_receipt_method_accounts_all D,
ar_cash_receipt_history_all E,
ar_payment_schedules_all F,
ar_receivable_applications_all G
WHERE a.party_id = hzca.party_id
AND b.pay_from_customer = hzca.cust_account_id
AND g.applied_payment_schedule_id = -3
AND g.cash_receipt_id = b.cash_receipt_id
AND g.cash_receipt_history_id = e.cash_receipt_history_id
AND g.status = 'ACTIVITY'
AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
AND d.receipt_method_id = b.receipt_method_id
AND d.cash_ccid = c.code_combination_id
AND e.cash_receipt_id = b.cash_receipt_id
AND f.cash_receipt_id = b.cash_receipt_id
-- AND b.ORG_ID = :P_ORG_ID
--AND trunc(e.gl_date) < trunc(p_start_date)
--commented and added the below condition for --bug#10137368
-- AND TRUNC (g.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND NOT EXISTS
(SELECT 1
FROM ar_cash_receipt_history_all
WHERE cash_receipt_id = b.cash_receipt_id
AND status = 'REVERSED')
UNION
-- Following Query for Receipt Reversal
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
e.gl_date gl_date,
0,
NULL,
TO_CHAR (e.trx_date, 'DD-MM-YYYY') trx_date,
b.receipt_number,
TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
NULL,
c.code_combination_id account_id,
b.currency_code,
b.exchange_rate,
b.amount amount,
(b.amount * NVL (b.exchange_rate, 1)) amount_other_currency,
'REV' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ar_cash_receipts_all B,
gl_code_combinations C,
ar_receipt_method_accounts_all D,
ar_cash_receipt_history_all E,
ar_payment_schedules_all F
WHERE a.party_id = hzca.party_id
AND b.pay_from_customer = hzca.cust_account_id
AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
AND d.receipt_method_id = b.receipt_method_id
AND d.cash_ccid = c.code_combination_id
AND e.cash_receipt_id = b.cash_receipt_id
AND f.cash_receipt_id = b.cash_receipt_id
-- AND b.ORG_ID = :P_ORG_ID
AND e.status = 'REVERSED'
--AND trunc(b.reversal_date) < trunc(p_start_date)
-- AND TRUNC (e.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND b.reversal_date IS NOT NULL -- SSUMAITH
UNION
-- Following Query for Adjustments
SELECT HZCA.CUST_ACCOUNT_ID CUSTOMER_ID,
hzca.account_number /*A.PARTY_NUMBER*/
CUSTOMER_NUMBER, /*Commented by nprashar for bug # 7256288*/
A.PARTY_NAME CUSTOMER_NAME,
B.GL_DATE,
0,
B.ADJUSTMENT_NUMBER,
TO_CHAR (B.APPLY_DATE, 'DD-MM-YYYY') trx_date,
NULL receipt_number,
NULL receipt_date,
SUBSTR (b.comments, 1, 50) remarks,
b.code_combination_id account_id,
c.invoice_currency_code currency_code,
c.exchange_rate,
b.amount amount,
(b.amount * NVL (c.exchange_rate, 1)) amount_other_currency,
'ADJ' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM HZ_PARTIES A,
HZ_CUST_ACCOUNTS HZCA,
ar_adjustments_all b,
ra_customer_trx_all c,
ar_payment_schedules_all d,
gl_code_combinations e
WHERE b.customer_trx_id = c.customer_trx_id
AND a.party_id = hzca.party_id
AND c.bill_to_customer_id = hzca.cust_account_id
AND b.status = 'A'
AND e.code_combination_id = b.code_combination_id
AND b.payment_schedule_id = d.payment_schedule_id
AND b.customer_trx_id = d.customer_trx_id
-- AND c.ORG_ID = :P_ORG_ID
--AND trunc(b.apply_date) < trunc(p_start_date)
-- AND TRUNC (b.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND b.customer_trx_id NOT IN
(SELECT customer_trx_id
FROM ar_adjustments_all
WHERE CHARGEBACK_CUSTOMER_TRX_ID IS NOT NULL
OR created_from LIKE 'REVERSE%') /*added by abezgam for bug#10228717*/
UNION
-- -- Following Query for Discounts
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
d.gl_date,
B.CUSTOMER_TRX_ID,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
NULL receipt_number,
NULL receipt_date,
SUBSTR (b.comments, 1, 50) remarks,
earned_discount_ccid account_id,
b.invoice_currency_code currency_code,
b.exchange_rate,
d.EARNED_discount_taken * -1 amount, --ADDED *-1 FOR BUG#10090032
d.ACCTD_EARNED_DISCOUNT_TAKEN * -1 amount_other_currency, --ADDED *-1 FOR BUG#10090032
'DSC' TYPE,
b.customer_trx_id,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_all B,
ar_receivable_applications_all D
WHERE a.party_id = hzca.party_id
AND b.bill_to_customer_id = hzca.cust_account_id
AND b.complete_flag = 'Y'
AND D.EARNED_DISCOUNT_TAKEN IS NOT NULL
AND D.EARNED_DISCOUNT_TAKEN <> 0
-- AND b.ORG_ID = :P_ORG_ID
AND b.customer_trx_id = d.applied_customer_trx_id
AND d.application_type = 'CASH'
AND d.display = 'Y'
-- AND TRUNC (d.GL_DATE) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
UNION ALL
-- Following Query for Exchange Gain and Loss
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
d.gl_date,
b.customer_trx_id,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
c.receipt_number,
TO_CHAR (c.receipt_date, 'DD-MM-yyyy') receipt_date,
DECODE (e.amount_dr, NULL, 'CR', 'DR') comments,
e.code_combination_id,
b.INVOICE_CURRENCY_CODE,
b.exchange_rate,
NVL (e.AMOUNT_DR * -1, e.AMOUNT_CR) amount, --ADDED *-1 FOR BUG#10090032
NVL (e.ACCTD_AMOUNT_DR * -1, e.ACCTD_AMOUNT_CR)
acctd_amount, --ADDED *-1 FOR BUG#10090032
e.source_type,
0 customer_trx_id,
0 customer_trx_line_id,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_all b,
ar_cash_receipts_all c,
ar_receivable_applications_all d,
ar_distributions_all e
WHERE a.party_id = hzca.party_id
AND hzca.cust_account_id = b.BILL_TO_CUSTOMER_ID
AND b.customer_trx_id = d.APPLIED_CUSTOMER_TRX_ID
AND c.cash_receipt_id = d.cash_receipt_id
AND e.SOURCE_ID = d.receivable_application_id
-- AND b.ORG_ID = :P_ORG_ID
AND e.source_Type IN ('EXCH_LOSS', 'EXCH_GAIN')
--AND TRUNC(c.receipt_date) < trunc(p_start_date)
-- AND TRUNC (d.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
UNION ALL
-- Following Query for Exchange Gain and Loss for CM applied to the invoice
/**ADDED for bug#10090032*/
SELECT hzca.cust_account_id customer_id,
hzca.account_number customer_number,
a.party_name customer_name,
d.gl_date,
b.customer_trx_id,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
NULL,
NULL,
DECODE (e.amount_dr, NULL, 'CR', 'DR') comments,
e.code_combination_id,
b.INVOICE_CURRENCY_CODE,
b.exchange_rate,
NVL (e.AMOUNT_DR * -1, e.AMOUNT_CR) amount, --ADDED *-1 FOR BUG#10090032
NVL (e.ACCTD_AMOUNT_DR * -1, e.ACCTD_AMOUNT_CR)
acctd_amount, --ADDED *-1 FOR BUG#10090032
e.source_type,
0 customer_trx_id,
0 customer_trx_line_id,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_all b,
ar_payment_schedules_all c,
ar_receivable_applications_all d,
ar_distributions_all e
WHERE a.party_id = hzca.party_id
AND hzca.cust_account_id = b.BILL_TO_CUSTOMER_ID
AND b.customer_trx_id = d.APPLIED_CUSTOMER_TRX_ID
AND c.payment_schedule_id = d.payment_schedule_id
AND c.class = 'CM'
AND e.SOURCE_ID = d.receivable_application_id
--AND b.ORG_ID = :P_ORG_ID
AND e.source_Type IN ('EXCH_LOSS', 'EXCH_GAIN')
-- AND TRUNC (d.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
ORDER BY 1, 4) a
GROUP BY A.CUSTOMER_ID, A.customer_name, A.trx_date, ORG_ID

Sunday, 11 December 2016

OAF Patch Details for Different version of Oracle Apps R12

OA Framework - How to Find the Correct Version of JDeveloper for OA Extensions to Use with E-Business Suite 11i or Release 12.x (Doc ID 416708.1)

JDevc for EBS R12.2.5

R12.2.3

OA Framework - How to Find the Correct Version of JDeveloper for OA Extensions to Use with E-Business Suite 11i or Release 12.x (Doc ID 416708.1)

To Bottom

This document lists Oracle JDeveloper patches that are needed for the corresponding Oracle E-Business Suite releases. To use Oracle JDeveloper with Oracle E-Business Suite, you must apply any patches that are relevant.

In This Document

The most current version of this document can be obtained in My Oracle Support Knowledge Document 416708.1. Refer to Section 4 for availability of translated versions.
There is a change log at the end of this document.

Section 1: Using This Document

When you create extensions to OA Framework-based pages in Oracle E-Business Suite, you must use the version of Oracle JDeveloper shipped by the Oracle E-Business Suite product development team. The version of Oracle JDeveloper is specific to the Oracle E-Business Suite Applications Technology patch level, so there is a new version of Oracle JDeveloper with each new release of the Oracle E-Business Suite Applications Technology patchset.
To find the correct Oracle JDeveloper patches for creating OA Extensions with Oracle E-Business Suite Release 12.x or Release 11i, follow these steps:

Identify the OA Framework version in your instance by setting the FND: Diagnostics / FND_DIAGNOSTICS profile option to activate Diagnostics
Select the "About This Page" link from any OA Framework page.
Select the "Technology Components" tab and make a note of the OA Framework version in the first row of the table, matching it to the Oracle JDeveloper Patch shown in the appropriate table from those listed in Section 2 below.

Note: The Oracle JDeveloper patches described in this document are intended for creating OA Extensions to OA Framework-based pages in Oracle E-Business Suite Release 11i and Release 12.x. If you are building other types of application extensions, such as web services, refer to the documentation resources of those technologies to identify the correct version of JDeveloper to use.

Section 2: Finding Oracle JDeveloper Patches For Oracle E-Business Suite

The following tables list the Oracle JDeveloper patches required for use with the corresponding versions of Oracle E-Business Suite.

Note: Oracle E-Business Suite works with all three Windows 10 servicing branches:

Current Branch (CB)
Current Branch for Business (CBB)
Long Term Servicing Branch (LTSB)

Release 12.2

ATG Release 12.2 Version	Oracle JDeveloper 10g Patches
12.2.6	Patch 24611686 JDeveloper 10g with OA Extension ARU for R12.2.6+, certified on Windows 7, Windows 8.1, Windows 10 and Windows 2012 Server, and Linux. Preferred web browser is Microsoft Internet Explorer 11.0 or above. Patch 24555396 JDeveloper 10g with OA Extension ARU for R12.2.6, certified on Windows 7, Windows 8.1 and Windows 2012 Server, and Linux. Preferred web browser is Microsoft Internet Explorer 11.0 or above. Known issues: Bug 24707646 (Linux) Bug 21458761 (Windows 10)
12.2.5	Patch 22064122 JDeveloper 10g with OA Extension ARU for R12.2.5 Consolidated Bundle 1, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 9.0 or above. Patch 21662342 JDeveloper 10g with OA Extension ARU for R12.2.5, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 9.0 or above.
12.2.4	Patch 19170592 JDeveloper 10g with OA Extension ARU for R12.2.4, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 8.0 or above.
12.2.3	Patch 17888411 JDeveloper 10g with OA Extension ARU for R12.2.3, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 8.0 or above for Skyros Look-and-Feel, or Internet Explorer 6.0 or above for Swan Look-and-Feel.
12.2	Patch 17513160 JDeveloper 10g with OA Extension ARU for R12.2, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 6.0 or above.

Release 12.1

ATG Release 12.1 Version	Oracle JDeveloper 10g Patch
12.1 (Controlled Release - only included for completeness)	Patch 7315332 JDeveloper 10g with OA Extension ARU for R12.1 (Controlled Release)
12.1.1 (Rapid Install, or Patch 7303030)	Patch 8431482 JDeveloper 10g with OA Extension ARU for R12.1.1
12.1.2 (Patch 7303033 or 7651091)	Patch 9172975 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1.2
12.1.3 (Patch 9239090 or 8919491)	Patch 9879989 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1.3
12.1.3.1 (Patch 11894708)	Patch 9879989 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1.3
12.1.3.2 (Patch 15880118)	Patch 9879989 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1.3
12.1 RUP4 (Patch 18936791)	Patch 22501192 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1 RUP4
12.1 RUP5 (Patch 22284589)	Patch 23111832 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1 RUP5

Release 12.0

ATG Release 12 Version	Oracle JDeveloper 10g Patch
12.0.0	Patch 5856648 JDeveloper 10g with OA Extension
12.0.1 (Patch 5907545)	Patch 5856648 JDeveloper 10g with OA Extension
12.0.2 (Patch 5484000 or 5917344)	Patch 6491398 JDeveloper 10g with OA Extension ARU for R12 RUP2 (replaces 6197418)
12.0.3 (Patch 6141000 or 6077669)	Patch 6509325 JDeveloper 10g with OA Extension ARU for R12 RUP3
12.0.4 (Patch 6435000 or 6272680)	Patch 6908968 JDeveloper 10g WITH OA EXTENSION ARU FOR R12 RUP4
12.0.5 (No new ATG code released)	(No new JDeveloper patch required)
12.0.6 (Patch 6728000 or 7237006)	Patch 7523554 JDeveloper 10g With OA Extension ARU for R12 RUP6

Release 11i

Date	Description
OA Framework 5.10 patch	Oracle JDeveloper 9i Patch
ATG.PF.H (Patch 3438354 or Oracle Applications 11.5.10)	Patch 4045639 JDeveloper 9i WITH OA EXTENSION ARU FOR FWK.H
ATG PF CU1 (Patch 4017300)	Patch 4141787 JDeveloper 9i WITH OA EXTENSION ARU FOR CU1
ATG PF CU2 (Patch 4125550)	Patch 4573517 JDeveloper 9i with OA Extension for 11.5.10 CU2
11i.ATG_PF.H RUP3 (Patch 4334965)	Patch 4725670 JDeveloper 9i WITH OA EXTENSION ARU FOR 11i10 RUP3
11i.ATG_PF.H RUP4 (Patch 4676589)	Patch 5455514 JDeveloper 9i WITH OA EXTENSION ARU FOR 11i10 RUP4
11i.ATG_PF.H RUP5 (Patch 5473858)	Patch 6012619 JDeveloper 9i With OA Extension ARU FOR 11i10 RUP5
11i.ATG_PF.H.RUP6 (Patch 5903765)	Patch 6739235 JDeveloper 9i With OA Extension ARU FOR 11i10 RUP6 ~~Patch 6469392 JDeveloper 9i WITH OA EXTENSION ARU FOR 11I10 RUP6~~
11i.ATG_PF.H.delta.7 (Patch 6241631)	Patch 8751878 JDeveloper 9i WITH OA EXTENSION ARU FOR 11I RUP7

Oracle Apps R12 Techno - Functional