错误 ORA-01427: 单行子查询返回多于一行

发布于 2024-09-25 06:09:52 字数 655 浏览 4 评论 0原文

当我执行查询时，出现错误 [ORA-01427: 单行子查询返回多于一行]。我的查询结构如下：

SELECT LV.PRICE,
 (SELECT C.MODEL_NAME FROM CARS C WHERE C.MODEL_ID = LV.MODEL_ID) as MODEL_NAME
FROM LEDGER_VIEW LV
WHERE LV.PRICE < 500

它在嵌套选择上中断。我知道视图和此查询中的逻辑都是正确的，并且嵌套选择不可能返回多于一行。 CARS 表的 MODEL_ID 是一个唯一字段。如果我在没有嵌套选择的情况下执行查询，它不会返回此错误。

LEDGER_VIEW 是一个构建在另一个视图之上的视图。这些堆叠视图在 Oracle 10g 中是否有可能存在错误？我不知道如何调试这个问题。

我知道我可以将此特定查询更改为联接而不是嵌套选择，但我想知道为什么会发生这种情况，因为我在其他不太容易修改的地方使用嵌套查询。

编辑：这是非常奇怪的事情。正如我所说，LEDGER_VIEW 是构建在另一个视图之上的。作为测试，我将嵌套视图的 SQL 直接复制到 LEDGER_VIEW 的 SQL 中，代替嵌套视图，并且它返回时没有错误（如预期）。这似乎向我证实，嵌套视图或嵌套视图+数据库链接的组合存在一些错误行为。

原文

I'm getting the error [ORA-01427: single-row subquery returns more than one row] when I execute a query. I have a query structured like so:

SELECT LV.PRICE,
 (SELECT C.MODEL_NAME FROM CARS C WHERE C.MODEL_ID = LV.MODEL_ID) as MODEL_NAME
FROM LEDGER_VIEW LV
WHERE LV.PRICE < 500

It's breaking on the nested select. I know the logic both in the view and in this query is correct, and that there's no chance of the nested select returning more than one row. The CARS table's MODEL_ID is a unique field. If I execute the query without the nested select it doesn't return this error.

The LEDGER_VIEW is a view built on top of another view. Is it possible that these stacked views are buggy in Oracle 10g? I don't know how else to debug this problem.

I am aware I could change this particular query to a join rather than a nested select, but I'd like to know why this is happening because I use nested queries in other places where it is not so easily modifiable.

EDIT: Here's the really strange thing. The LEDGER_VIEW is, as I said, built on top of another view. As a test, I copied the nested view's SQL directly into the SQL of the SQL of LEDGER_VIEW, in place of the nested view, and it returned with no errors (as expected). This seems to confirm to me that there is some buggy behavior either with nested views or with the combination of nested views + database links.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤独患者 2024-10-02 06:09:52

您的子查询返回多行。使用以下查询找出 Car 表中的哪些 MODELID 值重复：

select MODELID as CarsModelID, count(*) as Count
from cars 
where MODELID in (
    select MODEL_ID
    from LEDGER_VIEW  
    WHERE LV.PRICE < 500 
)
group by MODELID
having count(*) > 1

Your subquery is returning multiple rows. Use the query below to find out which MODELID values in the Car table are duplicated:

select MODELID as CarsModelID, count(*) as Count
from cars 
where MODELID in (
    select MODEL_ID
    from LEDGER_VIEW  
    WHERE LV.PRICE < 500 
)
group by MODELID
having count(*) > 1

回复收藏 0 原文

萌能量女王 2024-10-02 06:09:52

