Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to retrieve a minimum sequence number record

Posted on 2013-01-11
16
Medium Priority
?
605 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 74

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 74

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 74

Accepted Solution

by:
sdstuber earned 2000 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 74

Expert Comment

by:sdstuber
ID: 38776102
then include them in the select portion with the other columns
0
 

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 74

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 74

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 74

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 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