Solved

415/33  PL/SQL: ORA-01744: inappropriate INTO

Posted on 2010-08-20
13
2,649 Views
Last Modified: 2013-12-19
Hi Experts,

I am getting the following error, when I try to execute the following code in A package:

 (select flex_value INTO vFlexValue
              from fnd_flex_values
                          where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_Value_set_name = 'RDA_VENDOR_CATEGORY')
                          and flex_value_id = RSVS.ATTRIBUTE10),
415/33  PL/SQL: ORA-01744: inappropriate INTO

Please help.

thanks
Dileep.
0
Comment
Question by:dileepav
13 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 33483558
this seems to be part of a subquery?

you cannot do "into" in a subquery...
please show "all" the query
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33483563
(select flex_value INTO vFlexValue
              from fnd_flex_values
                          where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_Value_set_name = 'RDA_VENDOR_CATEGORY')
                          and flex_value_id = RSVS.ATTRIBUTE10),

i believe this full select statement itself is present in some other select statement... can u give your complete select query which is giving this error.

It has to be something like the below...

select
col1,
col2,
colN,
(select flex_value from fnd_flex_values
  where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_Value_set_name = 'RDA_VENDOR_CATEGORY')
                          and flex_value_id = RSVS.ATTRIBUTE10),
col10
into
col1_variable,
col2_variable,
colN_variable,
flex_value_variable,
col10_variable
from ....
where ...

Thanks
0
 

Author Comment

by:dileepav
ID: 33483593
Here is the full code:
PROCEDURE SMF_WEBAPP_ADD_VENDOR_SITE (

                                         PNREQUESTID         IN    NUMBER,

                                  PNRDAVID            IN  NUMBER,

                                  PNRDAVSID         IN  NUMBER,

                                  PNORGID           IN  NUMBER,

                                  PNUSERID          IN    NUMBER,

                                  PNERRCODE            OUT VARCHAR2,

                                  PNERRORFLAG       OUT VARCHAR2)     AS

/* -----------------------------------------------------------------------

REM

REM   PROCEDURE SMF_WEBAPP_ADD_VENDOR_SITE

REM

REM ------------------------------------------------------------------------

*/

   vProcName                   VARCHAR2(30) := 'ADD_VENDOR_SITE';

   vProgress                   VARCHAR2(80);

   vErrorCode               VARCHAR2(20)  := 'SUCCESS';

   vUserId                  NUMBER;

   vVendorId                NUMBER;

   vVendorSiteId            NUMBER;

   vVendorSiteCode          VARCHAR2(15);

   vAddressLine1            VARCHAR2(35);

   vAddressLine2            VARCHAR2(35);

   vAddressLine3            VARCHAR2(35);

   vAddressLine4            VARCHAR2(35);

   vCity                    VARCHAR2(25);

   vState                    VARCHAR2(25);

   vZip                        VARCHAR2(20);

   vProvince                VARCHAR2(25);

   vCountry                 VARCHAR2(25);

   vPaySiteFlag             VARCHAR2(1);

   vPurchasingSiteFlag        VARCHAR2(1);

   vVatCode                 VARCHAR2(20);

   vVatRegistrationNum         VARCHAR2(20);

   vTermsId                    NUMBER;

   vInvoiceCurrencyCode        VARCHAR2(15);

   vDistributionSetId        NUMBER;

   vAcctsPayCodeCombinationId       NUMBER;

   vPrepayCodeCombinationId           NUMBER;

   vOffsetVatCode            VARCHAR2(20);

   vPaymentMethodLookupCode    VARCHAR2(25);

   vPaymentCurrencyCode     VARCHAR2(15);

   vExclusivePaymentFlag    VARCHAR2(1);

   vAllowAwtFlag            VARCHAR2(1);

   vAWTGroupId                NUMBER;

   vAttribute1                VARCHAR2(3);

   vAttribute2                VARCHAR2(10);

   vTaxReportingSiteFlag    VARCHAR2(1);

   vPayGroupLookupCode      VARCHAR2(25);

   vUpdateVendorId          NUMBER;

   vVendorName              VARCHAR2(80);

   vEftFlag                      VARCHAR2(2);

   vEntity                    VARCHAR2(3);

   lvLocationCode            VARCHAR2(30);

   lvLocationId                NUMBER;

   --Variable for new Fields Joe Lui SMF Phase III 1/27/05

   vFreightTerms                 VARCHAR2(25);

   vShipToLocationID         NUMBER;



   --Added by Cyrene V. Jacob for the Flex project ()

   vFlexValue               VARCHAR2(125);



   vFax                         VARCHAR2(15);

   vFaxAreaCode                 VARCHAR2(10);

  v_jg_zz_attribute_category VARCHAR2(30);

  v_jgzz_site_info1          VARCHAR2(30);

  v_jgzz_site_info2          VARCHAR2(30);

  v_jgzz_site_info3          VARCHAR2(30);

  v_jgzz_site_info6          VARCHAR2(30);

  v_jgzz_site_info10           VARCHAR2(30);

  v_jgzz_site_info11          VARCHAR2(30);

  v_jgzz_site_info12          VARCHAR2(30);

  v_jgzz_site_info19          VARCHAR2(30);

   vFound                    VARCHAR2(1);



   vAttribute14                VARCHAR2(25);

  v_gTol_Id          NUMBER;

  v_sTol_Id          NUMBER;

  v_base_ccy         VARCHAR2(25);

