Solved

how to retrieve a minimum sequence number record

Posted on 2013-01-11
16
585 Views
Last Modified: 2013-01-15
duplication invoice lines in the report

I have troubleshot the issue in detail with the help from Team and the reported issue is due multiple lines in the table "ar_payment_schedules". As per my observation the below piece of code is causing the issue.

SELECT a.customer_trx_id, a.trx_number, NVL (p.terms_sequence_number, 1),
TYPES.TYPE, l_types.meaning, TYPES.accounting_affect_flag, a.trx_date,
a.org_id org_id -- Added for customization
,
a.ship_to_customer_id, a.ship_to_contact_id, a.remit_to_address_id,
a.primary_salesrep_id, b.account_number -- bug 1630907
,
a.internal_notes, a.batch_source_id, a.comments,
previous_customer_trx_id, ship_to_site_use_id, NVL (printing_count, 0),
printing_original_date, printing_last_printed, printing_pending,
last_printed_sequence_num, start_date_commitment, end_date_commitment,
initial_customer_trx_id, a.invoice_currency_code, a.term_id,
a.ship_date_actual, a.ship_via, a.waybill_number, a.purchase_order,
a.purchase_order_revision, a.purchase_order_date, p.due_date,
NVL (tl.relative_amount, 100) * (100 / NVL (t.base_amount, 100)),
t.NAME, a.bill_to_customer_id, a.bill_to_contact_id,
a.bill_to_site_use_id, u_bill.LOCATION bill_to_location,
NVL (a_bill.translated_customer_name, party.party_name) -- bug 1630907
,
RTRIM (RPAD (loc.address1, 40)) -- bug 1630907
,
RTRIM (RPAD (loc.address2, 40)), RTRIM (RPAD (loc.address3, 40)),
RTRIM (RPAD (loc.address4, 40)), loc.city,
NVL (loc.state, loc.province), loc.postal_code, loc.country,
u_bill.tax_reference, party.tax_reference -- bug 1630907
,
NVL ( amount_line_items_original
+ DECODE (a.initial_customer_trx_id,
'', 0,
NVL (com_adj.amount, 0)
),
TO_NUMBER (NULL)
),
NVL (tax_original, TO_NUMBER (NULL)),
NVL (freight_original, TO_NUMBER (NULL)),
p.amount_due_original
+ DECODE (a.initial_customer_trx_id, '', 0, NVL (com_adj.amount, 0)),
DECODE (:p_order_by,
'ADJUSTMENT_NUMBER', a.trx_number,
'CUSTOMER', NVL (a_bill.translated_customer_name,
party.party_name
),
'POSTAL_CODE', loc.postal_code,
'TRX_NUMBER', TO_NUMBER (a.trx_number),
TO_NUMBER (a.trx_number)
) order_by -- bug 1630907
,
loc.address1 -- bug 1630907
,
loc.address2, loc.address3, loc.address4, loc.state, loc.province,
a.interface_header_attribute1 interface_header_attribute1
-- Added for customization
FROM ra_terms_lines tl,
ra_cust_trx_types TYPES,
ar_lookups l_types,
hz_cust_accounts b,
hz_parties party,
hz_cust_site_uses u_bill,
hz_cust_acct_sites a_bill,
hz_party_sites party_site,
hz_locations loc,
ar_adjustments com_adj,
ra_customer_trx a,
ar_payment_schedules p,
ra_terms t
WHERE a.bill_to_customer_id = b.cust_account_id
AND p.payment_schedule_id + DECODE (p.CLASS, 'INV', 0, '') = com_adj.payment_schedule_id(+)
AND com_adj.subsequent_trx_id IS NULL
AND 'C' = com_adj.adjustment_type(+)
AND a.complete_flag = 'Y'
AND a.customer_trx_id = p.customer_trx_id
AND a.cust_trx_type_id = TYPES.cust_trx_type_id
AND l_types.lookup_type = 'INV/CM/ADJ'
AND a.printing_option IN ('PRI', 'REP')
AND l_types.lookup_code = DECODE (TYPES.TYPE, 'DEP', 'INV', TYPES.TYPE)
AND NVL (t.printing_lead_days, 0) > 0
AND a.bill_to_site_use_id = u_bill.site_use_id
AND u_bill.cust_acct_site_id = a_bill.cust_acct_site_id
AND a_bill.party_site_id = party_site.party_site_id
AND b.party_id = party.party_id
AND loc.location_id = party_site.location_id
AND NVL (loc.LANGUAGE,
tpco_utl_cfd_pkg.tpco_get_cust_lang_f (a.bill_to_site_use_id)
) = tpco_utl_cfd_pkg.tpco_get_cust_lang_f (a.bill_to_site_use_id)
AND NVL (p.terms_sequence_number, tl.sequence_num) = tl.sequence_num
AND NVL (p.amount_due_remaining, 0) <> 0
AND TYPES.TYPE = 'INV'
AND t.term_id = p.term_id
AND tl.term_id(+) = t.term_id
AND 1 = 1
AND NOT EXISTS (
SELECT 'X'
FROM ece_tp_details etd, ece_tp_headers eth
WHERE eth.tp_header_id = a_bill.tp_header_id
AND etd.tp_header_id = eth.tp_header_id
AND etd.edi_flag = 'Y'
AND etd.document_id = 'INO'
AND etd.document_type =
DECODE (TYPES.TYPE,
'CM', DECODE (a.previous_customer_trx_id,
NULL, 'OACM',
'CM'
),
TYPES.TYPE
))
AND a.trx_number >= :p_trx_number_low
AND a.trx_number <= :p_trx_number_high


