Find PPV Transactions

SELECT
T1.ILLITM,
T1.ILAITM,
T1.ILMCU,
T1.ILLOCN,
T1.ILLOTN,
T1.ILKCO,
T1.ILDOC,
T1.ILDCT,
T1.ILDGL,
T1.ILGLPT,
T1.ILDCTO,
T1.ILDOCO,
T1.ILPAID,
T2.CFDGJ,
T2.CFANI,
T2.CFICU

TESTDTA.F554199 T2

T1.ILUKID,FROM TESTDTA.F4111 T1,WHERE T1.ILUKID = T2.CFUKIDAND T2.CFDGL >= 111274AND T2.CFDGL <= 111304AND t1.ilmcu = ' 3100011'

Compare Exchange Rates on Receipts Vs Exchange Rates table

SELECT T1.PRMATC,
       T1.PRAN8,
       T1.PRKCOO,
       T1.PRDOCO,
       T1.PRDCTO,
       T1.PRKCO,
       T1.PRDOC,
       T1.PRDCT,
       T1.PRSFXO,
       T1.PRLNID,
       T1.PRNLIN,
       T1.PRALIN,
       T1.PRITM,
       T1.PRLITM,
       T1.PRAITM,
       T1.PRMCU,
       T1.PRGLC,
       (SELECT TO_CHAR(TO_DATE(T1.PRDGL + 1900000, 'YYYYDDD'), 'YYYY-MM-DD')
          FROM DUAL) AS GL_DATE,
       (SELECT TO_CHAR(TO_DATE(T1.PRRCDJ + 1900000, 'YYYYDDD'), 'YYYY-MM-DD')
          FROM DUAL) AS RECEIPT_DATE,
       T1.PRCRCD,
       T1.PRBCRC,
       T1.PRCRR,
       (SELECT CXCRR
          FROM PRODDTA.F0015 T2
         WHERE T1.PRRCDJ >= T2.CXEFT
           AND T1.PRCRCD = T2.CXCRCD
           AND T1.PRBCRC = T2.CXCRDC
           AND ROWNUM < 2) "Exchange Rate",
       T1.PRAREC / 100,
       T1.PRFREC / 100
  FROM PRODDTA.F43121 T1
 WHERE T1.PRCRCD <> T1.PRBCRC
   AND T1.PRBCRC <> ' '
   AND T1.PRMATC = 1
 --  AND T1.PRDOCO = 22169
 ORDER BY T1.PRRCDJ

Compare Costs between Cost Methods

SELECT
trim(a.COMCU) as BP,
trim(a.COLITM) as GLOBAL_ITEM#,
trim(a.coaitm) AS LEGACY_ITEM#,
(a.COUNCS/10000) "01",
b.councs/10000 as "02",
c.councs/10000 as "07"
FROM qaDTA.F4105 a, qadta.f4105 b, qadta.f4105 c
WHERE a.colitm = b.colitm
and a.COmcu = b.comcu
and a.colitm = c.colitm
and a.comcu = c.comcu
and b.colitm = c.colitm
and b.comcu = c.comcu
and a.coledg = '01'
and b.coledg = '02'
and c.coledg = '07'
and a.comcu = '     3110082'

Compare Costs between Branch Plants

SELECT TRIM(A.COLITM) AS GLOBAL_ITEM#,
       TRIM(A.COAITM) AS LEGACY_ITEM#,
TRIM(A.COLEDG) AS LEDG,
       (A.COUNCS / 10000) "3110022",
       (B.COUNCS / 10000)  "3110032",
       (C.COUNCS / 10000)  "3110042"
  FROM PRODDTA.F4105 A, PRODDTA.F4105 B, PRODDTA.F4105 C
 WHERE A.COITM = B.COITM
   AND A.COITM = C.COITM
   AND A.COLEDG = B.COLEDG
   AND A.COLEDG = C.COLEDG
   AND A.COLEDG = '07'
   AND A.COMCU = '     3110022'
   AND B.COMCU = '     3110032'
   AND C.COMCU = '     3110042'

RI Transactions that were NOT revalued - Missing Avg Cost

SELECT *
  FROM PRODDTA.F4111
 WHERE ILDGL >= 111274
   AND ILDGL <= 111304
   AND ILDCT = 'RI'