BEGIN

 /* get the current record from the source table */

   BEGIN

      SELECT RSVS.vendor_id,

             trim(RSVS.vendor_site_code),

             trim(address_line1),

             trim(address_line2),

             trim(address_line3),

             trim(address_line4),

             trim(city),

             trim(state),

             trim(zip),

             trim(province),

             trim(country),

             pay_site_flag,

             purchasing_site_flag,

             vat_code,

             vat_registration_num,

             terms_id,

             invoice_currency_code,

             distribution_set_id,

             accts_pay_code_combination_id,

             prepay_code_combination_id,

             offset_vat_code,

             payment_method_lookup_code,

             payment_currency_code,

             exclusive_payment_flag,

             allow_awt_flag,

             awt_group_id,

             RSVS.attribute1,

             attribute2,

             tax_reporting_site_flag,

             pay_group_lookup_code,

             update_vendor_id,

             vendor_name,

             EFT_FLAG,

             RSVS.entity,

             DECODE(  EFT_FLAG, NULL, NULL, EFT_FLAG), -- category

             DECODE(  EFT_FLAG, 'DE','N','LUM2'), -- jgzz info1

             DECODE(  EFT_FLAG, 'DE',NULL,'1'), -- jgzz info2

             DECODE(  EFT_FLAG, 'DE',NULL,'M'), -- jgzz info3

             DECODE(  EFT_FLAG, 'DE','Supplier', NULL), -- jgzz info6

             DECODE(  EFT_FLAG, 'DE','REPUBLIK', 'J'), -- jgzz info10

             DECODE(  EFT_FLAG, 'DE','U',NULL), -- jgzz info11

             DECODE(  EFT_FLAG, 'DE','096',NULL), -- jgzz info12

             DECODE(  EFT_FLAG, 'DE','MAZED',NULL), -- jgzz info19

             FREIGHT_TERMS_LOOKUP_CODE,

             SHIP_TO_LOCATION_ID,

             --

             --Flex Value Added by Cyrene V. Jacob -- INTO vFlexValue is added by Dileep for fixing VC not storing at Oracle.

             (select flex_value INTO vFlexValue 

              from fnd_flex_values

			        where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_Value_set_name = 'RDA_VENDOR_CATEGORY')

			        and flex_value_id = RSVS.ATTRIBUTE10),

             --

             FAX,

             FAX_AREA_CODE,

             ATTRIBUTE14

      INTO   vVendorId,

             vVendorSiteCode,

             vAddressLine1,

             vAddressLine2,

             vAddressLine3,

             vAddressLine4,

             vCity,

             vState,

             vZip,

             vProvince,

             vCountry,

             vPaySiteFlag,

             vPurchasingSiteFlag,

             vVatCode,

             vVatRegistrationNum,

             vTermsId,

             vInvoiceCurrencyCode,

             vDistributionSetId,

             vAcctsPayCodeCombinationId,

             vPrepayCodeCombinationId,

             vOffsetVatCode,

             vPaymentMethodLookupCode,

             vPaymentCurrencyCode,

             vExclusivePaymentFlag,

             vAllowAwtFlag,

             vAWTGroupId,

             vAttribute1,

             vAttribute2,

             vTaxReportingSiteFlag,

             vPayGroupLookupCode,

             vUpdateVendorId,

             vVendorName,

             vEftFlag,

             vEntity,

              v_jg_zz_attribute_category,

              v_jgzz_site_info1 ,

              v_jgzz_site_info2 ,

              v_jgzz_site_info3 ,

              v_jgzz_site_info6 ,

              v_jgzz_site_info10 ,

              v_jgzz_site_info11 ,

              v_jgzz_site_info12 ,

              v_jgzz_site_info19,

              vFreightTerms,              --new field

              vShipToLocationID,        --new field



              --Flex Value Field added by Cyrene V. Jacob

              vFlexValue,



                 vFax,                        --new field

                 vFaxAreaCode    ,

              vattribute14            --new field

      FROM   rda_suppmaint_vendor_sites RSVS,

             rda_suppmaint_vendors RSV

      WHERE  rsv.request_id = rsvs.request_id

        AND  rsv.rda_vid    = rsvs.rda_vid

        AND  rsvs.request_id = pnrequestid

         AND  rsvs.rda_vsid   = pnrdavsid

        AND  rsv.rda_vid    = pnrdavid;

   EXCEPTION

      WHEN OTHERS THEN

           vErrorCode := 'ERR201';

           Fnd_File.put_line (Fnd_File.LOG,'Reading Vendor SITE Values from SMF table '||SQLERRM);

   END;

/* ensure the vendor site does not exist in Oracle yet */

   BEGIN

      SELECT ('x')

        INTO vFound

        FROM PO_VENDOR_SITES_ALL

       WHERE VENDOR_SITE_CODE = vVendorSiteCode

         AND ORG_ID = pnOrgid

         AND VENDOR_ID IN (SELECT VENDOR_ID

                             FROM PO_VENDORS

                            WHERE PO_VENDORS.VENDOR_NAME = vVendorName);

      IF vFound IS NOT NULL THEN

         vErrorCode := 'ERR202';

      END IF;

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

         NULL;

      WHEN OTHERS THEN

         vErrorCode := 'ERR207';

         Fnd_File.put_line (Fnd_File.LOG,'Reading PO_VENDOR_SITES_ALL table '||SQLERRM);

   END;

   IF vErrorCode = 'SUCCESS' THEN

/* get the next available vendor site id */

      BEGIN

         SELECT  PO_VENDOR_SITES_S.NEXTVAL

           INTO    vVendorSiteId

           FROM    DUAL ;

         EXCEPTION

           WHEN OTHERS THEN

              vErrorCode := 'ERR203';

              Fnd_File.put_line (Fnd_File.LOG,'getting next vendor site id  '||SQLERRM);

      END;

   END IF;

 /* if the entity is Belgium or BAF Spain, change the default value for BILL_TO_LOCATION_ID */

   IF  vEntity IN ('392','461') THEN

      IF vEntity = '392' THEN

         lvLocationCode := 'RDA BEN-BEL';

      ELSE

         IF vEntity = '461' THEN

            lvLocationCode := 'RDA SP BAF BILL TO';

         END IF;

      END IF;

      BEGIN

         SELECT LOCATION_ID

           INTO lvLocationId

           FROM HR_LOCATIONS

          WHERE location_code = lvLocationCode;

          EXCEPTION

         WHEN OTHERS THEN

            vErrorCode := 'ERR208';

            Fnd_File.put_line (Fnd_File.LOG,'Reading HR_LOCATIONS table '||SQLERRM);

      END;

      gvBillToLocationId := lvLocationId;

   END IF;

   Begin

      SELECT TOLERANCE_ID,SERVICES_TOLERANCE_ID, base_currency_code

      INTO v_gTol_id,v_sTol_id, v_base_ccy

       FROM AP_SYSTEM_PARAMETERS_ALL

        WHERE ORG_ID = PNORGID;

        EXCEPTION

          WHEN OTHERS THEN

             vErrorCode := 'ERR209';

            Fnd_File.put_line (Fnd_File.LOG,'Reading AP_SYSTEM_PARAMETERS_ALL table '||SQLERRM);

   End;

 --

  		 IF PNORGID in (205, 509)

       AND NVL(vInvoiceCurrencyCode,0) != NVL(v_base_ccy,0)

           THEN gvMatchOptionSite := 'R';

       ELSE gvMatchOptionSite := gvMatchOption;

		   END IF;

