17,377
社区成员
发帖
与我相关
我的任务
分享
SELECT
A.AB_ADJUSTMENT_ID,
A.PARENT_TYPE,
A.PARENT_ID,
A.ORG_NUM,
A.ADJUSTMENT_DATE,
A.STATUS,
A.ADJUSTMENT_NUM,
B.AMT,
c.bizunit_key,
C.costcentre_key,
D.item_desc,
G.reg_name,
G.reg_num,
G.reg_type,
B.MYNUMS
FROM ABADJUSTMENT A
JOIN ABINVOICE C ON A.PARENT_ID = C.INVOICE_ID
JOIN ABINVOICEITEM D ON C.INVOICE_ID = D.INVOICE_ID
JOIN TMORGPROFILEBRANCH H ON A.ORG_NUM = H.PB_ID
JOIN TMORGBRANCH I ON H.BRANCH_ID = I.BRANCH_ID
JOIN TMORG G ON I.ORG_ID = G.ORG_ID
JOIN
(
SELECT B.AB_ADJUSTMENT_ID,SUM(ADJUSTMENT_AMT) AMT,WMSYS.WM_CONCAT(E.AB_CREDITNOTE_NUM || E.RECEIPT_NUM) MYNUMS
FROM ABADJUSTMENTDETAIL B
LEFT JOIN ABCREDITNOTE E ON B.ADJUSTMENTSRC_ID = E.AB_CREDITNOTE_ID
LEFT JOIN ABOFFLINERECEIPT F ON B.ADJUSTMENTSRC_ID = F.AB_RECEIPT_ID
GROUP BY B.AB_ADJUSTMENT_ID
) B ON A.AB_ADJUSTMENT_ID=B.AB_ADJUSTMENT_ID
--试一下:
SELECT *
FROM (SELECT tt.AB_ADJUSTMENT_ID,
tt.PARENT_TYPE,
tt.PARENT_ID,
tt.ORG_NUM,
tt.ADJUSTMENT_DATE,
tt.STATUS,
tt.ADJUSTMENT_NUM,
tt.SUM(AMT) AS ADJUSTMENT_AMT,
tt.bizunit_key,
tt.costcentre_key,
tt.item_desc,
tt.reg_name,
tt.reg_num,
tt.reg_type,
WMSYS.WM_CONCAT(DISTINCT tt.MYNUMS) ADJ_SOURCE_NUM,
row_number() over(partition by tt.AB_ADJUSTMENT_ID order by tt.AB_ADJUSTMENT_ID) row_number
FROM (SELECT A.AB_ADJUSTMENT_ID AB_ADJUSTMENT_ID,
A.PARENT_TYPE PARENT_TYPE,
A.PARENT_ID PARENT_ID,
A.ORG_NUM ORG_NUM,
A.ADJUSTMENT_DATE ADJUSTMENT_DATE,
A.STATUS STATUS,
A.ADJUSTMENT_NUM ADJUSTMENT_NUM,
B.ADJUSTMENT_AMT AMT,
B.AB_ADJUSTMENT_DETAIL_ID AB_ADJUSTMENT_DETAIL_ID,
B.ADJUSTMENTSRC_TYPE ADJUSTMENTSRC_TYPE,
C.bizunit_key bizunit_key,
C.costcentre_key costcentre_key,
D.item_desc item_desc,
G.reg_name reg_name,
G.reg_num reg_num,
G.reg_type reg_type,
E.AB_CREDITNOTE_NUM MYNUMS
FROM ABADJUSTMENT A,
ABADJUSTMENTDETAIL B,
ABINVOICE C,
ABINVOICEITEM D,
ABCREDITNOTE E,
TMORG G,
TMORGPROFILEBRANCH H,
TMORGBRANCH I
WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
AND A.PARENT_ID = C.INVOICE_ID
AND C.INVOICE_ID = D.INVOICE_ID
AND B.ADJUSTMENTSRC_ID = E.AB_CREDITNOTE_ID
AND A.ORG_NUM = H.PB_ID
AND H.BRANCH_ID = I.BRANCH_ID
AND I.ORG_ID = G.ORG_ID
UNION
SELECT A.AB_ADJUSTMENT_ID AB_ADJUSTMENT_ID,
A.PARENT_TYPE PARENT_TYPE,
A.PARENT_ID PARENT_ID,
A.ORG_NUM ORG_NUM,
A.ADJUSTMENT_DATE ADJUSTMENT_DATE,
A.STATUS STATUS,
A.ADJUSTMENT_NUM ADJUSTMENT_NUM,
B.ADJUSTMENT_AMT AMT,
B.AB_ADJUSTMENT_DETAIL_ID AB_ADJUSTMENT_DETAIL_ID,
B.ADJUSTMENTSRC_TYPE ADJUSTMENTSRC_TYPE,
c.bizunit_key bizunit_key,
C.costcentre_key costcentre_key,
D.item_desc item_desc,
G.reg_name reg_name,
G.reg_num reg_num,
G.reg_type reg_type,
F.RECEIPT_NUM MYNUMS
FROM ABADJUSTMENT A,
ABADJUSTMENTDETAIL B,
ABINVOICE C,
ABINVOICEITEM D,
ABOFFLINERECEIPT F,
TMORG G,
TMORGPROFILEBRANCH H,
TMORGBRANCH I
WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
AND A.PARENT_ID = C.INVOICE_ID
AND C.INVOICE_ID = D.INVOICE_ID
AND B.ADJUSTMENTSRC_ID = F.AB_RECEIPT_ID
AND A.ORG_NUM = H.PB_ID
AND H.BRANCH_ID = I.BRANCH_ID
AND I.ORG_ID = G.ORG_ID) TT
GROUP BY tt.AB_ADJUSTMENT_ID,
tt.PARENT_ID,
tt.PARENT_TYPE,
tt.ORG_NUM,
tt.ADJUSTMENT_DATE,
tt.STATUS,
tt.ADJUSTMENT_NUM,
tt.bizunit_key,
tt.costcentre_key,
tt.item_desc,
tt.reg_name,
tt.reg_num,
tt.reg_type) tab
where tab.row_number = 1;
SELECT * FROM (
SELECT AB_WRITEOFF_ID,WRITEOFFSRC_TYPE,WRITEOFFSRC_ID,WRITEOFF_DATE,WRITEOFF_AMT,
STATUS,WRITEOFF_NUM,reg_name,reg_num,reg_type,
WMSYS.WM_CONCAT(DISTINCT MYBIZUNIT) bizunit_key,
WMSYS.WM_CONCAT(DISTINCT MYCOSTCENTRE) costcentre_key,
WMSYS.WM_CONCAT(DISTINCT MYITEMDESC) item_desc,
WMSYS.WM_CONCAT(DISTINCT MYORGNUM) ORG_NUM,
WMSYS.WM_CONCAT(DISTINCT MYSOURCENUM) writeoff_src_num,
row_number() over (partition by AB_WRITEOFF_ID order by AB_WRITEOFF_ID) row_number
FROM (SELECT A.AB_WRITEOFF_ID AB_WRITEOFF_ID,
A.WRITEOFFSRC_TYPE WRITEOFFSRC_TYPE,
A.WRITEOFFSRC_ID WRITEOFFSRC_ID,
A.WRITEOFF_DATE WRITEOFF_DATE,
A.WRITEOFF_AMT WRITEOFF_AMT,
A.WRITEOFF_NUM WRITEOFF_NUM,
A.STATUS STATUS,
G.reg_name reg_name,
G.reg_num reg_num,
G.reg_type reg_type,
to_number(B.ORG_NUM) MYORGNUM,
B.BIZUNIT_KEY MYBIZUNIT,
B.costcentre_key MYCOSTCENTRE,
C.ITEM_DESC MYITEMDESC,
B.invoice_num MYSOURCENUM
FROM ABWRITEOFF A,
ABINVOICE B,
ABINVOICEITEM C,
TMORG G,
TMORGPROFILEBRANCH H,
TMORGBRANCH I
WHERE A.WRITEOFFSRC_ID = B.INVOICE_ID
AND B.INVOICE_ID = C.INVOICE_ID
AND B.ORG_NUM = H.PB_ID
AND H.BRANCH_ID = I.BRANCH_ID
AND I.ORG_ID = G.ORG_ID
UNION
SELECT A.AB_WRITEOFF_ID AB_WRITEOFF_ID,
A.WRITEOFFSRC_TYPE WRITEOFFSRC_TYPE,
A.WRITEOFFSRC_ID WRITEOFFSRC_ID,
A.WRITEOFF_DATE WRITEOFF_DATE,
A.WRITEOFF_AMT WRITEOFF_AMT,
A.WRITEOFF_NUM WRITEOFF_NUM,
A.STATUS STATUS,
G.reg_name reg_name,
G.reg_num reg_num,
G.reg_type reg_type,
J.ORG_ID MYORGNUM,
NULL MYBIZUNIT,
NULL MYCOSTCENTRE,
'Refund Request' MYITEMDESC,
J.RF_RECEIVABLE_CODE MYSOURCENUM
FROM ABWRITEOFF A,
ABINVOICE B,
RFRECEIVABLEREQUEST J,
ABINVOICEITEM C,
TMORG G,
TMORGPROFILEBRANCH H,
TMORGBRANCH I
WHERE A.WRITEOFFSRC_ID = J.RF_RECEIVABLE_ID
AND J.ORG_ID = H.PB_ID
AND H.BRANCH_ID = I.BRANCH_ID
AND I.ORG_ID = G.ORG_ID) TT
GROUP BY AB_WRITEOFF_ID,WRITEOFFSRC_TYPE,WRITEOFFSRC_ID,WRITEOFF_DATE,WRITEOFF_AMT,WRITEOFF_NUM ,STATUS
,reg_name,reg_num,reg_type) a where a.row_number = 1 ;
SELECT * FROM (SELECT AB_ADJUSTMENT_ID, PARENT_TYPE,PARENT_ID,ORG_NUM,ADJUSTMENT_DATE,
STATUS,ADJUSTMENT_NUM,SUM(AMT) AS ADJUSTMENT_AMT,
bizunit_key,costcentre_key,item_desc,reg_name,reg_num,reg_type,
WMSYS.WM_CONCAT(DISTINCT MYNUMS) ADJ_SOURCE_NUM ,
row_number() over (partition by AB_ADJUSTMENT_ID order by AB_ADJUSTMENT_ID) row_number
FROM (SELECT A.AB_ADJUSTMENT_ID AB_ADJUSTMENT_ID,
A.PARENT_TYPE PARENT_TYPE,
A.PARENT_ID PARENT_ID,
A.ORG_NUM ORG_NUM,
A.ADJUSTMENT_DATE ADJUSTMENT_DATE,
A.STATUS STATUS,
A.ADJUSTMENT_NUM ADJUSTMENT_NUM,
B.ADJUSTMENT_AMT AMT,
B.AB_ADJUSTMENT_DETAIL_ID AB_ADJUSTMENT_DETAIL_ID,
B.ADJUSTMENTSRC_TYPE ADJUSTMENTSRC_TYPE,
C.bizunit_key bizunit_key,
C.costcentre_key costcentre_key,
D.item_desc item_desc,
G.reg_name reg_name,
G.reg_num reg_num,
G.reg_type reg_type,
E.AB_CREDITNOTE_NUM MYNUMS
FROM ABADJUSTMENT A,
ABADJUSTMENTDETAIL B,
ABINVOICE C,
ABINVOICEITEM D,
ABCREDITNOTE E,
TMORG G,
TMORGPROFILEBRANCH H,
TMORGBRANCH I
WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
AND A.PARENT_ID = C.INVOICE_ID
AND C.INVOICE_ID = D.INVOICE_ID
AND B.ADJUSTMENTSRC_ID = E.AB_CREDITNOTE_ID
AND A.ORG_NUM = H.PB_ID
AND H.BRANCH_ID = I.BRANCH_ID
AND I.ORG_ID = G.ORG_ID
UNION
SELECT A.AB_ADJUSTMENT_ID AB_ADJUSTMENT_ID,
A.PARENT_TYPE PARENT_TYPE,
A.PARENT_ID PARENT_ID,
A.ORG_NUM ORG_NUM,
A.ADJUSTMENT_DATE ADJUSTMENT_DATE,
A.STATUS STATUS,
A.ADJUSTMENT_NUM ADJUSTMENT_NUM,
B.ADJUSTMENT_AMT AMT,
B.AB_ADJUSTMENT_DETAIL_ID AB_ADJUSTMENT_DETAIL_ID,
B.ADJUSTMENTSRC_TYPE ADJUSTMENTSRC_TYPE,
c.bizunit_key bizunit_key,
C.costcentre_key costcentre_key,
D.item_desc item_desc,
G.reg_name reg_name,
G.reg_num reg_num,
G.reg_type reg_type,
F.RECEIPT_NUM MYNUMS
FROM ABADJUSTMENT A,
ABADJUSTMENTDETAIL B,
ABINVOICE C,
ABINVOICEITEM D,
ABOFFLINERECEIPT F,
TMORG G,
TMORGPROFILEBRANCH H,
TMORGBRANCH I
WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
AND A.PARENT_ID = C.INVOICE_ID
AND C.INVOICE_ID = D.INVOICE_ID
AND B.ADJUSTMENTSRC_ID = F.AB_RECEIPT_ID
AND A.ORG_NUM = H.PB_ID
AND H.BRANCH_ID = I.BRANCH_ID
AND I.ORG_ID = G.ORG_ID) TT
GROUP BY AB_ADJUSTMENT_ID,PARENT_ID,PARENT_TYPE,ORG_NUM,ADJUSTMENT_DATE,STATUS,ADJUSTMENT_NUM,bizunit_key,
costcentre_key,item_desc,reg_name,reg_num,reg_type) a where a.row_number = 1 ;
-- JUST TRY IT ..
SELECT AB_ADJUSTMENT_ID, WMSYS.WM_CONCAT(DISTINCT MYNUMS) NEW_NUMS
FROM (SELECT A.AB_ADJUSTMENT_ID AB_ADJUSTMENT_ID,
E.AB_CREDITNOTE_NUM MYNUMS
FROM ABADJUSTMENT A,
ABADJUSTMENTDETAIL B,
ABINVOICE C,
ABINVOICEITEM D,
ABCREDITNOTE E,
TMORG G,
TMORGPROFILEBRANCH H,
TMORGBRANCH I
WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
AND A.PARENT_ID = C.INVOICE_ID
AND C.INVOICE_ID = D.INVOICE_ID
AND B.ADJUSTMENTSRC_ID = E.AB_CREDITNOTE_ID
AND A.ORG_NUM = H.PB_ID
AND H.BRANCH_ID = I.BRANCH_ID
AND I.ORG_ID = G.ORG_ID
UNION
SELECT A.AB_ADJUSTMENT_ID AB_ADJUSTMENT_ID, F.RECEIPT_NUM MYNUMS
FROM ABADJUSTMENT A,
ABADJUSTMENTDETAIL B,
ABINVOICE C,
ABINVOICEITEM D,
ABOFFLINERECEIPT F,
TMORG G,
TMORGPROFILEBRANCH H,
TMORGBRANCH I
WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
AND A.PARENT_ID = C.INVOICE_ID
AND C.INVOICE_ID = D.INVOICE_ID
AND B.ADJUSTMENTSRC_ID = F.AB_RECEIPT_ID
AND A.ORG_NUM = H.PB_ID
AND H.BRANCH_ID = I.BRANCH_ID
AND I.ORG_ID = G.ORG_ID) TT
GROUP BY AB_ADJUSTMENT_ID;
[Quote=引用楼主 glm_1103 的帖子:]