pardeshirahul
asked on
How to write the case statement
I have a following procedure
APPS.CCL_VAT_TAX_code_fluc tuation (
ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_date_type VARCHAR2,
p_date_from date,
p_date_to date
)
i have a select statement
base on the p_date_type
if it is 'INVOICE DATE' then
the filter should be
( ct.trx_date between nvl(p__date_from , ct.trx_date )
AND nvl(p_date_to , ct.trx_date )
and when the p_date_type= 'RECEIPT DATE'
the the filter should be
then ( cr.receipt_date between nvl(p__date_from , cr.receipt_date )
AND nvl(p_date_to , cr.receipt_date )
how to write the case statement
APPS.CCL_VAT_TAX_code_fluc
ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_date_type VARCHAR2,
p_date_from date,
p_date_to date
)
i have a select statement
base on the p_date_type
if it is 'INVOICE DATE' then
the filter should be
( ct.trx_date between nvl(p__date_from , ct.trx_date )
AND nvl(p_date_to , ct.trx_date )
and when the p_date_type= 'RECEIPT DATE'
the the filter should be
then ( cr.receipt_date between nvl(p__date_from , cr.receipt_date )
AND nvl(p_date_to , cr.receipt_date )
how to write the case statement
You shouldn't use between when doing dates, and a case statement won't work in your scenario because you're comparing different fields that don't tie to each other.
select * from apps.CLL_VAT_TAX_code_fluc ation
where
( p_date_type = 'INVOICE DATE' and
ct.trx_date >= nvl(p__date_from , ct.trx_date )
AND ct.trx_date < nvl(p_date_to , ct.trx_date
)
OR
(
p_date_type= 'RECEIPT DATE' AND
cr.receipt_date >= nvl(p__date_from , cr.receipt_date )
AND cr.receipt_date < nvl(p_date_to , cr.receipt_date )
)
select * from apps.CLL_VAT_TAX_code_fluc
where
( p_date_type = 'INVOICE DATE' and
ct.trx_date >= nvl(p__date_from , ct.trx_date )
AND ct.trx_date < nvl(p_date_to , ct.trx_date
)
OR
(
p_date_type= 'RECEIPT DATE' AND
cr.receipt_date >= nvl(p__date_from , cr.receipt_date )
AND cr.receipt_date < nvl(p_date_to , cr.receipt_date )
)
ASKER
why it is giving missing right parenthesis
ASKER
sorry my mistake
ASKER
it is giving me ORA-00907: missing right parenthesis
ASKER
This is my query
SELECT ct.trx_number,
aou.name Operating_Unit,
ct.invoice_currency_code Functional_Currency_Code,
--ct.type Transaction_Class,
(SELECT name
FROM ra_cust_trx_types_all TYPE
WHERE TYPE.CUST_TRX_TYPE_ID = ct.CUST_TRX_TYPE_ID)
Transaction_Class,
ct.trx_number Transaction_Number,
cl.line_number Line_Number,
ct.trx_date Transaction_Date,
ps.gl_date GL_Date,
SUBSTR (HZ_COMMON_PUB.get_party_n ame (b.party_id),
1,
240)
Bill_To_Customer_Name,
SUBSTR (
HZ_COMMON_PUB.GET_CUST_ADD RESS (
bisu.cust_acct_site_id),
1,
240)
Bill_To_Customer_Address,
c.country Bill_To_Customer_Country,
NULL Bill_To_Customer_Tax_ID,
ter.name Payment_Terms,
ct.invoice_currency_code Entered_Currency,
nvl(ps.exchange_rate,1) Exchange_Rate,
NVL (cl.quantity_ordered, quantity_invoiced) Quantity,
NVL (cl.taxable_amount, cl.unit_selling_price)
Unit_Selling_Price,
NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (cl.taxable_amount, cl.unit_selling_price)
Entered_Net_Line_Sales_Amo unt,
(SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_ ID =
cl.customer_trx_line_id
AND SIGN (tax_rate) = '+1')
Tax_Rate,
NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (cl.taxable_amount, cl.unit_selling_price)
* (SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_ ID =
cl.customer_trx_line_id
AND SIGN (tax_rate) = '+1')
/ 100
Entered_Tax_Amount,
--ps.tax_original Entered_Tax_Amount,
-- NVL (cl.quantity_ordered, quantity_invoiced) * cl.unit_selling_price + ps.tax_original Entered_Gross_Line_Amount,
NVL (cl.quantity_ordered, quantity_invoiced)
* cl.unit_selling_price
+ NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (cl.taxable_amount, cl.unit_selling_price)
* (SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_ ID =
cl.customer_trx_line_id
AND SIGN (tax_rate) = '+1')
/ 100
Entered_Gross_Line_Amount,
--ps.amount_due_original Entered_Gross_Line_Amount,
NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (NVL (cl.taxable_amount, cl.unit_selling_price),
0)
*nvl( ps.exchange_rate,1)
Functional_Net_Line_Amount ,
--NVL (ps.tax_original, 0) * NVL (ps.exchange_rate, 1) Funcational_Tax_Amount,
NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (cl.taxable_amount, cl.unit_selling_price)
* (SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_ ID =
cl.customer_trx_line_id
AND SIGN (tax_rate) = '+1')
/ 100
* NVL (ps.exchange_rate, 1)
Funcational_Tax_Amount,
-- NVL (cl.quantity_ordered, quantity_invoiced) * NVL (cl.unit_selling_price, 0) * ps.exchange_rate + NVL (ps.tax_original, 0) * NVL (ps.exchange_rate, 1)
--Functional_Gross_Line_Am ount,
NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (NVL (cl.taxable_amount, cl.unit_selling_price),
0)
* nvl(ps.exchange_rate,1)
+ NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (cl.taxable_amount, cl.unit_selling_price)
* (SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_ ID =
cl.customer_trx_line_id)
/ 100
* NVL (ps.exchange_rate, 1)
Functional_Gross_Line_Amou nt,
(SELECT ra3.apply_date
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.APPLIED_CUSTOMER_TRX_L INE_ID=cl. customer_t rx_line_id
AND ROWNUM = 1)
Collected_Date,
-- ra3.apply_date Collected_Date,
(SELECT ACR.RECEIPT_NUMBER
FROM AR_RECEIVABLE_APPLICATIONS _ALL ARA,
AR_CASH_RECEIPTS_ALL ACR,
RA_CUSTOMER_TRX_ALL RCT1
WHERE ARA.STATUS = 'APP'
AND ARA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND ARA.APPLIED_CUSTOMER_TRX_I D =
RCT1.CUSTOMER_TRX_ID
AND ARA.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
AND RCT1.CUSTOMER_TRX_ID = ct.CUSTOMER_TRX_ID)
Collected_Bank_Tfr_Num,
(SELECT ACR.RECEIPT_DATE
FROM AR_RECEIVABLE_APPLICATIONS _ALL ARA,
AR_CASH_RECEIPTS_ALL ACR,
RA_CUSTOMER_TRX_ALL RCT1
WHERE ARA.STATUS = 'APP'
AND ARA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND ARA.APPLIED_CUSTOMER_TRX_I D =
RCT1.CUSTOMER_TRX_ID
AND ARA.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
AND RCT1.CUSTOMER_TRX_ID = ct.CUSTOMER_TRX_ID)
Collected_Bank_Receipt_dat e,
NVL (cl.quantity_ordered, quantity_invoiced)
* cl.unit_selling_price
Collected_Before_VAT_Orig_ Curr,
--ps.tax_original VAT_Withholding_OC,
NVL (cl.quantity_ordered, quantity_invoiced)
* cl.unit_selling_price
* (SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_ ID =
cl.customer_trx_line_id
AND SIGN (tax_rate) = '+1')
/ 100
VAT_Withholding_OC,
-- ra3.tax_applied Collected_VAT_OC,
(SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
Collected_VAT_OC,
ps.tax_original
- (SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
Other_WHT_OC,
--ra3.line_applied + ra3.tax_applied Total_OC,
(SELECT RA3.line_applied
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
+ (SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
Total_OC,
(SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1))
Exchange_Rate_at_the_recei pt,
(SELECT currency_code
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1))
currency_code_at_the_recei pt,
(SELECT RA3.line_applied
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
* nvl(ps.exchange_rate,1)/*( SELECT RA3.TRANS_TO_RECEIPT_RATE
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)*/
/** (SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1))*/
Collected_bef_VAT_Funct_Cu rr,
NVL (ps.tax_original, 0) * NVL (ps.exchange_rate, 1)
VAT_With_holding_FC,
(SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
* ( SELECT RA3.TRANS_TO_RECEIPT_RATE
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
/* * (SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1))*/
Collected_VAT_FC,
NULL Other_WHT_FC,
(SELECT RA3.line_applied
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
*( SELECT RA3.TRANS_TO_RECEIPT_RATE
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
/* * (SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1))*/
+ (SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
)
* ( SELECT RA3.TRANS_TO_RECEIPT_RATE
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
/* * (SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1))*/
Total_FC,
(SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
* ( SELECT RA3.TRANS_TO_RECEIPT_RATE
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)
/** (SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1))*/
- NVL (ps.tax_original, 0) * NVL (ps.exchange_rate, 1)
VAT_Fluctuation,
NULL Collected_VAT_PER,
cl.attribute1
|| '-'
|| cl.attribute2
|| '-'
|| cl.attribute3
|| '-'
|| cl.attribute4
|| '-'
|| cl.attribute5
Desc_of_service,
NULL Total_Penalty,
c.country Cust_country_of_residence,
hca.account_number Related_party,
abaa.bank_account_num
|| '-'
|| abb.bank_name
|| '-'
|| abb.bank_branch_name
Bank
FROM ar_payment_schedules_all ps,
--AR_RECEIVABLE_APPLICATIO NS_ALL RA3,
ra_customer_trx_all ct,
ar_cash_receipts_all cr,
ra_terms ter,
hr_all_organization_units aou,
hz_cust_site_uses_all bisu,
ra_customer_trx_lines_all cl,
hz_cust_accounts hca,
hz_parties hp,
ap_bank_accounts_all abaa,
ap_bank_branches abb,
hz_party_sites b,
hz_locations c,
hz_cust_acct_sites_all a
WHERE 1=1
AND ( 'INVOICE DATE' = 'INVOICE DATE' and
ct.trx_date >= nvl('01-JAN-2013' , ct.trx_date )
AND ct.trx_date < nvl('30-JAN-2013' , ct.trx_date
)
OR
(
'INVOICE DATE' = 'RECEIPT DATE' AND
cr.receipt_date >= nvl('01-JAN-2013' , cr.receipt_date )
AND cr.receipt_date < nvl('30-JAN-2013' , cr.receipt_date )
) AND a.party_site_id = b.party_site_id
AND b.location_id = c.location_id
AND bisu.cust_acct_site_id = a.cust_acct_site_id
AND hca.party_id = hp.party_id(+)
AND ct.customer_trx_id = cl.customer_trx_id
AND cr.pay_from_customer = hca.cust_account_id(+)
--AND RA3.APPLIED_PAYMENT_SCHEDU LE_ID = PS.PAYMENT_SCHEDULE_ID
--AND cl.customer_trx_id(+) = RA3.applied_customer_trx_i d
--AND cl.customer_trx_line_id(+) =RA3.appli ed_custome r_trx_line _id
AND ps.customer_trx_id = ct.customer_trx_id(+)
AND ps.cash_receipt_id = cr.cash_receipt_id(+)
AND ps.term_id = ter.term_id(+)
AND ps.org_id = aou.organization_id(+)
AND ct.bill_to_site_use_id = bisu.site_use_id(+)
--and PS.CLASS <> 'PMT'
--AND NVL (PS.STATUS, 'Z') = 'OP'
AND cr.remittance_bank_account _id =
abaa.bank_account_id(+)
AND abaa.bank_branch_id = abb.bank_branch_id(+)
--AND ct.trx_number IN ('10061', '10062')
AND ct.trx_number IN ('43', '106' , '10084',
'10085',
'10086',
'10087',
'10089',
'10102'
)--('10061', '10062')
and ct.invoice_currency_code <> 'MXN'
and (SELECT currency_code
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1)) ='MXN'
AND ct.invoice_currency_code <> (SELECT currency_code
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS _ALL RA3
WHERE RA3.APPLIED_PAYMENT_SCHEDU LE_ID =
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i d =
cl.customer_trx_id --AND RA3.applied_customer_trx_l ine_id=cl. customer_t rx_line_id
AND ROWNUM = 1))
AND (SELECT name
FROM ra_cust_trx_types_all TYPE
WHERE TYPE.CUST_TRX_TYPE_ID = ct.CUST_TRX_TYPE_ID) <>
'PWMX16 SCRAP SALES'
AND cl.line_TYPE = 'LINE'
AND organization_id = 782
SELECT ct.trx_number,
aou.name Operating_Unit,
ct.invoice_currency_code Functional_Currency_Code,
--ct.type Transaction_Class,
(SELECT name
FROM ra_cust_trx_types_all TYPE
WHERE TYPE.CUST_TRX_TYPE_ID = ct.CUST_TRX_TYPE_ID)
Transaction_Class,
ct.trx_number Transaction_Number,
cl.line_number Line_Number,
ct.trx_date Transaction_Date,
ps.gl_date GL_Date,
SUBSTR (HZ_COMMON_PUB.get_party_n
1,
240)
Bill_To_Customer_Name,
SUBSTR (
HZ_COMMON_PUB.GET_CUST_ADD
bisu.cust_acct_site_id),
1,
240)
Bill_To_Customer_Address,
c.country Bill_To_Customer_Country,
NULL Bill_To_Customer_Tax_ID,
ter.name Payment_Terms,
ct.invoice_currency_code Entered_Currency,
nvl(ps.exchange_rate,1) Exchange_Rate,
NVL (cl.quantity_ordered, quantity_invoiced) Quantity,
NVL (cl.taxable_amount, cl.unit_selling_price)
Unit_Selling_Price,
NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (cl.taxable_amount, cl.unit_selling_price)
Entered_Net_Line_Sales_Amo
(SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_
cl.customer_trx_line_id
AND SIGN (tax_rate) = '+1')
Tax_Rate,
NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (cl.taxable_amount, cl.unit_selling_price)
* (SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_
cl.customer_trx_line_id
AND SIGN (tax_rate) = '+1')
/ 100
Entered_Tax_Amount,
--ps.tax_original Entered_Tax_Amount,
-- NVL (cl.quantity_ordered, quantity_invoiced) * cl.unit_selling_price + ps.tax_original Entered_Gross_Line_Amount,
NVL (cl.quantity_ordered, quantity_invoiced)
* cl.unit_selling_price
+ NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (cl.taxable_amount, cl.unit_selling_price)
* (SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_
cl.customer_trx_line_id
AND SIGN (tax_rate) = '+1')
/ 100
Entered_Gross_Line_Amount,
--ps.amount_due_original Entered_Gross_Line_Amount,
NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (NVL (cl.taxable_amount, cl.unit_selling_price),
0)
*nvl( ps.exchange_rate,1)
Functional_Net_Line_Amount
--NVL (ps.tax_original, 0) * NVL (ps.exchange_rate, 1) Funcational_Tax_Amount,
NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (cl.taxable_amount, cl.unit_selling_price)
* (SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_
cl.customer_trx_line_id
AND SIGN (tax_rate) = '+1')
/ 100
* NVL (ps.exchange_rate, 1)
Funcational_Tax_Amount,
-- NVL (cl.quantity_ordered, quantity_invoiced) * NVL (cl.unit_selling_price, 0) * ps.exchange_rate + NVL (ps.tax_original, 0) * NVL (ps.exchange_rate, 1)
--Functional_Gross_Line_Am
NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (NVL (cl.taxable_amount, cl.unit_selling_price),
0)
* nvl(ps.exchange_rate,1)
+ NVL (cl.quantity_ordered, quantity_invoiced)
* NVL (cl.taxable_amount, cl.unit_selling_price)
* (SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_
cl.customer_trx_line_id)
/ 100
* NVL (ps.exchange_rate, 1)
Functional_Gross_Line_Amou
(SELECT ra3.apply_date
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.APPLIED_CUSTOMER_TRX_L
AND ROWNUM = 1)
Collected_Date,
-- ra3.apply_date Collected_Date,
(SELECT ACR.RECEIPT_NUMBER
FROM AR_RECEIVABLE_APPLICATIONS
AR_CASH_RECEIPTS_ALL ACR,
RA_CUSTOMER_TRX_ALL RCT1
WHERE ARA.STATUS = 'APP'
AND ARA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND ARA.APPLIED_CUSTOMER_TRX_I
RCT1.CUSTOMER_TRX_ID
AND ARA.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
AND RCT1.CUSTOMER_TRX_ID = ct.CUSTOMER_TRX_ID)
Collected_Bank_Tfr_Num,
(SELECT ACR.RECEIPT_DATE
FROM AR_RECEIVABLE_APPLICATIONS
AR_CASH_RECEIPTS_ALL ACR,
RA_CUSTOMER_TRX_ALL RCT1
WHERE ARA.STATUS = 'APP'
AND ARA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND ARA.APPLIED_CUSTOMER_TRX_I
RCT1.CUSTOMER_TRX_ID
AND ARA.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
AND RCT1.CUSTOMER_TRX_ID = ct.CUSTOMER_TRX_ID)
Collected_Bank_Receipt_dat
NVL (cl.quantity_ordered, quantity_invoiced)
* cl.unit_selling_price
Collected_Before_VAT_Orig_
--ps.tax_original VAT_Withholding_OC,
NVL (cl.quantity_ordered, quantity_invoiced)
* cl.unit_selling_price
* (SELECT NVL (tax_rate, 0)
FROM ra_customer_trx_lines_all cl1
WHERE cl1.LINK_TO_CUST_TRX_LINE_
cl.customer_trx_line_id
AND SIGN (tax_rate) = '+1')
/ 100
VAT_Withholding_OC,
-- ra3.tax_applied Collected_VAT_OC,
(SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
Collected_VAT_OC,
ps.tax_original
- (SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
Other_WHT_OC,
--ra3.line_applied + ra3.tax_applied Total_OC,
(SELECT RA3.line_applied
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
+ (SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
Total_OC,
(SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1))
Exchange_Rate_at_the_recei
(SELECT currency_code
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1))
currency_code_at_the_recei
(SELECT RA3.line_applied
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
* nvl(ps.exchange_rate,1)/*(
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)*/
/** (SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1))*/
Collected_bef_VAT_Funct_Cu
NVL (ps.tax_original, 0) * NVL (ps.exchange_rate, 1)
VAT_With_holding_FC,
(SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
* ( SELECT RA3.TRANS_TO_RECEIPT_RATE
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
/* * (SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1))*/
Collected_VAT_FC,
NULL Other_WHT_FC,
(SELECT RA3.line_applied
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
*( SELECT RA3.TRANS_TO_RECEIPT_RATE
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
/* * (SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1))*/
+ (SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
)
* ( SELECT RA3.TRANS_TO_RECEIPT_RATE
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
/* * (SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1))*/
Total_FC,
(SELECT RA3.tax_applied
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
* ( SELECT RA3.TRANS_TO_RECEIPT_RATE
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)
/** (SELECT nvl(exchange_rate,1)
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1))*/
- NVL (ps.tax_original, 0) * NVL (ps.exchange_rate, 1)
VAT_Fluctuation,
NULL Collected_VAT_PER,
cl.attribute1
|| '-'
|| cl.attribute2
|| '-'
|| cl.attribute3
|| '-'
|| cl.attribute4
|| '-'
|| cl.attribute5
Desc_of_service,
NULL Total_Penalty,
c.country Cust_country_of_residence,
hca.account_number Related_party,
abaa.bank_account_num
|| '-'
|| abb.bank_name
|| '-'
|| abb.bank_branch_name
Bank
FROM ar_payment_schedules_all ps,
--AR_RECEIVABLE_APPLICATIO
ra_customer_trx_all ct,
ar_cash_receipts_all cr,
ra_terms ter,
hr_all_organization_units aou,
hz_cust_site_uses_all bisu,
ra_customer_trx_lines_all cl,
hz_cust_accounts hca,
hz_parties hp,
ap_bank_accounts_all abaa,
ap_bank_branches abb,
hz_party_sites b,
hz_locations c,
hz_cust_acct_sites_all a
WHERE 1=1
AND ( 'INVOICE DATE' = 'INVOICE DATE' and
ct.trx_date >= nvl('01-JAN-2013' , ct.trx_date )
AND ct.trx_date < nvl('30-JAN-2013' , ct.trx_date
)
OR
(
'INVOICE DATE' = 'RECEIPT DATE' AND
cr.receipt_date >= nvl('01-JAN-2013' , cr.receipt_date )
AND cr.receipt_date < nvl('30-JAN-2013' , cr.receipt_date )
) AND a.party_site_id = b.party_site_id
AND b.location_id = c.location_id
AND bisu.cust_acct_site_id = a.cust_acct_site_id
AND hca.party_id = hp.party_id(+)
AND ct.customer_trx_id = cl.customer_trx_id
AND cr.pay_from_customer = hca.cust_account_id(+)
--AND RA3.APPLIED_PAYMENT_SCHEDU
--AND cl.customer_trx_id(+) = RA3.applied_customer_trx_i
--AND cl.customer_trx_line_id(+)
AND ps.customer_trx_id = ct.customer_trx_id(+)
AND ps.cash_receipt_id = cr.cash_receipt_id(+)
AND ps.term_id = ter.term_id(+)
AND ps.org_id = aou.organization_id(+)
AND ct.bill_to_site_use_id = bisu.site_use_id(+)
--and PS.CLASS <> 'PMT'
--AND NVL (PS.STATUS, 'Z') = 'OP'
AND cr.remittance_bank_account
abaa.bank_account_id(+)
AND abaa.bank_branch_id = abb.bank_branch_id(+)
--AND ct.trx_number IN ('10061', '10062')
AND ct.trx_number IN ('43', '106' , '10084',
'10085',
'10086',
'10087',
'10089',
'10102'
)--('10061', '10062')
and ct.invoice_currency_code <> 'MXN'
and (SELECT currency_code
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1)) ='MXN'
AND ct.invoice_currency_code <> (SELECT currency_code
FROM ar_cash_receipts_all acra
WHERE 1 = 1
AND acra.cash_receipt_id =
(SELECT RA3.cash_receipt_id
FROM AR_RECEIVABLE_APPLICATIONS
WHERE RA3.APPLIED_PAYMENT_SCHEDU
PS.PAYMENT_SCHEDULE_ID
AND RA3.applied_customer_trx_i
cl.customer_trx_id --AND RA3.applied_customer_trx_l
AND ROWNUM = 1))
AND (SELECT name
FROM ra_cust_trx_types_all TYPE
WHERE TYPE.CUST_TRX_TYPE_ID = ct.CUST_TRX_TYPE_ID) <>
'PWMX16 SCRAP SALES'
AND cl.line_TYPE = 'LINE'
AND organization_id = 782
The concept is there. You just need to match up the parenthesis for the logic you want.
Since we don't have the entire select nor the full requirements, we cannot provide 100% copy/paste code.
Since we don't have the entire select nor the full requirements, we cannot provide 100% copy/paste code.
I was typing when you posted the select.
You will want to WRAP the entire piece in parans to make it a stand-alone check. Otherwise the OR might break the logic.
Try what I posted.
You will want to WRAP the entire piece in parans to make it a stand-alone check. Otherwise the OR might break the logic.
Try what I posted.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
isint it can be done with case or decode
ASKER
yes this works
I know this question is closed but note the change from BETWEEN to >= and < introduced a functional change that is probably NOT correct
look at the condtion if the p_date_to is NULL
AND ct.trx_date < nvl(p_date_to , ct.trx_date)
ct.trx_date can't be less than itself
but it can be less than or equal to itself (i.e. BETWEEN)
also, if your dates are already truncated then the switch to < on the upper limit effectively cuts off an entire days' worth of results.
If your dates are not truncated then it cuts off one second of results (midnight of the target day)
look at the condtion if the p_date_to is NULL
AND ct.trx_date < nvl(p_date_to , ct.trx_date)
ct.trx_date can't be less than itself
but it can be less than or equal to itself (i.e. BETWEEN)
also, if your dates are already truncated then the switch to < on the upper limit effectively cuts off an entire days' worth of results.
If your dates are not truncated then it cuts off one second of results (midnight of the target day)
ASKER
so the condition should be
AND ct.trx_date < = nvl(p_date_to , ct.trx_date)
AND ct.trx_date < = nvl(p_date_to , ct.trx_date)
yes, or use the original BETWEEN syntax.
I prefer to not use BETWEEN, but that's just a cosmetic preference.
It's the same as >= and <=
I prefer to not use BETWEEN, but that's just a cosmetic preference.
It's the same as >= and <=
select ...
where
((p_date_type ='INVOICE DATE' and
( ct.trx_date between nvl(p__date_from , ct.trx_date )
AND nvl(p_date_to , ct.trx_date )
)
or
(p_date_type= 'RECEIPT DATE' and
( cr.receipt_date between nvl(p__date_from , cr.receipt_date )
AND nvl(p_date_to , cr.receipt_date )
))