Oracle Blanket Sales Agreement Loader API

CREATE OR REPLACE PACKAGE      XX_BSA_LOAD_PKG
IS
–=================
— Global Variables
–=================
   gn_request_id          NUMBER := apps.fnd_global.conc_request_id;
   gn_prog_appl_id        NUMBER := apps.fnd_global.prog_appl_id;
   gn_responsibility_id   NUMBER := apps.fnd_global.resp_id;
   gn_respappl_id         NUMBER := apps.fnd_global.resp_appl_id;
   gn_program_id          NUMBER := apps.fnd_global.conc_program_id;
   gn_user_id             NUMBER := apps.fnd_global.user_id;
   gn_login_id            NUMBER := apps.fnd_global.login_id;
   gn_business_group_id   NUMBER := apps.fnd_global.per_business_group_id;
   gd_sysdate             DATE   := SYSDATE;
   p_api_version          NUMBER   := 1.0;
   PROCEDURE main(x_errbuf OUT VARCHAR2,  x_retcode OUT VARCHAR2);

   PROCEDURE trim_data;
 
   PROCEDURE prevalidation;
 
   PROCEDURE load_bsa;

   PROCEDURE record_status;
 
   FUNCTION validate_line_dates(l_item  VARCHAR2,l_source_contract VARCHAR2) RETURN VARCHAR2;
 
END XX_BSA_LOAD_PKG;
/

CREATE OR REPLACE PACKAGE BODY XX_BSA_LOAD_PKG
IS
   gc_status                 VARCHAR2 (1);
   g_org_id                  NUMBER        := fnd_profile.VALUE (‘ORG_ID’);
   gn_bulk_limit             NUMBER;
   gc_ret_status             VARCHAR2 (1);
   gn_bulk_err               NUMBER;
   gn_conc_req_id   CONSTANT VARCHAR2 (10) := fnd_global.conc_request_id;

— +====================================================================================+
— |
— |
— +====================================================================================+
— | |
— | $Id: XX_BSA_LOAD_PKG 12/06/2018 |
— | |
— | |
— |Description : Program to create blanket sales agreement  |
— | |
— | |
— | |
— |Change History: |
— |————— |
— |Version Date Author Remarks |
— |——- ———- ———— ——————- |
— | 1.0 12/06/2018 Mohan G Program to create Blanket Sales Agreement
— +====================================================================================+

   PROCEDURE main (x_errbuf OUT VARCHAR2, x_retcode OUT VARCHAR2)
   IS
   BEGIN
      fnd_file.put_line
         (apps.fnd_file.LOG,
          ‘###########################################################################################’
         );
      fnd_file.put_line (apps.fnd_file.LOG, ‘ ‘);
      fnd_file.put_line (apps.fnd_file.LOG,
                         ‘    —– Main Program Started —–‘
                        );
      fnd_file.put_line (apps.fnd_file.LOG, ‘ ‘);
      XX_BSA_LOAD_PKG.trim_data;
      XX_BSA_LOAD_PKG.prevalidation;
      XX_BSA_LOAD_PKG.load_bsa;
      fnd_file.put_line (apps.fnd_file.LOG, ‘ ‘);
      fnd_file.put_line (apps.fnd_file.LOG,
                         ‘    —– Main Program Completed —–‘
                        );
      fnd_file.put_line (apps.fnd_file.LOG, ‘ ‘);
      INSERT INTO xx_om_bsa_stg_a  select * from xx_om_bsa_stg
                                          WHERE NVL(PROCESS_FLAG,’~’) <> ‘N’;
      DELETE FROM xx_om_bsa_stg WHERE NVL(PROCESS_FLAG,’~’) <> ‘N’;
      COMMIT;
      XX_BSA_LOAD_PKG.record_status;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, ‘Exception Main Program ‘);
         fnd_file.put_line (fnd_file.LOG,’Error info  for main program:  ‘ || SQLERRM);
   END main;

——————————————————————-
— Procedure to Validate and Create Blanket Sales agreement Records
——————————————————————-
   PROCEDURE load_bsa
   AS
      p_init_msg_list           VARCHAR2 (200)                     := NULL;
      p_commit                  VARCHAR2 (200)                     := NULL;
      p_validation_level        NUMBER                             := 0;
      l_msg                     VARCHAR2 (200)                     := NULL;
      lc_count                  NUMBER                             := 0;
      l_sold_to_org_id          NUMBER;
      l_order_type_id           NUMBER                             := 0;
      l_hdr_rec                 oe_blanket_pub.header_rec_type;
      l_hdr_val_rec             oe_blanket_pub.header_val_rec_type;
      l_line_tbl                oe_blanket_pub.line_tbl_type;
      l_line_val_tbl            oe_blanket_pub.line_val_tbl_type;
      l_line_rec                oe_blanket_pub.line_rec_type;
      l_line_val_rec            oe_blanket_pub.line_val_rec_type;
      l_control_rec             oe_blanket_pub.control_rec_type;
      n_line_counter            NUMBER                             := 0;
      l_ship_from_org_id        NUMBER                             := 0;
      l_inv_to_org_id           NUMBER                             := 0;
      l_ship_to_orgid           NUMBER                             := 0;
      l_inv_item_id             NUMBER                             := 0;
      l_line_ship_to_orgid      NUMBER                             := 0;
      l_line_inv_to_org_id      NUMBER                             := 0;
      l_uom                     VARCHAR2 (200)                     := NULL;
      l_line_ship_from_org_id   NUMBER                             := 0;
      l_invo_rule_id            NUMBER                             := 0;
      l_acct_rule_id            NUMBER                             := 0;
      — output variables
      x_line_tbl                oe_blanket_pub.line_tbl_type;
      x_header_rec              oe_blanket_pub.header_rec_type;
      x_msg_count               NUMBER;
      x_msg_data                VARCHAR2 (4000);
      x_return_status           VARCHAR2 (30);

      CURSOR lcu_bsa_stg_hdr
      IS
         SELECT   customer_number, customer_po, commencement_date,
                  expiration_date, month_processed, contract_end_date,
                  ship_to_location, data_centre, bill_to_location,
                  source_contract_no
             FROM xx_om_bsa_stg
            WHERE process_flag = ‘V’
         GROUP BY customer_number,
                  customer_po,
                  commencement_date,
                  expiration_date,
                  month_processed,
                  contract_end_date,
                  ship_to_location,
                  data_centre,
                  bill_to_location,
                  source_contract_no
         ORDER BY source_contract_no;

      CURSOR lcu_bsa_stg_lns (
         pcust_no        VARCHAR2,
         pcust_po        VARCHAR2,
         p_cdate         VARCHAR2,
         p_expdate       VARCHAR2,
         p_mproc         VARCHAR2,
         p_cend_date     VARCHAR2,
         p_shiptoloc     VARCHAR2,
         p_datacen       VARCHAR2,
         p_bill_to_loc   VARCHAR2,
         p_s_cont_no     VARCHAR2
      )
      IS
         SELECT   item, line_number, line_activ_date, line_exp_date,
                  line_shipto, line_data_centre, line_bill_to_loc, pue,
                  product_code, start_date, termination_date, dsp_flag,
                  quantity, price, min_order_qty, account_rule,
                  invoicing_rule, record_id,source_contract_no
             FROM xx_om_bsa_stg
            WHERE process_flag = ‘V’
              AND customer_number = pcust_no
              AND NVL (customer_po, ‘~’) = NVL (pcust_po, ‘~’)
              AND commencement_date = p_cdate
              AND NVL (expiration_date, TRUNC(SYSDATE)) = NVL (p_expdate, TRUNC(SYSDATE))
              AND month_processed = p_mproc
              AND contract_end_date = p_cend_date
              AND ship_to_location = p_shiptoloc
              AND data_centre = p_datacen
              AND bill_to_location = p_bill_to_loc
              AND source_contract_no = p_s_cont_no
         ORDER BY source_contract_no,line_number;
   BEGIN
      fnd_file.put_line (fnd_file.LOG,
                         ‘Blanket Sales Order Loader Started’
                        );
      fnd_file.put_line
         (fnd_file.LOG,
          ‘===============================================================================’
         );
      fnd_file.put_line
         (fnd_file.LOG,
          ‘———————–Apps Initialize Started————————-‘
         );
      fnd_global.apps_initialize (gn_user_id,
                                  gn_responsibility_id,
                                  gn_respappl_id
                                 );
      mo_global.init (‘ONT’);
      fnd_file.put_line
         (fnd_file.LOG,
          ‘———————–Apps Initialize Completed————————-‘
         );
      fnd_file.put_line
         (fnd_file.LOG,
          ‘===============================================================================’
         );