--

 /* insert the data into PO_VENDOR_SITES_S */

   BEGIN

   IF vErrorCode = 'SUCCESS' THEN

     BEGIN

      INSERT INTO po_vendor_sites_all

         (accts_pay_code_combination_id,

          address_line1,

          address_line2,

          address_line3,

          address_line4,

          allow_awt_flag,

          always_take_disc_flag,

          attribute1,

          attribute2,

          auto_tax_calc_flag,

          auto_tax_calc_override,

          ap_tax_rounding_rule,

          amount_includes_tax_flag,

          awt_group_id,

          bill_to_location_id,

          city,

          country,

          created_by,

          creation_date,

          distribution_set_id,

          exclusive_payment_flag,

          hold_all_payments_flag,

          hold_future_payments_flag,

          hold_unmatched_invoices_flag,

          invoice_currency_code,

          last_updated_by,

          last_update_date,

          offset_vat_code,

          org_id,

          payment_currency_code,

          payment_method_lookup_code,

          payment_priority,

          pay_date_basis_lookup_code,

          pay_group_lookup_code,

          pay_site_flag,

          prepay_code_combination_id,

          province,

          purchasing_site_flag,

                  state,

          tax_reporting_site_flag,

          terms_date_basis,

          terms_id,

                  vat_code,

          vat_registration_num,

          vendor_id,

          vendor_site_id,

          vendor_site_code,

          zip,

          FREIGHT_TERMS_LOOKUP_CODE,

          SHIP_TO_LOCATION_ID,



          --Flex Value added by Cyrene V. Jacob

          ATTRIBUTE10,



          FAX,

          FAX_AREA_CODE,

          ATTRIBUTE14,

          MATCH_OPTION,

                  SERVICES_TOLERANCE_ID,  -- new field

                  TOLERANCE_ID            -- new field

         )

    VALUES (NVL(vAcctsPayCodeCombinationId,gvAccountsPayCodeComb),

          vAddressLine1,

          vAddressLine2,

          vAddressLine3,

          vAddressLine4,

          vAllowAwtFlag,

          gvAlwaysTakeDisc,

          vAttribute1,

          vAttribute2,

          gvAutoTaxCalcFlag,

          gvAutoTaxCalcOverride,

          gvAPTaxRoundingRule,

          gvAmountIncludesTaxFlag,

          vAWTGroupId,

          gvBillToLocationId,

          vCity,

          vCountry,

          pnUserid,

          SYSDATE,

          vDistributionSetId,

          vExclusivePaymentFlag,

          'N',

          'N',

          gvHoldUnmatched,

          NVL(vInvoiceCurrencyCode,gvInvoiceCurrency),

          pnUserid,

          SYSDATE,

          vOffsetVatCode,

          pnOrgid,

          NVL(vPaymentCurrencyCode,gvPaymentCurrency),

          NVL(vPaymentMethodLookupCode,gvPaymentMethodLookup),

          '99',

          gvPayDateBasisLookup,

          NVL(vPayGroupLookupCode,gvVendorPayGroupLookupCode),

          vPaySiteFlag,

          vPrepayCodeCombinationId,

          vProvince,

          vPurchasingSiteFlag,

          vState,

          vTaxReportingSiteFlag,

          gvTermsDateBasis,

          NVL(vTermsId,gvTerms),

          vVatCode,

          vVatRegistrationNum,

          NVL(vUpdateVendorId,vVendorId),

          vVendorSiteId,

          vVendorSiteCode,

          vZip,

          vFreightTerms,              --new field

          vShipToLocationID,        --new field



          --Flex Value Field added by Cyrene V. Jacob

          vFlexValue,



          vFax,                        --new field

          vFaxAreaCode    ,            --new field

          vAttribute14 ,

          gvMatchOptionSite,

                  v_sTol_Id,

                  v_gTol_Id

          );

       EXCEPTION

           WHEN OTHERS THEN

              Fnd_File.put_line (Fnd_File.LOG,'inserting into PO_VENDOR_SITES_ALL table '||SQLERRM);

              vErrorCode := 'ERR205';

       END;

      END IF;

   END;

 /* insert the data into jg_zz_vend_site_info  */

   IF vErrorCode = 'SUCCESS' AND  vEftFlag IS NOT NULL THEN

     BEGIN

      INSERT INTO jg_zz_vend_site_info

               (jgzz_country_code,

             vendor_site_id,

             jgzz_attribute_category,

             jgzz_site_info1,

             jgzz_site_info2,

             jgzz_site_info3,

             jgzz_site_info6,

             jgzz_site_info10,

             jgzz_site_info11,

             jgzz_site_info12,

             jgzz_site_info19

             )

       VALUES

                (vEftFlag,

              vVendorSiteId,

              v_jg_zz_attribute_category,

              v_jgzz_site_info1          ,

              v_jgzz_site_info2          ,

              v_jgzz_site_info3          ,

              v_jgzz_site_info6          ,

              v_jgzz_site_info10           ,

              v_jgzz_site_info11          ,

              v_jgzz_site_info12          ,

              v_jgzz_site_info19

             );

     EXCEPTION

           WHEN OTHERS THEN

              vErrorCode := 'ERR206';

              Fnd_File.put_line (Fnd_File.LOG,'inserting into JG_ZZ_VEND_SITE_INFO table '||SQLERRM);

     END;

   END IF;

/* update interface status and vendor id on the source table*/

   BEGIN

      UPDATE rda_suppmaint_vendor_sites

      SET    interface_status = vErrorCode,

             update_vendor_site_id = vVendorSiteId

      WHERE  request_id = PNREQUESTID

      AND     rda_vsid    = pnrdavsid

       AND   rda_vid   = pnrdavid;

   END;

/* set the return value */

   IF vErrorCode = 'SUCCESS' THEN

      PNERRORFLAG := 'N';

   ELSE

      PNERRORFLAG := 'Y';

      PNERRCODE   := vErrorCode;

   END IF;

END SMF_WEBAPP_ADD_VENDOR_SITE  ;

Open in new window

0
 

Author Comment

by:dileepav
ID: 33483612
The issue, that we are facing here is the Vendor Category is not storing at ATTRIBUTE10 column of "po_vendor_sites_all" table". THe following is the logic we have used for slecting the Vendor Category.

(select flex_value INTO vFlexValue
              from fnd_flex_values
                          where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_Value_set_name = 'RDA_VENDOR_CATEGORY')
                          and flex_value_id = RSVS.ATTRIBUTE10),
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 33483685
please remove the INTO vFlexValue from that subquery, you already have that destination in the global INTO clause
PROCEDURE SMF_WEBAPP_ADD_VENDOR_SITE (

                                         PNREQUESTID         IN    NUMBER,

                                  PNRDAVID            IN  NUMBER,

                                  PNRDAVSID         IN  NUMBER,

                                  PNORGID           IN  NUMBER,

                                  PNUSERID          IN    NUMBER,

                                  PNERRCODE            OUT VARCHAR2,

                                  PNERRORFLAG       OUT VARCHAR2)     AS