this is causing the issue
AND NVL (p.terms_sequence_number, tl.sequence_num) = tl.sequence_num

now i have to select only 1 row for this payment_term_id
there are 2 sequences
so i have to select minimum sequence
0
Comment
Question by:pardeshirahul
  • 9
  • 7
16 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 38768405
AND LEAST(NVL(p.terms_sequence_number, tl.sequence_num),
             NVL(tl.sequence_num, p.terms_sequence_number)
            )  = tl.sequence_num
0
 

Author Comment

by:pardeshirahul
ID: 38774485
the least is not working, is there some other way
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38774520
what isn't working about it?

no result? wrong result? error?


LEAST is the correct function to return the smaller of two values
0
 

Author Comment

by:pardeshirahul
ID: 38774600
no i add the condition in the report

 old
i commented this out
--where1 := where1 || cr || 'AND NVL(P.TERMS_SEQUENCE_NUMBER,nvl(TL.SEQUENCE_NUM,0))=nvl(TL.SEQUENCE_NUM,nvl(p.terms_sequence_number,0))';

new

    where1 := where1 || cr || 'AND LEAST(NVL(p.terms_sequence_number, tl.sequence_num), NVL(tl.sequence_num, p.terms_sequence_number) )  = tl.sequence_num';

still the result was the same duplicate rows
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38774664
your original line from the question did not force nulls to zero, but in your last post you are forcing that.

If that's important criteria then you need to put the same logic there as well.



where1 := where1 || cr || 'AND LEAST(coalesce(p.terms_sequence_number, tl.sequence_num,0), coalesce(tl.sequence_num, p.terms_sequence_number,0) )  = nvl(tl.sequence_num,0)';


if this doesn't work, please provide sample data and expected results   - text please, no screen shots
0
 

Author Comment

by:pardeshirahul
ID: 38775650
if i comment out
   -- NVL (TL.SEQUENCE_NUM, 1) TERM_SEQUENCE_NUMBER,
  --P.DUE_DATE TERM_DUE_DATE_FROM_PS,

this then i am getting only 1 row
then i don't even have to use least

i can only use

AND NVL (p.terms_sequence_number, tl.sequence_num) = tl.sequence_num
0
 

Author Comment

by:pardeshirahul
ID: 38775658
so what if i have to select these 2 columns
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38776102
then include them in the select portion with the other columns
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:pardeshirahul
ID: 38776139
what i mean to say they have 2 different values
thats why i am getting 2 rows

so i think i have to use min
and then group by the rest of the columns

what might be the other way
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38776195
yes, if they are on 2 rows you need to use MIN instead of LEAST

but, MIN works on a single column, so you'll have to create pseudo-column

min( case when ....... then p.terms_sequence_number
             else  tl.sequence_num end)


replace ......  with whatever conditions are appropriate to choose between those columns

I can't be anymore specific without a test case to work from.
0
 

Author Comment

by:pardeshirahul
ID: 38776204
i mean if i use  min function
then i have to use group by

what is the other way round if i don't have to use group by
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38776320
I don't know what you're asking.
give me a test case...

sample data, expected results.
0
 

Author Comment

by:pardeshirahul
ID: 38778392
what i mean to say is this the the main query in the report
its  a dynamic query