我无法通过创建堆叠视图来重新创建。（尽管 RedFilters 会找到罪魁祸首）

    CREATE TABLE t1
    (
        t1_id NUMBER        ,
        txt   VARCHAR2( 50 ),
        CONSTRAINT t1_pk PRIMARY KEY( t1_id )
    ) ;


    CREATE TABLE t2
    (
        t2_id NUMBER                      ,
        t1_id NUMBER                      ,
        price NUMBER( 10, 4 )             ,
        CONSTRAINT t2_pk PRIMARY KEY( t2_id ),
        CONSTRAINT t2_fk FOREIGN KEY( t1_id ) REFERENCES t1( t1_id )
    );

    insert into t1(t1_id, txt) values(1,'fit');
    insert into t1(t1_id, txt) values(2,'focus');
    insert into t1(t1_id, txt) values(3,'golf');
    insert into t1(t1_id, txt) values(4,'explorer');
    insert into t1(t1_id, txt) values(5,'corolla');

insert into t2(t2_id, t1_id, price) values(1,1,17000);
insert into t2(t2_id, t1_id, price) values(2,2,16000);
insert into t2(t2_id, t1_id, price) values(3,3,22000);
insert into t2(t2_id, t1_id, price) values(4,4,31000);
insert into t2(t2_id, t1_id, price) values(5,5,17000);


create view t1_view as select * from t1;
create view t2_view as select * from t2;
create view t_stacked_view as 
  select t1_view.txt ,
       t2_view.price ,
         t1_view.t1_id
    from t1_view 
          left join
          t2_view 
            on t1_view.t1_id = t2_view .t1_id
    ;   


--stacked view test
select t1_view.txt ,
       (select t_stacked_view.price 
            from t_stacked_view 
             where t1_view.t1_id = t_stacked_view .t1_id) price
    from t1_view ;

--or better yet, just drop the row level query
select t1_view.txt ,
       t2_view.price
    from t1_view 
          left join
          t2_view 
            on t1_view.t1_id = t2_view .t1_id
    ;

但这引出了一个问题，为什么你要在这里进行行级查询？虽然 10g 应该对它们进行相同的优化，但我总是发现编写如下查询更容易，无论是为了可读性、可维护性，还是为了专门避免您遇到的错误（是不是总是如此，3年后，由应用程序（在数据库和调用应用程序中）是否存在会导致此错误的条件？一条 rouge 语句进入后您的整个应用程序就会崩溃？

    SELECT LV.PRICE,
            c.model_name
FROM LEDGER_VIEW LV
      LEFT /* OR INNER */ JOIN CARS C 
       ON C.MODEL_ID = LV.MODEL_ID
WHERE LV.PRICE < 500

I am unable to recreate via a creation of a stacked view. (althoug RedFilters will find the culprit)

    CREATE TABLE t1
    (
        t1_id NUMBER        ,
        txt   VARCHAR2( 50 ),
        CONSTRAINT t1_pk PRIMARY KEY( t1_id )
    ) ;


    CREATE TABLE t2
    (
        t2_id NUMBER                      ,
        t1_id NUMBER                      ,
        price NUMBER( 10, 4 )             ,
        CONSTRAINT t2_pk PRIMARY KEY( t2_id ),
        CONSTRAINT t2_fk FOREIGN KEY( t1_id ) REFERENCES t1( t1_id )
    );

    insert into t1(t1_id, txt) values(1,'fit');
    insert into t1(t1_id, txt) values(2,'focus');
    insert into t1(t1_id, txt) values(3,'golf');
    insert into t1(t1_id, txt) values(4,'explorer');
    insert into t1(t1_id, txt) values(5,'corolla');

insert into t2(t2_id, t1_id, price) values(1,1,17000);
insert into t2(t2_id, t1_id, price) values(2,2,16000);
insert into t2(t2_id, t1_id, price) values(3,3,22000);
insert into t2(t2_id, t1_id, price) values(4,4,31000);
insert into t2(t2_id, t1_id, price) values(5,5,17000);


create view t1_view as select * from t1;
create view t2_view as select * from t2;
create view t_stacked_view as 
  select t1_view.txt ,
       t2_view.price ,
         t1_view.t1_id
    from t1_view 
          left join
          t2_view 
            on t1_view.t1_id = t2_view .t1_id
    ;   


--stacked view test
select t1_view.txt ,
       (select t_stacked_view.price 
            from t_stacked_view 
             where t1_view.t1_id = t_stacked_view .t1_id) price
    from t1_view ;