AND ilkco = '00311'
   AND ILDCTO IN ('S7',
                  'S9',
                  'SA',
                  'SE',
                  'SF',
                  'SH',
                  'SJ',
                  'SN',
                  'SQ',
                  'ST',
                  'SU',
                  'SV',
                  'SW',
                  'SX',
                  'V1',
                  'V2',
                  'V4',
                  'V7',
                  'V8',
                  'V9',
                  'VA',
                  'VF',
                  'VJ',
                  'VM',
                  'VN',
                  'VQ',
                  'VR',
                  'VS',
                  'VT',
                  'VU',
                  'VV',
                  'VX',
                  'VY')
   AND NOT EXISTS
 (SELECT * FROM PRODDTA.F554113 WHERE RAUKID = ILUKID)
 AND NOT EXISTS
 (SELECT * FROM proddta.F554105 WHERE c2ctry = 20 AND c2fy = 11 AND c2pn = 10 AND c2ledg = '02' AND c2itm = ilitm AND c2mcu = ilmcu)

RI Transactions that were NOT revalued

SELECT * FROM proddta.F4111 WHERE ildgl >= 111274 AND
ildgl <= 111304 AND
ildct = 'RI' AND
ildcto IN ('S7','S9','SA','SE','SF','SH','SJ','SN','SQ','ST','SU','SV','SW','SX','V1','V2','V4','V7','V8','V9','VA','VF','VJ','VM','VN','VQ','VR','VS','VT','VU','VV','VX','VY') AND
NOT EXISTS (SELECT * FROM proddta.F554113 WHERE raukid = ilukid)

Update STKT to M when we have activity

update ${JDE_SCHEMA}.F4102 set ibstkt = 'M'
WHERE ibstkt = 'P'
and exists (SELECT 'X'
            FROM proddta.F4111
            WHERE ilmcu = ibmcu
            AND ilitm = ibitm
            AND ildct = 'IC');

Identify *OP items where QTY <> 0

SELECT * FROM proddta.F4101, proddta.F41021 WHERE lipqoh <> 0 AND liitm = imitm AND imlitm LIKE '%*OP%'

Update IPCD Flag - X for *OP items

UPDATE proddta.F4111 -- 5035 rows
SET ilipcd = 'X'
WHERE ilipcd <> 'X' AND illitm LIKE '%*OP%'

Check REVAL Location for all Branch Plants

SELECT *
  FROM PRODDTA.F0006
 WHERE MCSTYL = 'BP'
   AND MCCO IN ('00310', '00311')
   AND NOT EXISTS (SELECT *
          FROM PRODDTA.F4100
         WHERE LMLOCN = 'REVAL'
           AND LMMCU = MCMCU)

Identify Purchased Items with Inventory Completion in Cardex

SELECT * FROM proddta.F4102 WHERE
ibstkt = 'P' AND
 EXISTS
(SELECT * FROM proddta.F4111 WHERE ilmcu = ibmcu AND ilitm = ibitm AND ildct = 'IC' AND ildgl <=111304)

Load F5541052 based on F554105

INSERT INTO proddta.F5541052
(SELECT c2ctry, c2fy, c2pn, c2ledg, c2itm, c2litm, c2aitm, c2mcu, c2locn, c2lotn, 0,c2uncs, 0, 0, 0, 0, 0, 0, ' ', 0, 0, 0, 'SQLSYNC', 'SQLFIX', 'SQL', 'SQL', 111273, 63700  FROM proddta.F554105 WHERE
c2litm = 'C2118220' AND
c2pn = 7 AND
NOT EXISTS (SELECT * FROM proddta.F5541052 WHERE c2itm = miitm AND c2mcu = mimmcu AND c2pn = mipn AND c2ctry = mictry AND c2fy = mify))

Compare F554105 Vs F5541052

SELECT * FROM proddta.F554105 WHERE c2pn = 8 AND c2litm = 'F204020' AND NOT EXISTS (SELECT * FROM proddta.F5541052 WHERE mipn = 8 AND miitm = c2itm AND mimmcu = c2mcu)

Negative Inventory in As of

