Sunday, May 18, 2014

AR Invoice Open Interface


AR Invoice Interface Package


CREATE OR REPLACE PACKAGE xx_ar_inv_pkg
IS
   PROCEDURE xx_ar_inv_main (
      v_error_buf     OUT      VARCHAR2,
      v_ret_code      OUT      NUMBER,
      p_load_option   IN       VARCHAR2
   );

   PROCEDURE xx_ar_inv_val_prc;

   PROCEDURE xx_ar_inv_std_pgm_prc;

   PROCEDURE xx_ar_inv_load_pgm_prc;

   FUNCTION xx_org_id_fun (p_operating_unit VARCHAR2)
      RETURN NUMBER;

   FUNCTION xx_batch_src_fun (p_batch_source_name VARCHAR2, p_org_id NUMBER)
      RETURN VARCHAR2;

   FUNCTION xx_line_type_fun (p_line_type VARCHAR2)
      RETURN VARCHAR2;

   FUNCTION xx_cust_trx_type_fun (p_cust_trx_type VARCHAR2, p_org_id NUMBER)
      RETURN NUMBER;

   FUNCTION xx_currency_fun (p_currency_code VARCHAR2)
      RETURN VARCHAR2;

   FUNCTION xx_term_fun (p_term_name VARCHAR2)
      RETURN NUMBER;

   FUNCTION xx_bill_cust_fun (p_bill_cust VARCHAR2)
      RETURN NUMBER;

   FUNCTION xx_bill_addr_fun (p_bill_addr VARCHAR2)
      RETURN NUMBER;

   FUNCTION xx_ship_cust_fun (p_ship_cust VARCHAR2)
      RETURN NUMBER;

   FUNCTION xx_ship_addr_fun (
      p_ship_addr      VARCHAR2,
      p_org_id         NUMBER,
      p_ship_cust_id   NUMBER
   )
      RETURN NUMBER;

   FUNCTION xx_sold_cust_fun (p_sold_cust VARCHAR2)
      RETURN NUMBER;

   FUNCTION xx_uom_fun (p_unit_of_measure VARCHAR2)
      RETURN VARCHAR2;

   FUNCTION xx_item_fun (p_item VARCHAR2, p_org_id NUMBER)
      RETURN NUMBER;

   FUNCTION xx_conv_type_fun (p_conversion_type VARCHAR2)
      RETURN VARCHAR2;

   FUNCTION xx_fob_fun (p_fob VARCHAR2)
      RETURN VARCHAR2;

   FUNCTION xx_code_combination_fun (p_code_combination VARCHAR2)
      RETURN NUMBER;

   v_process_flag          VARCHAR2 (1)                                := 'N';
   v_error_msg             VARCHAR2 (2000);
   v_org_id                hr_operating_units.organization_id%TYPE;
   v_batch_source_name     ra_batch_sources_all.NAME%TYPE;
   v_line_type             ar_lookups.lookup_code%TYPE;
   v_cust_trx_type_id      ra_cust_trx_types_all.cust_trx_type_id%TYPE;
   v_currency_code         fnd_currencies.currency_code%TYPE;
   v_term_id               ra_terms_tl.term_id%TYPE;
   v_bill_cust_id          hz_parties.party_id%TYPE;
   v_bill_addr_id          hz_cust_site_uses_all.cust_acct_site_id%TYPE;
   v_ship_cust_id          hz_parties.party_id%TYPE;
   v_ship_addr_id          hz_cust_site_uses_all.cust_acct_site_id%TYPE;
   v_sold_cust_id          hz_parties.party_id%TYPE;
   v_uom_code              mtl_units_of_measure_tl.uom_code%TYPE;
   v_inventory_item_id     mtl_system_items_b.inventory_item_id%TYPE;
   v_conversion_type       gl_daily_conversion_types.conversion_type%TYPE;
   v_fob                   fnd_lookup_values.lookup_code%TYPE;
   v_code_combination_id   gl_code_combinations_kfv.code_combination_id%TYPE;
   v_request_id            NUMBER;
   v_phase                 VARCHAR2 (50);
   v_status                VARCHAR2 (50);
   v_dev_phase             VARCHAR2 (50);
   v_dev_status            VARCHAR2 (50);
   v_message               VARCHAR2 (50);
   v_conc_prgm_id          NUMBER;
   v_conc_request_id       NUMBER;
   v_user_id               NUMBER;
   v_resp_id               NUMBER;
   v_resp_appl_id          NUMBER;
   v_boolean               BOOLEAN;
END xx_ar_inv_pkg;
/

/*************************************************************
 ----------------------Package Body--------------------------
*************************************************************/