/* -----------------------------------------------------------------------

REM

REM   PROCEDURE SMF_WEBAPP_ADD_VENDOR_SITE

REM

REM ------------------------------------------------------------------------

*/

   vProcName                   VARCHAR2(30) := 'ADD_VENDOR_SITE';

   vProgress                   VARCHAR2(80);

   vErrorCode               VARCHAR2(20)  := 'SUCCESS';

   vUserId                  NUMBER;

   vVendorId                NUMBER;

   vVendorSiteId            NUMBER;

   vVendorSiteCode          VARCHAR2(15);

   vAddressLine1            VARCHAR2(35);

   vAddressLine2            VARCHAR2(35);

   vAddressLine3            VARCHAR2(35);

   vAddressLine4            VARCHAR2(35);

   vCity                    VARCHAR2(25);

   vState                    VARCHAR2(25);

   vZip                        VARCHAR2(20);

   vProvince                VARCHAR2(25);

   vCountry                 VARCHAR2(25);

   vPaySiteFlag             VARCHAR2(1);

   vPurchasingSiteFlag        VARCHAR2(1);

   vVatCode                 VARCHAR2(20);

   vVatRegistrationNum         VARCHAR2(20);

   vTermsId                    NUMBER;

   vInvoiceCurrencyCode        VARCHAR2(15);

   vDistributionSetId        NUMBER;

   vAcctsPayCodeCombinationId       NUMBER;

   vPrepayCodeCombinationId           NUMBER;

   vOffsetVatCode            VARCHAR2(20);

   vPaymentMethodLookupCode    VARCHAR2(25);

   vPaymentCurrencyCode     VARCHAR2(15);

   vExclusivePaymentFlag    VARCHAR2(1);

   vAllowAwtFlag            VARCHAR2(1);

   vAWTGroupId                NUMBER;

   vAttribute1                VARCHAR2(3);

   vAttribute2                VARCHAR2(10);

   vTaxReportingSiteFlag    VARCHAR2(1);

   vPayGroupLookupCode      VARCHAR2(25);

   vUpdateVendorId          NUMBER;

   vVendorName              VARCHAR2(80);

   vEftFlag                      VARCHAR2(2);

   vEntity                    VARCHAR2(3);

   lvLocationCode            VARCHAR2(30);

   lvLocationId                NUMBER;

   --Variable for new Fields Joe Lui SMF Phase III 1/27/05

   vFreightTerms                 VARCHAR2(25);

   vShipToLocationID         NUMBER;



   --Added by Cyrene V. Jacob for the Flex project ()

   vFlexValue               VARCHAR2(125);



   vFax                         VARCHAR2(15);

   vFaxAreaCode                 VARCHAR2(10);

  v_jg_zz_attribute_category VARCHAR2(30);

  v_jgzz_site_info1          VARCHAR2(30);

  v_jgzz_site_info2          VARCHAR2(30);

  v_jgzz_site_info3          VARCHAR2(30);

  v_jgzz_site_info6          VARCHAR2(30);

  v_jgzz_site_info10           VARCHAR2(30);

  v_jgzz_site_info11          VARCHAR2(30);

  v_jgzz_site_info12          VARCHAR2(30);

  v_jgzz_site_info19          VARCHAR2(30);

   vFound                    VARCHAR2(1);



   vAttribute14                VARCHAR2(25);

  v_gTol_Id          NUMBER;

  v_sTol_Id          NUMBER;

  v_base_ccy         VARCHAR2(25);

BEGIN

 /* get the current record from the source table */

   BEGIN

      SELECT RSVS.vendor_id,

             trim(RSVS.vendor_site_code),

             trim(address_line1),

             trim(address_line2),

             trim(address_line3),

             trim(address_line4),

             trim(city),

             trim(state),

             trim(zip),

             trim(province),

             trim(country),

             pay_site_flag,

             purchasing_site_flag,

             vat_code,

             vat_registration_num,

             terms_id,

             invoice_currency_code,

             distribution_set_id,

             accts_pay_code_combination_id,

             prepay_code_combination_id,

             offset_vat_code,

             payment_method_lookup_code,

             payment_currency_code,

             exclusive_payment_flag,

             allow_awt_flag,

             awt_group_id,

             RSVS.attribute1,

             attribute2,

             tax_reporting_site_flag,

             pay_group_lookup_code,

             update_vendor_id,

             vendor_name,

             EFT_FLAG,

             RSVS.entity,

             DECODE(  EFT_FLAG, NULL, NULL, EFT_FLAG), -- category

             DECODE(  EFT_FLAG, 'DE','N','LUM2'), -- jgzz info1

             DECODE(  EFT_FLAG, 'DE',NULL,'1'), -- jgzz info2

             DECODE(  EFT_FLAG, 'DE',NULL,'M'), -- jgzz info3

             DECODE(  EFT_FLAG, 'DE','Supplier', NULL), -- jgzz info6

             DECODE(  EFT_FLAG, 'DE','REPUBLIK', 'J'), -- jgzz info10

             DECODE(  EFT_FLAG, 'DE','U',NULL), -- jgzz info11

             DECODE(  EFT_FLAG, 'DE','096',NULL), -- jgzz info12

             DECODE(  EFT_FLAG, 'DE','MAZED',NULL), -- jgzz info19

             FREIGHT_TERMS_LOOKUP_CODE,

             SHIP_TO_LOCATION_ID,

             --

             --Flex Value Added by Cyrene V. Jacob -- INTO vFlexValue is added by Dileep for fixing VC not storing at Oracle.

             (select flex_value 

              from fnd_flex_values

			        where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_Value_set_name = 'RDA_VENDOR_CATEGORY')

			        and flex_value_id = RSVS.ATTRIBUTE10),

             --

             FAX,

             FAX_AREA_CODE,

             ATTRIBUTE14

      INTO   vVendorId,

             vVendorSiteCode,

             vAddressLine1,

             vAddressLine2,

             vAddressLine3,

             vAddressLine4,

             vCity,

             vState,

             vZip,

             vProvince,

             vCountry,

             vPaySiteFlag,

             vPurchasingSiteFlag,

             vVatCode,

             vVatRegistrationNum,

             vTermsId,

             vInvoiceCurrencyCode,

             vDistributionSetId,

             vAcctsPayCodeCombinationId,

             vPrepayCodeCombinationId,

             vOffsetVatCode,

             vPaymentMethodLookupCode,

             vPaymentCurrencyCode,

             vExclusivePaymentFlag,

             vAllowAwtFlag,

             vAWTGroupId,

             vAttribute1,

             vAttribute2,

             vTaxReportingSiteFlag,

             vPayGroupLookupCode,

             vUpdateVendorId,

             vVendorName,

             vEftFlag,

             vEntity,

              v_jg_zz_attribute_category,

              v_jgzz_site_info1 ,

              v_jgzz_site_info2 ,

              v_jgzz_site_info3 ,

              v_jgzz_site_info6 ,

              v_jgzz_site_info10 ,

              v_jgzz_site_info11 ,

              v_jgzz_site_info12 ,

              v_jgzz_site_info19,

              vFreightTerms,              --new field

              vShipToLocationID,        --new field



              --Flex Value Field added by Cyrene V. Jacob

              vFlexValue,



                 vFax,                        --new field

                 vFaxAreaCode    ,

              vattribute14            --new field

      FROM   rda_suppmaint_vendor_sites RSVS,

             rda_suppmaint_vendors RSV

      WHERE  rsv.request_id = rsvs.request_id

        AND  rsv.rda_vid    = rsvs.rda_vid

        AND  rsvs.request_id = pnrequestid

         AND  rsvs.rda_vsid   = pnrdavsid

        AND  rsv.rda_vid    = pnrdavid;

   EXCEPTION

      WHEN OTHERS THEN

           vErrorCode := 'ERR201';

           Fnd_File.put_line (Fnd_File.LOG,'Reading Vendor SITE Values from SMF table '||SQLERRM);

   END;