—————————————
— Header Loop Started
—————————————
      FOR lc_rec IN lcu_bsa_stg_hdr
      LOOP
         n_line_counter := 0;
         l_ship_from_org_id := 0;
         l_order_type_id := 0;
         l_inv_to_org_id := 0;
         l_ship_to_orgid := 0;
         l_sold_to_org_id := 0;
         fnd_file.put_line
            (fnd_file.LOG,
                ‘———————–Header Part Started for the source contract number————————-‘
             || lc_rec.source_contract_no
            );

         BEGIN
            SELECT organization_id
              INTO l_ship_from_org_id
              FROM org_organization_definitions
             WHERE organization_code = lc_rec.data_centre;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_ship_from_org_id := -1;
               fnd_file.put_line (fnd_file.LOG,
                                     ‘Invalid data centre or ware house.’
                                  || lc_rec.data_centre
                                 );
         END;

         BEGIN
            SELECT transaction_type_id
              INTO l_order_type_id
              FROM oe_transaction_types_tl
             WHERE 1 = 1 AND UPPER (NAME) = ‘AGREEMENT’
                   AND LANGUAGE = ‘US’;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_order_type_id := -1;
               fnd_file.put_line (fnd_file.LOG, ‘Invalid Order type.’);
         END;
         BEGIN
            SELECT csu.site_use_id
              INTO l_inv_to_org_id
              FROM apps.hz_cust_accounts_all cus,
                   apps.hz_cust_acct_sites_all cussite,
                   apps.hz_cust_site_uses_all csu
             WHERE cus.cust_account_id = cussite.cust_account_id
               AND csu.cust_acct_site_id = cussite.cust_acct_site_id
               AND account_number = lc_rec.customer_number
               AND csu.LOCATION = lc_rec.bill_to_location
               AND csu.site_use_code = ‘BILL_TO’;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_inv_to_org_id := -1;
               fnd_file.put_line (fnd_file.LOG,
                                     ‘Invalid invoice to org id.’
                                  || lc_rec.customer_number
                                 );
         END;

         BEGIN
            SELECT csu.site_use_id
              INTO l_ship_to_orgid
              FROM apps.hz_cust_accounts_all cus,
                   apps.hz_cust_acct_sites_all cussite,
                   apps.hz_cust_site_uses_all csu
             WHERE cus.cust_account_id = cussite.cust_account_id
               AND csu.cust_acct_site_id = cussite.cust_acct_site_id
               AND account_number = lc_rec.customer_number
               AND csu.LOCATION = lc_rec.ship_to_location
               AND csu.site_use_code = ‘SHIP_TO’;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_ship_to_orgid := -1;
               fnd_file.put_line (fnd_file.LOG,
                                     ‘Invalid ship to org id.’
                                  || lc_rec.customer_number
                                 );
         END;

         BEGIN
            SELECT cust_account_id
              INTO l_sold_to_org_id
              FROM hz_cust_accounts
             WHERE account_number = lc_rec.customer_number AND status = ‘A’;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_sold_to_org_id := -1;
               fnd_file.put_line (fnd_file.LOG,
                                     ‘Invalid sold to org id.’
                                  || lc_rec.customer_number
                                 );
         END;

         fnd_file.put_line (fnd_file.LOG,
                               ‘   l_ship_from_org_id–>’
                            || l_ship_from_org_id
                            || ‘   l_order_type_id–>’
                            || l_order_type_id
                            || ‘   l_inv_to_org_id–>’
                            || l_inv_to_org_id
                            || ‘   l_ship_to_orgid–>’
                            || l_ship_to_orgid
                            || ‘   l_sold_to_org_id–>’
                            || l_sold_to_org_id
                           );
         l_hdr_rec := oe_blanket_pub.g_miss_header_rec;
         l_hdr_val_rec := oe_blanket_pub.g_miss_header_val_rec;
         l_hdr_rec.operation := oe_globals.g_opr_create;
         l_hdr_rec.sold_to_org_id := l_sold_to_org_id;
         l_hdr_rec.order_type_id := l_order_type_id;
         l_hdr_rec.ship_from_org_id := l_ship_from_org_id;
         l_hdr_rec.cust_po_number := lc_rec.customer_po;
         l_hdr_rec.invoice_to_org_id := l_inv_to_org_id;
         l_hdr_rec.ship_to_org_id := l_ship_to_orgid;
         l_hdr_rec.CONTEXT := ‘XXXX’;
         l_hdr_rec.attribute1 := to_char(to_date(lc_rec.month_processed,’MON-YY’),’MON-YY’);
         l_hdr_rec.attribute2 :=
                   TO_CHAR (lc_rec.contract_end_date, ‘YYYY/MM/DD HH24:MI:SS’);
         l_hdr_rec.start_date_active := lc_rec.commencement_date;
         l_hdr_rec.end_date_active := lc_rec.expiration_date;
         l_hdr_rec.attribute3 :=lc_rec.source_contract_no;