CREATE OR REPLACE PACKAGE BODY xx_ar_inv_pkg
IS
   PROCEDURE xx_ar_inv_main (
      v_error_buf     OUT      VARCHAR2,
      v_ret_code      OUT      NUMBER,
      p_load_option   IN       VARCHAR2
   )
   IS
   BEGIN
      IF p_load_option = 'LOAD RECORDS'
      THEN
  /**** Procedure Call to Load Data in Staging Tables ***/
         xx_ar_inv_load_pgm_prc;
      ELSIF p_load_option = 'VALIDATE RECORDS'
      THEN
 /**** Procedure Call to Validate Data ***/
         xx_ar_inv_val_prc;
      ELSIF p_load_option = 'STANDARD PROGRAM'
      THEN
/**** Procedure Call to Submit Standard Program ***/
         xx_ar_inv_std_pgm_prc;
      ELSIF p_load_option = 'ALL'
      THEN
/**** Procedure Calls to Load Data, Validate Data and Submit Standar Program ***/
         xx_ar_inv_load_pgm_prc;
         xx_ar_inv_val_prc;
         xx_ar_inv_std_pgm_prc;
      END IF;
   END;
/*************************************************************
 --------------Data Validation Procedure --------------
*************************************************************/
   PROCEDURE xx_ar_inv_val_prc
   IS
      CURSOR xx_ra_inv_line_cur
      IS
         SELECT xril.ROWID, xril.*
           FROM xx_ra_inv_lines_stg xril
          WHERE xril.process_flag = 'N';

      CURSOR xx_ra_inv_dist_cur (p_inv_num NUMBER)
      IS
         SELECT xrid.ROWID, xrid.*
           FROM xx_ra_inv_dist_stg xrid
          WHERE xrid.process_flag = 'N' AND xrid.invoice_num = p_inv_num;
   BEGIN
      v_conc_prgm_id := fnd_global.conc_program_id;
      v_conc_request_id := fnd_global.conc_request_id;
      v_user_id := fnd_global.user_id;
      v_resp_id := fnd_global.resp_id;
      v_resp_appl_id := fnd_global.resp_appl_id;
      fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);

      FOR xx_ra_inv_line IN xx_ra_inv_line_cur
      LOOP
         fnd_file.put_line (fnd_file.LOG, 'Enter Into  xx_ra_inv_line Loop');