SELECT      &P_SEL_TRX_ID                                CUSTOMER_TRX_ID
,      &P_SEL_TRX_NUMBER                            TRX_NUMBER
,      NVL(TL.SEQUENCE_NUM,1)                 TERM_SEQUENCE_NUMBER  
,      &P_SEL_TRX_TYPE                              TRX_TYPE
,              &P_SEL_TRX_TYPE_NAME                  TRX_TYPE_NAME
,              TYPES.ACCOUNTING_AFFECT_FLAG       OPEN_RECEIVABLE_FLAG
,              &P_SEL_TRX_DATE                              TRX_DATE
,               A.ORG_ID                                                ORG_ID                                                       -- Added for customization
,              SHIP_TO_CUSTOMER_ID                          SHIP_TO_CUSTOMER_ID
,              SHIP_TO_CONTACT_ID                           SHIP_TO_CONTACT_ID
,              REMIT_TO_ADDRESS_ID                          REMIT_TO_ADDRESS_ID
,              A.PRIMARY_SALESREP_ID                  PRIMARY_SALESREP_ID
,              B.ACCOUNT_NUMBER                            CUSTOMER_NUMBER -- bug 1630907
,              A.INTERNAL_NOTES                             INTERNAL_NOTES
,      A.BATCH_SOURCE_ID            BATCH_SOURCE_ID
,         A.COMMENTS                  TRX_COMMENTS
,              PREVIOUS_CUSTOMER_TRX_ID         PREVIOUS_CUSTOMER_TRX_ID
,              SHIP_TO_SITE_USE_ID                          SHIP_TO_SITE_USE_ID
,              NVL(PRINTING_COUNT,0)                        PRINTING_COUNT
,              PRINTING_ORIGINAL_DATE                PRINTING_ORIGINAL_DATE
,              PRINTING_LAST_PRINTED                  PRINTING_LAST_PRINTED
,              PRINTING_PENDING                             PRINTING_PENDING
,              LAST_PRINTED_SEQUENCE_NUM     LAST_PRINTED_SEQUENCE_NUMBER
,              START_DATE_COMMITMENT              START_DATE_COMMITMENT
,              END_DATE_COMMITMENT                  END_DATE_COMMITMENT
,              INITIAL_CUSTOMER_TRX_ID                    INITIAL_CUSTOMER_TRX_ID
,              A.INVOICE_CURRENCY_CODE                  INVOICE_CURRENCY_CODE
,              A.TERM_ID                  TERM_ID
,              A.SHIP_DATE_ACTUAL                           SHIP_DATE_ACTUAL
,              A.SHIP_VIA                                   SHIP_VIA
,              A.WAYBILL_NUMBER                            WAYBILL_NUMBER
--,              A.PURCHASE_ORDER                             PURCHASE_ORDER_NUMBER
,              nvl(A.PURCHASE_ORDER,(SELECT CUST_PO_NUMBER FROM OE_ORDER_HEADERS_ALL WHERE ORG_ID = A.ORG_ID AND TO_CHAR(ORDER_NUMBER) = A.INTERFACE_HEADER_ATTRIBUTE1))  PURCHASE_ORDER_NUMBER                         
,              A.PURCHASE_ORDER_REVISION       PURCHASE_ORDER_REVISION
,              A.PURCHASE_ORDER_DATE                    PURCHASE_ORDER_DATE
,              TO_CHAR(P.DUE_DATE,'DD-MON-YYYY')                                 TERM_DUE_DATE_FROM_PS
,              NVL(TL.RELATIVE_AMOUNT,100)* (100/NVL(T.BASE_AMOUNT,100))             TERM_RELATIVE_AMOUNT
,              T.NAME                                             TERM_NAME
,                T.DESCRIPTION                                   TERM_DESC
,             A.BILL_TO_CUSTOMER_ID                        BILL_TO_CUSTOMER_ID
,             A.BILL_TO_CONTACT_ID                         BILL_TO_CONTACT_ID
,              A.BILL_TO_SITE_USE_ID                        BILL_TO_SITE_USE_ID
,              U_BILL.LOCATION                                  BILL_TO_LOCATION
,              NVL(A_BILL.TRANSLATED_CUSTOMER_NAME,PARTY.PARTY_NAME)                              BILL_CUST_NAME  -- Bug 1630907
,              RTRIM(RPAD(LOC.ADDRESS1,40))          BILL_ADDRESS1 -- bug 1630907
,              RTRIM(RPAD(LOC.ADDRESS2,40) )          BILL_ADDRESS2
,              RTRIM(RPAD(LOC.ADDRESS3,40) )         BILL_ADDRESS3
,              RTRIM(RPAD(LOC.ADDRESS4,40) )          BILL_ADDRESS4
,              LOC.CITY                                        BILL_CITY -- bug 1630907
,              NVL(LOC.STATE , LOC.PROVINCE)       BILL_STATE
,              LOC.POSTAL_CODE                           BILL_POSTAL_CODE
,             LOC.COUNTRY                               BILL_COUNTRY
,              U_BILL.TAX_REFERENCE                         BILL_SITE_TAX_REFERENCE
,              PARTY.TAX_REFERENCE                          BILL_CUST_TAX_REFERENCE -- bug 1630907
,              &P_SEL_TRX_LINE_AMOUNT              TRX_LINE_AMOUNT
,              &P_SEL_TRX_TAX_AMOUNT               TRX_TAX_AMOUNT
,              &P_SEL_TRX_FREIGHT_AMOUNT            TRX_FREIGHT_AMOUNT
,              &P_SEL_TRX_ALL_AMOUNT                     TRX_ALL_AMOUNT
,              DECODE(:P_ORDER_BY,
                           'ADJUSTMENT_NUMBER',  &LP_ADJUSTMENT_NUMBER,
                           'CUSTOMER',                        NVL(A_BILL.TRANSLATED_CUSTOMER_NAME, PARTY.PARTY_NAME),
                           'POSTAL_CODE',                  LOC.POSTAL_CODE,
                           'TRX_NUMBER',                          to_number(A.TRX_NUMBER),
                                                                            to_number(A.TRX_NUMBER))   -- bug 1630907
                              ORDER_BY