/* ensure the vendor site does not exist in Oracle yet */

   BEGIN

      SELECT ('x')

        INTO vFound

        FROM PO_VENDOR_SITES_ALL

       WHERE VENDOR_SITE_CODE = vVendorSiteCode

         AND ORG_ID = pnOrgid

         AND VENDOR_ID IN (SELECT VENDOR_ID

                             FROM PO_VENDORS

                            WHERE PO_VENDORS.VENDOR_NAME = vVendorName);

      IF vFound IS NOT NULL THEN

         vErrorCode := 'ERR202';

      END IF;

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

         NULL;

      WHEN OTHERS THEN

         vErrorCode := 'ERR207';

         Fnd_File.put_line (Fnd_File.LOG,'Reading PO_VENDOR_SITES_ALL table '||SQLERRM);

   END;

   IF vErrorCode = 'SUCCESS' THEN

/* get the next available vendor site id */

      BEGIN

         SELECT  PO_VENDOR_SITES_S.NEXTVAL

           INTO    vVendorSiteId

           FROM    DUAL ;

         EXCEPTION

           WHEN OTHERS THEN

              vErrorCode := 'ERR203';

              Fnd_File.put_line (Fnd_File.LOG,'getting next vendor site id  '||SQLERRM);

      END;

   END IF;

 /* if the entity is Belgium or BAF Spain, change the default value for BILL_TO_LOCATION_ID */

   IF  vEntity IN ('392','461') THEN

      IF vEntity = '392' THEN

         lvLocationCode := 'RDA BEN-BEL';

      ELSE

         IF vEntity = '461' THEN

            lvLocationCode := 'RDA SP BAF BILL TO';

         END IF;

      END IF;

      BEGIN

         SELECT LOCATION_ID

           INTO lvLocationId

           FROM HR_LOCATIONS

          WHERE location_code = lvLocationCode;

          EXCEPTION

         WHEN OTHERS THEN

            vErrorCode := 'ERR208';

            Fnd_File.put_line (Fnd_File.LOG,'Reading HR_LOCATIONS table '||SQLERRM);

      END;

      gvBillToLocationId := lvLocationId;

   END IF;

   Begin

      SELECT TOLERANCE_ID,SERVICES_TOLERANCE_ID, base_currency_code

      INTO v_gTol_id,v_sTol_id, v_base_ccy

       FROM AP_SYSTEM_PARAMETERS_ALL

        WHERE ORG_ID = PNORGID;

        EXCEPTION

          WHEN OTHERS THEN

             vErrorCode := 'ERR209';

            Fnd_File.put_line (Fnd_File.LOG,'Reading AP_SYSTEM_PARAMETERS_ALL table '||SQLERRM);

   End;

 --

  		 IF PNORGID in (205, 509)

       AND NVL(vInvoiceCurrencyCode,0) != NVL(v_base_ccy,0)

           THEN gvMatchOptionSite := 'R';

       ELSE gvMatchOptionSite := gvMatchOption;

		   END IF;

--

 /* insert the data into PO_VENDOR_SITES_S */

   BEGIN

   IF vErrorCode = 'SUCCESS' THEN

     BEGIN

      INSERT INTO po_vendor_sites_all

         (accts_pay_code_combination_id,

          address_line1,

          address_line2,

          address_line3,

          address_line4,

          allow_awt_flag,

          always_take_disc_flag,

          attribute1,

          attribute2,

          auto_tax_calc_flag,

          auto_tax_calc_override,

          ap_tax_rounding_rule,

          amount_includes_tax_flag,

          awt_group_id,

          bill_to_location_id,

          city,

          country,

          created_by,

          creation_date,

          distribution_set_id,

          exclusive_payment_flag,

          hold_all_payments_flag,

          hold_future_payments_flag,

          hold_unmatched_invoices_flag,

          invoice_currency_code,

          last_updated_by,

          last_update_date,

          offset_vat_code,

          org_id,

          payment_currency_code,

          payment_method_lookup_code,

          payment_priority,

          pay_date_basis_lookup_code,

          pay_group_lookup_code,

          pay_site_flag,

          prepay_code_combination_id,

          province,

          purchasing_site_flag,

                  state,

          tax_reporting_site_flag,

          terms_date_basis,

          terms_id,

                  vat_code,

          vat_registration_num,

          vendor_id,

          vendor_site_id,

          vendor_site_code,

          zip,

          FREIGHT_TERMS_LOOKUP_CODE,

          SHIP_TO_LOCATION_ID,



          --Flex Value added by Cyrene V. Jacob

          ATTRIBUTE10,



          FAX,

          FAX_AREA_CODE,

          ATTRIBUTE14,

          MATCH_OPTION,

                  SERVICES_TOLERANCE_ID,  -- new field

                  TOLERANCE_ID            -- new field

         )

    VALUES (NVL(vAcctsPayCodeCombinationId,gvAccountsPayCodeComb),

          vAddressLine1,

          vAddressLine2,

          vAddressLine3,

          vAddressLine4,

          vAllowAwtFlag,

          gvAlwaysTakeDisc,

          vAttribute1,

          vAttribute2,

          gvAutoTaxCalcFlag,

          gvAutoTaxCalcOverride,

          gvAPTaxRoundingRule,

          gvAmountIncludesTaxFlag,

          vAWTGroupId,

          gvBillToLocationId,

          vCity,

          vCountry,

          pnUserid,

          SYSDATE,

          vDistributionSetId,

          vExclusivePaymentFlag,

          'N',

          'N',

          gvHoldUnmatched,

          NVL(vInvoiceCurrencyCode,gvInvoiceCurrency),

          pnUserid,

          SYSDATE,

          vOffsetVatCode,

          pnOrgid,

          NVL(vPaymentCurrencyCode,gvPaymentCurrency),

          NVL(vPaymentMethodLookupCode,gvPaymentMethodLookup),

          '99',

          gvPayDateBasisLookup,

          NVL(vPayGroupLookupCode,gvVendorPayGroupLookupCode),

          vPaySiteFlag,

          vPrepayCodeCombinationId,

          vProvince,

          vPurchasingSiteFlag,

          vState,

          vTaxReportingSiteFlag,

          gvTermsDateBasis,

          NVL(vTermsId,gvTerms),

          vVatCode,

          vVatRegistrationNum,

          NVL(vUpdateVendorId,vVendorId),

          vVendorSiteId,

          vVendorSiteCode,

          vZip,

          vFreightTerms,              --new field

          vShipToLocationID,        --new field



          --Flex Value Field added by Cyrene V. Jacob

          vFlexValue,



          vFax,                        --new field

          vFaxAreaCode    ,            --new field

          vAttribute14 ,

          gvMatchOptionSite,

                  v_sTol_Id,

                  v_gTol_Id

          );

       EXCEPTION

           WHEN OTHERS THEN

              Fnd_File.put_line (Fnd_File.LOG,'inserting into PO_VENDOR_SITES_ALL table '||SQLERRM);

              vErrorCode := 'ERR205';

       END;

      END IF;

   END;

 /* insert the data into jg_zz_vend_site_info  */

   IF vErrorCode = 'SUCCESS' AND  vEftFlag IS NOT NULL THEN

     BEGIN

      INSERT INTO jg_zz_vend_site_info

               (jgzz_country_code,

             vendor_site_id,

             jgzz_attribute_category,

             jgzz_site_info1,

             jgzz_site_info2,

             jgzz_site_info3,

             jgzz_site_info6,

             jgzz_site_info10,

             jgzz_site_info11,

             jgzz_site_info12,

             jgzz_site_info19

             )

       VALUES

                (vEftFlag,

              vVendorSiteId,

              v_jg_zz_attribute_category,

              v_jgzz_site_info1          ,

              v_jgzz_site_info2          ,

              v_jgzz_site_info3          ,

              v_jgzz_site_info6          ,

              v_jgzz_site_info10           ,

              v_jgzz_site_info11          ,

              v_jgzz_site_info12          ,

              v_jgzz_site_info19

             );

     EXCEPTION

           WHEN OTHERS THEN

              vErrorCode := 'ERR206';

              Fnd_File.put_line (Fnd_File.LOG,'inserting into JG_ZZ_VEND_SITE_INFO table '||SQLERRM);

     END;

   END IF;