—————————————
— Line Loop Started
—————————————
         l_line_rec := oe_blanket_pub.g_miss_blanket_line_rec;
         l_line_val_rec := oe_blanket_pub.g_miss_blanket_line_val_rec;
         l_line_tbl.delete;
         /* fnd_file.put_line (fnd_file.LOG,
                            ‘  l_line_rec Count is –>  ‘ ||   l_line_rec.COUNT
                           );
          fnd_file.put_line (fnd_file.LOG,
                            ‘  l_line_val_rec Count is –>  ‘ ||   l_line_val_rec.COUNT
                           );*/
         fnd_file.put_line (fnd_file.LOG,
                            ‘  l_line_tbl Count is –>  ‘ ||   l_line_tbl.COUNT
                           );

         FOR lc_rec_lines IN lcu_bsa_stg_lns (lc_rec.customer_number,
                                              lc_rec.customer_po,
                                              lc_rec.commencement_date,
                                              lc_rec.expiration_date,
                                              lc_rec.month_processed,
                                              lc_rec.contract_end_date,
                                              lc_rec.ship_to_location,
                                              lc_rec.data_centre,
                                              lc_rec.bill_to_location,
                                              lc_rec.source_contract_no
                                             )
         LOOP
            fnd_file.put_line
               (fnd_file.LOG,
                ‘———————–Line Started————————-‘
               );
           

———————————
— Getting the inventory item idlc
———————————
            BEGIN
               SELECT msib.inventory_item_id
                 INTO l_inv_item_id
                 FROM mtl_system_items_b msib,
                      org_organization_definitions ood
                WHERE msib.organization_id = ood.organization_id
                  AND segment1 = lc_rec_lines.item
                  AND ood.operating_unit = 82
                  AND ood.ORGANIZATION_CODE = lc_rec_lines.line_data_centre
                  AND msib.enabled_flag = ‘Y’;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_inv_item_id := -1;
                  fnd_file.put_line (fnd_file.LOG, ‘Invalid inventory Item ‘);
            END;

            fnd_file.put_line (fnd_file.LOG,
                               ‘ inventory Item –>  ‘ || l_inv_item_id
                              );

———————————
— Getting the Item UOM
———————————
            BEGIN
               SELECT msib.primary_uom_code
                 INTO l_uom
                 FROM mtl_system_items_b msib,
                      org_organization_definitions ood
                WHERE msib.organization_id = ood.organization_id
                  AND segment1 = lc_rec_lines.item
                  AND ood.operating_unit = 82
                  AND ood.ORGANIZATION_CODE = lc_rec_lines.line_data_centre
                  AND msib.enabled_flag = ‘Y’;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_uom := NULL;
                  fnd_file.put_line (fnd_file.LOG,
                                     ‘Invalid inventory Item UOM ‘
                                    );
            END;

    —————————————
—  Line level data centre
—————————————
            BEGIN
               SELECT organization_id
                 INTO l_line_ship_from_org_id
                 FROM org_organization_definitions
                WHERE organization_code = lc_rec_lines.line_data_centre;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_line_ship_from_org_id := -1;
                  fnd_file.put_line (fnd_file.LOG,
                                     ‘Invalid data centre or ware house.’
                                    );
            END;

———————————————–
—    Line level ship to
———————————————
            BEGIN
               SELECT csu.site_use_id
                 INTO l_line_ship_to_orgid
                 FROM apps.hz_cust_accounts_all cus,
                      apps.hz_cust_acct_sites_all cussite,
                      apps.hz_cust_site_uses_all csu
                WHERE cus.cust_account_id = cussite.cust_account_id
                  AND csu.cust_acct_site_id = cussite.cust_acct_site_id
                  AND account_number = lc_rec.customer_number
                  AND csu.LOCATION = lc_rec_lines.line_shipto
                  AND csu.site_use_code = ‘SHIP_TO’;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_line_ship_to_orgid := -1;
                  fnd_file.put_line (fnd_file.LOG, ‘Invalid ship to org id.’);
            END;

