Home » SQL & PL/SQL » SQL & PL/SQL » Need Help In Plsql Procedure (Plsql)
Need Help In Plsql Procedure [message #669307] Sat, 14 April 2018 17:25 Go to next message
Djpats
Messages: 17
Registered: January 2018
Junior Member
HI Guys, I m new to Pl/sql need your help
below is my procedure code

My requirement is Either I want to pass PV_CRN_NEW_PLAN value to procedure OR PV_USER_AMOUNT value to procedure..

I can not pass both the values at a time.


( I am handling errors by using 'raise_application_error' is there any other way to handle the errors);

how can I handle this issues.

any quick help will be appreciated...


thnxxx in advance...

below is a procedure code for the reference.....

 PROCEDURE prc_test11                (P_CRN_CIRCUIT_ID               VARCHAR2,
                                     P_CRN_BILL_FROM_DATE           DATE,
                                     P_CRN_BILL_TO_DATE             DATE,
                                     PV_CRN_OPEN_INV_NO             VARCHAR2,
                                     PV_CRN_REASON                  VARCHAR2,
                                     PV_CRN_EFFECTIVE_PERIOD_FRM    DATE,
                                     PV_CRN_EFFECTIVE_PERIOD_TO     DATE,
                                     PV_CRN_NEW_PLAN                NUMBER,
                                     PV_USER_AMOUNT                 NUMBER)
   IS
      v_credit_note_no           NUMBER;
      v_revised_invoice_amount   NUMBER;
      v_credit_amount            NUMBER;
      v_recurring_charge         NUMBER;
      v_otc                      NUMBER;
      v_usage_charge             NUMBER;
      v_credit_charge            NUMBER;
      v_in_words                 VARCHAR2 (2000);
      v_status                   VARCHAR2 (10);
      v_message                  VARCHAR2 (2000);
      v_total_wo_tax             NUMBER;
      l_hdr_seq                  NUMBER;
      l_dtl_Seq                  NUMBER;
      l_user_id                  NUMBER;
      l_dtl_cnt                  NUMBER;
      l_cnt                      NUMBER;
      l_first_day                DATE;
      l_last_day                 DATE;
      l_5th_day                  DATE;
      l_prov_amount              NUMBER;
      l_new_prov_amount          NUMBER;
      l_batch_number             NUMBER;
      L_CRN_REVISED_INV_AMT      NUMBER;
      L_CRN_CREDIT_AMT           NUMBER;
      L_CRN_RECURRING_CHARGE     NUMBER;
      L_CRN_OTC                  NUMBER;
      L_CRN_USAGE_CHARGE         NUMBER;
      L_CRN_MISC_CREDIT_CHARGE   NUMBER;
      L_RECUARRING_CHARGES       NUMBER;
      L_ONETIME_CHARGES          NUMBER;
      L_USAGE_CHARGES            NUMBER;
      L_MISC_CHARGES             NUMBER;
      L_TOTAL_TAX_EX             NUMBER;
      L_AMT_IN_WORDS             VARCHAR2 (250);

      P_LOCATION_ID              NUMBER;
      P_ADDRESS1                 VARCHAR2 (32767);
      P_ADDRESS2                 VARCHAR2 (32767);
      P_ADDRESS3                 VARCHAR2 (32767);
      P_CIRCLE                   VARCHAR2 (32767);
      P_CIRCLE2                  VARCHAR2 (32767);
      P_POSTAL_CODE              VARCHAR2 (32767);
      P_CITY                     VARCHAR2 (32767);
      P_STATE                    VARCHAR2 (32767);
      P_COUNTRY                  VARCHAR2 (32767);
      P_LANDMARK                 VARCHAR2 (32767);
      P_PHONE_NUMBER             VARCHAR2 (32767);
      P_EMPCODE                  VARCHAR2 (32767);
      P_PERSON_FIRST_NAME        VARCHAR2 (32767);
      P_PERSON_MIDDLE_NAME       VARCHAR2 (32767);
      P_PERSON_LAST_NAME         VARCHAR2 (32767);
      P_DESIGNATION              VARCHAR2 (32767);
      P_DEPARTMENT               VARCHAR2 (32767);
      P_FAX_NUMBER               VARCHAR2 (32767);
      P_ALT_NUMBER               VARCHAR2 (32767);
      P_EMAIL_ADDRESS            VARCHAR2 (32767);
      P_VEHICLE_NO               VARCHAR2 (32767);
      P_COMPANY_PAN_NO           VARCHAR2 (32767);
      P_TABLE_NAME               VARCHAR2 (250);

      L_CRN_OPEN_INV_AMT         NUMBER;
      L_CRN_DETAIL_ID            NUMBER;
      L_CRN_NOTE_ID              NUMBER;
      L_CREATED_BY               NUMBER;
      L_CREATION_DATE            DATE;
      L_LAST_UPDATED_BY          NUMBER;
      L_LAST_UPDATE_DATE         DATE;
      V_CLOSE_INV_NO             VARCHAR2 (250);
      V_ACT_INV_AMOUNT           NUMBER;

      CURSOR c1
      IS
         SELECT DISTINCT a.enterprise_code,
                         d.address1,
                         a.customer_id,
                         d.city,
                         d.postal_code,
                         d.state,
                         d.country,
                         e.party_name,
                         a.subscriber_id,
                         a.product_type,
                         e.attribute10,
                         e.attribute2,
                         a.resource_id,
                         a.table_name,
                         c.location_id,
                         a.FA_NUMBER,
                         a.STATUS
           FROM xxvfent_all_caf_header_v a,
                hz_cust_acct_sites_all b,
                hz_party_sites c,
                hz_locations d,
                hz_parties e
          WHERE     a.resource_id = P_CRN_CIRCUIT_ID
                AND a.PRODUCT IN ('Internet Leased Lines',
                                  'MPLS',
                                  'NPLC',
                                  'IPLC')
                --AND a.STATUS = 'CLOSED'
                AND a.customer_id = b.cust_account_id
                AND (   a.resource_id = b.attribute1
                     OR a.ba_number = b.attribute2)
                AND a.party_id = c.party_id
                AND a.party_id = e.party_id
                AND b.party_site_id = c.party_site_id
                AND c.location_id = d.location_id;

      CURSOR c2
      IS
         SELECT DISTINCT
                LEGAL_INVOICE_NO,
                (CASE
                    WHEN INVOICE_STATUS = 'O' THEN 'Open'
                    WHEN INVOICE_STATUS = 'C' THEN 'Close'
                    ELSE 'Other'
                 END)
                   INVOICE_STATUS,
                STATEMENT_DATE,
                INVOICE_AMOUNT,
                AMOUNT_CURRENCY,
                START_DATE,
                END_DATE,
                INVOICE_ID,
                ACCOUNT_ID
           FROM XXVFENT_CREDIT_NOTE_ALL_INV_V
          WHERE     prim_resource_val = P_CRN_CIRCUIT_ID
                AND (    START_DATE >= P_CRN_BILL_FROM_DATE
                     AND END_DATE <= P_CRN_BILL_TO_DATE)
                AND LEGAL_INVOICE_NO = PV_CRN_OPEN_INV_NO;

      r1                         c1%ROWTYPE;
      r2                         c2%ROWTYPE;
   BEGIN
      SELECT TRUNC (SYSDATE, 'Month') INTO l_first_day FROM DUAL;

      SELECT TRUNC (SYSDATE) - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')) - 5)
        INTO l_5th_day
        FROM DUAL;

      SELECT TRUNC (LAST_DAY (SYSDATE)) INTO l_last_day FROM DUAL;

      SELECT COUNT (1)
        INTO l_dtl_cnt
        FROM xxvfent_credit_note_details
       WHERE crn_note_id = L_crn_note_id;

      SELECT COUNT (1)
        INTO l_cnt
        FROM xxvfent_credit_note_details
       WHERE     crn_circuit_id = p_crn_circuit_id
             AND crn_reason = PV_crn_reason
             AND TRUNC (creation_date) BETWEEN l_first_day AND l_last_day
             AND crn_open_inv_no = PV_crn_open_inv_no;

      BEGIN
         SELECT balance_amount, batch_number
           INTO l_prov_amount, l_batch_number
           FROM hutchcs.xxvfent_credit_note_bulk_load
          WHERE     circuit_id = p_crn_circuit_id
                AND batch_number =
                       (SELECT MAX (batch_number)
                          FROM hutchcs.xxvfent_credit_note_bulk_load
                         WHERE     circuit_id = p_crn_circuit_id
                               AND file_upload_date BETWEEN l_first_day
                                                        AND l_last_day);
      EXCEPTION
         WHEN OTHERS
         THEN
            l_prov_amount := 0;
            l_batch_number := 0;
      END;


      IF l_dtl_cnt = 0
      THEN
         IF l_cnt > 0
         THEN
            raise_application_error (
               -20010,
               'Cannot create Credit Note for the same Circuit against same Invoice in the Calendar Month. Please select a different Invoice Number.');
         END IF;
      END IF;


      BEGIN
         P_LOCATION_ID := NULL;
         P_TABLE_NAME := NULL;
         P_ADDRESS1 := NULL;
         P_ADDRESS2 := NULL;
         P_ADDRESS3 := NULL;
         P_CIRCLE := NULL;
         P_CIRCLE2 := NULL;
         P_POSTAL_CODE := NULL;
         P_CITY := NULL;
         P_STATE := NULL;
         P_COUNTRY := NULL;
         P_LANDMARK := NULL;
         P_PHONE_NUMBER := NULL;
         P_EMPCODE := NULL;
         P_PERSON_FIRST_NAME := NULL;
         P_PERSON_MIDDLE_NAME := NULL;
         P_PERSON_LAST_NAME := NULL;
         P_DESIGNATION := NULL;
         P_DEPARTMENT := NULL;
         P_FAX_NUMBER := NULL;
         P_ALT_NUMBER := NULL;
         P_EMAIL_ADDRESS := NULL;
         P_VEHICLE_NO := NULL;
         P_COMPANY_PAN_NO := NULL;

         APPS.XXVFENT_CUSTOMER_CAF_PKG.XXVFENT_CUST_LOC_FETCH (
            P_LOCATION_ID,
            P_ADDRESS1,
            P_ADDRESS2,
            P_ADDRESS3,
            P_CIRCLE,
            P_CIRCLE2,
            P_POSTAL_CODE,
            P_CITY,
            P_STATE,
            P_COUNTRY,
            P_LANDMARK,
            P_PHONE_NUMBER,
            P_EMPCODE,
            P_PERSON_FIRST_NAME,
            P_PERSON_MIDDLE_NAME,
            P_PERSON_LAST_NAME,
            P_DESIGNATION,
            P_DEPARTMENT,
            P_FAX_NUMBER,
            P_ALT_NUMBER,
            P_EMAIL_ADDRESS,
            P_VEHICLE_NO,
            P_COMPANY_PAN_NO);
      END;

      IF PV_CRN_NEW_PLAN IS NOT NULL
      THEN
         APPS.xxvfent_credit_note_pkg.revised_invoice_amount (
            p_invoice_no               => PV_CRN_OPEN_INV_NO,
            p_reason                   => PV_CRN_REASON,
            p_effective_from_date      => TRUNC (PV_CRN_EFFECTIVE_PERIOD_FRM),
            p_effective_to_date        => TRUNC (PV_CRN_EFFECTIVE_PERIOD_TO),
            p_new_plan_amount          => PV_CRN_NEW_PLAN,
            p_revised_invoice_amount   => v_revised_invoice_amount,
            p_credit_amount            => v_credit_amount,
            p_recurring_charge         => v_recurring_charge,
            p_otc                      => v_otc,
            p_usage_charge             => v_usage_charge,
            p_credit_charge            => v_credit_charge,
            p_in_words                 => v_in_words,
            p_status                   => v_status,
            p_message                  => v_message);
      ELSIF PV_USER_AMOUNT IS NOT NULL
      THEN
         APPS.xxvfent_credit_note_pkg.user_revised_invoice_amount (
            p_invoice_no               => PV_CRN_OPEN_INV_NO,
            p_reason                   => PV_CRN_REASON,
            p_effective_from_date      => TRUNC (PV_CRN_EFFECTIVE_PERIOD_FRM),
            p_effective_to_date        => TRUNC (PV_CRN_EFFECTIVE_PERIOD_TO),
            p_user_amount              => PV_USER_AMOUNT,
            p_revised_invoice_amount   => v_revised_invoice_amount,
            p_credit_amount            => v_credit_amount,
            p_recurring_charge         => v_recurring_charge,
            p_otc                      => v_otc,
            p_usage_charge             => v_usage_charge,
            p_credit_charge            => v_credit_charge,
            p_in_words                 => v_in_words,
            p_status                   => v_status,
            p_message                  => v_message);
      END IF;

      IF v_status = 'S'
      THEN
         IF v_credit_amount > L_CRN_OPEN_INV_AMT
         THEN
            raise_application_error (
               -20011,
                  'Credit Amount should not be greater than the Open Invoice Amount. Credit Amount is '
               || v_credit_amount
               || ' and Open Invoice Amount is '
               || L_CRN_OPEN_INV_AMT);
         END IF;

         IF v_credit_amount <= 0
         THEN
            raise_application_error (
               -20012,
               'Credit Amount should not be less than or equal to 0');
         END IF;

         IF l_batch_number = 0
         THEN
            raise_application_error (
               -20013,
               'Provision is not loaded for this Circuit ID. Please Load Circuit and then procced.');
         END IF;

         l_new_prov_amount := l_prov_amount + ( (l_prov_amount / 100) * 10);

         IF v_credit_amount > l_new_prov_amount
         THEN
            raise_application_error (
               -20014,
                  'Credit Amount should not be greater than Provision  with 10% variation. Credit Amount is :'
               || v_credit_amount
               || ' and Provision Amount is '
               || l_prov_amount);
         ELSE
            IF v_credit_amount <= l_prov_amount
            THEN
               UPDATE hutchcs.xxvfent_credit_note_bulk_load
                  SET balance_amount = balance_amount - v_credit_amount
                WHERE     circuit_id = P_CRN_CIRCUIT_ID
                      AND batch_number = l_batch_number;
            ELSE
               UPDATE hutchcs.xxvfent_credit_note_bulk_load
                  SET balance_amount = 0
                WHERE     circuit_id = P_crn_circuit_id
                      AND batch_number = l_batch_number;
            END IF;

            COMMIT;
         END IF;

         L_CRN_REVISED_INV_AMT := v_revised_invoice_amount;
         L_CRN_CREDIT_AMT := v_credit_amount;
         L_CRN_RECURRING_CHARGE := v_recurring_charge;
         L_CRN_OTC := v_otc;
         L_CRN_USAGE_CHARGE := v_usage_charge;
         L_CRN_MISC_CREDIT_CHARGE := v_credit_charge;

         v_total_wo_tax :=
            v_recurring_charge + v_otc + v_usage_charge + v_credit_charge;

         L_RECUARRING_CHARGES := v_recurring_charge;
         L_ONETIME_CHARGES := v_otc;
         L_USAGE_CHARGES := v_usage_charge;
         L_MISC_CHARGES := v_credit_charge;
         L_TOTAL_TAX_EX := v_total_wo_tax;
         L_AMT_IN_WORDS := v_in_words;


         SELECT user_id
           INTO l_user_id
           FROM apps.fnd_user
          WHERE user_name = 'SFADMIN';          --FND_PROFILE.VALUE(USERNAME);

         SELECT DISTINCT LEGAL_INVOICE_NO, INVOICE_AMOUNT
           INTO V_CLOSE_INV_NO, V_ACT_INV_AMOUNT
           FROM xxvfent_credit_no_closed_inv_v
          WHERE     prim_resource_val = P_CRN_CIRCUIT_ID
                AND (    START_DATE >= P_CRN_BILL_FROM_DATE
                     AND END_DATE <= P_CRN_BILL_TO_DATE);



         --Genearet Sequence Numbers
         SELECT credit_note_seq.NEXTVAL INTO v_credit_note_no FROM DUAL;

         SELECT xxvfent_credit_note_dtl_seq.NEXTVAL INTO l_dtl_seq FROM DUAL;

         L_CRN_DETAIL_ID := l_dtl_seq;
         L_CRN_NOTE_ID := v_credit_note_no;
         L_CREATED_BY := l_user_id;
         L_CREATION_DATE := SYSDATE;
         L_LAST_UPDATED_BY := l_user_id;
         L_LAST_UPDATE_DATE := SYSDATE;

         SELECT xxvfent_credit_note_hdr_seq.NEXTVAL INTO l_hdr_seq FROM DUAL;

         INSERT INTO HUTCHCS.XXVFENT_CREDIT_NOTE_HEADER (CRN_HEADER_ID,
                                                         CRN_NOTE_ID,
                                                         CRN_BUSI_TYPE,
                                                         CRN_CIRCUIT_ID,
                                                         CRN_CREATED_DATE,
                                                         CRN_BILL_FROM_DATE,
                                                         CRN_BILL_TO_DATE,
                                                         CUST_CODE,
                                                         CUST_NAME,
                                                         CUST_SUBSCRIBER_ID,
                                                         CUST_CIRCLE_ID,
                                                         CUST_SEGMENT,
                                                         CUST_CONTACT_NO,
                                                         CUST_ADDRESS,
                                                         CUST_POSTCODE,
                                                         CUST_COUNTRY,
                                                         CUST_STATE,
                                                         ACCOUNT_ID,
                                                         ENTERPRISE_CODE,
                                                         FA_ID,
                                                         CIRCUIT_STATUS,
                                                         CREATED_BY,
                                                         CREATION_DATE,
                                                         LAST_UPDATED_BY,
                                                         LAST_UPDATE_DATE)
              VALUES (l_hdr_seq,
                      v_credit_note_no,
                      r1.product_type,
                      P_CRN_CIRCUIT_ID,
                      TRUNC (SYSDATE),
                      P_CRN_BILL_FROM_DATE,
                      P_CRN_BILL_TO_DATE,
                      r1.ENTERPRISE_CODE,
                      r1.PARTY_NAME,
                      r1.SUBSCRIBER_ID,
                      r1.ATTRIBUTE10,
                      r1.attribute2,
                      P_PHONE_NUMBER,
                      P_ADDRESS1,
                      P_POSTAL_CODE,
                      P_COUNTRY,
                      P_STATE,
                      0,
                      r1.enterprise_code,
                      r1.FA_NUMBER,
                      r1.STATUS,
                      l_user_id,
                      SYSDATE,
                      l_user_id,
                      SYSDATE);

         FOR r2 IN c2
         LOOP
            BEGIN
               INSERT
                 INTO HUTCHCS.XXVFENT_CREDIT_NOTE_DETAILS (
                         CRN_DETAIL_ID,
                         CRN_NOTE_ID,
                         CRN_CIRCUIT_ID,
                         CRN_REASON,
                         CRN_CLOSE_INV_NO,
                         CRN_ACT_INV_AMT,
                         CRN_NEW_PLAN,
                         CRN_EFFECTIVE_PERIOD_FRM,
                         CRN_EFFECTIVE_PERIOD_TO,
                         CRN_OPEN_INV_NO,
                         CRN_INV_DATE,
                         CRN_OPEN_INV_AMT,
                         CRN_REVISED_INV_AMT,
                         CRN_CREDIT_AMT,
                         CRN_RECURRING_CHARGE,
                         CRN_OTC,
                         CRN_USAGE_CHARGE,
                         CRN_MISC_CREDIT_CHARGE,
                         ATTRIBUTE1,
                         ATTRIBUTE2,
                         ATTRIBUTE3,
                         ATTRIBUTE4,
                         ATTRIBUTE5,
                         CREATED_BY,
                         CREATION_DATE,
                         LAST_UPDATED_BY,
                         LAST_UPDATE_DATE)
               VALUES (L_CRN_DETAIL_ID,
                       L_CRN_NOTE_ID,
                       P_CRN_CIRCUIT_ID,
                       PV_CRN_REASON,
                       V_CLOSE_INV_NO,
                       V_ACT_INV_AMOUNT,
                       PV_CRN_NEW_PLAN,
                       PV_CRN_EFFECTIVE_PERIOD_FRM,
                       PV_CRN_EFFECTIVE_PERIOD_TO,
                       PV_CRN_OPEN_INV_NO,
                       r2.STATEMENT_DATE,
                       r2.INVOICE_AMOUNT,
                       L_CRN_REVISED_INV_AMT,
                       L_CRN_CREDIT_AMT,
                       L_RECUARRING_CHARGES,
                       L_ONETIME_CHARGES,
                       L_USAGE_CHARGES,
                       L_MISC_CHARGES,
                       r2.INVOICE_ID,
                       r2.ACCOUNT_ID,
                       PV_USER_AMOUNT,
                       r2.AMOUNT_CURRENCY,
                       r2.INVOICE_STATUS,
                       l_user_id,
                       SYSDATE,
                       l_user_id,
                       SYSDATE);
            END;
         END LOOP;

         COMMIT;
      ELSE
         v_status := 'E';

         raise_application_error (-20017, 'Error Occured ');
      END IF;
   END prc_test11;

Re: Need Help In Plsql Procedure [message #669308 is a reply to message #669307] Sat, 14 April 2018 17:47 Go to previous messageGo to next message
Djpats
Messages: 17
Registered: January 2018
Junior Member
In addition to above,,, i want to run a concurrent program for the procedure
Re: Need Help In Plsql Procedure [message #669312 is a reply to message #669307] Sun, 15 April 2018 00:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65456
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
If you don't know how to format the code, learn it using SQL Formatter.

And FEEDBACK to and THANK people who take time to help you.

Re: Need Help In Plsql Procedure [message #669331 is a reply to message #669312] Mon, 16 April 2018 03:23 Go to previous message
cookiemonster
Messages: 13084
Registered: September 2008
Location: Rainy Manchester
Senior Member
If PV_CRN_NEW_PLAN IS NOT NUL AND PV_USER_AMOUNT IS NOT NULL THEN
  raise_application_error (<error code>, 'you can only supply one of PV_CRN_NEW_PLAN and PV_USER_AMOUNT);

Concurrent program is an oracle apps thing - yoou'd better off asking in the apps forums about that.


Some other comments on the code:

1) Setting local variables to null at the start is a waste of time. They'll be null if you don't do anything.
2) select from dual is almost never needed in PL/SQL eg.
SELECT TRUNC (LAST_DAY (SYSDATE)) INTO l_last_day FROM DUAL;
should be:
l_last_day := TRUNC (LAST_DAY (SYSDATE));
There are a handful of SQL functions that don't work in PL/SQL and for those you'll need select from dual, but not otherwise.
3) SELECT COUNT(1) should be SELECT COUNT(*)
4) The final for loop/insert will be more performant written as an insert/select.
5) You've got unused variables.
6) Variables should be typed to columns where ever possible:
l_prov_amount  xxvfent_credit_note_bulk_load.balance_amount%TYPE
Previous Topic: divide string into multiple parts
Next Topic: Please help for resolving error for PIPELINED table function call
Goto Forum:
  


Current Time: Tue Apr 24 09:45:27 CDT 2018

Total time taken to generate the page: 0.02184 seconds