--==========================================================--
--                  Validation  Starts                                      ============================================================--

         ---------------  Operating Unit Validation Starts -----------------------------
         v_org_id := xx_org_id_fun (xx_ra_inv_line.operating_unit);
         ---------------  Operating Unit Validation Ends -----------------------------

         ---------------  Batch Source Name Validation Starts -----------------------------
         v_batch_source_name :=
                xx_batch_src_fun (xx_ra_inv_line.batch_source_name, v_org_id);
         ---------------  Batch Source Name Validation Ends -----------------------------

         ---------------  Line Type Validation Starts -----------------------------
         v_line_type := xx_line_type_fun (xx_ra_inv_line.line_type);
         ---------------  Line Type Validation Ends -----------------------------

         ---------------  Cust Trx Type Validation Starts -----------------------------
         v_cust_trx_type_id :=
            xx_cust_trx_type_fun (xx_ra_inv_line.cust_trx_type_name,
                                  v_org_id);
         ---------------  Cust Trx Type Validation Ends -----------------------------

         ---------------  Currency Validation Starts -----------------------------
         v_currency_code := xx_currency_fun (xx_ra_inv_line.currency_code);
         ---------------  Currency Validation Ends -----------------------------

         ---------------  Term Validation Starts -----------------------------
         v_term_id := xx_term_fun (xx_ra_inv_line.term_name);
         ---------------  Term Validation Ends -----------------------------

         ---------------  Bill Customer Validation Starts -----------------------------
         v_bill_cust_id :=
             xx_bill_cust_fun (xx_ra_inv_line.orig_system_bill_customer_name);
         ---------------  Bill Customer  Validation Ends -----------------------------

         ---------------  Bill Address Validation Starts -----------------------------
         v_bill_addr_id :=
                   xx_bill_addr_fun (xx_ra_inv_line.orig_system_bill_address);
         ---------------  Bill Address Validation Ends -----------------------------

         ---------------  Ship Customer Validation Starts -----------------------------
         v_ship_cust_id :=
             xx_ship_cust_fun (xx_ra_inv_line.orig_system_ship_customer_name);
         ---------------  Ship Customer Validation Ends -----------------------------

         ---------------  Ship Address Validation Starts -----------------------------
         v_ship_addr_id :=
            xx_ship_addr_fun (xx_ra_inv_line.orig_system_ship_address,
                              v_org_id,
                              v_ship_cust_id
                             );
         ---------------  Ship Address Validation Ends -----------------------------

         ---------------  Unit of Measure Validation Starts -----------------------------
         v_uom_code := xx_uom_fun (xx_ra_inv_line.unit_of_measure);
         ---------------  Unit of Measure Validation Ends -----------------------------

         ---------------  Ship Customer Validation Starts -----------------------------
         v_sold_cust_id :=
                   xx_sold_cust_fun (xx_ra_inv_line.orig_system_sold_customer);
         ---------------  Ship Customer Validation Ends -----------------------------

         ---------------  Item Validation Starts -----------------------------
         v_inventory_item_id :=
                         xx_item_fun (xx_ra_inv_line.inventory_item, v_org_id);
         ---------------  Item Validation Ends -----------------------------

         ---------------  Conversion Type Validation Starts -----------------------------
         v_conversion_type :=
                             xx_conv_type_fun (xx_ra_inv_line.conversion_type);
         ---------------  Conversion Type Validation Ends -----------------------------

         ---------------  FOB Validation Starts -----------------------------
         v_fob := xx_fob_fun (xx_ra_inv_line.fob_point);

         ---------------  FOB Validation Ends -----------------------------
         INSERT INTO ra_interface_lines_all
                     (interface_line_id, org_id,
                      batch_source_name, line_number,
                      line_type, cust_trx_type_id,
                      trx_date, gl_date,
                      currency_code, term_id, orig_system_bill_customer_id,
                      orig_system_bill_address_id,
                      orig_system_ship_customer_id,
                      orig_system_ship_address_id,
                      orig_system_sold_customer_id, sales_order,
                      inventory_item_id, uom_code,
                      quantity,
                      unit_selling_price,
                      amount, description,
                      conversion_type, conversion_rate,
                      interface_line_context,
                      interface_line_attribute1,
                      interface_line_attribute2, fob_point,
                      last_update_date, last_updated_by, creation_date,
                      created_by
                     )
              VALUES (ra_customer_trx_lines_s.NEXTVAL, v_org_id,
                      v_batch_source_name, xx_ra_inv_line.line_number,
                      v_line_type, v_cust_trx_type_id,
                      xx_ra_inv_line.trx_date, xx_ra_inv_line.gl_date,
                      v_currency_code, v_term_id, v_bill_cust_id,
                      v_bill_addr_id,
                      v_ship_cust_id,
                      v_ship_addr_id,
                      v_sold_cust_id, xx_ra_inv_line.sales_order,
                      v_inventory_item_id, v_uom_code,
                      xx_ra_inv_line.quantity,
                      xx_ra_inv_line.unit_selling_price,
                      xx_ra_inv_line.amount, xx_ra_inv_line.description,
                      v_conversion_type, xx_ra_inv_line.conversion_rate,
                      v_batch_source_name,
                      xx_ra_inv_line.interface_line_attribute1,
                      xx_ra_inv_line.interface_line_attribute2, v_fob,
                      SYSDATE, fnd_global.user_id, SYSDATE,
                      fnd_global.user_id
                     );

         FOR xx_ra_inv_dist IN xx_ra_inv_dist_cur (xx_ra_inv_line.invoice_num)
         LOOP
            fnd_file.put_line (fnd_file.LOG,
                               'Enter Into  xx_ra_inv_dist Loop'
                              );
            --==============VALIDATION STARTS =========================================

            ---------------  FOB Validation Starts -----------------------------
            v_code_combination_id :=
                     xx_code_combination_fun (xx_ra_inv_dist.code_combination);

            ---------------  FOB Validation Ends -----------------------------
            INSERT INTO ra_interface_distributions_all
                        (interface_line_id,
                         account_class, amount,
                         code_combination_id, PERCENT,
                         interface_line_context,
                         interface_line_attribute1,
                         interface_line_attribute2, org_id,
                         last_update_date, last_updated_by, creation_date,
                         created_by
                        )
                 VALUES (ra_customer_trx_lines_s.CURRVAL,
                         xx_ra_inv_dist.account_class, xx_ra_inv_dist.amount,
                         v_code_combination_id, xx_ra_inv_dist.PERCENT,
                         v_batch_source_name,
                         xx_ra_inv_dist.interface_line_attribute1,
                         xx_ra_inv_dist.interface_line_attribute2, v_org_id,
                         SYSDATE, fnd_global.user_id, SYSDATE,
                         fnd_global.user_id
                        );

            COMMIT;
         END LOOP;
      END LOOP;
   END;

   --=============================================================