—————————————————————-
—              Line level Bill To
—————————————————————-
            BEGIN
               SELECT csu.site_use_id
                 INTO l_line_inv_to_org_id
                 FROM apps.hz_cust_accounts_all cus,
                      apps.hz_cust_acct_sites_all cussite,
                      apps.hz_cust_site_uses_all csu
                WHERE cus.cust_account_id = cussite.cust_account_id
                  AND csu.cust_acct_site_id = cussite.cust_acct_site_id
                  AND account_number = lc_rec.customer_number
                  AND csu.LOCATION = lc_rec_lines.line_bill_to_loc
                  AND csu.site_use_code = ‘BILL_TO’;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_line_inv_to_org_id := -1;
                  fnd_file.put_line (fnd_file.LOG,
                                     ‘Invalid invoice to org id.’
                                    );
            END;

            fnd_file.put_line (fnd_file.LOG,
                                  ‘   l_inv_item_id –> ‘
                               || l_inv_item_id
                               || ‘   l_line_ship_from_org_id –> ‘
                               || l_line_ship_from_org_id
                               || ‘   l_line_ship_to_orgid –> ‘
                               || l_line_ship_to_orgid
                               || ‘   l_line_inv_to_org_id –> ‘
                               || l_line_inv_to_org_id
                              );
            n_line_counter := n_line_counter + 1;
            l_line_tbl (n_line_counter) :=
                                        oe_blanket_pub.g_miss_blanket_line_rec;
            l_line_tbl (n_line_counter).operation := oe_globals.g_opr_create;
            l_line_tbl (n_line_counter).sold_to_org_id := l_sold_to_org_id;
            l_line_tbl (n_line_counter).inventory_item_id := l_inv_item_id;
            l_line_tbl (n_line_counter).ship_from_org_id :=
                                                       l_line_ship_from_org_id;
            l_line_tbl (n_line_counter).invoice_to_org_id :=
                                                          l_line_inv_to_org_id;
            l_line_tbl (n_line_counter).ship_to_org_id := l_line_ship_to_orgid;
            l_line_tbl (n_line_counter).item_identifier_type := ‘INT’;
            l_line_tbl (n_line_counter).order_quantity_uom := l_uom;
            l_line_tbl (n_line_counter).CONTEXT := ‘XXXX’;
            l_line_tbl (n_line_counter).start_date_active :=
                                                  lc_rec_lines.line_activ_date;
            l_line_tbl (n_line_counter).end_date_active :=
                                                    lc_rec_lines.line_exp_date;
           — l_line_tbl (n_line_counter).accounting_rule_id := l_acct_rule_id;
           — l_line_tbl (n_line_counter).invoicing_rule_id := l_invo_rule_id;
            l_line_tbl (n_line_counter).attribute1 := lc_rec_lines.pue;
            l_line_tbl (n_line_counter).attribute2 :=
                    TO_CHAR (lc_rec_lines.start_date, ‘YYYY/MM/DD HH24:MI:SS’);
            l_line_tbl (n_line_counter).attribute3 :=
               TO_CHAR (lc_rec_lines.termination_date,
                        ‘YYYY/MM/DD HH24:MI:SS’);
            l_line_tbl (n_line_counter).attribute4 := lc_rec_lines.dsp_flag;
            l_line_tbl (n_line_counter).attribute5 := lc_rec_lines.quantity;
            l_line_tbl (n_line_counter).attribute6 := TO_NUMBER(lc_rec_lines.price,’99999999999.999999′);
            l_line_tbl (n_line_counter).attribute7 :=
                                                    lc_rec_lines.min_order_qty;
            l_line_tbl (n_line_counter).attribute8 :=
                                                     lc_rec_lines.product_code;
            l_line_tbl (n_line_counter).attribute9 :=
                                                     lc_rec_lines.account_rule;
            l_line_val_tbl (n_line_counter) := l_line_val_rec;
         END LOOP;

         fnd_file.put_line (fnd_file.LOG,
                            ‘l_line_tbl Count is –>  ‘ || l_line_tbl.COUNT
                           );
         –DBMS_OUTPUT.put_line (‘Before calling Process Blanket API’);
         oe_msg_pub.initialize;
         fnd_file.put_line (fnd_file.LOG,
                            ‘Line Counter is –>  ‘ || n_line_counter
                           );

         IF n_line_counter > 0
         THEN
            fnd_file.put_line
               (fnd_file.LOG,
                ‘———————–API Calling————————-‘
               );

            BEGIN
               oe_blanket_pub.process_blanket
                                         (p_org_id                  => 82,
                                          p_operating_unit          => NULL,
                                          p_api_version_number      => p_api_version,
                                          x_return_status           => x_return_status,
                                          x_msg_count               => x_msg_count,
                                          x_msg_data                => x_msg_data,
                                          p_header_rec              => l_hdr_rec,
                                          p_header_val_rec          => l_hdr_val_rec,
                                          p_line_tbl                => l_line_tbl,
                                          p_line_val_tbl            => l_line_val_tbl,
                                          p_control_rec             => l_control_rec,
                                          x_header_rec              => x_header_rec,
                                          x_line_tbl                => x_line_tbl
                                         );
            EXCEPTION
               WHEN OTHERS
               THEN
                  fnd_file.put_line (fnd_file.LOG, ‘API Error :’ || SQLERRM);
            END;
         END IF;

         fnd_file.put_line (fnd_file.LOG, ‘API Completed -> ‘);

         IF NVL (x_return_status, ‘~’) <> ‘S’
         THEN
            fnd_file.put_line (fnd_file.LOG,
                               ‘Return status –> ‘ || x_return_status
                              );
            FOR lc_err IN 1 .. oe_msg_pub.count_msg
            LOOP
               l_msg :=
                  oe_msg_pub.get (p_msg_index      => lc_err,
                                  p_encoded        => fnd_api.g_false
                                 );
               fnd_file.put_line (fnd_file.LOG,
                                  ‘Error Message in Loop–> ‘ || l_msg
                                 );
            END LOOP;
               BEGIN
               UPDATE xx_om_bsa_stg
                  SET error_message = l_msg,
                      process_flag = x_return_status
                WHERE process_flag = ‘V’
                  AND source_contract_no = lc_rec.source_contract_no;
            END;
         END IF;

         IF NVL (x_return_status, ‘~’) = ‘S’
         THEN
            BEGIN
               UPDATE xx_om_bsa_stg
                  SET order_number = x_header_rec.order_number,
                      process_flag = x_return_status
                WHERE process_flag = ‘V’
                  AND source_contract_no = lc_rec.source_contract_no;
            END;

            fnd_file.put_line (fnd_file.LOG,
                               ‘Header ID :’ || x_header_rec.header_id
                              );
            fnd_file.put_line (fnd_file.LOG,
                               ‘Order number :’ || x_header_rec.order_number
                              );
         END IF;

         COMMIT;
         fnd_file.put_line (fnd_file.LOG,
                               ‘Exit In Header Loop for ->’
                            || lc_rec.source_contract_no
                           );
      END LOOP;
   END load_bsa;

——————————————-
—  Procedure to do the total Validation
——————————————-
   PROCEDURE prevalidation
   IS
      lc_verify_flag      VARCHAR2 (1)    := NULL;
      l_error_message     VARCHAR2 (4000) := NULL;
      l_cnt               NUMBER          := 0;
      ln_cust_acct_id     NUMBER          := 0;
      ln_party_id         NUMBER          := 0;
      lc_item_cnt         VARCHAR2 (20)   := NULL;
      l_ship_from_orgid   NUMBER          := 0;
      l_inv_to_orgid      NUMBER          := 0;
      l_ship_to_orgid     NUMBER          := 0;
      l_dspflag           VARCHAR2(20)    := NULL;
      l_inv_rule_id       NUMBER          := 0;
      l_acc_rule_id       NUMBER          := 0;
      l_mth_processed     VARCHAR2(20)    := NULL;
      l_dc                VARCHAR2(100)   := NULL;
      l_sl                VARCHAR2(200)   := NULL;
      l_bl                VARCHAR2(200)   := NULL;
      l_hdr_cnt           NUMBER          := 0;
   

      CURSOR lcu_bsa_stg
      IS
         SELECT   customer_number, customer_po, commencement_date,
                  expiration_date, month_processed, contract_end_date,
                  ship_to_location, data_centre, bill_to_location, item,
                  line_number, line_activ_date, line_exp_date, line_shipto,
                  line_data_centre, line_bill_to_loc, pue, product_code,
                  start_date, termination_date, dsp_flag, quantity, price,
                  min_order_qty, account_rule, invoicing_rule, record_id,
                  source_contract_no
             FROM xx_om_bsa_stg
            WHERE process_flag = ‘N’
         ORDER BY   source_contract_no;– record_id;
   BEGIN
      fnd_file.put_line
         (fnd_file.LOG,
          ‘###################################################################################’
         );
      fnd_file.put_line
                  (fnd_file.LOG,
                   ‘—————   Entering into Prevalidation ———–‘
                  );

—————————
–Validating the Customer details
—————————
      FOR lc_rec IN lcu_bsa_stg
      LOOP
         lc_verify_flag := ‘Y’;
         l_error_message := NULL;

         BEGIN
            SELECT cust_account_id, party_id
              INTO ln_cust_acct_id, ln_party_id
              FROM hz_cust_accounts
             WHERE account_number = lc_rec.customer_number AND status = ‘A’;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               ln_cust_acct_id := 0;
               l_error_message :=
                     l_error_message
                  || ‘ ~ Customer is not exist ‘
                  || ‘~’
                  || SQLERRM;
               lc_verify_flag := ‘N’;
            WHEN OTHERS
            THEN
               lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message || ‘ ~ Customer error’ || ‘~’ || SQLERRM;
         END;

—————————————-
— Data centre count validation
—————————————

    BEGIN
    l_dc := NULL;
        select distinct data_centre into l_dc
        FROM xx_om_bsa_stg
        WHERE process_flag = ‘N’
        AND customer_number = lc_rec.customer_number
        AND source_contract_no = lc_rec.source_contract_no;

  EXCEPTION WHEN TOO_MANY_ROWS THEN
                       lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message || ‘ ~ Multiple header level data centre for same customer ‘ || ‘~’ || lc_rec.customer_number;
    WHEN OTHERS THEN
                       lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message || ‘ ~ data centre validation error ‘ || ‘~’ || lc_rec.customer_number|| ‘ ~ ‘||SQLERRM;

    END;

—————————————-
— ship to location count validation
—————————————

    BEGIN
    l_sl := NULL;
        select distinct ship_to_location into l_sl
        FROM xx_om_bsa_stg
        WHERE process_flag = ‘N’
        AND customer_number = lc_rec.customer_number
        AND source_contract_no = lc_rec.source_contract_no;

  EXCEPTION WHEN TOO_MANY_ROWS THEN
                       lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message || ‘ ~ Multiple header level ship to location for same customer ‘ || ‘~’ || lc_rec.customer_number;
    WHEN OTHERS THEN
                       lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message || ‘ ~ ship to location validation error ‘ || ‘~’ || lc_rec.customer_number|| ‘ ~ ‘||SQLERRM;
     END;

—————————————-
— bill to location count validation
—————————————

    BEGIN
    l_bl := NULL;
        select distinct bill_to_location into l_bl
        FROM xx_om_bsa_stg
        WHERE process_flag = ‘N’
        AND customer_number = lc_rec.customer_number
        AND source_contract_no = lc_rec.source_contract_no;

  EXCEPTION WHEN TOO_MANY_ROWS THEN
                       lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message || ‘ ~ Multiple header level bill to location for same customer ‘ || ‘~’ || lc_rec.customer_number;
    WHEN OTHERS THEN
                       lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message || ‘ ~ bill to location validation error ‘ || ‘~’ || lc_rec.customer_number|| ‘ ~ ‘||SQLERRM;
     END;

—————————————-
— Header data count validation
—————————————

    BEGIN
      l_hdr_cnt := 0;
       SELECT COUNT(*) INTO l_hdr_cnt
       FROM (
        SELECT DISTINCT  customer_number, customer_po, commencement_date,
                  expiration_date, month_processed, contract_end_date,
                  ship_to_location, data_centre, bill_to_location,
                  source_contract_no
             FROM xx_om_bsa_stg
            WHERE process_flag = ‘N’
              AND customer_number = lc_rec.customer_number
              AND source_contract_no = lc_rec.source_contract_no);
  IF l_hdr_cnt > 1 THEN
               lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message || ‘ ~ Multiple header level data for same customer ‘ || ‘~’ || lc_rec.customer_number;
  END IF;
    EXCEPTION WHEN OTHERS THEN
                       lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message || ‘ ~ header level data validation error ‘ || ‘~’ || lc_rec.customer_number|| ‘ ~ ‘||SQLERRM;
     END;

—————————–
–Validating the Activation Date
—————————–
         IF lc_rec.commencement_date IS NULL
         THEN
            lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ Activation date is null – ‘
               || ‘-‘
               || lc_rec.customer_number;
            fnd_file.put_line (fnd_file.LOG,
                                  ‘ Activation date is null – ‘
                               || ‘-‘
                               || lc_rec.customer_number
                              );
         END IF;

———————————
–Validating the Month Processed
———————————
         IF lc_rec.month_processed IS NULL
         THEN
            lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~  Month Processed value is null – ‘
               || ‘-‘
               || lc_rec.customer_number;
            fnd_file.put_line (fnd_file.LOG,
                                  ‘ ~ Month Processed value is null – ‘
                               || ‘-‘
                               || lc_rec.customer_number
                              );
         END IF;

———————————
–Validating the Month Processed
———————————
         BEGIN
       
          SELECT to_char(to_date(lc_rec.MONTH_PROCESSED,’MON-YY’),’MON-YY’)
          INTO l_mth_processed
          FROM DUAL;
        EXCEPTION WHEN OTHERS  THEN
            lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~  Month Processed value is Invalid – ‘
               || ‘-‘
               || lc_rec.customer_number
               || ‘-‘
               ||lc_rec.MONTH_PROCESSED;
            fnd_file.put_line (fnd_file.LOG,
                                  ‘ ~ Month Processed value is Invalid – ‘
                               || ‘-‘
                               || lc_rec.customer_number
                               || ‘-‘
                               ||lc_rec.MONTH_PROCESSED
                              );
         END;

———————————–
–Validating the Contract End Date
———————————–
         IF lc_rec.contract_end_date IS NULL
         THEN
            lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~  Contract End Date is null – ‘
               || ‘-‘
               || lc_rec.customer_number;
            fnd_file.put_line (fnd_file.LOG,
                                  ‘ ~ Contract End Date is null – ‘
                               || ‘-‘
                               || lc_rec.customer_number
                              );
         END IF;

———————————–
–Validating the Ship to
———————————–
         IF lc_rec.ship_to_location IS NULL
         THEN
            lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~  Ship To Location is null – ‘
               || ‘-‘
               || lc_rec.customer_number;
            fnd_file.put_line (fnd_file.LOG,
                                  ‘ ~ Ship To Location is null – ‘
                               || ‘-‘
                               || lc_rec.customer_number
                              );
         END IF;

———————————–
–Validating the Bill to
———————————–
         IF lc_rec.bill_to_location IS NULL
         THEN
            lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~  Bill To Location is null – ‘
               || ‘-‘
               || lc_rec.customer_number;
            fnd_file.put_line (fnd_file.LOG,
                                  ‘ ~ Bill To Location is null – ‘
                               || ‘-‘
                               || lc_rec.customer_number
                              );
         END IF;

————————————
— Inventory Item Validation
————————————
         BEGIN
            SELECT DISTINCT ‘1’
                       INTO lc_item_cnt
                       FROM mtl_system_items_b msib,
                            org_organization_definitions ood
                      WHERE msib.organization_id = ood.organization_id
                        AND segment1 = lc_rec.item
                        AND ood.operating_unit = 82
                         AND ood.ORGANIZATION_CODE = lc_rec.data_centre
                        AND msib.enabled_flag = ‘Y’;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message || ‘ ~ Inventory item is not available ‘;
         END;

—————————–
–Validating the Activation date and expiration date
—————————–
  /*       IF     lc_rec.line_activ_date IS NOT NULL
            AND lc_rec.line_exp_date IS NOT NULL
         THEN
            IF TO_DATE (lc_rec.line_activ_date, ‘DD-MON-YYYY’) >
                                TO_DATE (lc_rec.line_exp_date, ‘DD-MON-YYYY’)
            THEN
               lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message
                  || ‘ ~ Expiration date should be greater than Activation date  ‘;
            END IF;
         END IF;

*/
—————————–
–Validating the expiration date
—————————–
         IF    lc_rec.line_exp_date IS NOT NULL
         THEN
            IF TO_DATE (lc_rec.line_exp_date, ‘DD-MON-YYYY’) > trunc(sysdate)
            THEN
               lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message
                  || ‘ ~ Expiration date should be greater than current date ‘;
            END IF;
         END IF;

