Friday, 20 July 2018

Run the Workflow backend or PL/SQL Code

The below script to run workflow from PL/SQL code-


DECLARE
   v_itemtype   VARCHAR2 (50);
   v_itemkey    VARCHAR2 (50);
   v_process    VARCHAR2 (50);
   v_userkey    VARCHAR2 (50);
BEGIN
   v_itemtype := 'TEXPO_W1';
   v_itemkey := '123';
   v_userkey := '123';
   v_process := 'TEX_PO_P';
   wf_engine.threshold := -1;
   wf_engine.createprocess (v_itemtype, v_itemkey, v_process);
   wf_engine.setitemuserkey (v_itemtype, v_itemkey, v_userkey);
   wf_engine.setitemowner (v_itemtype, v_itemkey, 'SYSADMIN');
   wf_engine.startprocess (v_itemtype, v_itemkey);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

Monday, 2 July 2018

Query of Calculate Balance Owed Oracle Payables

SELECT   (SELECT NAME
            FROM hr_operating_units
           WHERE organization_id = aia.org_id) operating_unit_name,
         (SELECT segment1
            FROM ap_suppliers
           WHERE vendor_id = aia.vendor_id) vendor_code,
         (SELECT vendor_name
            FROM ap_suppliers
           WHERE vendor_id = aia.vendor_id) vendor_name,
         SUM (NVL (ps.gross_amount, 0)) balance,
         SUM (NVL (ps.amount_remaining, 0)) unpaid_amount,
         COUNT (aia.invoice_num) number_of_invoices
    FROM ap_invoices_all aia,
         ap_payment_schedules_all ps,
         ap_supplier_sites_all vs
   WHERE 1 = 1
     AND ps.amount_remaining != 0
     AND ps.invoice_id = aia.invoice_id
     AND NVL (aia.payment_status_flag, 'N') != 'Y'
     AND aia.cancelled_date IS NULL
     AND vs.vendor_site_id = aia.vendor_site_id
     AND NOT (    aia.invoice_type_lookup_code = 'PREPAYMENT'
              AND NVL (aia.payment_status_flag, 'N') = 'N'
             )
     AND aia.org_id = NVL (:p_org_id, aia.org_id)
     AND aia.vendor_id = NVL (:p_vendor_id, aia.vendor_id)
     AND aia.vendor_site_id = NVL (:p_vendor_site_id, aia.vendor_site_id)
GROUP BY aia.org_id, aia.vendor_id
ORDER BY vendor_code