--or better yet, just drop the row level query
select t1_view.txt ,
       t2_view.price
    from t1_view 
          left join
          t2_view 
            on t1_view.t1_id = t2_view .t1_id
    ;

But that begs the question, why are you doing the row level query here? While 10g ought to optimize them the same, I have always found it easier to write queries as below, both for readability, maintainability, and to specifically avoid the error you are having (is it always, 3 years down the road, guaranteed by the application (both in the db and the calling app) that you cannot have a condition that will cause this error? One rouge statement gets in and your entire app dies?

    SELECT LV.PRICE,
            c.model_name
FROM LEDGER_VIEW LV
      LEFT /* OR INNER */ JOIN CARS C 
       ON C.MODEL_ID = LV.MODEL_ID
WHERE LV.PRICE < 500

回复收藏 0 原文

十年九夏 2024-10-02 06:09:52

我建议使用 RedFilter 的答案来检查是否有多个具有给定 MODEL_ID 的汽车。

如果您绝对确定 CARS.MODEL_ID 是唯一的，则意味着错误消息是通过从 LEDGER_VIEW 中进行选择而生成的 - 因此请尝试在 CARS 上运行不带子查询的等效查询，如下所示：

SELECT LV.PRICE
FROM LEDGER_VIEW LV
WHERE LV.PRICE < 500

如果您仍然看到相同的错误 (你应该，如果 CARS.MODEL_ID 是唯一的）你将需要调试 LEDGER_VIEW - 即。检查 LEDGER_VIEW 中返回多行的子查询及其所基于的基础视图。

在大多数形式的 SQL 中，基于视图创建视图是可能的，但这通常是一个坏主意 - 正是因为这个原因。

I suggest using RedFilter's answer to check whether there are multiple cars with a given MODEL_ID.

If you're absolutely certain that CARS.MODEL_ID is unique, then it implies that the error message is generated by selection from LEDGER_VIEW - so try running the equivalent query without the subquery on CARS, like so:

SELECT LV.PRICE
FROM LEDGER_VIEW LV
WHERE LV.PRICE < 500

If you still see the same error (you should, if CARS.MODEL_ID is unique) you will need to debug LEDGER_VIEW - ie. check for sub-queries returning multiple rows in LEDGER_VIEW and the underlying views it is based on.

Creating views based on views is possible in most forms of SQL, but it is usually a bad idea - for this very reason.

回复收藏 0 原文

执妄 2024-10-02 06:09:52

尝试通过附加 rownum = 1 来强制子查询返回单个结果，如下所示：

SELECT LV.PRICE,
(SELECT C.MODEL_NAME FROM CARS C WHERE C.MODEL_ID = LV.MODEL_ID AND ROWNUM = 1) as MODEL_NAME
FROM LEDGER_VIEW LV
WHERE LV.PRICE < 500

它可能会起作用，如果起作用，您将知道您的子查询返回多行，从错误代码来看它应该是。当然，这不是一个解决方案，因此您可能必须修复 cars 表中的数据才能真正解决问题。如果 model_id 再次重复，则保留 rownum = 1 将消除错误，从而防止您注意到问题。

Try forcing your subquery to return a single result by appending rownum = 1, like this:

SELECT LV.PRICE,
(SELECT C.MODEL_NAME FROM CARS C WHERE C.MODEL_ID = LV.MODEL_ID AND ROWNUM = 1) as MODEL_NAME
FROM LEDGER_VIEW LV
WHERE LV.PRICE < 500

It will probably work and if it does, you will know that your subquery returns multiple rows, which judging by the error code it should be. Of course this is not a solution so you might have to fix your data in cars table to actually solve the problem. Leaving and rownum = 1 will eliminate the error if model_id is duplicated again, preventing you from noticing the problem.

回复收藏 0 原文

〆凄凉。 2024-10-02 06:09:52

select
                                    a.account_number,
                                    a.party_id,
                                    a.TRX_NUMBER,
                                    a.trx_date,
                                    a.order_number,
                                    adv.unapplied_amt,
                                    a.Finance,
                                    a.customer_name,a.PARTY_NAME,
                                    a.customer_number,a.contact_number,
                                    a.name,
                                    a.Aging,
                                    a.transaction_type,
                                    a.exec_name,
                                    a.team_leader,
                                    sum(a.O_SAmount),
                                    (case when (trunc(sysdate) - trunc(a.trx_date)) <=:ag1 then sum(a.O_SAmount) else 0 end ) bucket1,--"<" || :ag1,
                                    (case when (trunc(sysdate) - trunc(a.trx_date)) between :ag1+1 and :ag2 then sum(a.O_SAmount) else 0 end ) bucket2,--:ag1+1 || "to" || :ag2,
                                    (case when (trunc(sysdate) - trunc(a.trx_date)) between :ag2+1 and :ag3 then sum(a.O_SAmount) else 0 end ) bucket3,--:ag2+1 || "to" || :ag3,
                                    (case when (trunc(sysdate) - trunc(a.trx_date)) >:ag3 then sum(a.O_SAmount) else 0 end ) bucket4,
                                    :AS_ON_date
from
(select distinct hca.account_number,hp.party_id,--rcta.CUSTOMER_TRX_ID,
--rcta.trx_number,rcta.trx_date,apsa.due_date,
(
select distinct
                            --ooha.order_number,
                            rcta.trx_number
                            --to_char(rcta.trx_date,'DD-MON-YYYY') trx_date
from                            
                            ra_customer_trx_all rcta,
                            oe_order_headers_all ooh,
                            oe_order_lines_all oola,
                            --ra_customer_trx_all rcta,
                            ra_customer_trx_lines_all rctla,
                            ra_cust_trx_types_all rctta
                            --ra_customer_trx_lines_all rctl
where 1=1
                AND ooh.header_id = oola.header_id
                   --AND ooh.order_number = '111111010101698'
                   AND ooh.order_number=oohA.order_number
                   AND TO_CHAR (ooh.order_number) = rcta.ct_reference
                   AND rcta.customer_trx_id = rctla.customer_trx_id
                   AND rctla.inventory_item_id = oola.inventory_item_id
                   and rcta.CUST_TRX_TYPE_ID = rctta.cust_trx_type_id
                   and rcta.org_id = rctta.org_id
                   and rctta.type like 'INV'
                   and oola.ordered_item LIKE 'MV%' 
                   AND oola.attribute3 = 'Y'
                   AND ooh.flow_status_code <> 'ENTERED'
                   AND oola.flow_status_code <> 'CANCELLED'
)TRX_NUMBER,
(select distinct
--ooha.order_number,
--rcta.trx_number
rcta.trx_date
from
            ra_customer_trx_all rcta,
            oe_order_headers_all ooh,
            oe_order_lines_all oola,
            --ra_customer_trx_all rcta,
            ra_customer_trx_lines_all rctla,
            ra_cust_trx_types_all rctta
            --ra_customer_trx_lines_all rctl
where 1=1
               AND ooh.header_id = oola.header_id
               --AND ooh.order_number = '111111010101698'
               AND ooh.order_number=oohA.order_number
               AND TO_CHAR (ooh.order_number) = rcta.ct_reference
               AND rcta.customer_trx_id = rctla.customer_trx_id
               AND rctla.inventory_item_id = oola.inventory_item_id
               and rcta.CUST_TRX_TYPE_ID = rctta.cust_trx_type_id
               and rcta.org_id = rctta.org_id
               and rctta.type like 'INV'
               and oola.ordered_item LIKE 'MV%' 
               AND oola.attribute3 = 'Y'
               AND ooh.flow_status_code <> 'ENTERED'
               AND oola.flow_status_code <> 'CANCELLED'
)TRX_Date,
rcta.INTERFACE_HEADER_ATTRIBUTE1 order_number,
ooha.attribute10 Finance,
f.customer_name,HP.PARTY_NAME,
TO_NUMBER(f.customer_number)customer_number,hp.primary_phone_number contact_number,--csi.incident_number,
--cii.instance_number,
haou.name,
--sum(acr.amount) Advance,--rcta.CUST_TRX_TYPE_ID,--acr.cash_receipt_id,
--sum(abs((apsa.AMOUNT_DUE_REMAINING-nvl(acr.amount,0)))) "O_SAmount",
apsa.AMOUNT_DUE_REMAINING O_SAmount,
--sum(abs((apsa.AMOUNT_DUE_REMAINING))) "O_SAmount",
round(months_between(sysdate,rcta.trx_date)*30) Aging,
--(case when ((round(months_between(sysdate,rcta.trx_date)*30)>=0) or (round(months_between(sysdate,rcta.trx_date)*30)<:aging1)) then apsa.AMOUNT_DUE_REMAINING end) "0 TO 30"
--(case when (trunc(sysdate) - trunc(apsa.Due_Date)) <=:ag1 then apsa.AMOUNT_DUE_REMAINING else 0 end ) bucket1,--"<" || :ag1,
--(case when (trunc(sysdate) - trunc(apsa.Due_Date)) between :ag1+1 and :ag2 then apsa.AMOUNT_DUE_REMAINING else 0 end ) bucket2,--:ag1+1 || "to" || :ag2,
--(case when (trunc(sysdate) - trunc(apsa.Due_Date)) between :ag2+1 and :ag3 then apsa.AMOUNT_DUE_REMAINING else 0 end ) bucket3,--:ag2+1 || "to" || :ag3,
--(case when (trunc(sysdate) - trunc(apsa.Due_Date)) >:ag3 then apsa.AMOUNT_DUE_REMAINING else 0 end ) bucket4,
--apsa.amount_due_original,
--TO_NUMBER(apsa.AMOUNT_DUE_REMAINING)AMOUNT_DUE_REMAINING,
rctta.name transaction_type,
PAPF.full_name||'-'||PAPF.EMPLOYEE_NUMBER exec_name,
ooha.attribute9 team_leader,
:AS_ON_date
from                    ra_customer_trx_all rcta,
                        oe_order_headers_all ooha,
                        hz_cust_accounts hca,
                        hz_parties hp,
                        --cs_incidents_all_b csi,
                        --csi_item_instances cii,
                        hr_all_organization_units haou,
                        ar_cash_receipts_all acr,
                        ar_receivable_applications_all aaa,
                        ra_cust_trx_types_all RCTTA,
                        hr.per_all_people_f papf,
                        ar_customers f,
                        ar_payment_schedules_all apsa,
                        jtf.JTF_RS_SALESREPS jrs
where 1=1
                        --and INTERFACE_HEADER_ATTRIBUTE1 like '111111060100538'
                        --and INTERFACE_HEADER_ATTRIBUTE1 like '111111010105402'
                        --and INTERFACE_HEADER_ATTRIBUTE1 like '111111010102791'
                        and rcta.ct_reference(+)=TO_CHAR(ooha.order_number)
                        AND f.customer_id = (rcta.bill_to_customer_id) 
                        and f.customer_id=hca.cust_account_id
                        and hca.party_id=hp.party_id
                        and haou.organization_id=rcta.INTERFACE_HEADER_ATTRIBUTE10
                        --and hp.party_id=cii.owner_party_id
                        --and csi.inventory_item_id=cii.inventory_item_id
                        --and csi.inv_organization_id=haou.organization_id
                        --and haou.organization_id=nvl(:location,haou.organization_id)
                        and ooha.SHIP_FROM_ORG_ID=nvl(:location,haou.organization_id)
                        AND RCTTA.NAME like :transaction_type||'%'
                        --decode(:org_id,null,null,(select name from ar_cash_receipts_all where organization_id = :org_id)) ||'%')
                        and rcta.trx_date<=to_date(:AS_ON_date)
                        --AND RCTTA.NAME=NVL(:TRANS_TYPE,RCTTA.NAME)
                        and rcta.org_id=nvl(:org_id,rcta.org_id)
                        --and f.customer_name like 'VIKAS SATAV'
                        and aaa.applied_customer_trx_id(+)=rcta.customer_trx_id
                        and aaa.cash_receipt_id=acr.cash_receipt_id(+)
                        and rcta.status_trx like 'OP'
                        and rcta.CUST_TRX_TYPE_ID=rctta.CUST_TRX_TYPE_ID
                        and apsa.CUSTOMER_TRX_ID=rcta.CUSTOMER_TRX_ID
                        and TO_NUMBER(apsa.AMOUNT_DUE_REMAINING) >0
                        --and hp.party_id=papf.party_id(+)
                        and jrs.salesrep_id = ooha.SALESREP_ID
                        and jrs.ORG_ID = ooha.ORG_ID
                        and jrs.PERSON_ID = papf.PERSON_ID(+)
) a,
(
select
b.order_number,
sum(b.AMOUNT_APPLIED) unapplied_amt
from
                        (select distinct to_char(ooha.order_number) order_number,ara.* from 
                        oe_order_headers_all ooha,
                        oe_payments oe,
                        ar_receivable_applications_all ara
where                    1=1--ooha.order_number = :p_order_num
                        and oe.header_id=ooha.header_id
                        and ara.PAYMENT_SET_ID=oe.PAYMENT_SET_ID
                        and ara.DISPLAY='Y'
                        and (ara.STATUS like 'OTHER ACC' or ara.STATUS like 'UNAPP') --or ara.STATUS like 'ACC')
                        ) b
group by b.order_number
) adv
where                       adv.order_number(+)=a.order_number
group by                    a.account_number,
                            a.party_id,
                            a.TRX_NUMBER,
                            a.trx_date,
                            a.order_number,
                            adv.unapplied_amt,
                            a.Finance,
                            a.customer_name,a.PARTY_NAME,
                            a.customer_number,a.contact_number,
                            a.name,
                            a.Aging,
                            a.transaction_type,
                            a.exec_name,
                            a.team_leader