--        AUTO INVOICE MASTER PROGRAM--STANDARD PROGRAM
--============================================================
   PROCEDURE xx_ar_inv_std_pgm_prc
   IS
   BEGIN
      --------------SUBMIT STANDARD CONCURRENT Program--------------
      fnd_file.put_line (fnd_file.LOG,
                         'submit the standard cocurrent program'
                        );
      v_request_id :=
         fnd_request.submit_request
            (application      => 'AR',
             program          => 'RAXMTR',
             description      => 'Interface your transactions to Oracle Receivables',
             start_time       => SYSDATE,
             sub_request      => FALSE,
             argument1        => 1,
             argument2        => 204,
             argument3        => 1714,
             argument4        => 'VISION BUILD',
             argument5        => SYSDATE,
             argument6        => NULL,
             argument7        => NULL,
             argument8        => NULL,
             argument9        => NULL,
             argument10       => NULL,
             argument11       => NULL,
             argument12       => NULL,
             argument13       => NULL,
             argument14       => NULL,
             argument15       => NULL,
             argument16       => NULL,
             argument17       => NULL,
             argument18       => NULL,
             argument19       => NULL,
             argument20       => NULL,
             argument21       => NULL,
             argument22       => NULL,
             argument23       => NULL,
             argument24       => NULL,
             argument25       => NULL,
             argument26       => 'Y',
             argument27       => NULL
            );
      COMMIT;
      v_boolean :=
         fnd_concurrent.wait_for_request (request_id      => v_request_id,
                                          INTERVAL        => 60,
                                          max_wait        => 0,
                                          phase           => v_phase,
                                          status          => v_status,
                                          dev_phase       => v_dev_phase,
                                          dev_status      => v_dev_status,
                                          MESSAGE         => v_message
                                         );
      fnd_file.put_line (fnd_file.LOG, 'PHASE          :' || v_phase);
      fnd_file.put_line (fnd_file.LOG, 'STATUS         :' || v_status);
      fnd_file.put_line (fnd_file.LOG, 'DEV_PHASE      :' || v_dev_phase);
      fnd_file.put_line (fnd_file.LOG, 'DEV STATUS     :' || v_dev_status);
      fnd_file.put_line (fnd_file.LOG, 'MESSAGE        :' || v_message);
      fnd_file.put_line
              (fnd_file.LOG,
                  'Request ID from Standard AR Auto Invoice Master Program : '
               || v_request_id
              );

      IF v_request_id > 0
      THEN
         fnd_file.put_line (fnd_file.LOG, v_request_id);
         fnd_file.put_line (fnd_file.LOG,
                            'In Submitting the Standard Concurrent Program'
                           );
      ELSE
         fnd_file.put_line
            (fnd_file.LOG,
             'In Submitting the Standard Concurrent Program not successfully'
            );
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line
                   (fnd_file.LOG,
                    'In others of submitting the Standard Concurrent Program'
                   );
   END;

/*************************************************************
 ---- Procedure for Data Loader through Control file ----
*************************************************************/

   PROCEDURE xx_ar_inv_load_pgm_prc
   IS
   BEGIN
      --------------SUBMIT STANDARD CONCURRENT Program--------------
      fnd_file.put_line (fnd_file.LOG,
                         'submit the standard cocurrent program'
                        );
      fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);

      BEGIN
         v_request_id :=
            fnd_request.submit_request
                         (application      => 'AR',
                          program          => 'XXARINVLINE',
                          description      => 'XX AR Invoice Line Loader Program',
                          start_time       => SYSDATE,
                          sub_request      => FALSE
                         );
         COMMIT;
         v_boolean :=
            fnd_concurrent.wait_for_request (request_id      => v_request_id,
                                             INTERVAL        => 60,
                                             max_wait        => 0,
                                             phase           => v_phase,
                                             status          => v_status,
                                             dev_phase       => v_dev_phase,
                                             dev_status      => v_dev_status,
                                             MESSAGE         => v_message
                                            );
         fnd_file.put_line (fnd_file.LOG, 'PHASE          :' || v_phase);
         fnd_file.put_line (fnd_file.LOG, 'STATUS         :' || v_status);
         fnd_file.put_line (fnd_file.LOG, 'DEV_PHASE      :' || v_dev_phase);
         fnd_file.put_line (fnd_file.LOG, 'DEV STATUS     :' || v_dev_status);
         fnd_file.put_line (fnd_file.LOG, 'MESSAGE        :' || v_message);
         fnd_file.put_line
            (fnd_file.LOG,
                'Request ID from Standard AR XX AR Invoice Line Loader Program : '
             || v_request_id
            );

         IF v_request_id > 0
         THEN
            fnd_file.put_line (fnd_file.LOG, v_request_id);
            fnd_file.put_line
                       (fnd_file.LOG,
                        'In Submitting the XX AR Invoice Line Loader Program'
                       );
         ELSE
            fnd_file.put_line
               (fnd_file.LOG,
                'In Submitting the XX AR Invoice Line Loader Program not successfully'
               );
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line
                   (fnd_file.LOG,
                    'In others of submitting the Standard Concurrent Program'
                   );
      END;

      BEGIN
         v_request_id :=
            fnd_request.submit_request
                         (application      => 'AR',
                          program          => 'XXARINVDIST',
                          description      => 'XX AR Invoice Dist Loader Program',
                          start_time       => SYSDATE,
                          sub_request      => FALSE
                         );
         COMMIT;
         v_boolean :=
            fnd_concurrent.wait_for_request (request_id      => v_request_id,
                                             INTERVAL        => 60,
                                             max_wait        => 0,
                                             phase           => v_phase,
                                             status          => v_status,
                                             dev_phase       => v_dev_phase,
                                             dev_status      => v_dev_status,
                                             MESSAGE         => v_message
                                            );
         fnd_file.put_line (fnd_file.LOG, 'PHASE          :' || v_phase);
         fnd_file.put_line (fnd_file.LOG, 'STATUS         :' || v_status);
         fnd_file.put_line (fnd_file.LOG, 'DEV_PHASE      :' || v_dev_phase);
         fnd_file.put_line (fnd_file.LOG, 'DEV STATUS     :' || v_dev_status);
         fnd_file.put_line (fnd_file.LOG, 'MESSAGE        :' || v_message);
         fnd_file.put_line
            (fnd_file.LOG,
                'Request ID from Standard AR XX AR Invoice Dist Loader Program : '
             || v_request_id
            );

         IF v_request_id > 0
         THEN
            fnd_file.put_line (fnd_file.LOG, v_request_id);
            fnd_file.put_line
                       (fnd_file.LOG,
                        'In Submitting the XX AR Invoice Dist Loader Program'
                       );
         ELSE
            fnd_file.put_line
               (fnd_file.LOG,
                'In Submitting the XX AR Invoice Dist Loader Program not successfully'
               );
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line
                   (fnd_file.LOG,
                    'In others of submitting the Standard Concurrent Program'
                   );
      END;
   END;