,          LOC.ADDRESS1                  BILL_TO_ADDRESS1 -- bug 1630907
,         LOC.ADDRESS2                  BILL_TO_ADDRESS2
,          LOC.ADDRESS3                  BILL_TO_ADDRESS3
,          LOC.ADDRESS4                  BILL_TO_ADDRESS4
,          LOC.STATE                  BILL_TO_STATE
,          LOC.PROVINCE                  BILL_TO_PROVINCE
,               A.INTERFACE_HEADER_ATTRIBUTE1  INTERFACE_HEADER_ATTRIBUTE1                       -- Added for customization
,               TYPES.NAME  CUST_TRX_TYPE_NAME   --Added for SR1116 by Venkatesh on 26th May, 2006

FROM     &P_TABLE1

WHERE      &P_WHERE1
      &P_USER_WHERE_CLAUSE1
      &P_USER_WHERE_CLAUSE2
      &P_USER_WHERE_CLAUSE3
      &P_MLS_CONDITION
      &C_EC_WHERE_CLAUSE
                   &P_BR_WHERE_CLAUSE
                  &lp_trx_num_low
                  &lp_trx_num_high
               &lp_salesrep
AND A.PRINTING_PENDING=DECODE(NVL(:P_UNPRINTED_INVOICE,'N'),'Y','Y',A.PRINTING_PENDING)
UNION
SELECT       &P_SEL_TRX_ID
,      &P_SEL_TRX_NUMBER
,      NVL(P.TERMS_SEQUENCE_NUMBER,1)
,       &P_SEL_TRX_TYPE
,      &P_SEL_TRX_TYPE_NAME
,      TYPES.ACCOUNTING_AFFECT_FLAG
,      &P_SEL_TRX_DATE
,               A.ORG_ID                                                ORG_ID                                                       -- Added for customization
,      A.SHIP_TO_CUSTOMER_ID
,      A.SHIP_TO_CONTACT_ID
,      A.REMIT_TO_ADDRESS_ID
,      A.PRIMARY_SALESREP_ID
,      B.ACCOUNT_NUMBER -- bug 1630907
,      A.INTERNAL_NOTES
,      A.BATCH_SOURCE_ID
,      A.COMMENTS
,      PREVIOUS_CUSTOMER_TRX_ID
,      SHIP_TO_SITE_USE_ID
,       NVL(PRINTING_COUNT,0)
,      PRINTING_ORIGINAL_DATE
,      PRINTING_LAST_PRINTED                
,      PRINTING_PENDING                      
,      LAST_PRINTED_SEQUENCE_NUM
,      START_DATE_COMMITMENT
,      END_DATE_COMMITMENT
,      INITIAL_CUSTOMER_TRX_ID
,      A.INVOICE_CURRENCY_CODE
,       A.TERM_ID
,       A.SHIP_DATE_ACTUAL
,      A.SHIP_VIA
,      A.WAYBILL_NUMBER
,      A.PURCHASE_ORDER
,      A.PURCHASE_ORDER_REVISION
,      A.PURCHASE_ORDER_DATE
,      TO_CHAR(P.DUE_DATE,'DD-MON-YYYY')
,       NVL(TL.RELATIVE_AMOUNT,100)* (100/NVL(T.BASE_AMOUNT,100))
,      T.NAME
,              T.DESCRIPTION                                             
,      A.BILL_TO_CUSTOMER_ID
,      A.BILL_TO_CONTACT_ID
,       A.BILL_TO_SITE_USE_ID
,      U_BILL.LOCATION                            BILL_TO_LOCATION
,      NVL(A_BILL.TRANSLATED_CUSTOMER_NAME,PARTY.PARTY_NAME)  -- bug 1630907                
,      RTRIM(RPAD(LOC.ADDRESS1, 40))               -- bug 1630907        
,      RTRIM(RPAD(LOC.ADDRESS2, 40) )                      
,       RTRIM(RPAD(LOC.ADDRESS3,  40) )                      
,      RTRIM(RPAD(LOC.ADDRESS4 ,  40) )                      
,       LOC.CITY                            
,      NVL(LOC.STATE , LOC.PROVINCE)                          
,       LOC.POSTAL_CODE                    
,      LOC.COUNTRY
,      U_BILL.TAX_REFERENCE
,      PARTY.TAX_REFERENCE    -- bug 1630907
,       &P_SEL_TRX_LINE_AMOUNT                
,      &P_SEL_TRX_TAX_AMOUNT                  
,      &P_SEL_TRX_FREIGHT_AMOUNT        
,      &P_SEL_TRX_ALL_AMOUNT                  
,       DECODE(:P_ORDER_BY,
                           'ADJUSTMENT_NUMBER',  &LP_ADJUSTMENT_NUMBER,
                           'CUSTOMER',                        NVL(A_BILL.TRANSLATED_CUSTOMER_NAME, PARTY.PARTY_NAME),
                           'POSTAL_CODE',                  LOC.POSTAL_CODE,
                            'TRX_NUMBER',                          to_number(A.TRX_NUMBER),
                                                                         to_number(A.TRX_NUMBER))   ORDER_BY  -- bug 1630907