order by a.Aging desc

select
                                    a.account_number,
                                    a.party_id,
                                    a.TRX_NUMBER,
                                    a.trx_date,
                                    a.order_number,
                                    adv.unapplied_amt,
                                    a.Finance,
                                    a.customer_name,a.PARTY_NAME,
                                    a.customer_number,a.contact_number,
                                    a.name,
                                    a.Aging,
                                    a.transaction_type,
                                    a.exec_name,
                                    a.team_leader,
                                    sum(a.O_SAmount),
                                    (case when (trunc(sysdate) - trunc(a.trx_date)) <=:ag1 then sum(a.O_SAmount) else 0 end ) bucket1,--"<" || :ag1,
                                    (case when (trunc(sysdate) - trunc(a.trx_date)) between :ag1+1 and :ag2 then sum(a.O_SAmount) else 0 end ) bucket2,--:ag1+1 || "to" || :ag2,
                                    (case when (trunc(sysdate) - trunc(a.trx_date)) between :ag2+1 and :ag3 then sum(a.O_SAmount) else 0 end ) bucket3,--:ag2+1 || "to" || :ag3,
                                    (case when (trunc(sysdate) - trunc(a.trx_date)) >:ag3 then sum(a.O_SAmount) else 0 end ) bucket4,
                                    :AS_ON_date