--=============================================================================
--                        LINES VALIDATION FUNCTIONS
--==============================================================================

   --==================== Operating unit Function Starts===========================
   FUNCTION xx_org_id_fun (p_operating_unit VARCHAR2)
      RETURN NUMBER
   IS
      v_organization_id   NUMBER;
   BEGIN
      SELECT organization_id
        INTO v_organization_id
        FROM hr_operating_units
       WHERE NAME = p_operating_unit;

      RETURN v_organization_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'NO DATA for Operating Unit:' || p_operating_unit
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'NO DATA for Operating Unit :'
            || p_operating_unit;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'TOO MANY ROWS for Operating Unit :'
                            || p_operating_unit
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS found for Operating Unit :'
            || p_operating_unit;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'In Others for Operating Unit:'
                            || p_operating_unit
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'In Others for Operating Unit :'
            || p_operating_unit;
   END xx_org_id_fun;

--==================== Operating unit Function Ends===========================

   --==================== Batch Source Name Function Starts========================
   FUNCTION xx_batch_src_fun (p_batch_source_name VARCHAR2, p_org_id NUMBER)
      RETURN VARCHAR2
   IS
   BEGIN
      SELECT NAME
        INTO v_batch_source_name
        FROM ra_batch_sources_all
       WHERE NAME = p_batch_source_name AND org_id = p_org_id;

      RETURN v_batch_source_name;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'NO DATA for Batch Source Name :'
                            || p_batch_source_name
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'NO DATA for Batch Source Name :'
            || p_batch_source_name;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'TOO MANY ROWS for Batch Source Name :'
                            || p_batch_source_name
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS for Batch Source Name :'
            || p_batch_source_name;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'In Others for Batch Source Name :'
                            || SQLERRM
                            || p_batch_source_name
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'In Others for Batch Source Name :'
            || p_batch_source_name;
   END xx_batch_src_fun;

--==================== Batch Source Name Function Ends=========================

   --==================== Line Type Function Starts========================
   FUNCTION xx_line_type_fun (p_line_type VARCHAR2)
      RETURN VARCHAR2
   IS
   BEGIN
      SELECT lookup_code
        INTO v_line_type
        FROM ar_lookups
       WHERE meaning = p_line_type AND lookup_type = 'AR_LINE_INVOICE';

      RETURN v_line_type;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'NO DATA for Line Type :' || p_line_type
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg || '  ' || 'NO DATA for Line Type :' || p_line_type;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'TOO MANY ROWS for Line Type :' || p_line_type
                           );
         v_process_flag := 'E';
         v_error_msg :=
            v_error_msg || '  ' || 'TOO MANY ROWS for Line Type :'
            || p_line_type;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'In Others for Line Type :' || p_line_type
                           );
         v_process_flag := 'E';
         v_error_msg :=
             v_error_msg || '  ' || 'In Others for Line Type :' || p_line_type;
   END xx_line_type_fun;