—————————–
–Validating the Start Date
—————————–
         IF lc_rec.start_date IS NULL
         THEN
            lc_verify_flag := ‘N’;
            l_error_message :=
                              l_error_message || ‘ ~ Start Date is mandatory’;
         END IF;

 —————————–
–Validating the Start Date and Termination date
—————————–
         IF     lc_rec.start_date IS NOT NULL
            AND lc_rec.termination_date IS NOT NULL
         THEN
            IF TO_DATE (lc_rec.start_date, ‘DD-MON-YYYY’) >
                             TO_DATE (lc_rec.termination_date, ‘DD-MON-YYYY’)
            THEN
               lc_verify_flag := ‘N’;
               l_error_message :=
                     l_error_message
                  || ‘ ~ Start date should be lesser than termination date’;
            END IF;
         END IF;

 ——————————————–
–Validating for PUE to accept only numeric
———————————————
         IF lc_rec.pue IS NOT NULL
         THEN
            IF NVL (LENGTH (TRIM (TRANSLATE (lc_rec.pue, ‘ +-.0123456789’,
                                             ‘ ‘)
                                 )
                           ),
                    0
                   ) > 0
            THEN
               lc_verify_flag := ‘N’;
               l_error_message :=
                      l_error_message || ‘ ~ PUE value should be in numeric ‘;
            END IF;
         END IF;

——————————————–
  — Validation for ware house / data centre
——————————————–
         IF lc_rec.data_centre IS NOT NULL
         THEN
            BEGIN
               SELECT organization_id
                 INTO l_ship_from_orgid
                 FROM org_organization_definitions
                WHERE organization_code = lc_rec.data_centre;
            EXCEPTION
               WHEN OTHERS
               THEN
                  lc_verify_flag := ‘N’;
                  l_error_message :=
                             l_error_message || ‘ ~ data center is invalid  ‘;
                  fnd_file.put_line (fnd_file.LOG,
                                     ‘data centre –> ‘ || lc_rec.data_centre
                                    );
            END;
         END IF;

  ——————————————–
  — Validation for Dynamic Static Flag
——————————————–
         IF lc_rec.dsp_flag IS NULL
         THEN
            lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~ Dynamic Static Pro service Flag is null  ‘;
            fnd_file.put_line
                            (fnd_file.LOG,
                                ‘Dynamic Static Pro service Flag is null –> ‘
                             || lc_rec.dsp_flag
                            );
         END IF;

  ——————————————–
  — Validation for Dynamic Static Flag
———————————————-

IF lc_rec.dsp_flag IS NOT NULL THEN

   BEGIN
   select CATEGORY_CONCAT_SEGS
      into l_dspflag
      from MTL_ITEM_CATEGORIES_V  micv, mtl_system_items_b msib,
           org_organization_definitions ood
    WHERE micv.inventory_item_id = msib.inventory_item_id
    and msib.organization_id = micv.organization_id
    and msib.organization_id = ood.organization_id
    and ood.ORGANIZATION_CODE = lc_rec.data_centre
    and micv.CATEGORY_SET_NAME = ‘Type Category Set’
    and msib.SEGMENT1 = lc_rec.item;
 
    EXCEPTION WHEN OTHERS THEN
       l_dspflag := ‘~’;
    END;
         IF lc_rec.dsp_flag <> l_dspflag
         THEN
            lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~ Dynamic Static Pro service Flag is Invalid  ‘;
            fnd_file.put_line
                         (fnd_file.LOG,
                             ‘Dynamic Static Pro service Flag is Invalid –> ‘
                          || lc_rec.dsp_flag||’ ~ ‘||lc_rec.item
                         );
         END IF;
      END IF;

——————————————–
  — Validation for Quantity
———————————————-

/*IF lc_rec.dsp_flag = ‘D’ AND lc_rec.quantity > 0  THEN

     lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~ For Dynamic, Quantity should be null  ‘;
            fnd_file.put_line
                         (fnd_file.LOG,
                             ‘For Dynamic, Quantity should be null –> ‘
                          || lc_rec.dsp_flag||’ ~ ‘||lc_rec.quantity||’ ~ ‘||lc_rec.item
                         );
         END IF;*/

——————————————————————-
  — Validation for line Item level active date and expiration date
——————————————————————-

IF validate_line_dates(lc_rec.item,lc_rec.source_contract_no) = ‘INVALID’ THEN
     lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~ Line Item level activation and expiration date is not in order ‘;
            fnd_file.put_line
                         (fnd_file.LOG,
                             ‘Line Item level activation and expiration date is not in order  –> ‘
                          || lc_rec.customer_number||’ ~ ‘||lc_rec.item
                         );
END IF;

————————————————
  — Validation for Price and minimum order qty
————————————————

/*IF lc_rec.price is not null AND lc_rec.min_order_qty IS NOT NULL THEN

     lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~ Value should not given for Both price and minimum order qty ‘;
            fnd_file.put_line
                         (fnd_file.LOG,
                             ‘Value should not given for Both price and minimum order qty  –> ‘
                          || lc_rec.customer_number||’ ~ ‘||lc_rec.min_order_qty||’ ~ ‘||lc_rec.price
                         );
         END IF;*/
       
————————————-
  — Validation for Invoicing  rules
————————————-
/*
IF lc_rec.invoicing_rule IS NOT NULL THEN
  BEGIN
  SELECT rule_id
    INTO l_inv_rule_id
    FROM ra_rules
   WHERE type = ‘I’
     AND NAME = lc_rec.invoicing_rule;
   EXCEPTION WHEN OTHERS THEN
        lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~ Invoicing rule name is invalid  ‘;
            fnd_file.put_line
                         (fnd_file.LOG,
                             ‘Invoicing rule name is invalid    –> ‘
                          || lc_rec.customer_number||’ ~ ‘||lc_rec.invoicing_rule
                         );
     END;
 END IF;
*/
————————————-
  — Validation for Accounting  rules
————————————-
IF lc_rec.account_rule IS  NULL THEN
        lc_verify_flag := ‘N’;
            l_error_message :=
                  l_error_message
               || ‘ ~ Accounting rule  is null  ‘;
            fnd_file.put_line
                         (fnd_file.LOG,
                             ‘Accounting rule is null    –> ‘
                          || lc_rec.customer_number||’ ~ ‘||lc_rec.account_rule
                         );
 END IF;

——————————————–
  — Validation for Bill to Location
——————————————–
         IF lc_rec.bill_to_location IS NOT NULL
         THEN
            BEGIN
               SELECT csu.site_use_id
                 INTO l_inv_to_orgid
                 FROM apps.hz_cust_accounts_all cus,
                      apps.hz_cust_acct_sites_all cussite,
                      apps.hz_cust_site_uses_all csu
                WHERE cus.cust_account_id = cussite.cust_account_id
                  AND csu.cust_acct_site_id = cussite.cust_acct_site_id
                  AND account_number = lc_rec.customer_number
                  AND csu.LOCATION = lc_rec.bill_to_location
                  AND csu.site_use_code = ‘BILL_TO’;
            EXCEPTION
               WHEN OTHERS
               THEN
                  lc_verify_flag := ‘N’;
                  l_error_message :=
                       l_error_message || ‘ ~ Bill to location is invalid.  ‘;
                  fnd_file.put_line (fnd_file.LOG,
                                        ‘Bill to Location  –> ‘
                                     || lc_rec.bill_to_location
                                    );
            END;
         END IF;