SELECT initm, inmcu, ((SUM(incmqt)+SUM(innq01)+SUM(innq02)+SUM(innq03)+SUM(innq04)+SUM(innq05)+SUM(innq06)+SUM(innq07)+SUM(innq08)+SUM(innq09))/1000) Balance_QTY
FROM qadta.F41112
WHERE indct = 'BF' AND
infy = 11 AND
inctry = 20
GROUP BY initm, inmcu
HAVING (SUM(incmqt)+SUM(innq01)+SUM(innq02)+SUM(innq03)+SUM(innq04)+SUM(innq05)+SUM(innq06)+SUM(innq07)+SUM(innq08)+SUM(innq09)) <0

Identify Reval Records by DCTO

SELECT ilitm, illitm, ilaitm, ilmcu, illocn, illotn, ilkco, ildoc, ildct, ilicu, ildgl, ilglpt, iltrex, ilrcd, iluncs, ilpaid, ilukid  FROM  proddta.F4111, proddta.F554114
WHERE
ilukid = pcukid AND
pcev07 = 'A' AND
--ildgl >= 111244 AND
ildgl = 111273 AND
--ilitm = 300000 AND
pcuk01 IN (SELECT DISTINCT(pcuk01) FROM proddta.F554114, proddta.F554113 WHERE
rauk01 = pcuk01 AND pcev07 = 'D' AND
radcto = 'O7')

REVAL Join of Cardex with Control Tables

SELECT ilkco, pcev07, COUNT(*) FROM proddta.F554113, proddta.F554114, proddta.F4111
WHERE
rauk01 = pcuk01 AND
raukid = pcukid AND
raukid = ilukid AND
--raitm = 349869 AND
pcev07 = 'E'
GROUP BY ilkco, pcev07

AVG Vs Std Cost with Inv from As of

SELECT t1.C2ctry, t1.C2fy, t1.C2itm, t1.C2litm, t1.C2mcu, t3.c0mmcu, t1.C2ledg, t2.Coledg,  t1.C2uncs/10000, t2.Councs/10000 ,t4.inlocn, t4.inlotn, t4.inglpt, t4.innq09/1000 FROM proddta.F554105 t1, proddta.F4105 t2, proddta.F554103 t3, proddta.F41112 t4
WHERE
t1.c2ledg = '02' AND
t2.coledg = '07' AND
t1.c2itm = t2.coitm AND
t1.c2mcu = t2.comcu AND
t1.c2pn = 9 AND
t2.coitm = t4.initm AND
t2.comcu = t4.inmcu AND
t4.innq09 <> 0 AND
--t2.c2pn  = 8 AND
--t1.c2uncs <> t2.c2uncs AND
t1.c2mcu = '     3110022' AND
t1.c2mcu = t3.c0mcu
ORDER BY t1.C2ctry, t1.C2fy, t1.C2itm, t1.C2litm, t3.C0mmcu, t1.c2mcu;

Compare Transaction Nature

SELECT prkcoo, prdoco, prdcto, prlnid, prnlin, prsfxo, prmcu, fdbnnf,  prdcto, pjbnop, pjbsop,fdbnop, fdbsop FROM proddta.F43121, proddta.F76432, proddta.F7611B WHERE
prdoco = pjdoco AND
prdcto = pjdcto AND
prkcoo = pjkcoo AND
prsfxo = pjsfxo AND
prlnid = pjlnid AND
PrDOCO = FDDOCO AND
PRDCTO = fdPDCT AND
PRLNID = FDLNID AND
prnlin = fdnlin AND
prsfxo = fdsfxo AND
prdgl <= 111243 AND
prmatc = 1 AND
pjbnop||pjbsop <> fdbnop||fdbsop

Validate Transaction Nature

SELECT prkcoo, prdcto, pjbnop, pjbsop, COUNT(*) FROM qadta.F43121, qadta.F76432 WHERE
prdoco = pjdoco AND
prdcto = pjdcto AND
prkcoo = pjkcoo AND
prsfxo = pjsfxo AND
prlnid = pjlnid AND
prdgl <= 111243 AND
prmatc = 1 AND
pjbnop||pjbsop IN (SELECT trim(drky) FROM qactl.F0005 WHERE drsy = '55' AND drrt IN ('FA'))
--prlitm IN ( 'F194548', 'F194524', 'F194643','F194700')
GROUP BY prkcoo, prdcto, pjbnop, pjbsop