--==================== Line Type Function Ends=========================

   --==================== CUST_TRX_TYPE  Function Starts========================
   FUNCTION xx_cust_trx_type_fun (p_cust_trx_type VARCHAR2, p_org_id NUMBER)
      RETURN NUMBER
   IS
   BEGIN
      SELECT cust_trx_type_id
        INTO v_cust_trx_type_id
        FROM ra_cust_trx_types_all
       WHERE NAME = p_cust_trx_type AND org_id = p_org_id;

      RETURN v_cust_trx_type_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'NO DATA for p_CUST_TRX_TYPE :' || p_cust_trx_type
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'NO DATA for p_CUST_TRX_TYPE :'
            || p_cust_trx_type;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'TOO MANY ROWS for p_CUST_TRX_TYPE :'
                            || p_cust_trx_type
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS for p_CUST_TRX_TYPE :'
            || p_cust_trx_type;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'In Others for p_CUST_TRX_TYPE :'
                            || p_cust_trx_type
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'In Others for p_CUST_TRX_TYPE :'
            || p_cust_trx_type;
   END xx_cust_trx_type_fun;

--====================CUST_TRX_TYPE Function Ends=========================

   --==================== Currency Code Function Starts===========================
   FUNCTION xx_currency_fun (p_currency_code VARCHAR2)
      RETURN VARCHAR2
   IS
   BEGIN
      SELECT currency_code
        INTO v_currency_code
        FROM fnd_currencies
       WHERE currency_code = p_currency_code;

      RETURN v_currency_code;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'NO DATA for Line Type :' || p_currency_code
                           );
         v_process_flag := 'E';
         v_error_msg :=
            v_error_msg || '  ' || 'NO DATA for Line Type :'
            || p_currency_code;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'TOO MANY ROWS for Line Type :' || p_currency_code
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS for Line Type :'
            || p_currency_code;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'In Others for Line Type :' || p_currency_code
                           );
         v_process_flag := 'E';
         v_error_msg :=
            v_error_msg || '  ' || 'In Others for Line Type :'
            || p_currency_code;
   END xx_currency_fun;

--==================== Currency Code Function Ends===========================

   --==================== BILL CUSTOMER Function Starts===========================
   FUNCTION xx_bill_cust_fun (p_bill_cust VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      SELECT party_id
        INTO v_bill_cust_id
        FROM hz_parties
       WHERE party_name = p_bill_cust;

      RETURN v_bill_cust_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'NO DATA for p_bill_cust :' || p_bill_cust
                           );
         v_process_flag := 'E';
         v_error_msg :=
             v_error_msg || '  ' || 'NO DATA for p_bill_cust :' || p_bill_cust;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'TOO MANY ROWS for p_bill_cust:' || p_bill_cust
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS for p_bill_cust :'
            || p_bill_cust;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'In Others for p_bill_cust :' || p_bill_cust
                           );
         v_process_flag := 'E';
         v_error_msg :=
            v_error_msg || '  ' || 'In Others for p_bill_cust :'
            || p_bill_cust;
   END xx_bill_cust_fun;

--==================== BILL CUSTOMER Function Ends===========================

   --==================== BILL ADDRESS Function Starts===========================
   FUNCTION xx_bill_addr_fun (p_bill_addr VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      SELECT cust_acct_site_id
        INTO v_bill_addr_id
        FROM hz_cust_site_uses_all
       WHERE site_use_code = 'BILL_TO' AND LOCATION = p_bill_addr;

      RETURN v_bill_addr_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'NO DATA for P_BILL_ADDR :' || p_bill_addr
                           );
         v_process_flag := 'E';
         v_error_msg :=
             v_error_msg || '  ' || 'NO DATA for P_BILL_ADDR :' || p_bill_addr;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'TOO MANY ROWS for P_BILL_ADDR:' || p_bill_addr
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS for P_BILL_ADDR :'
            || p_bill_addr;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'In Others for P_BILL_ADDR :' || p_bill_addr
                           );
         v_process_flag := 'E';
         v_error_msg :=
            v_error_msg || '  ' || 'In Others for P_BILL_ADDR :'
            || p_bill_addr;
   END xx_bill_addr_fun;

