5 Replies Latest reply on Apr 9, 2018 3:39 PM by John_K

    GDPR - Data Protection Act Request

    sheebs_sheebs

      Hello,

      Could someone share SQL's for personal data for a person hold in EBS under this new act which is linked to Party id please? Please feel free to add for other modules. I am interested for these now. All we receive is anything related to post code and address or the party name.

       

      Oracle EBS : R12.1.3

       

      AR - Invoices /transactions

      SELECT   party_name, account_number, rct.trx_number,

               (SELECT SUM (extended_amount)

                  FROM ra_customer_trx_lines_all

                 WHERE customer_trx_id = rct.customer_trx_id

                   AND line_type = 'LINE') net_amount,

               (SELECT SUM (extended_amount)

                  FROM ra_customer_trx_lines_all

                 WHERE customer_trx_id = rct.customer_trx_id

                   AND line_type = 'TAX') vat_amount,

               SUM (extended_amount) gross_amount, rct.trx_date

          FROM hz_parties hzp,

               hz_cust_accounts hca,

               ra_customer_trx_all rct,

               ra_customer_trx_lines_all rctl

         WHERE hzp.party_id = hca.party_id

           AND hca.cust_account_id = rct.bill_to_customer_id

           AND rct.customer_trx_id = rctl.customer_trx_id

      GROUP BY party_name,

               account_number,

               rct.trx_number,

               rct.trx_date,

               rct.customer_trx_id

       

      AP - Invoices / Expenses

      SELECT DISTINCT hp.party_name,as1.vendor_name,AIa.creation_date invoice_creation_date,aia.INVOICE_DATE

          ,aia.INVOICE_NUM,aia.INVOICE_AMOUNT,aia.INVOICE_CURRENCY_CODE

          ,aia.AMOUNT_PAID ,CANCELLED_DATE

      FROM   AP_INVOICES_ALL AIA,

             HZ_PARTIES HP,

             ap_suppliers as1

      WHERE  AIA.PARTY_ID    = HP.PARTY_ID

      and aia.VENDOR_ID = as1.vendor_id

      AND   HP.party_id=2879508         

      and aia.INVOICE_AMOUNT <>0

       

      -- Approved Requisitions , PO# , Invoice# and Receipt #

      SELECT

      DISTINCT party_name,

        r.segment1 "Req Num",

        p.segment1 "PO Num",

        rsh.receipt_num "Receipt Num",

        ai.invoice_num "Invoice Num",

      AI.creation_date invoice_creation_date,ai.INVOICE_DATE

          ,ai.INVOICE_AMOUNT,ai.INVOICE_CURRENCY_CODE

          ,ai.AMOUNT_PAID ,CANCELLED_DATE

      FROM po_req_distributions_all rd,

        po_requisition_lines_all rl,

        po_requisition_headers_all r ,

        po_headers_all p,

        po_distributions_all d,

        RCV_SHIPMENT_HEADERS rsh,

        rcv_shipment_lines rsl,

        ap_invoice_distributions_all apid,

        ap_invoices_all ai,

        PO_ACTION_HISTORY pah,

        hz_parties hp

      WHERE r.requisition_header_id =rl.requisition_header_id

      AND rl.requisition_line_id    = rd.requisition_line_id

      AND rd.distribution_id        =d.req_distribution_id

      AND d.po_header_id            =p.po_header_id

      AND d.po_distribution_id      =apid.po_distribution_id

      AND apid.invoice_id           = ai.invoice_id

      AND p.po_header_id            =rsl.po_header_id

      AND rsl.shipment_header_id    = rsh.shipment_header_id

      AND r.requisition_header_id   = pah.object_id

      and hp.party_id= ai.party_id

      AND pah.OBJECT_TYPE_CODE      = 'REQUISITION'

      AND pah.ACTION_CODE           = 'APPROVE'

      AND  ai.party_id=2879508

      PA - Projects/tasks

       

      HR - Employee details

       

      Thanks

      Sheeba

       

      Message was edited by: sheebs_sheebs