Oracle Interface to Upload Item Stock in Inventory

Oracle Interface to Upload Item Stock in Inventory


DECLARE
          l_num_inv_org_id      NUMBER;
          v_num_success_cntr    NUMBER  := 0;
          v_num_failure_cntr    NUMBER  := 0;
          v_num_ou_org          NUMBER  := fnd_profile.VALUE ('ORG_ID');
          v_num_imo_org         NUMBER;
          v_num_loop_cntr       NUMBER  := 0;
          v_chr_err_flag        VARCHAR2 (2) := 'N';
          v_chr_uom_code        mtl_system_items.primary_uom_code%TYPE;
          v_chr_err_code        mtl_transactions_interface.ERROR_CODE%TYPE;
          v_chr_err_expl        mtl_transactions_interface.error_explanation%TYPE;
          v_chr_sql_error       VARCHAR2 (1000);
          v_num_project_id      NUMBER;
          v_num_task_id         NUMBER;
          v_num_dist_acct       NUMBER;
          v_num_locator_cntrl   NUMBER;
          v_num_qty             NUMBER;
          v_num_item_id         NUMBER;
          v_num_loc_operation   VARCHAR2 (50);
          l_num_org_id          NUMBER := fnd_profile.VALUE ('ORG_ID');
          l_num_user_id         NUMBER := fnd_global.user_id;
          l_num_conc_log_id     NUMBER := fnd_global.conc_login_id;
          l_num_conc_req_id     NUMBER := fnd_global.conc_request_id;
          --Rev#1: Added
          v_trx_qty             NUMBER;
          v_serial_cntrl_yn     VARCHAR2 (10);
          v_lot_cntrl_yn        VARCHAR2 (10);
          --Rev#1: Modified to transaction_reference column
          CURSOR c_onhand_qty_temp
          IS
             SELECT    organization_id, item_segment1, subinventory_code,
                      source_code, source_line_id, source_header_id,
                      transaction_quantity, transaction_date,
                      transaction_type_id, transaction_uom,
                      account_combination,lot_number, error_code,
                      loc_segment1, loc_segment2, loc_segment3, loc_segment4,
                      loc_segment19, loc_segment20, process_flag, lock_flag
                 FROM xx_ONHAND_QTY_TEMP
                WHERE process_flag <> 2
                AND organization_id = i_org_id
                GROUP BY organization_id,item_segment1, subinventory_code,
                source_code, source_line_id, source_header_id,transaction_quantity, transaction_date,
                      transaction_type_id, transaction_uom,
                      account_combination,lot_number, error_code,
                      loc_segment1, loc_segment2, loc_segment3, loc_segment4,
                      loc_segment19, loc_segment20, process_flag, lock_flag
             ORDER BY organization_id,item_segment1,lot_number;
        
             CURSOR C_SERIAL_NUMBER(P_ORG_ID NUMBER,P_ITEM VARCHAR2,P_LOT VARCHAR2,P_SUBINV VARCHAR2) IS
     SELECT   ROWID, organization_id, item_segment1, subinventory_code,
                      source_code, source_line_id, source_header_id,
                      transaction_quantity, transaction_date,
                      transaction_type_id, transaction_uom, transaction_reference,
                      account_combination,lot_number, fm_serial_number, to_serial_number, error_code,
                      loc_segment1, loc_segment2, loc_segment3, loc_segment4,
                      loc_segment19, loc_segment20, process_flag, lock_flag
                 FROM xx_ONHAND_QTY_TEMP
                WHERE process_flag <> 2
                AND organization_id = P_ORG_ID
                AND item_segment1=P_ITEM
                AND lot_number=P_LOT
                AND subinventory_code=P_SUBINV
             ORDER BY fm_serial_number;
        
        
       BEGIN
          fnd_file.put_line
             (fnd_file.LOG,
              '----------------------------------------------------------------------'
             );
          fnd_file.put_line (fnd_file.LOG, 'On Hand Quantity Staging Log');
          fnd_file.put_line
             (fnd_file.LOG,
              '----------------------------------------------------------------------'
             );
          FOR rec_onhand IN c_onhand_qty_temp
          LOOP
             --Initializing Variables
             v_num_loop_cntr := v_num_loop_cntr + 1;
             v_chr_err_flag := 'N';
             v_chr_uom_code := '';
             v_num_project_id := NULL;
             v_num_task_id := NULL;
             v_num_dist_acct := NULL;
             v_num_locator_cntrl := 0;
             v_chr_err_code := '';
             v_chr_err_expl := '';
             v_num_qty := NULL;
             v_num_imo_org := NULL;
             v_trx_qty := 0;
             v_serial_cntrl_yn := NULL;
        
             fnd_file.put_line (fnd_file.LOG,
                                '==========================================='
                               );
             fnd_file.put_line (fnd_file.LOG,
                                   'Processing Item: '
                                || rec_onhand.item_segment1
                                || ' in Organization: '
                                || TO_CHAR (v_num_ou_org)
                               );
             fnd_file.put_line (fnd_file.LOG,
                                   'Transaction Qty: '
                                || TO_CHAR (rec_onhand.transaction_quantity)
                               );
             fnd_file.put_line (fnd_file.LOG,
                                'SubInventory   : '
                                || rec_onhand.subinventory_code
                               );
        
             -- Get IMO Organizatio ID and Locator Type
             BEGIN
                SELECT organization_id, locator_type
                  INTO v_num_imo_org, v_num_locator_cntrl
                  FROM mtl_secondary_inventories
                 WHERE secondary_inventory_name = rec_onhand.subinventory_code
                   AND organization_id = NVL (rec_onhand.organization_id, '0')
                   AND disable_date IS NULL;
                fnd_file.put_line (fnd_file.LOG,
                                      'SubInventory Locator Control '
                                   || 'Code = '
                                   || TO_CHAR (v_num_locator_cntrl)
                                  );
             EXCEPTION
                WHEN NO_DATA_FOUND
                THEN
                   fnd_file.put_line
                      (fnd_file.LOG,
                       'Organisation and SubInventory Locator Control Does not exist in the Organization'
                      );
                   v_chr_err_code := 'GET_ORG_ID_ERROR';
                   v_chr_err_expl := v_chr_err_expl || '/Org Id, sub inv loc error';
                   v_chr_err_flag := 'Y';
                WHEN OTHERS
                THEN
                   fnd_file.put_line
                        (fnd_file.LOG,
                         'General Error while validating Org Id and SubInventory'
                        );
                   fnd_file.put_line (fnd_file.LOG,
                                      'Error code       : ' || SQLCODE
                                     );
                   fnd_file.put_line (fnd_file.LOG,
                                      'Error message    : ' || SQLERRM
                                     );
                   v_chr_err_flag := 'Y';
              
             END;
            -- Get CC id using Account Combination
                BEGIN
                   SELECT gcc.code_combination_id
                     INTO v_num_dist_acct
                     FROM gl_code_combinations_kfv gcc
                    WHERE gcc.concatenated_segments =
                                                    rec_onhand.account_combination;
                EXCEPTION
                   WHEN NO_DATA_FOUND
                   THEN
                      v_chr_err_code := 'GET_DIST_ACCT_ERROR';
                      v_chr_err_expl :=
                      v_chr_err_expl || '/Distribution Account Id error';
                      v_chr_err_flag := 'Y';
                   WHEN OTHERS
                   THEN
                      fnd_file.put_line
                            (fnd_file.LOG,
                             'General Error while validating account_combination'
                            );
                      fnd_file.put_line (fnd_file.LOG,
                                         'Error code       : ' || SQLCODE
                                        );
                      fnd_file.put_line (fnd_file.LOG,
                                         'Error message    : ' || SQLERRM
                                        );
                      v_chr_err_flag := 'Y';
                 
                END;

          --Get Inventory Item id and Primary UOM code.
             BEGIN
                SELECT inventory_item_id, primary_uom_code
                  INTO v_num_item_id, v_chr_uom_code
                  FROM mtl_system_items
                 WHERE segment1 = rec_onhand.item_segment1
                   AND organization_id = rec_onhand.organization_id;
                fnd_file.put_line (fnd_file.LOG,
                                   'Inventory Item ID: ' || v_num_item_id
                                  );
             EXCEPTION
                WHEN NO_DATA_FOUND
                THEN
                   fnd_file.put_line (fnd_file.LOG,
                                      'Inventory Item id or UOM is not found'
                                     );
                   v_chr_err_code := 'GET_INV_UOM_ID_ERROR';
                   v_chr_err_expl := v_chr_err_expl || '/Inv ID, UOM error';
                   v_chr_err_flag := 'Y';
                WHEN OTHERS
                THEN
                   fnd_file.put_line (fnd_file.LOG,
                                      'General Error while validating item code'
                                     );
                   fnd_file.put_line (fnd_file.LOG,
                                      'Error code       : ' || SQLCODE
                                     );
                   fnd_file.put_line (fnd_file.LOG,
                                      'Error message    : ' || SQLERRM
                                     );
                   v_chr_err_flag := 'Y';
             END;
        
             --Rev#1: Added to check if trx qty is negative for Misc issues
             --Rev#1: 32 is the transaction_type_id for Misc.issue transaction
             --Rev#1: 42 is the transaction_type_id for Misc.Receipt transaction
             --Rev#2: Modified the code for handling Misc. Receipts
             IF (rec_onhand.transaction_type_id = 32) THEN
              IF (rec_onhand.transaction_quantity <= 0) THEN
               v_trx_qty := rec_onhand.transaction_quantity;
              ELSE
               v_trx_qty := (rec_onhand.transaction_quantity * -1);
              END IF;
             ELSE
              v_trx_qty := rec_onhand.transaction_quantity;
             END IF;
        
             --Rev#1: Added to check if the item is serialized or not
             BEGIN
              SELECT decode(serial_number_control_code,5,'Y','N'),decode(LOT_CONTROL_CODE,2,'Y','N')
                INTO v_serial_cntrl_yn,v_lot_cntrl_yn
                FROM mtl_system_items
               WHERE inventory_item_id = v_num_item_id
                 AND organization_id = rec_onhand.organization_id;
        
                  fnd_file.put_line(fnd_file.LOG, 'Item is lot enabled (Y/N)  = '||v_lot_cntrl_yn);
                fnd_file.put_line(fnd_file.LOG, 'Item is Serialized (Y/N)  = '||v_serial_cntrl_yn);
        
             EXCEPTION
              WHEN OTHERS THEN
                fnd_file.put_line (fnd_file.LOG, 'Error in checking serialized attribute');
                fnd_file.put_line (fnd_file.LOG, 'Error message    : ' || SUBSTR(SQLERRM, 1, 100));
                v_chr_err_flag := 'Y';
             END;
        
              IF v_lot_cntrl_yn = 'Y' THEN
               IF rec_onhand.lot_number is null then
              v_chr_err_flag := 'Y';
                v_chr_err_code := 'MISSING_LOT_NO';
                v_chr_err_expl := v_chr_err_expl ||'Item is LOT Controlled But missing Lot Number';
                end if;
                else
                 IF rec_onhand.lot_number is not null then
                  v_chr_err_flag := 'Y';
                v_chr_err_code := 'UNWANTED_LOT_NO';
                v_chr_err_expl := v_chr_err_expl ||'Item is not LOT Controlled Lot Number should be null';
                end if;
                end if;
           
     
          --Insert records with error flag 'N'
          --Rev#1: Modified the query to include the transaction_reference column
          --Rev#1: Modified the query to insert source_code into transaction_source_name
             IF v_chr_err_flag = 'N'
             THEN
                BEGIN
                   INSERT INTO mtl_transactions_interface
                               (transaction_interface_id,
                                organization_id,
                                inventory_item_id,
                                item_segment1,
                                subinventory_code,
                                source_code,
                                transaction_source_name,
                                source_line_id,
                                source_header_id,
                                transaction_mode,
                                transaction_quantity,
                                transaction_date,
                                transaction_type_id,
                                transaction_uom,
                                transaction_reference,
                                distribution_account_id,
                                loc_segment1,
                                loc_segment2,
                                loc_segment3,
                                loc_segment4,
                                loc_segment19,
                                loc_segment20,
                                created_by,
                                creation_date,
                                last_updated_by,
                                last_update_date,
                                process_flag,
                                lock_flag
                               )
                        VALUES (mtl_material_transactions_s.NEXTVAL,
                                rec_onhand.organization_id,
                                v_num_item_id,
                                rec_onhand.item_segment1,
                                rec_onhand.subinventory_code,
                                rec_onhand.source_code,
                                --Rev#1: Added to pass the source code to transaction_source_name
                                rec_onhand.source_code,
                                rec_onhand.source_line_id,
                                rec_onhand.source_header_id,
                                3,
                                --Rev#1: Modified to change the sign of trx qty
                                v_trx_qty,
                                rec_onhand.transaction_date,
                                42,--rec_onhand.transaction_type_id,
                                rec_onhand.transaction_uom,
                                --Rev#1: Added to pass the transaction reference
                               NULL,
                                v_num_dist_acct,
                                rec_onhand.loc_segment1,
                                rec_onhand.loc_segment2,
                                rec_onhand.loc_segment3,
                                rec_onhand.loc_segment4,
                                v_num_project_id,
                                v_num_task_id,
                                l_num_user_id,
                                SYSDATE,
                                l_num_user_id,
                                SYSDATE,
                                1,
                                2
                               );
                          
                EXCEPTION
                   WHEN OTHERS
                   THEN
                      v_chr_sql_error := SUBSTR (SQLERRM, 1, 1000);
                      fnd_file.put_line
                                (fnd_file.LOG,
                                    'Error in inserting into MTL TRX INTERFACE Table: '
                                 || v_chr_sql_error
                                );
                     v_chr_err_flag := 'Y';
                     v_chr_err_code:=v_chr_err_code||'Error in inserting into MTL TRX INTERFACE Table: ';
                END;
           
                 IF (v_lot_cntrl_yn = 'Y' AND rec_onhand.lot_number IS NOT NULL)
                THEN
                   BEGIN
              
    INSERT INTO mtl_transaction_lots_interface
                (transaction_interface_id,
                 serial_transaction_temp_id, last_update_date, last_updated_by,
                 creation_date, created_by, last_update_login, lot_number,
                 transaction_quantity, primary_quantity
                )
         VALUES (mtl_material_transactions_s.CURRVAL,
                 mtl_system_items_intf_sets_s.NEXTVAL, SYSDATE, l_num_user_id,
                 SYSDATE, l_num_user_id, l_num_user_id, rec_onhand.lot_number,
                 v_trx_qty, v_trx_qty
                );
                end ;
                end if;
           
                FOR V_SERIAL_NUMBER IN C_SERIAL_NUMBER(rec_onhand.organization_id,rec_onhand.item_segment1,rec_onhand.lot_number,rec_onhand.subinventory_code) LOOP

             IF v_serial_cntrl_yn = 'Y' THEN
              IF V_SERIAL_NUMBER.fm_serial_number IS NULL OR V_SERIAL_NUMBER.to_serial_number IS NULL THEN
                --fnd_file.put_line (fnd_file.LOG, 'Item is Serialized but missing FROM or TO serial Nos');
                v_chr_err_flag := 'Y';
                v_chr_err_code := 'MISSING_FROM_TO_SERIAL_NOS';
                v_chr_err_expl := v_chr_err_expl ||'Item is Serialized but missing FROM or TO serial Nos';
              END IF;
             ELSE
              IF V_SERIAL_NUMBER.fm_serial_number IS NOT NULL or V_SERIAL_NUMBER.to_serial_number IS NOT NULL THEN
                --fnd_file.put_line (fnd_file.LOG, 'Item is not Serialized, FROM or TO serial nos should be blank');
                v_chr_err_flag := 'Y';
                v_chr_err_code := 'UNWANTED_FROM_TO_SERIAL_NOS';
                v_chr_err_expl := v_chr_err_expl ||'Item is not Serialized FROM or TO serial nos should be blank';
              END IF;
             END IF;
            
          IF v_chr_err_flag = 'N' THEN
           
                IF (v_serial_cntrl_yn = 'Y' AND V_SERIAL_NUMBER.fm_serial_number IS NOT NULL AND V_SERIAL_NUMBER.to_serial_number IS NOT NULL)
                THEN
                   BEGIN
                      INSERT INTO mtl_serial_numbers_interface
                                  (transaction_interface_id,
                                   source_code,
                                   source_line_id,
                                   fm_serial_number,
                                   to_serial_number,
                                   creation_date,
                                   last_update_date,
                                   created_by,
                                   last_updated_by,
                                   process_flag
                                  )
                           VALUES (DECODE (v_lot_cntrl_yn,'Y',mtl_system_items_intf_sets_s.CURRVAL,mtl_material_transactions_s.CURRVAL),
                                   rec_onhand.source_code,
                                   rec_onhand.source_line_id,
                                   V_SERIAL_NUMBER.fm_serial_number,
                                   V_SERIAL_NUMBER.fm_serial_number,
                                   SYSDATE,
                                   SYSDATE,
                                   l_num_user_id,
                                   l_num_user_id,
                                   1
                                  );
                   EXCEPTION
                      WHEN OTHERS
                      THEN
                         v_chr_sql_error := SUBSTR (SQLERRM, 1, 1000);
                         fnd_file.put_line
                            (fnd_file.LOG,
                                'Error in inserting into MTL SERIAL NUMBERS INTERFACE Table: '
                             || v_chr_sql_error
                            );
                   END;
                END IF;
                END IF;
           
                END LOOP;      
           
             END IF;
             IF v_chr_err_flag = 'Y'
             THEN
                fnd_file.put_line (fnd_file.LOG, 'Error in Processing Transaction');
                fnd_file.put_line (fnd_file.LOG, v_chr_err_expl);
           
                v_num_failure_cntr := v_num_failure_cntr + 1;
                BEGIN
                   UPDATE xx_ONHAND_QTY_TEMP
                      SET process_flag = 3,
                          ERROR_CODE = v_chr_err_code,
                          error_explanation = v_chr_err_expl,
                          last_updated_by = l_num_user_id,
                          last_update_date = SYSDATE,
                          last_update_login = l_num_conc_log_id,
                          conc_request_id = l_num_conc_req_id
                    WHERE  organization_id =rec_onhand.organization_id
                AND item_segment1=rec_onhand.item_segment1
                AND lot_number=rec_onhand.lot_number
                AND subinventory_code=rec_onhand.subinventory_code;
                EXCEPTION
                   WHEN OTHERS
                   THEN
                      v_chr_sql_error := SUBSTR (SQLERRM, 1, 1000);
                      fnd_file.put_line
                                      (fnd_file.LOG,
                                          'Error in inserting into On-Hand Stg Table: '
                                       || v_chr_sql_error
                                      );
                END;
             ELSE
                fnd_file.put_line (fnd_file.LOG, 'Transaction successfully Processed');
                v_num_success_cntr := v_num_success_cntr + 1;
                BEGIN
                   UPDATE xx_ONHAND_QTY_TEMP
                      SET process_flag = 2,
                          last_updated_by = l_num_user_id,
                          last_update_date = SYSDATE,
                          last_update_login = l_num_conc_log_id,
                          conc_request_id = l_num_conc_req_id
                    WHERE  organization_id =rec_onhand.organization_id
                AND item_segment1=rec_onhand.item_segment1
                AND lot_number=rec_onhand.lot_number
                AND subinventory_code=rec_onhand.subinventory_code;
                EXCEPTION
                   WHEN OTHERS
                   THEN
                      v_chr_sql_error := SUBSTR (SQLERRM, 1, 1000);
                      fnd_file.put_line
                                      (fnd_file.LOG,
                                          'Error in inserting into On-Hand Stg Table: '
                                       || v_chr_sql_error
                                      );
                END;
             END IF;
        
             COMMIT;
        
          END LOOP;
          fnd_file.put_line (fnd_file.LOG,
                             '--------------------------------------------------'
                            );
          fnd_file.put_line (fnd_file.LOG,
                             '--------------------------------------------------'
                            );
          fnd_file.put_line (fnd_file.LOG,
                                'Total Number of Records Processed: '
                             || TO_CHAR (v_num_loop_cntr)
                            );
          fnd_file.put_line (fnd_file.LOG,
                                'Total Number of Records Successfully Processed: '
                             || TO_CHAR (v_num_success_cntr)
                            );
        fnd_file.put_line (fnd_file.LOG,
                                'Total Number of Records Failed Process: '
                             || TO_CHAR (v_num_failure_cntr)
                            );  
                       
                       
       IF i_delete_pro_flag = 'Y'
       THEN
       BEGIN
      DELETE FROM xx_ONHAND_QTY_TEMP
      WHERE process_flag = 2;
 
      EXCEPTION
        WHEN OTHERS
        THEN
          v_chr_sql_error := SUBSTR (SQLERRM, 1, 1000);
          fnd_file.put_line
            (fnd_file.LOG,
               'Error in deleting processed records from custom interface table: '
             || v_chr_sql_error
            );
      END;
      END IF;
 
       END;