--==================== BILL ADDRES Function Ends===========================

   --==================== SHIP CUSTOMER Function Starts===========================
   FUNCTION xx_ship_cust_fun (p_ship_cust VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      SELECT party_id
        INTO v_ship_cust_id
        FROM hz_parties
       WHERE party_name = p_ship_cust;

      RETURN v_ship_cust_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'NO DATA for p_ship_cust :' || p_ship_cust
                           );
         v_process_flag := 'E';
         v_error_msg :=
             v_error_msg || '  ' || 'NO DATA for p_ship_cust :' || p_ship_cust;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'TOO MANY ROWS for p_ship_cust:' || p_ship_cust
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS for p_ship_cust :'
            || p_ship_cust;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'In Others for p_ship_cust :' || p_ship_cust
                           );
         v_process_flag := 'E';
         v_error_msg :=
            v_error_msg || '  ' || 'In Others for p_ship_cust :'
            || p_ship_cust;
   END xx_ship_cust_fun;

--==================== SHIP  CUSTOMER Function Ends===========================

   --==================== SHIP ADDRESS Function Starts===========================
   FUNCTION xx_ship_addr_fun (
      p_ship_addr      VARCHAR2,
      p_org_id         NUMBER,
      p_ship_cust_id   NUMBER
   )
      RETURN NUMBER
   IS
   BEGIN
      SELECT hcas.cust_acct_site_id
        INTO v_ship_addr_id
        FROM hz_parties hp,
             hz_party_sites hps,
             hz_cust_accounts hca,
             hz_cust_acct_sites_all hcas,
             hz_cust_site_uses_all hcsu
       WHERE hp.party_id = hps.party_id
         AND hca.party_id = hp.party_id
         AND hca.cust_account_id = hcas.cust_account_id
         AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
         AND hps.party_site_id = hcas.party_site_id
         AND hcsu.site_use_code = 'SHIP_TO'
         AND hp.party_id = p_ship_cust_id
         AND hcas.org_id = p_org_id
         AND LOCATION = p_ship_addr;

      RETURN v_ship_addr_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'NO DATA for P_ship_ADDR :' || p_ship_addr
                           );
         v_process_flag := 'E';
         v_error_msg :=
             v_error_msg || '  ' || 'NO DATA for P_BILL_ADDR :' || p_ship_addr;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'TOO MANY ROWS for P_ship_ADDR:' || p_ship_addr
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS for P_ship_ADDR :'
            || p_ship_addr;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'In Others for P_ship_ADDR :' || p_ship_addr
                           );
         v_process_flag := 'E';
         v_error_msg :=
            v_error_msg || '  ' || 'In Others for P_ship_ADDR :'
            || p_ship_addr;
   END xx_ship_addr_fun;

--==================== SHIP ADDRES Function Ends===========================

   --==================== SOLD CUSTOMER Function Starts===========================
   FUNCTION xx_sold_cust_fun (p_sold_cust VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      SELECT party_id
        INTO v_sold_cust_id
        FROM hz_parties
       WHERE party_name = p_sold_cust;

      RETURN v_sold_cust_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'NO DATA for p_sold_cust :' || p_sold_cust
                           );
         v_process_flag := 'E';
         v_error_msg :=
             v_error_msg || '  ' || 'NO DATA for p_sold_cust :' || p_sold_cust;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'TOO MANY ROWS for p_bill_cust:' || p_sold_cust
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS for p_sold_cust :'
            || p_sold_cust;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'In Others for p_bill_cust :' || p_sold_cust
                           );
         v_process_flag := 'E';
         v_error_msg :=
            v_error_msg || '  ' || 'In Others for p_sold_cust :'
            || p_sold_cust;
   END xx_sold_cust_fun;

--==================== SOLD CUSTOMER Function Ends===========================

   --==================== UNIT_OF_MEASURE Function Starts===========================
   FUNCTION xx_uom_fun (p_unit_of_measure VARCHAR2)
      RETURN VARCHAR2
   IS
   BEGIN
      SELECT uom_code
        INTO v_uom_code
        FROM mtl_units_of_measure_tl
       WHERE unit_of_measure = p_unit_of_measure;

      RETURN v_uom_code;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'NO DATA for UNIT_OF_MEASURE :'
                            || p_unit_of_measure
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'NO DATA for UNIT_OF_MEASURE :'
            || p_unit_of_measure;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'TOO MANY ROWS for UNIT_OF_MEASURE :'
                            || p_unit_of_measure
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS for UNIT_OF_MEASURE :'
            || p_unit_of_measure;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'In Others for UNIT_OF_MEASURE :'
                            || p_unit_of_measure
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'In Others for UNIT_OF_MEASURE :'
            || p_unit_of_measure;
   END xx_uom_fun;