Standard Vs Avg Cost

SELECT t1.C2ctry, t1.C2fy, t1.C2itm, t1.C2litm, t1.C2mcu, t3.c0mmcu, t1.C2ledg, t2.Coledg,  t1.C2uncs/10000, t2.Councs/10000  FROM qadta.F554105 t1, qadta.F4105 t2, qadta.F554103 t3
WHERE
t1.c2ledg = '02' AND
t2.coledg = '07' AND
t1.c2itm = t2.coitm AND
t1.c2mcu = t2.comcu AND
t1.c2pn = 8 AND
--t2.c2pn  = 8 AND
--t1.c2uncs <> t2.c2uncs AND
t1.c2mcu LIKE '%310%' AND
t1.c2mcu = t3.c0mcu
ORDER BY t1.C2ctry, t1.C2fy, t1.C2itm, t1.C2litm, t3.C0mmcu, t1.c2mcu;

Data Selection Security in EnterpriseOne

Data Selection Security in EnterpriseOne

One of newest types of security for JD Edwards EnterpriseOne is Data Selection security.  Using Data Selection security CNC administrators can secure users from modifying, adding, deleting, and viewing the data selection for batch applications or specific versions of batch applications.

Data Selection security was made available with Tools Release 8.98 Update 1 (8.98.1.0) and has a minimum application release level requirement of 8.12.  The functionality also requires that Tools Baseline ESU JK17733 or newer be applied.

Data Selection is already disallowed for versions secured with an "old style" version security value of 1 where the Last Modified User is the only one who can change the version. Typically, the XJDE and ZJDE versions are delivered with this security value.  However, for custom versions that are not secured in this manner the new Data Selection security can be used to gain fine-grained control over the actions that are to be allowed for Data Selection.

Some important points to keep in mind when considering Data Selection Security:

• Data Selection security applies to data selection during submission of a batch application or report.
• Data selection security is enforced only for end users submitting batch applications or reports from a web client.

Enabling
 
Data Selection security is enabled when the application release is at 8.12 or higher, Tools Release 8.98.1.0 has been installed and Tools Baseline ESU JK17733 or newer has been applied.  Once Data Selection security is enabled you will see a new Hyper Exit button in Work With User/Role Security (P00950).

If you do not see the button, chances are that you have not met one or more of the requirements mentioned above.  The form used for Data Selection security is the same one formerly used solely for Processing Option Security.



Setup and Utilization

There are four different Data Selection security options - Prompt for Data Selection, Full Access Data Selection, Modify Data Selection and Add Data Selection.

Prompt for Data Selection is the most restrictive, disallowing the user from even seeing the Data Selection.  

Full Access Data Selection prevents a user from deleting existing Data Selection rows. 

Modify Data Selection prevents expanding or changing existing criteria.  

Add Data Selection prevents a user from adding new Data Selection criteria.

  
 Prompt for Data Selection

When only the Prompt for Data Selection option is selected the user will still be able to select the "Data Selection" check box but will receive the following error:

 Full Access Data Selection

The next most restrictive option is Full Access Data Selection.  This option prevents a user from having a full set of the editing capabilities on the data selection screen.


When only the Full Access Data Selection option is selected the user will be able to modify values for existing data selection rows and add data selection rows with AND operator but not OR operator.  The user will not be able to delete existing rows.

Enabling the Full Access Data Selection option allows the use of two more options that can be used to further restrict Data Selection - Modify Data Selection and Add Data Selection.  The Full Access Data Selection, Modify Data Selection and Add Data Selection options can be used in any combination to provide the desired level of Data Selection security.

 Modify Data Selection

When the Full Access Data Selection and Modify Data Selection options are selected the user will not be able to modify values for existing data selection rows but will be able to add data selection rows with AND operator but not OR operator.  The user will not be able to delete existing rows.

 Add Data Selection

When the Full Access Data Selection and Add Data Selection options are selected the user will be able to modify values for existing data selection rows but will not be able to add data selection rows. The user will not be able to delete existing rows.

 Modify Data Selection plus Add Data Selection

When the Full Access Data Selection, Modify Data Selection and Add Data Selection options are selected the user will not be able to modify values for existing data selection rows and will not be able to add data selection rows. The user will not be able to delete existing rows.  This is essentially a read-only configuration for Data Selection.

