Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-08-20
13
Medium Priority
?
3,661 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1600 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 400 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
 

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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1600 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1600 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

963 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