Friday 5 January 2018

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;

4 comments:

12345 said...

Good Blog , Thanks for sharing this informative article.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Anonymous said...

Good blog, Thanks for sharing this informative article. It would be helpful to all if you write a full article.
Oracle Fusion HCM Online Training"

shaik shah said...

Goodblog, thanks for sharing this informative article. It would be helpful to all .your way of explaining in this good in this article.
Oracle Fusion HCM Online Training

Rainbow Training Institute said...

Thank you for sharing such a nice and really very helpful article

Oracle Fusion Financials Online Training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *