Prepayment Status Report Custom Query

Prepayment Status Report Custom Query

/* Formatted on 10-01-2015 04:48:39 PM (QP5 v5.163.1008.3004) */

SELECT vendor_name,

       --       ACCOUNT_CODE,

       (SELECT segment4

          FROM gl_code_combinations_kfv

         WHERE code_combination_id = account_code)

          account_code,

       invoice_type_lookup_code,

       invoice_no,

       invoice_id,

       invoice_date,

       exchange_rate,

       invoice_currency_code,

       invoice_amount,

       --       (INVOICE_AMOUNT - PERPAY)/EXCHANGE_RATE,

       ( ( (invoice_amount - perpay) * exchange_rate) / exchange_rate)

          amount_remaining_cy,

       ( (invoice_amount - perpay) * exchange_rate) amount_remaining_aed,

       perpay,

       description

  FROM (  SELECT b.vendor_name vendor_name,

                 --c.vendor_site_id,

                 --c.PREPAY_CODE_COMBINATION_ID ACCOUNT_CODE,

                 d.dist_code_combination_id account_code,

                 a.invoice_type_lookup_code invoice_type_lookup_code,

                 a.invoice_num invoice_no,

                 a.invoice_id invoice_id,

                 a.invoice_date invoice_date,

                 a.invoice_currency_code invoice_currency_code,

                 NVL (a.exchange_rate, 1) exchange_rate,

                 SUM (d.amount) invoice_amount,

                 NVL (

                    (SELECT SUM (prepay_amount_applied)

                       FROM ap_view_prepays_fr_prepay_v

                      WHERE prepay_id = a.invoice_id

                            AND TO_DATE (accounting_date, 'DD-MON-RRRR') <=

                                   TO_DATE (:p_to_date, 'DD-MON-RRRR')),

                    0)

                    perpay,

                 a.description description

            FROM apps.ap_invoices_all a,

                 apps.ap_invoice_distributions_all d,

                 apps.ap_suppliers b,

                 apps.ap_supplier_sites_all c

           WHERE     a.invoice_id = d.invoice_id

                 AND a.vendor_id = b.vendor_id

                 AND b.vendor_id = c.vendor_id

                 AND a.vendor_site_id = c.vendor_site_id

                 AND a.invoice_type_lookup_code = 'PREPAYMENT'

                 AND a.org_id = NVL (:org_id, a.org_id)

                 AND b.vendor_name = NVL (:p_vendor_name, b.vendor_name)

                 --               and  d.DIST_CODE_COMBINATION_ID=2016--(233100)

                 AND TO_DATE (d.accounting_date, 'DD-MON-RRRR') <=

                        TO_DATE (:p_to_date, 'DD-MON-RRRR')

        GROUP BY b.vendor_name,

                 --c.vendor_site_id,

                 d.dist_code_combination_id,

                 a.invoice_type_lookup_code,

                 a.invoice_num,

                 a.invoice_id,

                 a.invoice_date,

                 a.invoice_currency_code,

                 NVL (a.exchange_rate, 1),

                 a.description)

 WHERE (invoice_amount - perpay) <> 0;

--       AND a.invoice_num = 'IPO-107253';

Supplier Statement Of Accounts Custom Report Query

