Business Intelligence Applications Consumer -
Version 7.9.6.3 [AN 1900] and later
Information in this document applies to any platform.
Information in this document applies to any platform.
Implemented OBIA 7.9.6.3 from JDE Edwards 9.1 for finance and
Supply chain analytics. In SDE_JDE_AR_Transaction_Fact_Extract workflow the
query used in the session SDE_JDE_AR_Transaction_Fact_Extract_Invoice_Receipt
load is NOT having any date and time or $$Last_Extract_JDE_Date parameter
filter for bringing incremental records. Also it's pointing to
SDE_JDE_AR_Transaction_Fact_Extract_Full_Load mapping.Every time it brings the
complete set of records in source.Hence the mapping is taking huge time to complete.
<Bug 16392156>
OBIA7963:JDE9.1 SDE_JDE_AR_TRANSACTION_FACT_EXTRACT_INVOICE_RECEIPT LOAD ISSUE
Please reapply the SQL
override to the SDE_JDE_AR_Transaction_Fact_Extract_Invoice_Receipt_Load
session.
Here is the OOTB SQL for that session:
Here is the OOTB SQL for that session:
SELECT
F03B11.RPDOC,
F03B11.RPDCT,
F03B11.RPKCO,
F03B11.RPSFX,
F03B11.RPAN8,
F03B11.RPDGJ,
F03B11.RPDIVJ,
F03B11.RPICUT,
F03B11.RPICU,
F03B11.RPFY,
F03B11.RPCTRY,
F03B11.RPCO,
F03B11.RPAID,
F03B11.RPPYR,
F03B11.RPPOST,
F03B11.RPPST,
F03B11.RPAG,
F03B11.RPAAP,
B.CCCRCD,
F03B11.RPTXA1,
F03B11.RPGLBA,
F03B11.RPMCU,
F03B11.RPSBLT,
F03B11.RPSBL,
F03B11.RPPTC,
F03B11.RPDDJ,
F03B11.RPRMK,
F03B11.RPITM,
F03B11.RPU,
F03B11.RPUM,
F03B11.RPRYIN,
F03B11.RPUSER,
F03B11.RPUPMJ,
F03B11.RPUPMT,
F4201.SHDOCO,
F4201.SHAN8,
F4201.SHSHAN,
F4201.SHTRDJ,
F4201.SHVR01,
(CASE WHEN F03B11.RPACR != 0.0 THEN F03B11.RPACR
ELSE F03B11.RPAG END) AS
DOC_AMT,
RECEIPT_NO ,
RECEIPT_ITEM,
(CASE WHEN F03B11.RPPST='P' THEN RECEIPT_DATE ELSE NULL END)
AS CLEARED_ON_DT,
RECEIPT_DATE AS CLEARING_DOC_DT,
(CASE WHEN F03B11.RPFAP != 0.0 THEN F03B11.RPFAP
ELSE F03B11.RPAAP END) AS
DOC_AMT_OPEN,
F03B14_DATE.RZDCTM,
F03B11.RPCRCD,
CAST(F03B11.RPDOC as char(53)) AS ACCT_DOC_NUM,
CAST(F03B11.RPSFX as char(53))AS ACCT_DOC_ITEM,
RPPN,
$$DB_SPECIFIC_NULL AS RZTYIN,
RZTYIN AS CLRNG_DOC,
0 AS RZPYID,
F4201.SHUPMJ,
0 AS RYUPMJ,
F4201.SHCO,
RZKCO,
MULT_CURR_FLG_CHK.CCCRYR,
'N' AS DELETE_FLG,
(CASE WHEN F03B11.RPDCT ='RB' AND F03B11.RPODOC > 0 THEN 'Y' ELSE 'N' END) AS
CGBK_FLG,
F03B11.RPTORG,
F03B11.RPERDJ,
F03B11.RPDCT AS INVOICE_DOC_TYPE,
0 AS X_CUSTOM
FROM F03B11 LEFT OUTER JOIN F4201
ON (F03B11.RPSKCO=F4201.SHKCOO
AND F03B11.RPSDCT=F4201.SHDCTO AND
F03B11.RPSDOC = F4201.SHDOCO)
LEFT OUTER JOIN
(SELECT RZDOC,RZDCT,RZKCO,max(RZDCTM) AS
RZDCTM,RZSFX,
MAX(RZCKNU) AS RECEIPT_NO, MAX(RZRC5) AS RECEIPT_ITEM,
MAX(RZDMTJ)AS RECEIPT_DATE,MAX(RZPYID) AS RZPYID,MAX(RZTYIN)
AS RZTYIN,
MAX(RZUPMJ) AS RZUPMJ
FROM F03B14 X
WHERE RZDMTJ IN (SELECT MAX(RZDMTJ)
FROM F03B14 Y
WHERE X.RZDOC = Y.RZDOC
AND X.RZDCT =
Y.RZDCT
AND X.RZKCO = Y.RZKCO
AND X.RZSFX = Y.RZSFX) AND RZVDGJ = 0
GROUP BY RZKCO,RZDCT,RZDOC,RZSFX) F03B14_DATE
ON
(F03B11.RPDOC=F03B14_DATE.RZDOC AND
F03B11.RPDCT=F03B14_DATE.RZDCT AND
F03B11.RPKCO=F03B14_DATE.RZKCO AND
F03B14_DATE.RZSFX=F03B11.RPSFX ),
F0010 B,(SELECT CCCRYR FROM F0010 WHERE CCCO = '00000')
MULT_CURR_FLG_CHK
WHERE RPDCT NOT IN('R1','RU','R5')
AND B.CCCO = F03B11.RPCO
AND F03B11.RPISTR <= '0'
AND RPVDGJ = 0
AND (F03B11.RPUPMJ >=$$LAST_EXTRACT_JDEDATE OR F4201.SHUPMJ >=
$$LAST_EXTRACT_JDEDATE OR F03B14_DATE.RZUPMJ >= $$LAST_EXTRACT_JDEDATE )
UNION ALL
SELECT
F03B14.RZDOC,
F03B14.RZDCTm AS rzdct,
F03B14.RZKCO,
F03B14.RZSFX,
F03B14.RZAN8,
F03B14.RZDGJ,
F03B11.RPDIVJ,
F03B14.RZICUT,
F03B14.RZICU,
F03B14.RZFY,
F03B14.RZCTRY,
F03B14.RZCO,
F03B14.RZAID,
F03B13.RYPYR AS PYR,
F03B14.RZPOST,
F03B11.RPPST,
(F03B14.RZPAAP+F03B14.RZADSA) AS RZPAAP,
0.00 AS AAP,
B.CCCRCD,
F03B11.RPTXA1,
F03B11.RPGLBA,
F03B14.RZMCU,
F03B14.RZSBLT,
F03B14.RZSBL,
F03B11.RPPTC,
F03B14.RZDDJ,
F03B14.RZRMK,
F03B11.RPITM,
F03B11.RPU,
F03B11.RPUM,
F03B11.RPRYIN,
F03B14.RZUSER,
F03B14.RZUPMJ,
F03B14.RZUPMT,
F4201.SHDOCO,
F4201.SHAN8,
F4201.SHSHAN,
F4201.SHTRDJ,
F4201.SHVR01,
(CASE WHEN F03B14.RZTAAP != 0.0 THEN
(F03B14.RZTAAP+F03B14.RZTADA) ELSE (F03B14.RZPAAP+F03B14.RZADSA) END) AS
DOC_AMT,
F03B14.RZCKNU,
F03B14.RZRC5,
F03B14.RZDMTJ AS CLEARED_ON_DT,
F03B14.RZDMTJ AS CLEARING_DOC_DT,
0.0 AS FAP,
F03B14.RZDCTM,
(CASE WHEN F03B14.RZTAAP != 0.0 THEN F03B14.RZTCRC ELSE
F03B14.RZCRCD END) AS RZCRCD,
CAST(F03B14.RZCKNU as char(53)) AS ACCT_DOC_NUM,
CAST(F03B14.RZRC5 as char(53)) AS ACCT_DOC_ITEM,
RZPN
,RZTYIN,
RZTYIN AS CLRNG_DOC,
RZPYID,
F4201.SHUPMJ,
F03B13.RYUPMJ,
F4201.SHCO,
RZKCO,
MULT_CURR_FLG_CHK.CCCRYR,
(CASE WHEN F03B14.RZVDGJ=0 THEN 'N' ELSE 'Y' END) AS DELETE_FLG,
'N' AS CGBK_FLG,
F03B14.RZTORG,
F03B11.RPERDJ,
F03B14.RZDCT AS INVOICE_DOC_TYPE,
0 AS X_CUSTOM
FROM (SELECT CCCRYR FROM F0010 WHERE CCCO = '00000')
MULT_CURR_FLG_CHK,F03B14, F03B13, F0010 B,F03B11
LEFT OUTER JOIN F4201 ON
(F03B11.RPSKCO=F4201.SHKCOO AND
F03B11.RPSDCT=F4201.SHDCTO AND
CAST(F03B11.RPSDOC as char(16)) = CAST(F4201.SHDOCO as char(16)))
WHERE F03B14.RZDOC=F03B11.RPDOC AND
F03B14.RZDCT=F03B11.RPDCT AND
F03B14.RZKCO=F03B11.RPKCO AND
F03B14.RZSFX=F03B11.RPSFX AND
B.CCCO = F03B14.RZCO
AND ((RZPYID = RYPYID AND RZDCTM != 'R1'
AND RZDCT LIKE 'R%' )
OR (RZPYID = RYPYID
AND RZTYIN = 'A'
AND RZDCTM = 'R1'
AND RYDDST = '0' ))
AND( F03B14.RZUPMJ >= $$LAST_EXTRACT_JDEDATE OR
F03B13.RYUPMJ >= $$LAST_EXTRACT_JDEDATE OR
F03B11.RPUPMJ >= $$LAST_EXTRACT_JDEDATE OR F4201.SHUPMJ >=
$$LAST_EXTRACT_JDEDATE )
F03B11.RPDOC,
F03B11.RPDCT,
F03B11.RPKCO,
F03B11.RPSFX,
F03B11.RPAN8,
F03B11.RPDGJ,
F03B11.RPDIVJ,
F03B11.RPICUT,
F03B11.RPICU,
F03B11.RPFY,
F03B11.RPCTRY,
F03B11.RPCO,
F03B11.RPAID,
F03B11.RPPYR,
F03B11.RPPOST,
F03B11.RPPST,
F03B11.RPAG,
F03B11.RPAAP,
B.CCCRCD,
F03B11.RPTXA1,
F03B11.RPGLBA,
F03B11.RPMCU,
F03B11.RPSBLT,
F03B11.RPSBL,
F03B11.RPPTC,
F03B11.RPDDJ,
F03B11.RPRMK,
F03B11.RPITM,
F03B11.RPU,
F03B11.RPUM,
F03B11.RPRYIN,
F03B11.RPUSER,
F03B11.RPUPMJ,
F03B11.RPUPMT,
F4201.SHDOCO,
F4201.SHAN8,
F4201.SHSHAN,
F4201.SHTRDJ,
F4201.SHVR01,
(CASE WHEN F03B11.RPACR != 0.0 THEN F03B11.RPACR
ELSE F03B11.RPAG END) AS
DOC_AMT,
RECEIPT_NO ,
RECEIPT_ITEM,
(CASE WHEN F03B11.RPPST='P' THEN RECEIPT_DATE ELSE NULL END)
AS CLEARED_ON_DT,
RECEIPT_DATE AS CLEARING_DOC_DT,
(CASE WHEN F03B11.RPFAP != 0.0 THEN F03B11.RPFAP
ELSE F03B11.RPAAP END) AS
DOC_AMT_OPEN,
F03B14_DATE.RZDCTM,
F03B11.RPCRCD,
CAST(F03B11.RPDOC as char(53)) AS ACCT_DOC_NUM,
CAST(F03B11.RPSFX as char(53))AS ACCT_DOC_ITEM,
RPPN,
$$DB_SPECIFIC_NULL AS RZTYIN,
RZTYIN AS CLRNG_DOC,
0 AS RZPYID,
F4201.SHUPMJ,
0 AS RYUPMJ,
F4201.SHCO,
RZKCO,
MULT_CURR_FLG_CHK.CCCRYR,
'N' AS DELETE_FLG,
(CASE WHEN F03B11.RPDCT ='RB' AND F03B11.RPODOC > 0 THEN 'Y' ELSE 'N' END) AS
CGBK_FLG,
F03B11.RPTORG,
F03B11.RPERDJ,
F03B11.RPDCT AS INVOICE_DOC_TYPE,
0 AS X_CUSTOM
FROM F03B11 LEFT OUTER JOIN F4201
ON (F03B11.RPSKCO=F4201.SHKCOO
AND F03B11.RPSDCT=F4201.SHDCTO AND
F03B11.RPSDOC = F4201.SHDOCO)
LEFT OUTER JOIN
(SELECT RZDOC,RZDCT,RZKCO,max(RZDCTM) AS
RZDCTM,RZSFX,
MAX(RZCKNU) AS RECEIPT_NO, MAX(RZRC5) AS RECEIPT_ITEM,
MAX(RZDMTJ)AS RECEIPT_DATE,MAX(RZPYID) AS RZPYID,MAX(RZTYIN)
AS RZTYIN,
MAX(RZUPMJ) AS RZUPMJ
FROM F03B14 X
WHERE RZDMTJ IN (SELECT MAX(RZDMTJ)
FROM F03B14 Y
WHERE X.RZDOC = Y.RZDOC
AND X.RZDCT =
Y.RZDCT
AND X.RZKCO = Y.RZKCO
AND X.RZSFX = Y.RZSFX) AND RZVDGJ = 0
GROUP BY RZKCO,RZDCT,RZDOC,RZSFX) F03B14_DATE
ON
(F03B11.RPDOC=F03B14_DATE.RZDOC AND
F03B11.RPDCT=F03B14_DATE.RZDCT AND
F03B11.RPKCO=F03B14_DATE.RZKCO AND
F03B14_DATE.RZSFX=F03B11.RPSFX ),
F0010 B,(SELECT CCCRYR FROM F0010 WHERE CCCO = '00000')
MULT_CURR_FLG_CHK
WHERE RPDCT NOT IN('R1','RU','R5')
AND B.CCCO = F03B11.RPCO
AND F03B11.RPISTR <= '0'
AND RPVDGJ = 0
AND (F03B11.RPUPMJ >=$$LAST_EXTRACT_JDEDATE OR F4201.SHUPMJ >=
$$LAST_EXTRACT_JDEDATE OR F03B14_DATE.RZUPMJ >= $$LAST_EXTRACT_JDEDATE )
UNION ALL
SELECT
F03B14.RZDOC,
F03B14.RZDCTm AS rzdct,
F03B14.RZKCO,
F03B14.RZSFX,
F03B14.RZAN8,
F03B14.RZDGJ,
F03B11.RPDIVJ,
F03B14.RZICUT,
F03B14.RZICU,
F03B14.RZFY,
F03B14.RZCTRY,
F03B14.RZCO,
F03B14.RZAID,
F03B13.RYPYR AS PYR,
F03B14.RZPOST,
F03B11.RPPST,
(F03B14.RZPAAP+F03B14.RZADSA) AS RZPAAP,
0.00 AS AAP,
B.CCCRCD,
F03B11.RPTXA1,
F03B11.RPGLBA,
F03B14.RZMCU,
F03B14.RZSBLT,
F03B14.RZSBL,
F03B11.RPPTC,
F03B14.RZDDJ,
F03B14.RZRMK,
F03B11.RPITM,
F03B11.RPU,
F03B11.RPUM,
F03B11.RPRYIN,
F03B14.RZUSER,
F03B14.RZUPMJ,
F03B14.RZUPMT,
F4201.SHDOCO,
F4201.SHAN8,
F4201.SHSHAN,
F4201.SHTRDJ,
F4201.SHVR01,
(CASE WHEN F03B14.RZTAAP != 0.0 THEN
(F03B14.RZTAAP+F03B14.RZTADA) ELSE (F03B14.RZPAAP+F03B14.RZADSA) END) AS
DOC_AMT,
F03B14.RZCKNU,
F03B14.RZRC5,
F03B14.RZDMTJ AS CLEARED_ON_DT,
F03B14.RZDMTJ AS CLEARING_DOC_DT,
0.0 AS FAP,
F03B14.RZDCTM,
(CASE WHEN F03B14.RZTAAP != 0.0 THEN F03B14.RZTCRC ELSE
F03B14.RZCRCD END) AS RZCRCD,
CAST(F03B14.RZCKNU as char(53)) AS ACCT_DOC_NUM,
CAST(F03B14.RZRC5 as char(53)) AS ACCT_DOC_ITEM,
RZPN
,RZTYIN,
RZTYIN AS CLRNG_DOC,
RZPYID,
F4201.SHUPMJ,
F03B13.RYUPMJ,
F4201.SHCO,
RZKCO,
MULT_CURR_FLG_CHK.CCCRYR,
(CASE WHEN F03B14.RZVDGJ=0 THEN 'N' ELSE 'Y' END) AS DELETE_FLG,
'N' AS CGBK_FLG,
F03B14.RZTORG,
F03B11.RPERDJ,
F03B14.RZDCT AS INVOICE_DOC_TYPE,
0 AS X_CUSTOM
FROM (SELECT CCCRYR FROM F0010 WHERE CCCO = '00000')
MULT_CURR_FLG_CHK,F03B14, F03B13, F0010 B,F03B11
LEFT OUTER JOIN F4201 ON
(F03B11.RPSKCO=F4201.SHKCOO AND
F03B11.RPSDCT=F4201.SHDCTO AND
CAST(F03B11.RPSDOC as char(16)) = CAST(F4201.SHDOCO as char(16)))
WHERE F03B14.RZDOC=F03B11.RPDOC AND
F03B14.RZDCT=F03B11.RPDCT AND
F03B14.RZKCO=F03B11.RPKCO AND
F03B14.RZSFX=F03B11.RPSFX AND
B.CCCO = F03B14.RZCO
AND ((RZPYID = RYPYID AND RZDCTM != 'R1'
AND RZDCT LIKE 'R%' )
OR (RZPYID = RYPYID
AND RZTYIN = 'A'
AND RZDCTM = 'R1'
AND RYDDST = '0' ))
AND( F03B14.RZUPMJ >= $$LAST_EXTRACT_JDEDATE OR
F03B13.RYUPMJ >= $$LAST_EXTRACT_JDEDATE OR
F03B11.RPUPMJ >= $$LAST_EXTRACT_JDEDATE OR F4201.SHUPMJ >=
$$LAST_EXTRACT_JDEDATE )
No comments:
Post a Comment