Options Summary

Prompt for Data Selection

•     Cannot see or change data selection

Full Access Data Selection

•     Can modify values for existing data selection rows
•     Can add data selection rows with AND operator but not OR operator
•     Cannot delete existing rows

Full Access Data Selection + Modify Data Selection

•     Cannot modify values for existing data selection rows
•     Can add data selection rows with AND operator but not OR operator
•     Cannot delete existing rows

Full Access Data Selection + Add Data Selection

•     Can modify values for existing data selection rows
•     Cannot add data selection rows with AND operator but not OR operator
•     Cannot delete existing rows

Full Access Data Selection + Modify Data Selection + Add Data Selection

•     Cannot modify values for existing data selection rows
•     Cannot add data selection rows with AND operator but not OR operator
•     Cannot delete existing rows
•     Read-only

Summary

Data Selection security is another security type to be used by CNC administrators or consultants to lock down batch versions data selection during submission in the EnterpriseOne web client.  It should be implemented as a part of a larger effort to secure batch processing and in such a manner as to maintain consistency with your organization's security practices and methods.

More information can be found in Oracle Document ID # 814174.1 JD Edwards EnterpriseOne Tools 8.98 Update 1 Batch Application Data Selection Security

Copy 02 into L1 into F4105

insert into bfdta.F4105

SELECT
    T1.COITM,T1.COLITM,T1.COAITM,T1.COMCU,T1.COLOCN,T1.COLOTN,T1.COLOTG,'L1',T1.COUNCS,T1.COCSPO,T1.COCSIN,T1.COURCD,T1.COURDT,T1.COURAT,T1.COURAB,'L1_FIX',T1.COUSER,T1.COPID,T1.COJOBN,T1.COUPMJ,T1.COTDAY,T1.COCCFL,T1.COCRCS,T1.COOSTC,T1.COSTOC
FROM
    (
        SELECT
            *
        FROM
            BFDTA.F4105
        WHERE
            COLEDG = '02'
        AND TRIM(COMCU)LIKE '311%') T1
LEFT OUTER JOIN
    (
        SELECT
            *
        FROM
            BFDTA.F4105
        WHERE
            COLEDG = 'L1'
        AND TRIM(COMCU)LIKE '311%') T2
ON
    T1.COMCU = T2.COMCU
AND T1.COITM = T2.COITM
WHERE
    T2.COLEDG IS NULL
ORDER BY
    T1.COMCU,
    T1.COLITM

Update Last In Cost from F43121 - Brazil

SELECT * FROM testdta.F43121
WHERE prmatc = '1' AND
prlitm = 'F231947' AND
prmcu = '     3110082' AND
prrcdj = 111243 AND
--prdoc = 80484 AND
prglc LIKE  'IN%'
ORDER BY pritm asc , prmcu ASC, prdoc desc , prupmj DESC, prtday DESC

SELECT * FROM testdta.F43121
WHERE prmatc = '1' AND prlitm = 'F231947' AND prmcu = '     3110082' AND prrcdj = 111243 AND prdoc = 57797 AND pralin = 2 AND prlnid = 191000

JDE Error Messages

861E - Purchase Order Line Closed

One OV with Multiple OPs

SELECT PRMCU, PRDOC, COUNT(DISTINCT(PRDOCO))
  FROM PRODDTA.F43121
 WHERE PRDCT = 'OV'
 GROUP BY PRMCU, PRDOC
HAVING COUNT(DISTINCT(PRDOCO)) > 1

Fiscal Period (PN) based on a date

Table: F0008

BSFN: X0903 RetrunFYPN function

EDI 861 Non-Stock Purchase Order

Documenting the process for the EDI Receiving Advice Edit/Create - Purchasing (R47071) - Inbound 861 - for a non-stock purchase orders. 

Overview - Inbound 861 - Non-Stock

The EDI Receiving Advice Edit/Create - Purchasing (R47071) - Inbound 861 - for a non-stock purchase order, confirms the receipt of services against an outstanding purchase order. 
Company A enters a non-stock purchase order. The Vendor on the order fills the services for Company B. Company B is the recipient of the services and is invoiced, they send the advice to Company A that the services are complete. Company A will run the EDI Receiving Advice Edit/Create - Purchasing (R47071) program to record the receipt of those services in their system. 
The system verifies the inbound receiving advice against an open non-stock purchase order. The EDI Receiving Advice Edit/Create - Purchasing (R47071) is looking for data in the Purchase Order Header (F4301) file and the Purchase Order Detail (F4311) file.