——————————————–
  — Validation for ship to Location
——————————————–
         IF lc_rec.ship_to_location IS NOT NULL
         THEN
            BEGIN
               SELECT csu.site_use_id
                 INTO l_ship_to_orgid
                 FROM apps.hz_cust_accounts_all cus,
                      apps.hz_cust_acct_sites_all cussite,
                      apps.hz_cust_site_uses_all csu
                WHERE cus.cust_account_id = cussite.cust_account_id
                  AND csu.cust_acct_site_id = cussite.cust_acct_site_id
                  AND account_number = lc_rec.customer_number
                  AND csu.LOCATION = lc_rec.ship_to_location
                  AND csu.site_use_code = ‘SHIP_TO’;
            EXCEPTION
               WHEN OTHERS
               THEN
                  lc_verify_flag := ‘N’;
                  l_error_message :=
                       l_error_message || ‘ ~ Ship to location is invalid.  ‘;
                  fnd_file.put_line (fnd_file.LOG,
                                        ‘Ship to Location  –> ‘
                                     || lc_rec.ship_to_location
                                    );
            END;
         END IF;

         IF lc_verify_flag = ‘N’
         THEN
            UPDATE xx_om_bsa_stg
               SET error_message = l_error_message,
                   process_flag = ‘VE’                     — Validation Error
             WHERE record_id = lc_rec.record_id
               AND process_flag = ‘N’
               AND request_id = gn_conc_req_id
               AND source_contract_no = lc_rec.source_contract_no;

            fnd_file.put_line (fnd_file.LOG,
                                  ‘ Validation error records –>  ‘
                               || ‘~’
                               || lc_rec.customer_number
                              );
         ELSE
            UPDATE xx_om_bsa_stg
               SET error_message = l_error_message,
                   process_flag = ‘V’                             — Validated
             WHERE record_id = lc_rec.record_id
               AND process_flag = ‘N’
               AND request_id = gn_conc_req_id 
               AND source_contract_no = lc_rec.source_contract_no;

            fnd_file.put_line
                        (fnd_file.LOG,
                            ‘Validation Success records customer number –>  ‘
                         || lc_rec.customer_number
                        );
         END IF;
      END LOOP;

      fnd_file.put_line (fnd_file.LOG,
                         ‘—————   Prevalidation ended ———–‘
                        );
      fnd_file.put_line
         (fnd_file.LOG,
          ‘###################################################################################’
         );

————————————————-
— Updating the Dependent Supplier Records
— if any 1 record has Validation error Mark all
— depedent records as Error
————————————————-
      UPDATE xx_om_bsa_stg stg
         SET error_message =
                ‘DEPENDENCY ERROR: Blanket Sales Agreement Validations Failed ‘,
             process_flag = ‘VE’                           — Validation Error
       WHERE NVL (process_flag, ‘X’) <> ‘VE’
         AND request_id = gn_conc_req_id
         AND EXISTS (
                SELECT ‘1’
                  FROM xx_om_bsa_stg stg1
                 WHERE 1 = 1
                 AND stg1.request_id = stg.request_id
                   AND NVL (stg1.process_flag, ‘X’) = ‘VE’
                   AND stg.source_contract_no = stg1.source_contract_no);

      COMMIT;
   END prevalidation;

   PROCEDURE trim_data
   IS
   BEGIN