,       LOC.ADDRESS1   -- bug 1630907
,      LOC.ADDRESS2
,      LOC.ADDRESS3
,      LOC.ADDRESS4
,      LOC.STATE
,      LOC.PROVINCE
,               A.INTERFACE_HEADER_ATTRIBUTE1  INTERFACE_HEADER_ATTRIBUTE1                       -- Added for customization
,               TYPES.NAME CUST_TRX_TYPE_NAME   --Added for SR1116 by Venkatesh on 26th May,
FROM      &P_TABLE2

WHERE      &P_WHERE2
      &P_USER_WHERE_CLAUSE1
      &P_USER_WHERE_CLAUSE2
      &P_USER_WHERE_CLAUSE3
      &P_MLS_CONDITION
      &C_EC_WHERE_CLAUSE
                &P_BR_WHERE_CLAUSE
                &lp_trx_num_low
                &lp_trx_num_high
                &lp_salesrep
AND A.PRINTING_PENDING=DECODE(NVL(:P_UNPRINTED_INVOICE,'N'),'Y','Y',A.PRINTING_PENDING)

it gives 2 rows
i need only 1 row

for which i don't have to select ,      
NVL(TL.SEQUENCE_NUM,1)                 TERM_SEQUENCE_NUMBER  
TO_CHAR(P.DUE_DATE,'DD-MON-YYYY')                

but this field is used NVL(TL.SEQUENCE_NUM,1)                 TERM_SEQUENCE_NUMBER  
soe in the report

i just want to see if we can do something
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38778460
please show me some data (fake data is fine, as long as it represents what you're trying to ask)

and expected results
0
 

Author Comment

by:pardeshirahul
ID: 38778504
some how i am trying to attac the file it is not getting attached
0
 

Author Comment

by:pardeshirahul
ID: 38778542
i figured it out i have to use group by and use MIN for TERM_SEQUENCE_NUMBER
AND DUE_DATE
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now