/* update interface status and vendor id on the source table*/

   BEGIN

      UPDATE rda_suppmaint_vendor_sites

      SET    interface_status = vErrorCode,

             update_vendor_site_id = vVendorSiteId

      WHERE  request_id = PNREQUESTID

      AND     rda_vsid    = pnrdavsid

       AND   rda_vid   = pnrdavid;

   END;

/* set the return value */

   IF vErrorCode = 'SUCCESS' THEN

      PNERRORFLAG := 'N';

   ELSE

      PNERRORFLAG := 'Y';

      PNERRCODE   := vErrorCode;

   END IF;

END SMF_WEBAPP_ADD_VENDOR_SITE  ;

Open in new window

0
 
LVL 14

Assisted Solution

by:shru_0409
shru_0409 earned 100 total points
ID: 33483718
(select flex_value    from fnd_flex_values
                          where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_Value_set_name = 'RDA_VENDOR_CATEGORY')
                          and flex_value_id = RSVS.ATTRIBUTE10),


remove the into from this query.....
u r already use the INTO  statement  like .....INTO vFlexValue

try this
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dileepav
ID: 33483728
Yes I agree, I removed INTO, and it is owrking fine, But the issue here is the vendor category is not storing at ATTRIBUTE10 column of "po_vendor_sites_all" table", Could you please help me to fix that issue.

There is also another one part for this package for updating the Vendor Category value, I will paste that code as well.

thanks
Dil.
PROCEDURE SMF_WEBAPP_UPDATE_VENDOR_SITE (

                                         PNREQUESTID         IN    NUMBER,

                                  PNRDAVID            IN  NUMBER,

                                  PNRDAVSID         IN  NUMBER,

                                  PNORGID           IN  NUMBER,

                                  PNUSERID          IN    NUMBER,

                                  PNERRCODE            OUT VARCHAR2,

                                  PNERRORFLAG       OUT VARCHAR2)     AS

/* -----------------------------------------------------------------------

REM

REM   PROCEDURE SMF_WEBAPP_UPDATE_VENDOR _SITE

REM

REM ------------------------------------------------------------------------

*/

   vProcName                   VARCHAR2(30) := 'UPDATE_VENDOR_SITE';

   vProgress                   VARCHAR2(80);

   vErrorCode               VARCHAR2(20)  := 'SUCCESS';

   vUserId                  NUMBER;

   vVendorSiteId            NUMBER;

   vVendorSiteCode          VARCHAR2(15);

   vAddressLine1            VARCHAR2(35);

   vAddressLine2            VARCHAR2(35);

   vAddressLine3            VARCHAR2(35);

   vAddressLine4            VARCHAR2(35);

   vCity                    VARCHAR2(25);

   vState                    VARCHAR2(25);

   vZip                        VARCHAR2(20);

   vProvince                VARCHAR2(25);

   vCountry                 VARCHAR2(25);

   vPaySiteFlag             VARCHAR2(1);

   vPurchasingSiteFlag        VARCHAR2(1);

   vVatCode                 VARCHAR2(20);

   vVatRegistrationNum         VARCHAR2(20);

   vTermsId                    NUMBER;

   vInvoiceCurrencyCode        VARCHAR2(15);

   vDistributionSetId        NUMBER;

   vAcctsPayCodeCombinationId       NUMBER;

   vPrepayCodeCombinationId           NUMBER;

   vOffsetVatCode            VARCHAR2(20);

   vPaymentMethodLookupCode    VARCHAR2(25);

   vPaymentCurrencyCode     VARCHAR2(15);

   vExclusivePaymentFlag    VARCHAR2(1);

   vAllowAwtFlag            VARCHAR2(1);

   vAWTGroupId                 NUMBER;

   vAttribute1                VARCHAR2(3);

   vAttribute2                VARCHAR2(10);

   vTaxReportingSiteFlag    VARCHAR2(1);

   vPayGroupLookupCode      VARCHAR2(25);

   vReactivateFlag            VARCHAR2(1);

   vUpdateVendorId          NUMBER;

   oAddressLine1            VARCHAR2(35);

   oAddressLine2            VARCHAR2(35);

   oAddressLine3            VARCHAR2(35);

   oAddressLine4            VARCHAR2(35);

   oCity                    VARCHAR2(25);

   oState                    VARCHAR2(25);

   oZip                        VARCHAR2(20);

   oProvince                VARCHAR2(25);

   oCountry                 VARCHAR2(25);

   oPaySiteFlag             VARCHAR2(1);

   oPurchasingSiteFlag        VARCHAR2(1);

   oVatCode                 VARCHAR2(20);

   oVatRegistrationNum         VARCHAR2(20);

   oTermsId                    NUMBER;

   oInvoiceCurrencyCode        VARCHAR2(15);

   oDistributionSetId        NUMBER;

   oAcctsPayCodeCombinationId       NUMBER;

   oPrepayCodeCombinationId           NUMBER;

   oOffsetVatCode            VARCHAR2(20);

   oPaymentMethodLookupCode    VARCHAR2(25);

   oPaymentCurrencyCode     VARCHAR2(15);

   oExclusivePaymentFlag    VARCHAR2(1);

   oAllowAwtFlag            VARCHAR2(1);

   oAWTGroupId                NUMBER;

   oAttribute1                VARCHAR2(3);

   oAttribute2                VARCHAR2(10);

   oTaxReportingSiteFlag    VARCHAR2(1);

   oPayGroupLookupCode      VARCHAR2(25);

      --Variable for new Fields Joe Lui SMF Phase III 1/27/05

   vFreightTerms                 VARCHAR2(25);

   vShipToLocationID         NUMBER;



   --Variables for Flex Value. Inserted by Cyrene V. Jacob

   vFlexValue               VARCHAR2(125);

   oFlexValue               VARCHAR2(125);



   vFax                         VARCHAR2(15);

   vFaxAreaCode                 VARCHAR2(10);

   oFreightTerms                 VARCHAR2(25);

   oShipToLocationID         NUMBER;

   oFax                         VARCHAR2(15);

   oFaxAreaCode                 VARCHAR2(10);



   vAttribute14                 VARCHAR2(25);

   oAttribute14                 VARCHAR2(25);

   v_gTol_Id          NUMBER;

   v_sTol_Id          NUMBER;

   v_base_ccy         VARCHAR2(25);