/* Formatted on 10-01-2015 04:44:00 PM (QP5 v5.163.1008.3004) */
SELECT *
  FROM (SELECT vendor_name,
               invoice_date,
               gl_date,
               invoice_currency_code,
               name,
               document_type,
               supplier_reference,
               posting_status,
               narration,
               document_no,
               due_date,
               payment_status,
               DECODE (document_type,
                       'DEBIT', inv_amount,
                       (payment_amount + prepay_applied))
                  entered_dr,
               DECODE (document_type,
                       'STANDARD', inv_amount,
                       'CREDIT', inv_amount,
                       (payment_amount + prepay_applied))
                  entered_cr,
               --       INV_AMOUNT ENTERED_DR,
               --       (PAYMENT_AMOUNT + PREPAY_APPLIED) ENTERED_CR,
               (inv_amount - (payment_amount + prepay_applied))
                  entered_balance,
               DECODE (document_type,
                       'DEBIT', (inv_amount * exchange_rate),
                       ( (payment_amount + prepay_applied) * exchange_rate))
                  functional_dr,
               DECODE (document_type,
                       'STANDARD', inv_amount * exchange_rate,
                       'CREDIT', inv_amount * exchange_rate,
                       ( (payment_amount + prepay_applied) * exchange_rate))
                  functional_cr,
               --       (INV_AMOUNT * EXCHANGE_RATE) FUNCTIONAL_DR,
               --       ( (PAYMENT_AMOUNT + PREPAY_APPLIED) * EXCHANGE_RATE) FUNCTIONAL_CR,
               ( (inv_amount * exchange_rate)
                - ( (payment_amount + prepay_applied) * exchange_rate))
                  functional_balance
          FROM (  SELECT pv.vendor_name vendor_name,
                         aia.invoice_date invoice_date,
                         aia.gl_date gl_date,
                         aia.invoice_currency_code invoice_currency_code,
                         hou.name,
                         aia.invoice_type_lookup_code document_type,
                         --         AIA.INVOICE_ID,
                         aia.invoice_num supplier_reference,
                         ap_invoices_pkg.get_posting_status (aia.invoice_id)
                            posting_status,
                         NVL (aia.exchange_rate, 1) exchange_rate,
                         aia.description narration,
                         aia.doc_sequence_value document_no,
                         (SELECT MIN (apsa.due_date)
                            FROM ap.ap_payment_schedules_all apsa
                           WHERE apsa.invoice_id = aia.invoice_id)
                            due_date,
                         DECODE (aia.payment_status_flag,
                                 'N', 'Not Paid',
                                 'P', 'Partially Paid',
                                 'Y', 'Fully Paid')
                            payment_status,
                         SUM (aida.amount) inv_amount,
                         NVL (
                            (SELECT SUM (aipa.amount)
                               FROM ap_invoice_payments_all aipa
                              WHERE 1 = 1 AND aipa.invoice_id = aia.invoice_id
                                    AND TO_DATE (aipa.accounting_date,
                                                 'DD-MON-RRRR') <=
                                           TO_DATE (:p_as_of_date,
                                                    'DD-MON-RRRR')),
                            0)
                            payment_amount,
                         NVL (
                            (SELECT SUM (avp.prepay_amount_applied)
                               FROM ap_view_prepays_fr_prepay_v avp
                              WHERE 1 = 1 AND avp.invoice_id = aia.invoice_id
                                    AND TO_DATE (avp.accounting_date,
                                                 'DD-MON-RRRR') <=
                                           TO_DATE (:p_as_of_date,
                                                    'DD-MON-RRRR')),
                            0)
                            prepay_applied
                    FROM ap_invoices_all aia,
                         ap_invoice_distributions_all aida,
                         po_vendors pv,
                         hr_operating_units hou
                   WHERE     1 = 1
                         AND aia.invoice_id = aida.invoice_id
                         AND aia.vendor_id = pv.vendor_id
                         AND aia.org_id = hou.organization_id
                         AND aida.line_type_lookup_code IN
                                ('ITEM',
                                 'ACCRUAL',
                                 'ERV',
                                 'IPV',
                                 'FREIGHT',
                                 'MISCELLANEOUS')
                         AND aia.invoice_type_lookup_code <> 'PREPAYMENT'
                         AND ap_invoices_pkg.get_posting_status (
                                aia.invoice_id) = 'Y'
                         AND aia.org_id = NVL (:org_id, aia.org_id)
                         AND pv.vendor_name =
                                NVL (:p_vendor_name, pv.vendor_name)
                         AND TO_DATE (aida.accounting_date, 'DD-MON-RRRR') <=
                                TO_DATE (:p_as_of_date, 'DD-MON-RRRR')
                --         AND aia.vendor_id = 86014
                GROUP BY aia.invoice_id,
                         aia.vendor_id,
                         pv.vendor_name,
                         aia.invoice_date,
                         aia.gl_date,
                         aia.invoice_currency_code,
                         hou.name,
                         aia.description,
                         aia.exchange_rate,
                         aia.doc_sequence_value,
                         aia.payment_status_flag,
                         aia.invoice_type_lookup_code,
                         aia.invoice_num)
        UNION ALL
        -- =========== PREPAYMENT QUERY ================= --

        SELECT vendor_name,
               invoice_date,
               gl_date,
               invoice_currency_code,
               name,
               document_type,
               supplier_reference,
               posting_status,
               narration,
               document_no,
               due_date,
               payment_status,
               inv_amount entered_cr,
               prepay_applied entered_dr,
               (inv_amount - prepay_applied) entered_balance,
               (inv_amount * exchange_rate) functional_cr,
               (prepay_applied * exchange_rate) functional_dr,
               ( (inv_amount * exchange_rate)
                - (prepay_applied * exchange_rate))
                  functional_balance
          FROM (  SELECT aps.vendor_name vendor_name,
                         aia.invoice_date invoice_date,
                         aia.gl_date gl_date,
                         aia.invoice_currency_code invoice_currency_code,
                         hou.name,
                         aia.invoice_type_lookup_code document_type,
                         aia.invoice_num supplier_reference,
                         ap_invoices_pkg.get_posting_status (aia.invoice_id)
                            posting_status,
                         NVL (aia.exchange_rate, 1) exchange_rate,
                         aia.description narration,
                         aia.doc_sequence_value document_no,
                         (SELECT MIN (apsa.due_date)
                            FROM ap.ap_payment_schedules_all apsa
                           WHERE apsa.invoice_id = aia.invoice_id)
                            due_date,
                         DECODE (aia.payment_status_flag,
                                 'N', 'Not Paid',
                                 'P', 'Partially Paid',
                                 'Y', 'Fully Paid')
                            payment_status,
                         SUM (aipa.amount) inv_amount,
                         0 payment_amount,
                         NVL (
                            (SELECT SUM (prepay_amount_applied)
                               FROM ap_view_prepays_fr_prepay_v
                              WHERE prepay_id = aia.invoice_id
                                    AND TO_DATE (accounting_date,
                                                 'DD-MON-RRRR') <=
                                           TO_DATE (:p_as_of_date,
                                                    'DD-MON-RRRR')),
                            0)
                            prepay_applied
                    FROM apps.ap_invoices_all aia,
                         --APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
                         apps.ap_invoice_payments_all aipa,
                         apps.ap_suppliers aps,
                         apps.ap_supplier_sites_all apsa,
                         apps.hr_operating_units hou
                   WHERE     aia.invoice_id = aipa.invoice_id
                         --AND AIDA.INVOICE_ID = AIPA.INVOICE_ID
                         AND aia.vendor_id = aps.vendor_id
                         AND aps.vendor_id = apsa.vendor_id
                         AND aia.vendor_site_id = apsa.vendor_site_id
                         AND aia.org_id = hou.organization_id
                         AND aia.invoice_type_lookup_code = 'PREPAYMENT'
                         AND ap_invoices_pkg.get_posting_status (
                                aia.invoice_id) = 'Y'
                         AND aia.org_id = NVL (:org_id, aia.org_id)
                         AND aps.vendor_name =
                                NVL (:p_vendor_name, aps.vendor_name)
                         --AND TO_DATE (AIDA.ACCOUNTING_DATE, 'DD-MON-RRRR') <=
                         --     TO_DATE (:P_AS_OF_DATE, 'DD-MON-RRRR')
                         AND TO_DATE (aipa.accounting_date, 'DD-MON-RRRR') <=
                                TO_DATE (:p_as_of_date, 'DD-MON-RRRR')
                GROUP BY aps.vendor_name,
                         --AIDA.DIST_CODE_COMBINATION_ID,
                         aia.invoice_type_lookup_code,
                         aia.invoice_num,
                         aia.invoice_id,
                         aia.invoice_date,
                         aia.gl_date,
                         hou.name,
                         aia.invoice_currency_code,
                         NVL (aia.exchange_rate, 1),
                         aia.doc_sequence_value,
                         aia.payment_status_flag,
                         aia.description))
 WHERE functional_balance <> 0;