--==================== UNIT_OF_MEASURE Function Ends===========================

   --==================== Term Function Starts===========================
   FUNCTION xx_term_fun (p_term_name VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      SELECT term_id
        INTO v_term_id
        FROM ra_terms_tl
       WHERE NAME = p_term_name;

      RETURN v_term_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'NO DATA for p_term_name:' || p_term_name
                           );
         v_process_flag := 'E';
         v_error_msg :=
             v_error_msg || '  ' || 'NO DATA for p_term_name :' || p_term_name;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'TOO MANY ROWS for p_term_name :' || p_term_name
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS  for p_term_name :'
            || p_term_name;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'In Others for p_term_name:' || p_term_name
                           );
         v_process_flag := 'E';
         v_error_msg :=
            v_error_msg || '  ' || 'In Others for p_term_name :'
            || p_term_name;
   END xx_term_fun;

--==================== Term Function Ends===========================

   --==================== Item Function Starts===========================
   FUNCTION xx_item_fun (p_item VARCHAR2, p_org_id NUMBER)
      RETURN NUMBER
   IS
   BEGIN
      SELECT inventory_item_id
        INTO v_inventory_item_id
        FROM mtl_system_items_b
       WHERE segment1 = p_item AND organization_id = p_org_id;

      RETURN v_inventory_item_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG, 'NO DATA for p_item:' || p_item);
         v_process_flag := 'E';
         v_error_msg :=
                      v_error_msg || '  ' || 'NO DATA for p_item :' || p_item;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'TOO MANY ROWS for p_item :' || p_item
                           );
         v_process_flag := 'E';
         v_error_msg :=
                v_error_msg || '  ' || 'TOO MANY ROWS  for p_item :' || p_item;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'In Others for p_item:' || p_item);
         v_process_flag := 'E';
         v_error_msg :=
                    v_error_msg || '  ' || 'In Others for p_item :' || p_item;
   END xx_item_fun;

--==================== Item Function Ends===========================

   --==================== Conversion Type Function Starts===========================
   FUNCTION xx_conv_type_fun (p_conversion_type VARCHAR2)
      RETURN VARCHAR2
   IS
   BEGIN
      SELECT conversion_type
        INTO v_conversion_type
        FROM gl_daily_conversion_types
       WHERE conversion_type = p_conversion_type;

      RETURN v_conversion_type;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'NO DATA for p_conversion_type:'
                            || p_conversion_type
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'NO DATA for p_conversion_type :'
            || p_conversion_type;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'TOO MANY ROWS for p_conversion_type :'
                            || p_conversion_type
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS  for p_conversion_type :'
            || p_conversion_type;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'In Others for p_conversion_type:'
                            || p_conversion_type
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'In Others for p_conversion_type :'
            || p_conversion_type;
   END xx_conv_type_fun;

--==================== Conversion Type Function Ends===========================

   --==================== Fob  Function Starts===========================
   FUNCTION xx_fob_fun (p_fob VARCHAR2)
      RETURN VARCHAR2
   IS
   BEGIN
      SELECT lookup_code
        INTO v_fob
        FROM fnd_lookup_values
       WHERE lookup_type = 'FOB'
         AND meaning = p_fob
         AND view_application_id = 222;

      RETURN v_fob;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG, 'NO DATA for p_fob:' || p_fob);
         v_process_flag := 'E';
         v_error_msg := v_error_msg || '  ' || 'NO DATA for p_fob :' || p_fob;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'TOO MANY ROWS for p_fob :' || p_fob
                           );
         v_process_flag := 'E';
         v_error_msg :=
                  v_error_msg || '  ' || 'TOO MANY ROWS  for p_fob :' || p_fob;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'In Others for p_fob:' || p_fob);
         v_process_flag := 'E';
         v_error_msg :=
                      v_error_msg || '  ' || 'In Others for p_fob :' || p_fob;
   END xx_fob_fun;

--==================== Fob  Function Ends===========================

   --==============================================================================
--                    DISTRIBUTIONS VALIDATION FUNCTIONS
--==============================================================================

   --==================== CODE_COMBINATION  Function Starts===========================
   FUNCTION xx_code_combination_fun (p_code_combination VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      SELECT code_combination_id
        INTO v_code_combination_id
        FROM gl_code_combinations_kfv
       WHERE concatenated_segments = p_code_combination;

      RETURN v_code_combination_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'NO DATA for P_CODE_COMBINATION:'
                            || p_code_combination
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'NO DATA for P_CODE_COMBINATION :'
            || p_code_combination;
      WHEN TOO_MANY_ROWS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'TOO MANY ROWS for P_CODE_COMBINATION :'
                            || p_code_combination
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'TOO MANY ROWS  for P_CODE_COMBINATION :'
            || p_code_combination;
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'In Others for P_CODE_COMBINATION:'
                            || p_code_combination
                           );
         v_process_flag := 'E';
         v_error_msg :=
               v_error_msg
            || '  '
            || 'In Others for P_CODE_COMBINATION :'
            || p_code_combination;
   END xx_code_combination_fun;
--==================== CODE_COMBINATION  Function Ends===========================
END xx_ar_inv_pkg;









No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...