from
(select distinct hca.account_number,hp.party_id,--rcta.CUSTOMER_TRX_ID,
--rcta.trx_number,rcta.trx_date,apsa.due_date,
(
select distinct
                            --ooha.order_number,
                            rcta.trx_number
                            --to_char(rcta.trx_date,'DD-MON-YYYY') trx_date
from                            
                            ra_customer_trx_all rcta,
                            oe_order_headers_all ooh,
                            oe_order_lines_all oola,
                            --ra_customer_trx_all rcta,
                            ra_customer_trx_lines_all rctla,
                            ra_cust_trx_types_all rctta
                            --ra_customer_trx_lines_all rctl
where 1=1
                AND ooh.header_id = oola.header_id
                   --AND ooh.order_number = '111111010101698'
                   AND ooh.order_number=oohA.order_number
                   AND TO_CHAR (ooh.order_number) = rcta.ct_reference
                   AND rcta.customer_trx_id = rctla.customer_trx_id
                   AND rctla.inventory_item_id = oola.inventory_item_id
                   and rcta.CUST_TRX_TYPE_ID = rctta.cust_trx_type_id
                   and rcta.org_id = rctta.org_id
                   and rctta.type like 'INV'
                   and oola.ordered_item LIKE 'MV%' 
                   AND oola.attribute3 = 'Y'
                   AND ooh.flow_status_code <> 'ENTERED'
                   AND oola.flow_status_code <> 'CANCELLED'
)TRX_NUMBER,
(select distinct
--ooha.order_number,
--rcta.trx_number
rcta.trx_date
from
            ra_customer_trx_all rcta,
            oe_order_headers_all ooh,
            oe_order_lines_all oola,
            --ra_customer_trx_all rcta,
            ra_customer_trx_lines_all rctla,
            ra_cust_trx_types_all rctta
            --ra_customer_trx_lines_all rctl
where 1=1
               AND ooh.header_id = oola.header_id
               --AND ooh.order_number = '111111010101698'
               AND ooh.order_number=oohA.order_number
               AND TO_CHAR (ooh.order_number) = rcta.ct_reference
               AND rcta.customer_trx_id = rctla.customer_trx_id
               AND rctla.inventory_item_id = oola.inventory_item_id
               and rcta.CUST_TRX_TYPE_ID = rctta.cust_trx_type_id
               and rcta.org_id = rctta.org_id
               and rctta.type like 'INV'
               and oola.ordered_item LIKE 'MV%' 
               AND oola.attribute3 = 'Y'
               AND ooh.flow_status_code <> 'ENTERED'
               AND oola.flow_status_code <> 'CANCELLED'
)TRX_Date,
rcta.INTERFACE_HEADER_ATTRIBUTE1 order_number,
ooha.attribute10 Finance,
f.customer_name,HP.PARTY_NAME,
TO_NUMBER(f.customer_number)customer_number,hp.primary_phone_number contact_number,--csi.incident_number,
--cii.instance_number,
haou.name,
--sum(acr.amount) Advance,--rcta.CUST_TRX_TYPE_ID,--acr.cash_receipt_id,
--sum(abs((apsa.AMOUNT_DUE_REMAINING-nvl(acr.amount,0)))) "O_SAmount",
apsa.AMOUNT_DUE_REMAINING O_SAmount,
--sum(abs((apsa.AMOUNT_DUE_REMAINING))) "O_SAmount",
round(months_between(sysdate,rcta.trx_date)*30) Aging,
--(case when ((round(months_between(sysdate,rcta.trx_date)*30)>=0) or (round(months_between(sysdate,rcta.trx_date)*30)<:aging1)) then apsa.AMOUNT_DUE_REMAINING end) "0 TO 30"
--(case when (trunc(sysdate) - trunc(apsa.Due_Date)) <=:ag1 then apsa.AMOUNT_DUE_REMAINING else 0 end ) bucket1,--"<" || :ag1,
--(case when (trunc(sysdate) - trunc(apsa.Due_Date)) between :ag1+1 and :ag2 then apsa.AMOUNT_DUE_REMAINING else 0 end ) bucket2,--:ag1+1 || "to" || :ag2,
--(case when (trunc(sysdate) - trunc(apsa.Due_Date)) between :ag2+1 and :ag3 then apsa.AMOUNT_DUE_REMAINING else 0 end ) bucket3,--:ag2+1 || "to" || :ag3,
--(case when (trunc(sysdate) - trunc(apsa.Due_Date)) >:ag3 then apsa.AMOUNT_DUE_REMAINING else 0 end ) bucket4,
--apsa.amount_due_original,
--TO_NUMBER(apsa.AMOUNT_DUE_REMAINING)AMOUNT_DUE_REMAINING,
rctta.name transaction_type,
PAPF.full_name||'-'||PAPF.EMPLOYEE_NUMBER exec_name,
ooha.attribute9 team_leader,
:AS_ON_date
from                    ra_customer_trx_all rcta,
                        oe_order_headers_all ooha,
                        hz_cust_accounts hca,
                        hz_parties hp,
                        --cs_incidents_all_b csi,
                        --csi_item_instances cii,
                        hr_all_organization_units haou,
                        ar_cash_receipts_all acr,
                        ar_receivable_applications_all aaa,
                        ra_cust_trx_types_all RCTTA,
                        hr.per_all_people_f papf,
                        ar_customers f,
                        ar_payment_schedules_all apsa,
                        jtf.JTF_RS_SALESREPS jrs
where 1=1
                        --and INTERFACE_HEADER_ATTRIBUTE1 like '111111060100538'
                        --and INTERFACE_HEADER_ATTRIBUTE1 like '111111010105402'
                        --and INTERFACE_HEADER_ATTRIBUTE1 like '111111010102791'
                        and rcta.ct_reference(+)=TO_CHAR(ooha.order_number)
                        AND f.customer_id = (rcta.bill_to_customer_id) 
                        and f.customer_id=hca.cust_account_id
                        and hca.party_id=hp.party_id
                        and haou.organization_id=rcta.INTERFACE_HEADER_ATTRIBUTE10
                        --and hp.party_id=cii.owner_party_id
                        --and csi.inventory_item_id=cii.inventory_item_id
                        --and csi.inv_organization_id=haou.organization_id
                        --and haou.organization_id=nvl(:location,haou.organization_id)
                        and ooha.SHIP_FROM_ORG_ID=nvl(:location,haou.organization_id)
                        AND RCTTA.NAME like :transaction_type||'%'
                        --decode(:org_id,null,null,(select name from ar_cash_receipts_all where organization_id = :org_id)) ||'%')
                        and rcta.trx_date<=to_date(:AS_ON_date)
                        --AND RCTTA.NAME=NVL(:TRANS_TYPE,RCTTA.NAME)
                        and rcta.org_id=nvl(:org_id,rcta.org_id)
                        --and f.customer_name like 'VIKAS SATAV'
                        and aaa.applied_customer_trx_id(+)=rcta.customer_trx_id
                        and aaa.cash_receipt_id=acr.cash_receipt_id(+)
                        and rcta.status_trx like 'OP'
                        and rcta.CUST_TRX_TYPE_ID=rctta.CUST_TRX_TYPE_ID
                        and apsa.CUSTOMER_TRX_ID=rcta.CUSTOMER_TRX_ID
                        and TO_NUMBER(apsa.AMOUNT_DUE_REMAINING) >0
                        --and hp.party_id=papf.party_id(+)
                        and jrs.salesrep_id = ooha.SALESREP_ID
                        and jrs.ORG_ID = ooha.ORG_ID
                        and jrs.PERSON_ID = papf.PERSON_ID(+)
) a,
(
select
b.order_number,
sum(b.AMOUNT_APPLIED) unapplied_amt
from
                        (select distinct to_char(ooha.order_number) order_number,ara.* from 
                        oe_order_headers_all ooha,
                        oe_payments oe,
                        ar_receivable_applications_all ara
where                    1=1--ooha.order_number = :p_order_num
                        and oe.header_id=ooha.header_id
                        and ara.PAYMENT_SET_ID=oe.PAYMENT_SET_ID
                        and ara.DISPLAY='Y'
                        and (ara.STATUS like 'OTHER ACC' or ara.STATUS like 'UNAPP') --or ara.STATUS like 'ACC')
                        ) b
group by b.order_number
) adv
where                       adv.order_number(+)=a.order_number
group by                    a.account_number,
                            a.party_id,
                            a.TRX_NUMBER,
                            a.trx_date,
                            a.order_number,
                            adv.unapplied_amt,
                            a.Finance,
                            a.customer_name,a.PARTY_NAME,
                            a.customer_number,a.contact_number,
                            a.name,
                            a.Aging,
                            a.transaction_type,
                            a.exec_name,
                            a.team_leader
order by a.Aging desc

回复收藏 0 原文

~没有更多了~