BEGIN

 /* get the current record from the source table */

   BEGIN

      SELECT vendor_site_id,

             accts_pay_code_combination_id,

             trim(address_line1),

             trim(address_line2),

             trim(address_line3),

             trim(address_line4),

             trim(city),

             trim(state),

             trim(zip),

             trim(province),

             trim(country),

             pay_site_flag,

             purchasing_site_flag,

             vat_code,

             vat_registration_num,

             terms_id,

             invoice_currency_code,

             distribution_set_id,

             accts_pay_code_combination_id,

             prepay_code_combination_id,

             offset_vat_code,

             payment_method_lookup_code,

             payment_currency_code,

             exclusive_payment_flag,

             allow_awt_flag,

             awt_group_id,

             attribute1,

             attribute2,

             tax_reporting_site_flag,

             pay_group_lookup_code,

             reactivate_flag,

             FREIGHT_TERMS_LOOKUP_CODE,

             SHIP_TO_LOCATION_ID,

             --

             --Flex Value added by Cyrene V. Jacob

            (select flex_value

              from fnd_flex_values

			        where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_Value_set_name = 'RDA_VENDOR_CATEGORY')

			        and flex_value_id = rsvs.ATTRIBUTE10),

              --

             FAX,

             FAX_AREA_CODE,

             ATTRIBUTE14

      INTO   vVendorSiteId,

             vAcctsPayCodeCombinationId,

             vAddressLine1,

             vAddressLine2,

             vAddressLine3,

             vAddressLine4,

             vCity,

             vState,

             vZip,

             vProvince,

             vCountry,

             vPaySiteFlag,

             vPurchasingSiteFlag,

             vVatCode,

             vVatRegistrationNum,

             vTermsId,

             vInvoiceCurrencyCode,

             vDistributionSetId,

             vAcctsPayCodeCombinationId,

             vPrepayCodeCombinationId,

             vOffsetVatCode,

             vPaymentMethodLookupCode,

             vPaymentCurrencyCode,

             vExclusivePaymentFlag,

             vAllowAwtFlag,

             vAWTGroupId,

             vAttribute1,

             vAttribute2,

             vTaxReportingSiteFlag,

             vPayGroupLookupCode,

             vReactivateFlag,

             vFreightTerms,              --new field

                vShipToLocationID,        --new field

                          --Flex Value field inserted by Cyrene V. Jacob

             vFlexValue,

                             vFax,                        --new field

                vFaxAreaCode    ,

             vAttribute14

      FROM   rda_suppmaint_vendor_sites rsvs

      WHERE  request_id = pnrequestid

         AND  rda_vsid   = pnrdavsid ;

   EXCEPTION

      WHEN OTHERS THEN

           vErrorCode := 'ERR601';

           Fnd_File.put_line (Fnd_File.LOG,'Reading Vendor site Values from SMF table '||SQLERRM);

   END;

/* get the existing record from the PO_VENDOR_SITES_ALL table */