The Minimum Required EDI Files/Fields

The transmitter will need to map data to the EDI Invoice Header - Inbound (F47071) and EDI Invoice Detail - Inbound (F47072). These files will be used by the EDI Receiving Advice Edit/Create - Purchasing (R47071) to update the EnterpriseOne purchasing files (noted later). 
EDI Invoice Header - Inbound (F47071):

• SYEKCO - EDI Document Key Company
• SYEDOC - EDI Document Number
• SYEDCT - EDI Document Type
• SYEDST - EDI Transaction Set
• SYEDER - Send/Receive Flag = R
• SYEDDL - Number of Detail Lines
• SYTPUR - Transaction Set Purpose
• SYRATY - Receiving Advice Type
• SYKCOO - J.D. Edwards Purchase Order Key Company
• SYDOCO - J.D. Edwards Purchase Order Number
• SYDCTO - J.D. Edwards Purchase Order Document Type
• SYSFXO - J.D. Edwards Purchase Order Suffix

EDI Invoice Detail - Inbound (F47072):

• SZEKCO - EDI Document Key Company
• SZEDOC - EDI Document Number
• SZEDCT - EDI Document Type
• SZEDLN - EDI Line Number
• SZEDST - EDI Transaction Set
• SZEDER - Send/Receive Flag = R
• SZKCOO - J.D. Edwards Purchase Order Key Company
• SZDOCO - J.D. Edwards Purchase Order Number
• SZDCTO - J.D. Edwards Purchase Order Document Type
• SZSFXO - J.D. Edwards Purchase Order Suffix
• SZLNID - J.D. Edwards Purchase Order Line Number
• SZLSTS - Line Item Status Code
• SZUOM - Unit of Measure as Input
• SZUOM3 - Unit of Measure - Purchasing

Note: Receiving an advice for a non-stock line or purchase order map a dollar amount to the Amount Received Field SZAREC.
Additional Fields to Consider in the EDI Invoice Detail - Inbound (F47072): Quantity, Item Number, and Account Number
The Inventory Interface of the Order Line Types will determine what is required to be mapped in the quantity, item number and account number fields of the EDI Invoice Detail - Inbound (F47072) file.  
The information below will take into account the "N" and the "A" inventory interface value of a line type:
Quantity - SZUREC Field: 

• "A"  inventory interface - quantity is optional
• "N" inventory interface - mandatory to map the quantity or an error "blanks invalid" (0003) will occur on the UREC field  

Item Number - SZLITM Field:

• "A" inventory interface - item number is optional, and is not validated against Item Master (F4101).
• "N" inventory interface - if not mapped, then non-stock item will be taken from the purchase order to create the Purchase Order Receiver (F43121) record. The non stock item is required to have an Item Master (F4101) record.

Account Number - SZANI Field:

• "A" inventory interface - account number is optional. However, if an account number for the expense is mapped, it will be recorded in the Purchase Order Receiver (F43121) file, and the Accounts Payable Ledger (F0411) file. If no account number is mapped, then the account number will be taken from the account number on the purchase order.
• "N" inventory interface - not required in that the account numbers will be taken from the appropriate J.D. Edwards purchasing AAI Tables.

J.D. Edwards Application Files Updated

The data from the non-stock purchase order in the EDI Invoice Header - Inbound (F47071) and EDI Invoice Detail - Inbound (F47072) files are updated to the following J.D. Edwards files:

• Purchase Order Header (F4301)
• Purchase Order Detail (F4311)
• Purchase Order Receiver (F43121)
• Account Ledger (F0911)
• PO Detail Ledger (F43199)
• Item Branch (F41021)
• Supplier/Item Relationships (F43090)

Unable to save Purchase Order screen

Entering Details. The Entry occurs without any messages and Next number is generated on closing the screen, find that nothing has been saved, neither in the table nor the Enquiry screen.


Believe it has something to do with security or approvals, but unable to pinpoint the issue.