Convert Number to Words Using PL/SQL Function

Convert Number to Words Using PL/SQL Function

/* Formatted on 2013/03/07 18:40 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION spell_number (p_amt IN NUMBER, POH_CURRENCY_CODE in varchar2)
   RETURN VARCHAR2
IS
   m_main_amt_text     VARCHAR2 (2000);
   m_top_amt_text      VARCHAR2 (2000);
   m_bottom_amt_text   VARCHAR2 (2000);
   m_decimal_text      VARCHAR2 (2000);
   m_top               NUMBER (20, 5);
   m_main_amt          NUMBER (20, 5);
   m_top_amt           NUMBER (20, 5);
   m_bottom_amt        NUMBER (20, 5);
   m_decimal           NUMBER (20, 5);
   m_amt               NUMBER (20, 5);
   m_text              VARCHAR2 (2000);
   r_val               VARCHAR2 (10)   := 'Dollar ';
   v_curr varchar2(10);
BEGIN
   m_main_amt := NULL;
   m_top_amt_text := NULL;
   m_bottom_amt_text := NULL;
   m_decimal_text := NULL;
   -- To get paise part
   m_decimal := p_amt - TRUNC (p_amt);

   IF m_decimal > 0
   THEN
      m_decimal := m_decimal * 100;
   END IF;

   m_amt := TRUNC (p_amt);
   m_top := TRUNC (m_amt / 100000);
   m_main_amt := TRUNC (m_top / 100);
   m_top_amt := m_top - m_main_amt * 100;
   m_bottom_amt := m_amt - (m_top * 100000);
   m_top_amt_text := TO_CHAR (TO_DATE (m_amt, 'J'), 'JSP');

   /*IF m_main_amt > 0
   THEN
      m_main_amt_text := TO_CHAR (TO_DATE (m_amt, 'J'), 'JSP');

     /* IF m_main_amt = 1
      THEN
         m_main_amt_text := m_main_amt_text || ' CRORE ';
      ELSE
         m_main_amt_text := m_main_amt_text || ' CRORES ';
      END IF;
   END IF;

   IF m_top_amt > 0
   THEN
      m_top_amt_text := TO_CHAR (TO_DATE (m_amt, 'J'), 'JSP');

      IF m_top_amt = 1
      THEN
         m_top_amt_text := m_top_amt_text || ' LAKH ';
      ELSE
         m_top_amt_text := m_top_amt_text || ' LAKHS ';
      END IF;
   END IF;*/

   IF m_bottom_amt > 0
   THEN
      m_bottom_amt_text := TO_CHAR (TO_DATE (m_bottom_amt, 'J'), 'JSP')
                           || ' ';
   END IF;

   IF m_decimal > 0  
   THEN  
      IF NVL (m_bottom_amt, 0) + NVL (m_top_amt, 0) > 0
      THEN
          --          srw.message(111,'m_bottom_amt is '||m_bottom_amt);
          --    srw.message(111,'m_top_amt is '||m_top_amt);
      BEGIN
         
          if (POH_CURRENCY_CODE = 'EUR') then
                 v_curr := 'Cents';
          elsif (POH_CURRENCY_CODE = 'USD') then
                 v_curr := 'Cents';
          elsif (POH_CURRENCY_CODE = 'OMR') then
                 v_curr := 'Baisa';
          elsif (POH_CURRENCY_CODE = 'AED') then
                 v_curr := 'Fils';    
          else
                v_curr := 'Paisa';
           end if;
         
            --m_decimal_text := ' AND ' || TO_CHAR (TO_DATE (m_decimal, 'J'), 'JSP')|| ' Cent ';
            m_decimal_text := ' AND ' ||' '||v_curr||' '|| TO_CHAR (TO_DATE (m_decimal, 'J'), 'JSP');
           
         END;
      ELSE
              if (POH_CURRENCY_CODE = 'EUR') then
                 v_curr := 'Cents';
          elsif (POH_CURRENCY_CODE = 'USD') then
                 v_curr := 'Cents';
          elsif (POH_CURRENCY_CODE = 'OMR') then
                 v_curr := 'Baisa';
          elsif (POH_CURRENCY_CODE = 'AED') then
                 v_curr := 'Fils';    
          else
                v_curr := 'Paisa';
           end if;
         
         --m_decimal_text :=  TO_CHAR (TO_DATE (m_decimal, 'J'), 'JSP') || ' Cent ';
         m_decimal_text := 'AND'||' '||v_curr||' '||  TO_CHAR (TO_DATE (m_decimal, 'J'), 'JSP');
       
         r_val := '';
      END IF;
   END IF;

   m_text :=
    --     r_val
    --  || LOWER (   m_main_amt_text
                (m_top_amt_text
                || m_decimal_text
               )
      || ' ONLY';
 
   --   dbms_output.put_line('m_text is '||m_text);
 --  m_text := UPPER (SUBSTR (m_text, 1, 1)) || SUBSTR (m_text, 2);  252345.36
--   m_text := SUBSTR (m_text, 2);
 --  m_text := ' ' || m_text;
   RETURN (REPLACE(UPPER(m_text),'-',' '));
END ;

Example:

SELECT spell_number(:AMOUNT,'USD') FROM DUAL;

==========================================================


/* Formatted on 10-01-2015 03:20:54 PM (QP5 v5.163.1008.3004) */
CREATE OR REPLACE FUNCTION MONEY_TO_WORDS (P_MONEY IN NUMBER)
   RETURN VARCHAR2
AS
   W_RETURN   VARCHAR2 (255);
BEGIN
   IF P_MONEY < 0
   THEN
      W_RETURN := 'Negative number is not allowed!';
   ELSIF P_MONEY = 0
   THEN
      W_RETURN := 'Zero Euro and zero cents';
   ELSE
      IF (TRUNC (P_MONEY) = 0)
      THEN
         W_RETURN := 'Zero Euros';
      ELSIF (TRUNC (P_MONEY) = 1)
      THEN
         W_RETURN := 'One Euro';
      ELSE
         W_RETURN := TO_CHAR (TO_DATE (TRUNC (P_MONEY), 'J'), 'JSP');
      END IF;

      IF ( (P_MONEY - TRUNC (P_MONEY)) = 0)
      THEN
         W_RETURN := W_RETURN || ' Exactly';
      ELSIF ( (P_MONEY - TRUNC (P_MONEY)) * 100 = 1)
      THEN
         W_RETURN := W_RETURN || ' and one cent';
      ELSE
         W_RETURN :=
            W_RETURN || 'and'
            || TO_CHAR (TO_DATE ( (P_MONEY - TRUNC (P_MONEY)) * 100, 'J'),
                        'JSP')
            || 'cents';
      END IF;
   END IF;

   RETURN W_RETURN;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error : ' || SQLERRM);
      RETURN 'CAN NOT CONVERT YOUR INPUT TO WORDS!';
END;
/


Example:
SELECT MONEY_TO_WORDS(0) FROM DUAL;

How to Add Responsibility to User from Backend

How to Add Responsibility to User from Backend

A responsibility can be added to a user using the “AddResp” procedure of “fnd_user_pkg”.

AddResp Procedure parameters

PROCEDURE AddResp(
    username       VARCHAR2,
    resp_app       VARCHAR2,
    resp_key       VARCHAR2,
    security_group VARCHAR2,
    description    VARCHAR2,
    start_date DATE,
    end_date DATE
  );
Query to find parameter values

SELECT fa.application_short_name,
  fr.responsibility_key,
  fsg.security_group_key,
  frt.description
FROM apps.fnd_responsibility fr,
  fnd_application fa,
  fnd_security_groups fsg,
  fnd_responsibility_tl frt
WHERE frt.responsibility_name = 'System Administrator'
AND frt.LANGUAGE              = USERENV ('LANG')
AND frt.responsibility_id     = fr.responsibility_id
AND fr.application_id         = fa.application_id
AND fr.data_group_id          = fsg.security_group_id;
API to add responsibility to user

You can use following api to add responsibility to the user you want. It will prompt you for the username and responsibility name.

DECLARE
  v_user_name           VARCHAR2 (30)  := '&user_name';
  v_responsibility_name VARCHAR2 (100) := '&responsibility_name';
  v_application_name    VARCHAR2 (100) := NULL;
  v_responsibility_key  VARCHAR2 (100) := NULL;
  v_security_group      VARCHAR2 (100) := NULL;
  v_description         VARCHAR2 (100) := NULL;
BEGIN
  SELECT fa.application_short_name,
    fr.responsibility_key,
    fsg.security_group_key,
    frt.description
  INTO v_application_name,
    v_responsibility_key,
    v_security_group,
    v_description
  FROM apps.fnd_responsibility fr,
    fnd_application fa,
    fnd_security_groups fsg,
    fnd_responsibility_tl frt
  WHERE frt.responsibility_name = v_responsibility_name
  AND frt.LANGUAGE              = USERENV ('LANG')
  AND frt.responsibility_id     = fr.responsibility_id
  AND fr.application_id         = fa.application_id
  AND fr.data_group_id          = fsg.security_group_id;
  fnd_user_pkg.addresp (
    username => v_user_name,
    resp_app => v_application_name,
    resp_key => v_responsibility_key,
    security_group => v_security_group,
    description => v_description,
    start_date => SYSDATE,
    end_date => NULL
  );
  COMMIT;
  DBMS_OUTPUT.put_line ( 'Responsiblity ' || v_responsibility_name ||
    ' is attached to the user ' || v_user_name || ' Successfully' );
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Unable to attach responsibility to user due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
END;

Query to check responsibilities attached to a user

SELECT frt.responsibility_name
FROM fnd_user_resp_groups furg,
  fnd_user fu,
  fnd_responsibility_tl frt
WHERE fu.user_name         = '&user_name'
AND fu.user_id             = furg.user_id
AND furg.responsibility_id = frt.responsibility_id;