IF vErrorCode = 'SUCCESS' THEN

   BEGIN

      SELECT accts_pay_code_combination_id,

             address_line1,

             address_line2,

             address_line3,

             address_line4,

             city,

             state,

             zip,

             province,

             country,

             pay_site_flag,

             purchasing_site_flag,

             vat_code,

             vat_registration_num,

             terms_id,

             invoice_currency_code,

             distribution_set_id,

             accts_pay_code_combination_id,

             prepay_code_combination_id,

             offset_vat_code,

             payment_method_lookup_code,

             payment_currency_code,

             exclusive_payment_flag,

             allow_awt_flag,

             awt_group_id,

             attribute1,

             attribute2,

             tax_reporting_site_flag,

             pay_group_lookup_code,

             FREIGHT_TERMS_LOOKUP_CODE,

             SHIP_TO_LOCATION_ID,

             --

             --flex value inserted by Cyrene V. Jacob

            ATTRIBUTE10 ,

             --

             FAX,

             FAX_AREA_CODE,

             ATTRIBUTE14

      INTO   oAcctsPayCodeCombinationId,

               oAddressLine1,

             oAddressLine2,

             oAddressLine3,

             oAddressLine4,

             oCity,

             oState,

             oZip,

             oProvince,

             oCountry,

             oPaySiteFlag,

             oPurchasingSiteFlag,

             oVatCode,

             oVatRegistrationNum,

             oTermsId,

             oInvoiceCurrencyCode,

             oDistributionSetId,

             oAcctsPayCodeCombinationId,

             oPrepayCodeCombinationId,

             oOffsetVatCode,

             oPaymentMethodLookupCode,

             oPaymentCurrencyCode,

             oExclusivePaymentFlag,

             oAllowAwtFlag,

             oAWTGroupId,

             oAttribute1,

             oAttribute2,

             oTaxReportingSiteFlag,

             oPayGroupLookupCode,

             oFreightTerms,              --new field

                oShipToLocationID,        --new field



             oFlexValue,            --flex value inserted by Cyrene V. Jacob



                oFax,                        --new field

                oFaxAreaCode        ,

             oAttribute14        --new field

      FROM   po_vendor_sites_all

      WHERE  vendor_site_id = vVendorSiteId;

   EXCEPTION

      WHEN OTHERS THEN

           vErrorCode := 'ERR602';

           Fnd_File.put_line (Fnd_File.LOG,'Reading PO_VENDOR_SITES_ALL table '||SQLERRM);

   END;

   END IF;

   Begin

     SELECT TOLERANCE_ID,SERVICES_TOLERANCE_ID, base_currency_code

      INTO v_gTol_id,v_sTol_id, v_base_ccy

       FROM AP_SYSTEM_PARAMETERS_ALL

        WHERE ORG_ID = PNORGID;

        EXCEPTION

          WHEN OTHERS THEN

             vErrorCode := 'ERR604';

			Fnd_File.put_line (Fnd_File.LOG,'Reading AP_SYSTEM_PARAMETERS_ALL table '||SQLERRM);

   End;

 --RB

  IF PNORGID in (205, 509)

     AND NVL(vInvoiceCurrencyCode,0) != NVL(v_base_ccy,0)

           THEN gvMatchOptionSite := 'R';

       ELSE gvMatchOptionSite := gvMatchOption;

		   END IF;

 /* update the data into PO_VENDOR_SITES_S */

   IF vErrorCode = 'SUCCESS' THEN

   BEGIN

      UPDATE po_vendor_sites_all

         SET accts_pay_code_combination_id = REPLACE(NVL(vAcctsPayCodeCombinationId,oAcctsPayCodeCombinationId),-999,NULL),

             address_line1 = REPLACE(NVL(vAddressLine1,oAddressLine1),'NULL',NULL),

             address_line2 = REPLACE(NVL(vAddressLine2,oAddressLine2),'NULL',NULL),

             address_line3 = REPLACE(NVL(vAddressLine3,oAddressLine3),'NULL',NULL),

             address_line4 = REPLACE(NVL(vAddressLine4,oAddressLine4),'NULL',NULL),

             allow_awt_flag = (NVL(vAllowAwtFlag,oAllowAwtFlag)),

             awt_group_id   = (NVL(vAWTGroupId,oAWTGroupId)),

             attribute1 = REPLACE(NVL(vAttribute1,oAttribute1),'NULL',NULL),

             attribute2 = (NVL(vAttribute2,oAttribute2)),

             city = REPLACE(NVL(vCity,oCity),'NULL',NULL),

             country = REPLACE(NVL(vCountry,oCountry),'NULL',NULL),

             distribution_set_id = REPLACE(NVL(vDistributionSetId,oDistributionSetId),-999,NULL),

             inactive_date = REPLACE(vReactivateFlag,'Y',NULL),

             exclusive_payment_flag = (NVL(vExclusivePaymentFlag,oExclusivePaymentFlag)) ,

             invoice_currency_code = REPLACE(NVL(vInvoiceCurrencyCode,oInvoiceCurrencyCode),'NULL',NULL) ,

             last_updated_by = pnUserId,

             last_update_date = SYSDATE,

             offset_vat_code = REPLACE(NVL(vOffsetVatCode,oOffsetVatCode),'NULL',NULL) ,

               payment_currency_code = REPLACE(NVL(vPaymentCurrencyCode,oPaymentCurrencyCode),'NULL',NULL) ,

             payment_method_lookup_code = REPLACE(NVL(vPaymentMethodLookupCode,oPaymentMethodLookupCode),'NULL',NULL) ,

             pay_group_lookup_code = REPLACE(NVL(vPayGroupLookupCode,oPayGroupLookupCode),'NULL',NULL),

             pay_site_flag = NVL(vPaySiteFlag,oPaySiteFlag) ,

             prepay_code_combination_id = REPLACE(NVL(vPrepayCodeCombinationId,oPrepayCodeCombinationId),-999,NULL) ,

             province = REPLACE(NVL(vProvince,oProvince),'NULL',NULL) ,

             purchasing_site_flag = NVL(vPurchasingSiteFlag,oPurchasingSiteFlag) ,

             state = REPLACE(NVL(vState,oState),'NULL',NULL) ,

             tax_reporting_site_flag = NVL(vTaxReportingSiteFlag,oTaxReportingSiteFlag) ,

             terms_id = REPLACE(NVL(vTermsId,oTermsId),-999,NULL) ,

             vat_code = REPLACE(NVL(vVatCode,oVatCode),'NULL',NULL) ,

             vat_registration_num = REPLACE(NVL(vVatRegistrationNum,oVatRegistrationNum),'NULL',NULL) ,

             zip = REPLACE(NVL(vZip,oZip),'NULL',NULL),

             match_option = gvMatchOptionSite,

             FREIGHT_TERMS_LOOKUP_CODE = REPLACE(NVL(vFreightTerms,oFreightTerms),'NULL',NULL),  --new field

             SHIP_TO_LOCATION_ID = REPLACE(NVL(vShipToLocationID,oShipToLocationID),-999,NULL) , --new field



             ATTRIBUTE10 = REPLACE(NVL(vFlexValue, oFlexValue), 'NULL', NULL), --flex value field inserted by Cyrene V. Jacob



             FAX = REPLACE(NVL(vFAX,oFAX),'NULL',NULL),                                                --new field

             FAX_AREA_CODE = REPLACE(NVL(vFaxAreaCode,oFaxAreaCode),'NULL',NULL)    ,

             ATTRIBUTE14 =     REPLACE(NVL(vAttribute14,oAttribute14),'NULL',NULL),         --new field

                         TOLERANCE_ID=v_gTol_id,                                     -- new field

                         SERVICES_TOLERANCE_ID=v_sTol_id                             -- new field

        WHERE vendor_site_id = vVendorSiteId;

      EXCEPTION

      WHEN OTHERS THEN

           vErrorCode := 'ERR603';

           Fnd_File.put_line (Fnd_File.LOG,'updating PO_VENDOR_SITES_ALL table '||SQLERRM);

      END;

    END IF;

/* update interface status and vendor id on the source table*/

   BEGIN

      UPDATE rda_suppmaint_vendor_sites

      SET    interface_status = vErrorCode,

             update_vendor_site_id = vVendorSiteId

      WHERE  request_id = PNREQUESTID

      AND     rda_vsid    = pnrdavsid

       AND   rda_vid   = pnrdavid;

   EXCEPTION

      WHEN OTHERS THEN

           vErrorCode := 'ERR604';

   END;

/* set the return value */

   IF vErrorCode = 'SUCCESS' THEN

      PNERRORFLAG := 'N';

   ELSE

      PNERRORFLAG := 'Y';

      PNERRCODE   := vErrorCode;

   END IF;

END SMF_WEBAPP_UPDATE_VENDOR_SITE  ;

Open in new window

0
 

Author Comment

by:dileepav
ID: 33483741
The first code that I have attached is for Inserting the value for the first time, the second one is for each updation.

thanks
Dil.
0
 

Author Comment

by:dileepav
ID: 33483744
I looked at the code, but I am unable to find out the reason, why it is not storing.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 33483754
I see no issue in the code, unless the queries that fill vFlexCode and/or oFlexCode don't give the correct values ...
0
 

Author Comment

by:dileepav
ID: 33483861
Also I am wondering why they have used = sign here  instead of :=, I think this is an assignment statement.            

ATTRIBUTE10 = REPLACE(NVL(vFlexValue, oFlexValue), 'NULL', NULL), --flex value field inserted by Cyrene V. Jacob

thanks
Dil.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 33483886
:= is to assign to a variable, the = is (which is your case) for a UPDATE
0
 

Author Closing Comment

by:dileepav
ID: 33609110
Thx Experts, the issue got resolved.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now