————————————–
—  Update to remove unwanted spaces
————————————-
      fnd_file.put_line
         (fnd_file.LOG,
          ‘###################################################################################’
         );
      fnd_file.put_line (fnd_file.LOG,
                         ‘—————   Entering Trim Data———–‘
                        );

      UPDATE xx_om_bsa_stg
         SET customer_number = LTRIM (RTRIM (customer_number)),
             customer_po = LTRIM (RTRIM (customer_po)),
             commencement_date = LTRIM (RTRIM (commencement_date)),
             expiration_date = LTRIM (RTRIM (expiration_date)),
             month_processed = UPPER(LTRIM (RTRIM (month_processed))),
             contract_end_date = LTRIM (RTRIM (contract_end_date)),
             ship_to_location = LTRIM (RTRIM (ship_to_location)),
             data_centre = LTRIM (RTRIM (data_centre)),
             bill_to_location = LTRIM (RTRIM (bill_to_location)),
             item = LTRIM (RTRIM (item)),
             line_number = LTRIM (RTRIM (line_number)),
             line_activ_date = LTRIM (RTRIM (line_activ_date)),
             line_exp_date = LTRIM (RTRIM (line_exp_date)),
             line_shipto = LTRIM (RTRIM (line_shipto)),
             line_data_centre = LTRIM (RTRIM (line_data_centre)),
             line_bill_to_loc = LTRIM (RTRIM (line_bill_to_loc)),
             pue = LTRIM (RTRIM (pue)),
             product_code = LTRIM (RTRIM (product_code)),
             start_date = LTRIM (RTRIM (start_date)),
             termination_date = LTRIM (RTRIM (termination_date)),
             dsp_flag = LTRIM (RTRIM (dsp_flag)),
             quantity = LTRIM (RTRIM (quantity)),
             price = LTRIM (RTRIM (replace(price,’-‘,NULL))),
             min_order_qty = LTRIM (RTRIM (min_order_qty)),
             account_rule = LTRIM (RTRIM (account_rule)),
             invoicing_rule = ltrim(rtrim(replace(INVOICING_RULE,chr(13),”))),
             request_id = gn_conc_req_id,
             created_by = gn_user_id,
             last_updated_by = gn_user_id
       WHERE NVL (process_flag, ‘N’) = ‘N’;

      fnd_file.put_line (fnd_file.LOG,
                         ‘—————    Trim Data Completed  ———–‘
                        );
      fnd_file.put_line
         (fnd_file.LOG,
          ‘###################################################################################’
         );
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line
                          (fnd_file.LOG,
                              ‘Error Updating the NEW RECORDS – TRIM DATA : ‘
                           || SQLCODE
                           || ‘ – ‘
                           || SQLERRM
                          );
   END trim_data;
 
   PROCEDURE record_status
    AS

            l_total_cnt             NUMBER;
            l_bsa_success_cnt       NUMBER;
            l_bsa_error_cnt         NUMBER;
            l_order_num             VARCHAR2(20); 
            lc_hdr_message          VARCHAR2(255) := NULL;
            lc_err_cnt              NUMBER := 0;
         
     CURSOR lcu_err_rec
     IS
            SELECT DISTINCT source_contract_no,customer_number, error_message, request_id
              FROM xx_om_bsa_stg_a
             WHERE request_id = gn_conc_req_id
               AND PROCESS_FLAG LIKE  ‘%E%’; 
                 
     CURSOR lcu_succ_rec
     IS
            SELECT DISTINCT REQUEST_ID, RPAD(Substr(source_contract_no,1,38),40)||
                   RPAD(Substr(customer_number,1,38),40)||
                   RPAD(Substr(order_number,1,38),40)||
                   RPAD(Decode(NVL(PROCESS_FLAG,’X’),’S’,’Success ‘,’NA’,’NA’,’No’),8)   Succ_Message
              FROM xx_om_bsa_stg_a
             WHERE request_id = gn_conc_req_id
               AND PROCESS_FLAG in (‘S’,’IE’);
                                     
   BEGIN
            ————————————————————-
            — UPdating All the Success Records with Process Flag = ‘S’
            ————————————————————-
 
            UPDATE xx_om_bsa_stg_a
                SET PROCESS_FLAG = ‘S’
              WHERE REQUEST_ID =  GN_CONC_REQ_ID
                AND PROCESS_FLAG like ‘S%’;
             
             COMMIT;
 
              SELECT COUNT(DISTINCT customer_number)
                INTO l_total_cnt
                FROM xx_om_bsa_stg_a
               WHERE request_id = gn_conc_req_id;
             
               SELECT COUNT(DISTINCT customer_number)
                INTO l_bsa_success_cnt
               FROM xx_om_bsa_stg_a
               WHERE request_id = gn_conc_req_id
               AND process_flag = ‘S’;
             
              SELECT COUNT(DISTINCT customer_number)
                INTO l_bsa_error_cnt
               FROM xx_om_bsa_stg_a
               WHERE request_id = gn_conc_req_id
               AND process_flag LIKE ‘%E%’;
             
               fnd_file.put_line(fnd_file.output,’Total Number of records ‘ || l_total_cnt);
               fnd_file.put_line(fnd_file.output,’====================================================================’); 
               fnd_file.put_line(fnd_file.output,’================ Success Records ===================================’); 
               fnd_file.put_line(fnd_file.output,’====================================================================’);
               fnd_file.put_line(fnd_file.output,’Total Number of BSA success records ‘ || l_bsa_success_cnt);
               fnd_file.put_line(fnd_file.output,’====================================================================’); 
               fnd_file.put_line(fnd_file.output,’================ Error Records ===================================’); 
               fnd_file.put_line(fnd_file.output,’====================================================================’);               
               fnd_file.put_line(fnd_file.output,’Total Number of BSA error records ‘ || l_bsa_error_cnt);
               fnd_file.put_line(fnd_file.output,’====================================================================’); 
               fnd_file.put_line(fnd_file.output,’ ‘);
               fnd_file.put_line(fnd_file.output,’ ‘);
               fnd_file.put_line(fnd_file.output,’ ‘);
               fnd_file.put_line(fnd_file.output,’================ Error output =======================================’); 
               fnd_file.put_line(fnd_file.output,’====================================================================’); 

          —————————–
          — Printing Error Records
          —————————– 
          lc_err_cnt := 0;

          FOR lcu_rec IN lcu_err_rec
          LOOP
               lc_err_cnt := lc_err_cnt + 1;
               IF lc_err_cnt = 1
               THEN
                   fnd_file.put_line(fnd_file.output,’ source_contract_no      ‘ || ‘ customer_number           ‘ ||’  Error Message  ‘);
                   fnd_file.put_line(fnd_file.output,’====================================================================’);               
               END IF;
               fnd_file.put_line(fnd_file.output,lcu_rec.source_contract_no||’                   ‘ || SUBSTR(lcu_rec.customer_number,1,18)||’           ‘ ||Substr(lcu_rec.error_message,1,200) );
          END LOOP;
       
          lc_hdr_message := RPAD(‘Source Contract Number’,40)||RPAD(‘ Customer Number ‘,25)||
                            RPAD(‘ Order Number ‘,25);
                         
          —————————–
          — Printing Success Records
          —————————–
         fnd_file.put_line(fnd_file.output,’ ‘); 
         fnd_file.put_line(fnd_file.output,’ ‘); 
         fnd_file.put_line(fnd_file.output,’………Printing Success / Partial Success Records……..’);
         fnd_file.put_line(fnd_file.output,’ ‘);       
         fnd_file.put_line(fnd_file.output,lc_hdr_message);
         fnd_file.put_line(fnd_file.output,RPAD(‘ ‘,200,’=’) );
       —  fnd_file.put_line(fnd_file.output,’========================================================================================================’); 

          FOR lcu_succ in lcu_succ_rec
          LOOP                       
            fnd_file.put_line(fnd_file.output,lcu_succ.Succ_message);
          END LOOP;
         fnd_file.put_line(fnd_file.output,RPAD(‘ ‘,200,’=’) );
      —    fnd_file.put_line(fnd_file.output,’========================================================================================================’);
   END;

   FUNCTION validate_line_dates(l_item  VARCHAR2,l_source_contract VARCHAR2 ) RETURN VARCHAR2
   IS
   l_flag NUMBER := 0;
   BEGIN
   select NVL(SUM(DISTINCT fla),0) into l_flag from (
   select T.*,CASE WHEN ran = 1 AND LINE_ACTIV_DATE <= NVL(LINE_EXP_DATE,SYSDATE) THEN 1
                    WHEN RAN >1  AND LINE_ACTIV_DATE <= NVL(LINE_EXP_DATE,SYSDATE) AND LINE_ACTIV_DATE > LAG_EXP_DATE  AND  NVL(LINE_EXP_DATE,LINE_ACTIV_DATE+1) > NVL(lag_ac_date,SYSDATE)  THEN 1
                    ELSE 2 END AS FLA from (
                select item,LINE_ACTIV_DATE,LINE_EXP_DATE,lead(item,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lead_item,
                      lag(item,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lag_item,
                      lead(LINE_ACTIV_DATE,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lead_ac_date,
                      lag(LINE_ACTIV_DATE,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lag_ac_date,
                      lead(LINE_EXP_DATE,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lead_exp_date,
                      lag(LINE_EXP_DATE,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lag_exp_date,
                      ROW_NUMBER() OVER (PARTITION BY item ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) ran
                      from xx_om_bsa_stg
                      where (SOURCE_CONTRACT_NO, CUSTOMER_NUMBER,item) in (
                      select SOURCE_CONTRACT_NO, CUSTOMER_NUMBER,item from xx_om_bsa_stg
                      WHERE ITEM = l_item
                      and SOURCE_CONTRACT_NO=l_source_contract
                      group by SOURCE_CONTRACT_NO, CUSTOMER_NUMBER,item
                      having count(*) > 1)
                      order by item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))
                      ) T);
          IF l_flag <= 1 THEN
            RETURN ‘VALID’;
            ELSE
            RETURN ‘INVALID’;
          END IF;
        END;
END XX_BSA_LOAD_PKG;
/

  • September 26, 2018 | 20 views
  • Comments